Excel Yourself: Filling in the blanks (part 1)

Excel has a number of shortcuts that don’t handle blank cells very well. The AutoSum is one of them.

Excel has a number of shortcuts that don’t handle blank cells very well.

The AutoSum is one of them. When estimating ranges it will stop at a blank cell.

Luckily, Excel has a quick way to select all the blank cells in a range. There is also a technique to then populate all the blanks once selected.

We’ll look at a range that should have values, but also has blank cells. Rather than blanks it is often better to have zeroes entered. See Figure 1 for an example.

Figure 1

Entering zeroes in blank cells

1.    Select the range involved (C2:C10 in the example) and press the F5 key
2.    Click the Special Button
3.    In the dialog click the Blanks option - Figure 2 - and click OK 
4.    Now all the blank cells are selected. Type 0 then hold the Ctrl key down and press the Enter key.

Figure 2

All the blank cells now have zeroes entered. See Figure 3

Figure 3

Figure 3

Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services.