I’ve often had customers watch me work in an Excel sheet and ask about various shortcuts they see me use. So, this is the first in a series of occasional blogs on Excel tips and tricks I hope you find useful.
If you’re not familiar with the “fill down” function, this is a great way to repeat a formula down a column. For example, let’s say that you have a row of unit types with the number of expiring leases and the number of renewals and you want to calculate the renewal rate as the number of renewals divided by the number expirations. In the example below, you can see that the studio rate is 58.3%.
To repeat this, you can re-type the formula three more times or you could copy and paste the formula three times, but both of these are pretty tedious; they would be prohibitively time consuming if you were working with a list many rows long.
Many of you may be familiar with a shortcut whereby you first select the range of cells you want to have use the same formula and then use the keyboard shortcut CTRL-D to fill down:
That’s great but still a bit of a pain on a very long list. Here’s my favorite shortcut…and on long lists almost looks like magic.
Notice the little box at the bottom right of the cell selected? I’ve highlighted it with a red circle to make it more obvious:
Simply double-click on that box and VOILA!...the formula fills all the way down.
Please note that, for this to work the way you want, there can be no blank cells in column C for any rows you want filled. This shortcut fills down until it finds a blank cell in the column to its left. Any blank cell in that column will end the “fill down” even if there is content just a single row lower.
There you go…simple as that you have one of the bigger time savers in the Excel shortcut arsenal!