Highlights all the blanks in a list of data in Excel!

All Excel spreadsheets which are used for data analysis, calculation or manipulation; have large amounts of data and so it is very difficult to locate all the blank spaces within those Excel spreadsheet. The blank spaces in Excel spreadsheet could cause numerous issues including errors and breaks in formatting. This tutorial presents the easiest and simple ways to locate and highlight all blanks within the data.

Get yourself ready!

In the following example, we have an Excel sheet where several bank spaces are present there. We would use a automated method of Excel to search all the blank spaces and mark those cells with a different color says light blue.


Steps to highlights all the blanks in a list of data in Excel!

Step 1: Open the spreadsheet in Excel that you need to highlight the blank spaces.

Step 2: From the opened file, click on Home tab. Under the Home tab, there is an option Find & Select and just click on that option Find & Select and then choose Go To Special and click on that option. This will brings the Go to Special dialog box.


Step 3: From Go To Special dialog box, select the option Blanks and click  on OK.


Step 4: Now, you will see that all of the blanks within the data spreadsheet have been highlighted.


Step 5: Now, we would like to apply a format to all those blank cells. For example, we would like to fill all the blank cells orange color. So, we choose the color.  This now provides a contrasting visual indicator to locate blanks on the screen or when printed and anybody could easily figure out all the blank cells in the spreadsheet.


After applying the cell format, you will see that all the blank spaces are marked with orange color.


How this method works?

The Go To Special is a built-in find feature of Excel and provides several criteria to find data within the spreadsheet. When the Blanks are selected from Go To Special dialog box,  Excel quickly parsed through the data and select all those cells that contained no data i.e. the blank spaces. After that, with all of the blank cells selected, you would  easily apply a visual indicator in your spreadsheet.

Think more!

We use just the orange color as the indicator of the blank cells. However, it is possible to use other type of indicators like lines, characters, and so on.

No comments:

Post a Comment