The most valuable trick I know in Excel: a faster way to copy formulas

Image for post
Image for post
Screen recording above is in slow motion to demonstrate the concept

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:

Image for post
Image for post

The first step is to put in the formula in the top-right most cell (here, Cell C2).

Image for post
Image for post

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).

Image for post
Image for post

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:

Image for post
Image for post

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
Image for post
Image for post

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
Image for post
Image for post

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
Image for post
Image for post

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
Image for post
Image for post
I slowed this down for you to follow along. It could potentially be done MUCH faster!

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.

Image for post
Image for post

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.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store