Podcast: Play in new window | Download

Subscribe: Spotify | TuneIn | RSS

This spreadsheet tip is based on a question I get asked all the time when I teach (well taught) Excel at in-person classes: How do I fill a formula down to the last row of my data set without over-shooting the last row with keyboard shortcuts? This problem occurs with larger data sets where you have several hundred or thousands of rows and need to quickly apply a formula in a column for all these rows. This screenshot show the problem:

As the text on the screenshot shows, the Revenue per passenger formula needs to be applied to all rows in the data set, but we don’t know where the data set ends. It could be row 100, 500, or 800,000. If you want to try this exercise for yourself, see this Google Sheet and make a copy for yourself.

Method 1: Double-click the bottom-right of the cell

This is how most people approach this problem, but the downside is that it requires you to use your mouse or trackpad. You basically hover your cursor over the bottom-right corner of the cell that contains the formula (in this case E2) and wait until your cursor turn into a black plus sign. Then you double-click and the formula for Revenue per passenger fills down to the last row in the data set (in this case row 281):

Method 2: Drag-and-drop the formula until it reaches the last row of data set

Even less ideal, you can drag the bottom right corner of the cell down and basically wait until the window scrolls to the last row of the data set. The downsides of this method:

1. You’re still using your mouse or trackpad
2. You might under-shoot or over-shoot the last row of your data set because the scroll depends on how far down you are holding down your mouse

Method 3: Press page down while having the first cell selected

This method uses keyboard shortcuts so definitely more ideal compared to methods 1 and 2. You keep the first cell with the formula selected by holding down SHIFT and then press PAGE DOWN a few times until you get close to the bottom of your data set. The downside is that you might overshoot your data set which means you have to keep SHIFT pressed while pressing UP ARROW a few times to get the select to “stop” right on the last row (row 281) in the Google Sheet. You can then press CMD+D on the Mac or CTRL+D on the PC to fill the formula down:

Some people ask me about using CMD+DOWN ARROW at this point to get to the bottom of the column (column E in this case) but the problem is that since all of column E is pretty much empty (rows 2 and below), you will simply go to the last row of the spreadsheet. You are over-shooting the last for of your data set by a lot in this scenario.

Method 4 (most ideal): Go to the bottom of the data set in the column to the left and then use the fill formula down shortcut

This method involves using only keyboard shortcuts and hence the most ideal. These are the steps:

1. Move your cursor to the column to the left of your column that contains the formula you want to fill (in this case column D)
2. Press CMD+DOWN ARROW on the Mac or CTRL+DOWN ARROW on the PC and you’ll most likely go to the last row of the data set (column D in this case) since the data should be contiguous.
3. Move your cursor to the right which puts you in the last row of your data set but also in the column that contains the formula you want to fill down (cell E281 in this data set)
4. Press CTRL+SHIFT+UP ARROW to select all the empty cells including the first cell that contains your formula above your current empty cell (in this data set, you’ll have the E2:E281 range selected).
5. Press CMD+D on the Mac or CTRL+D on the PC to fill the formula down

The reason this method works is because of step 4 where you’re able to select all the empty cells above your empty cell while the first cell of the selection contains the formula you want to fill down. While this is technically a workaround, I’ve found this is the easiest way to get the range selection properly set up before applying the fill formula down shortcut:

Other Podcasts & Blog Posts

In the 2nd half of the episode, I talk about some episodes and blogs from other people I found interesting:

• Animalz Podcast episode #36: Why Community Is the Most Important Trend in Marketing
• SyntaxFM episode # 219: Hasty Treat – Non-Glamorous Skills You Should Have

Want to learn how to use Microsoft Excel?

Join 40,000+ students who are taking my introduction to Excel class on Skillshare. Learn the basics of Excel in 1 hour!