Fill Blank Cells

Question: I’ve selected all blank cells in a column, I would now like to enter a formula in all of the cells at the same time. Is this possible?

 

Answer: Yes, with a  keyboard shortcut.

 

Process (Excel 2003, 2007, 2010):

 

In last week’s tip (http://www.alchemex.com/Resources/EOS%20Previous%20Tips%20And%20Tricks/Go%20To%20Blanks.pdf), we looked at selecting all cells in a column using the ‘Go To Special…’ feature. Now, we’re going to enter in a formula to all of the selected cells in one stroke. Well, two keystrokes to be precise.

 

 

In this example, some Unit Price fields are empty, however we do have the Quantity and the Product Sales values so we can work out the Unit Price through simple division. All blank cells have been selected – you can confirm this through the row numbers being highlighted. The current live cell is E3, which can be confirmed by looking at the Name Box in the upper left hand corner.

 

NB: all blank cells are currently selected. If you move to another cell, either by arrow keys, pressing Enter or selecting another cell with the mouse, all blank cells will be de-selected.

 

1. Without selecting any other cell (i.e. cell E3 is still the active cell), type in =F3/D3 (do not press Enter at this stage).

 

 2. Press Ctrl+Enter. This will fill all the empty cells with the same formula structure.

 

 

Because of the same rules that govern AutoFill, all other cells fill using the relative references – in the above graphic, cell E6 has been selected, and you can see in the Formula Bar that the formula is =F6/D6.