Finding missing data in large data sets

Question

I have a large data set with several columns and more than 3,000 rows. In some of the columns, data is missing on one or more rows below. How can I quickly identify which rows are missing data?

Answer 1

You can do this easily with filters. First, be sure to have a header row that has a label over every column. It is best to make the text in this row bold and to freeze the header row. To freeze the header row:

·        Assuming your data starts at cell A1, place the cursor just below the header row and to the far left in cell A2

·        Click the ‘View’ tab on the ribbon

·        Click the ‘Freeze Panes’ command and select the first option ‘Freeze Panes’

Before turning on filters, it is best to first highlight the entire data set. Sometimes, when there is missing data, the filters may not capture the entire data set if you just turn it on without highlighting:

·        Click on cell A1 then hold down Shift and arrow to the right until you get to the last column of data.

·        While continuing to hold down Shift, press Page-Down to highlight all rows of data

·        Get as close to the bottom of the data set as possible then use the up/down arrow keys to get to the last line of data (release the Shift key).

·        Click the ‘Data’ tab on the ribbon, then click Filter (keyboard shortcut Ctrl+Shift+L)

·        The filters are now on. You will see them in the header row.

To locate rows that may be missing data:

·        Click one of the filters

·        Just below to the ‘Search’ window, uncheck the ‘Select All’ box

·        Scroll to the bottom of that list and check the ‘Blanks’ box, click OK

·        All the blank rows for that column will be revealed

·        To bring the list back, click on the same filter, check the ‘Select All’ box and click OK

Repeat these last 5 steps for other columns with blank rows.