The most valuable trick I know in Excel: a faster way to copy formulas
Using a sequence of keyboard shortcuts to copy a formula across multiple rows/columns in Excel- instead of a mouse- is the greatest time saver I have.
My most common use-case is when I have a formula at the top right of my data that I need to paste it downwards, filling all cells in the right-most column. While this can be done with the mouse, I find it faster to use keyboard shortcuts.
In this example, I will assume the formula in Cell C2 needs to be copied down to through Cell C7:
The first step is to put in the formula in the top-right most cell (here, Cell C2).
When you’re done, hit Enter. The formula will save and display the desired output. The cursor will automatically go to the cell immediately below it (here, Cell C3).
Normally, most users would re-select the Cell with the formula (Cell C2) and then use the mouse to drag or double-click the bottom-right corner of the cell to have it copied down:
This obviously works, but my sequence of keyboard shortcuts can save you a little time. And if you need to do this often, those seconds can add up!
First: basic Excel keyboard shortcuts
Note: I’m using a Windows computer. This works in multiple versions of Excel.
The first shortcut to mention is that to get to the top-left of your worksheet (Cell A1), you can press the “Ctrl” key and the “Home” key at the same time.
The next thing to know: in order to get to the end of a row/column, as defined by the last cell that has data in it:
- Press and then release the “End” key
- Immediately press then release the arrow key in the direction you want to go in
For example: to get from the top of your data set to the bottom:
- Press and release the “End” key
- Press and release the “Down” arrow key
Notice we are not pressing the two keys at the same time (as we did with the Ctrl + Home keys going to Cell A1)
We can do more with this keyboard shortcut
To highlight all of the cells from the starting point to the end, press and hold the “Shift” key:
- Select Cell A1
- Press and hold “Shift”
- Press and release “End” (still while holding “Shift”)
- Press and release the “Down” arrow key
- Release the “Shift” key
The last baby step
Go to the blank cell in the last row that you want the formula to be in (here, Cell C7). To get to the first cell above it that has data:
- Press and release “End”
- Press and release the “Up” arrow key
You’re probably already familiar with the keyboard shortcut to copy (Ctrl + C) and paste (Ctrl + V), but now we can combine that with these navigation shortcuts!
The actual trick
- Select the cell that you want to copy the formula down from (the example below is using Cell C2)
- Copy the formula (Ctrl + C)
- Press the “Left” arrow key (so you are now in Cell B2)
- Go to the last row of data in this column by pressing and releasing “End” key and then the “Down” arrow (now you’re in Cell B7)
- Press the “Right” arrow key so you are now in the last row of the column that you want the formula to be in (here, Cell C7)
- Paste the formula (Ctrl + V)
- Press and hold “Shift” — then press and then release the “End” key
- While still holding Shift, press and release the “Up” arrow key
- Let go of the “Shift” key
- Paste the formula (Ctrl + V) in all of the cells highlighted
That’s the trick!
Out of habit, I tend to hit the “Esc” key to stop the animating dashed lines around the original cell to let Excel know that I don’t plan to copy that formula again.
With practice, you can do this sequence of keyboard shortcuts much faster then you can move your hand to your mouse to obtain the same outcome.
One small “gotcha”
Pressing “End” and then an arrow key will bring you to the last cell that has data. Sometimes the data that I work with has a blank value in the cell immediately to the left of where the formula goes.
In the example below, once you’re in Column C and you press End and then Down, you’ll stop mid way through the data set because there’s a blank value in Cell C4.
In the data I work with, in most cases, the data in Column A is always present. What I often do is:
- Copy the formula in the first row of the desired data
- Remember what Column I’m in
- Use the keyboard shortcut for going to Cell A1 (Ctrl + Home)
- Press “End” then press “Down” to get to the last row (assuming all rows in Column A have a value)
- Use the Right arrow key to navigate back to the desired column
- Continue on with the keyboard shortcut
You just need to be mindful of your data!
Speaking of which, I found that the keyboard shortcut of going to the bottom-rightmost cell (Ctrl + End) is not always reliable: if you are working with a dataset that originally had extra rows/columns that you deleted, using this keyboard shortcut will bring you “further out” than you want to be.
If you liked this article, you may find my tutorial for doing an “auto-fit” of your data in Excel helpful.
