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.
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.
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!
- Sample Excel file: FillDown