Prevent Blanks/Missing Fields in a Table

You can easily manipulate the Data Validation feature of Excel to ensure that you have no blank cells within your list.

With the aid of Data Validation, we can ensure that a table or list cannot have blank/missing entries. For example, let's use a simple two-column table. Suppose you have a heading of Name in A1 and Department in B1. Underneath these headings, you want users to fill out both the name and their associated departments leaving neither column blank. (If you're starting with a table that has blank cells that need to be filled sidebar.

Select A3:B100 and ensure that your selection starts from cell A3. Now, select Data → Data Tools → Data Validation (pre-2007, Data → Validation). Select Custom from the Allow: drop-down list, and then add the following formula in the Formula box:

=AND(COUNTA($A$2:$A2)=ROW()-2,COUNTA($B$2:$B2)=ROW( )-2)

It is very important to note the absolute reference of $A$2 and $B$2 and the relative row/absolute column of $A2 and $B2; otherwise, you will show incorrect results.

Select the Error Alert tab and type an applicable error message that users will see if they leave blanks in the table. Make sure the Error style is set to Stop and click OK.

The validation applied will ensure that all entries (in the table A2:B100) have both a name and a department by not permitting blank cells between the filled-in names and the names being entered.

Fill All Blank Cells

As you are no doubt aware, most of Excel's tools, PivotTables, sorting, filters, etc, run into problems when they find a blank cell in a range of cells, so here is a quick way to fill every blank cell with the value of the cell above. Say you have a list of entries in column A with many blank cells. Select column A, press F5 (on some notebook compputers, such as MacBooks, you will need to hold down the Fn key when you press F5), select Special, check the Blanks option, and click OK. You should nowhave all blanks selected. Now, press the equals sign (=), followed the Up arrow, and finally, holding down the Ctrl key, press Enter.