This question already has an answer here:. 7 answers Does anybody know the keyboard shortcuts or menu commands to fill a formula down without dragging?
I'm using Excel for Mac 2011 on a MacBook. I've got a spreadsheet like this pattern but for like 5000 rows: A, B, C, D 5, 1, 3 =SUM(A1:C1) 3, 2, 4. I know how to hover over D1 and get the handle and drag down, but there must be an easier way. There are a billion tips online but I'm not finding any that work.
How to quickly apply formula to an entire column or row with/without dragging in Excel? Sometimes you may need to apply one same formula to an entire column or row in Excel, such as C1=A1*2, C2=A2*2,, Cn=An*2.It will be quite tedious if you enter the formula in each cell one by one.
FWIW, Macbook has some exceptions to shortcut key rules, so that's probably why, I guess. But anyhow, anyone know how to do this? PS: I would have thought 'fill series' would be a choice but no. Here's a picture.
Excel expert Dennis Taylor helps Excel for Mac 2016 users take their spreadsheet skills to the next level with this collection of tips and tricks. He begins with his top 10 productivity boosters, and then highlights navigation, display, and selection techniques to keep you moving quickly. The course then dives into data entry and editing techniques, formatting and drag-and-drop tricks, keyboard shortcuts for working with formulas, data management strategies, and chart tricks. Short on time?
Make sure to check out the 'Ten Tiny Tips' chapter for a quick productivity boost. Instructor. Excel expert Dennis Taylor has 25+ years experience in spreadsheet authoring and training. Dennis Taylor has over 25 years of experience working with spreadsheet programs as an author, speaker, seminar leader, and facilitator. Since the mid-90s, he has been the author/presenter of numerous Excel video and online courses and has traveled throughout the US and Canada presenting over 300 seminars and classes.
He has authored or co-authored multiple books on spreadsheet software and has presented over 500 Excel webinars to a diversity of audiences. Dennis has worked with hundreds of different corporations and governmental agencies as well as colleges and universities. He lives in Boulder, Colorado. By: Curt Frye course. 2h 3m 10s. 46,210 viewers. Course Transcript - Voiceover We're looking at a worksheet called CopyDataFormuulas.
This is a worksheet that has about 700 rows or so. If you've never seen a worksheet before or maybe you haven't seen this in a long time, click on a cell in a column where you know there's data in it, and double click the bottom edge of a cell. It'll take you down to the bottom.
If there was an empty cell in there, it would've stopped at that point. This goes down to row 742. I'll just double click the top edge of this cell, we're back up top. I'm about a write a formula here in column I.
One of Excel's best shortcuts is the ability to quickly copy a formula down a column. There's no corresponding shortcut for copying across a row. It's more likely to have the need to copy a formula down a long column than across a wide row. So I'm going to write a formula in cell I2 that calculates a new compensation amount. Everybody in this list is going to get 2.73% increase, so certainly one way to write the formula is equal H2 times this percentage amount in J1. That needs to be an absolute reference because I want to copy this down the column. I'll press the function key F4 to make that an absolute address.
Then after calculating the amount of the increase I'll add it to the original compensation amount. Instead of pressing return here, I'm going to press control+return. The advantage is if I press return, the active cell will move down. If I press control+return, it stays in place. Now rather than using copy-paste techniques, or dragging from the fill handle in the lower right hand corner. By the way, that's not a bad tip if it's just five or six or 10 cells whatever, we can certainly drag from the corner, but we've got 700 rows here.
What if this were thousands of rows? This would be pun, a real drag to drag downward. I'm simply going to double click the lower right hand corner and the data gets copied downward. I think the question would enter your mind, how far did it get copied?
A good follow up to this and not so well known, control+period moves the active cell to the bottom of the list. Now if you weren't sure that 742 was the last cell in the list, just scroll a little bit more to confirm that, and you'd see immediately. Control+period will take us back up top. We can also copy just raw data. We can also copy data that happens to be within Excel's custom lists. For example, I put in these numbers here.
I haven't done the months yet, but they're gonna be January through December here. So I'm gonna type Jan.
Once again not pressing return, but control+return. Now I'll double click the lower right hand corner, and we see what happens here. That's one of Excel's built-in custom lists. Abbreviations for days of the week also fall into that category as well as full spellings for the months and days of the week.
If it's just raw data, maybe for the time being I want to put in 5% for all these here. I'll just type in 5%, once again control+return, double click the lower right hand corner. For the moment they're all gonna be 5%. We'll end up changing some of those a bit later perhaps. But you can copy, as we saw here, text, you can copy items from a custom list, and you can copy formulas down a column simply by double clicking that fill handle in the lower right hand corner.
Excel essentially figures out your adjacent data and knows when to stop. So that's a built-in feature that makes working with Excel particularly handy in these kinds of situations. Remember there's no corresponding shortcut for quickly copying formulas or data across a row the way there is down a column.
Practice while you learn with exercise files. Watch this course anytime, anywhere. Course Contents.
Introduction Introduction. 1. Top Ten Shortcuts 1.
Top Ten Shortcuts. 2. Display and Navigation Shortcuts 2. Display and Navigation Shortcuts.
3. Selection Shortcuts 3. Selection Shortcuts. 4. Data Entry and Editing Shortcuts 4.
Data Entry and Editing Shortcuts. 5. Formatting Shortcuts 5. Formatting Shortcuts.
Data Management Techniques. 10. Tips for Using Charts and Visual Objects 10. Tips for Using Charts and Visual Objects. 11. Ten Tiny Tips 11. Ten Tiny Tips.