Dear Analyst #28: 4 methods to fill a formula down to the last row of data you didn’t know about
Podcast: Play in new window | Download
Subscribe: Google Podcasts | Spotify | Stitcher | 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:
- 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)
CMD+DOWN ARROWon the Mac or
CTRL+DOWN ARROWon 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)
CTRL+SHIFT+UP ARROWto 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).
CMD+Don the Mac or
CTRL+Don 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 30,000+ students who are taking my introduction to Excel class on Skillshare. Learn the basics of Excel in 1 hour!
Method 4 – Awesome
Any idea on how can this be made to work if I wanted to create a macro. Using your example, potentially every passenger column maybe it’s a different number and length in every spreadsheet ? In your case it’s 281, what if the next one is 301 ? I tried creating macro but it would only calculate to 281
It’s definitely doable. Take a look at this post where I wrote a macro for Excel and Google Apps Script for Google Sheets and edit the code to fit your use case.