Excel Yourself: Filling in the blanks (part 1)

Excel Yourself

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

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

Figure 2

Ctrl + Enter will enter what you have typed in all the selected cells.

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







Figure 3

Figure 3

Figure 3

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


How-to articles, sample spreadsheets and more

Excel yourself

Combine annual and monthly data in Excel with these simple steps


Making data relationships work

Easy steps to make data Relationships work in Excel


Excel Yourself

Easy tips for matching salary to job class in Excel