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.
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.
Ctrl + Enter will enter what you have typed in all the selected cells.
All the blank cells now have zeroes entered. See Figure 3
Neale Blackwood CPA runs A4 Accounting, providing Excel training, webinars and consulting services.