Fill Down through Blank Rows
Are you familiar with the double-click shortcut to fill formulas down? If so, have you noticed it stops filling down at the first blank row? This post will discuss the double-click shortcut as well as a simple workaround for how to fill it down through a report range even when there are blank rows in the middle.
Objective
Before we get started, let me quickly cover the double-click shortcut. When you have a formula in a cell, and want to fill the formula down, there are several ways to do this. For example, you could copy and paste, you could also interactively click-and-drag the fill handle. The fill handle is that little square in the lower right corner of the selected formula cell.
But, in addition to these two techniques, you can also double-click the fill handle. Excel will push the formula down as far as it thinks it should. That is, as long as the cells in the adjacent column are populated. When Excel encounters a blank cell in the adjacent column, Excel stops filling the formula down. In many cases, this is exactly what we want. This is illustrated below.
But, sometimes, we have blank rows in the worksheet and would like Excel to fill down through the blank rows instead of stopping. This is illustrated below.
The simple workaround is to filter the worksheet first.
Filter
Basically, here are the steps involved:
- Turn on filters
- Apply a filter to temporarily hide blank rows
- Double-click to fill the formula down
- Turn off filters
Let’s briefly walk through these steps.
First, we turn on filters by selecting the entire range, and then using the Data > Filter command icon.
Note: it is important to first select the entire range. If you skip this step, Excel may not filter the entire region as desired.
Next, we apply a filter to hide blank rows by unchecking the (Blanks) filter option.
Now we use the double-click shortcut to fill the formula down.
Finally, we turn off the filters by once again clicking the Data > Filter command icon.
These steps are illustrated below.
If you’d like to give this a try, feel free to download the sample workbook.
If you have any other fun tricks for filling formulas, please share by posting a comment below…thanks!
Resources
- Sample Excel file: FillDown
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Our training programs start at $29 and will help you learn Excel quickly.
Very nice post, Jeff, and additional kudos for pointing out the need to highlight the entire data set before turning on the filter because of the blank rows. Here’s another, keyboard-only, workaround that I like for this situation:
(1) Navigate to the last populated cell (G20 in your example) by hitting Ctrl+End;
(2) Click right one cell to get to the last cell in the column you want to fill with the formula (H20 in your example);
(3) Use Ctrl+Shift+Up to select the entire stack of blank cells and the cell containing the formula (H7);
(4) Hit Ctrl+D to copy the formula in cell H7 to the rest of the selected cells.
Ben,
Hey…that is a great technique! Thanks for posting that tip!
Thanks
Jeff
Hey Ben,
Thanks for sharing that tip as I didn’t know Ctrl+D would work with formulas. I only ever used it to copy cells with hard coded numbers or text.
Thanks,
Michael
When I tried this using your workbook, it didn’t work. It only filled down 3 rows even though there were no blank rows showing. Any idea what I might be doing wrong? I highlighted the entire range, turned on the filters, unchecked the Blanks box, and then double clicked on the fill handle just as you instructed. Is there a setting that might be keeping mine from working?
Meredith,
That could happen if you have three formula cells instead of one and you double-click the first formula cell. That is, if you filled the formula down first and it went down three cells, and then tried to fill it down the rest of the way after applying a filter by double-clicking the first formula cell. So, what you can do is either double-click on the last formula cell in the column or delete the excess formulas so there is only one formula cell. Either way should work…thanks!
Thanks
Jeff
Jeff, Thanks for the post! This will save me a lot of time since I usually just did copy/paste in all the cells that weren’t blank. On a big spreadsheet, that took a while!
Excellent…glad to help!
Thanks,
Jeff
Thanks for the post, Jeff – very efficient.
🙂
I love it! It’s amazing how often there is a simple workaround to a vexing problem.
Some of my favorite keyboard shortcuts are useful here…
If you don’t want to use the mouse to select the range, press Ctrl + Home to get to the beginning of the active range, then press Ctrl + Shift + End to highlight everything.
Once the range is selected, press Ctrl + Shift + L to turn the Autofilter on. This shortcut toggles the Autofilter, so once you are done with the filter, you can reapply the same keyboard combination to turn the Autofilter off (very handy… no navigation of menus is required!).
Finally, a filter can be applied by navigating to one of the header cells and pressing Alt + Down to view the dropdown menu… it’s so much better than trying to click that little arrow using an unweildy mouse 🙂
Control return enters the same formula into all selected cells at once. Because the formula contains a relative reference, Excel will update the address at each location. In effect, we’re telling Excel to use “the cell above” to fill in all blank cells. This creates a chain of formulas that all “look up” to retrieve values.
So great. Thank you for sharing!!
Thanks for the tip!