Fill All Blank Cells in a List

Many of Excel's functions are designed to be used on lists. For most of these functions to work correctly, lists should not contain any blank cells, and column headings should be formatted differently from the data in the list.

When setting up data in Excel, it is good practice to ensure that all cells within a list are occupied. However, many lists are set up in a similar manner to the one shown in Figure below.

Whereas prices are repeated in the Cost column, the types of fruits in the Fruits column are not. As discussed at the previous tutorial, this will create many problems when using features such as Subtotals and PivotTables. In most cases, Excel expects your related data will be set up in a continuous list or table, with no blank cells.

You can fill blank cells in a list by using a formula or by using a macro.