Dear Analyst #28: Filling a formula down to the last row of your data set

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:

You’re still using your mouse or trackpad

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:

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)

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.

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)

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

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:

I really enjoy using my KeyCuts keyboard cover! In addition to providing helpful Excel shortcuts for my Mac, it protects my keys from wear and tear that typically happens when I carry my laptop from place to place. I get so many compliments on my keyboard cover that I’ve started gifting them to clients!

It’s often said there are no shortcuts in life. Sure, maybe this is true. But with KeyCuts I’ve learned how to manipulate formats and basic functions in Excel such as autosum, sorting, and paste special without using my mouse and cutting down the time I spend in spreadsheets.

I used Excel on a PC extensively at my previous job, but became a Mac user for the first time when I went to business school. KeyCuts has been super helpful in teaching me how to use the shortcuts I had become reliant on in Excel for Mac. Truly a great product!

I’m in a position where I must frequently use both Macs and PCs. Prior to KeyCuts, if I had to do anything with spreadsheets, I would just have to dig up a PC and turn it on. Now that I have KeyCuts, my productivity is worlds better, and I feel like I’m finally able to get my money’s worth out of the Mac.

KeyCuts has helped expedite my work because the shortcuts are ready at my fingertips. It’s also helped me transition from a PC to a Mac. The cover is thin and tactile and protects my computer. Love the neon pink — it adds flash and gets compliments.

MISSION KeyCuts makes you faster and more productive on the job with Excel keyboard shortcuts. Through keyboard cover products, classes, and custom consulting, KeyCuts will help you become the Excel guru you've always wanted to be.