If you have inherited a massive Excel database containing 5,000 rows of employee data, discovering that hundreds of random cells are missing information is a nightmare. If you want to color-code all of those blank cells red so your team can easily spot them and fill them in, manually clicking and highlighting each one individually would take hours. Instead, you can use a hidden auditing feature called “Go To Special” to mathematically select every single blank cell in the entire document simultaneously.
What is the Go To Special Tool?
The standard “Find” tool in Excel looks for specific text or numbers. The “Go To Special” tool, however, searches for structural elements and mathematical conditions. It can instantly highlight every cell that contains a broken formula, every cell that has a comment attached to it, or, most importantly, every cell that is completely empty.
How to Select All Blank Cells Instantly
- Open your Microsoft Excel spreadsheet.
- Press Ctrl + A on your keyboard to highlight the entire dataset. (If you only want to audit a specific column, like Column C, click the letter C at the top of the screen instead).
- In the top menu ribbon, ensure you are on the Home tab.
- Look to the far right side of the ribbon and click on the Find & Select button (it usually has a magnifying glass icon).
- From the dropdown menu, select Go To Special…
A new dialogue box will appear containing a list of advanced auditing options.
- Click the radio button next to the word Blanks.
- Click OK.
Applying the Formatting
The exact moment you click OK, Excel will instantly un-select all the normal data, leaving only the completely blank cells highlighted. You will see them shaded in gray across your entire massive spreadsheet.
Do not click anywhere on the spreadsheet, or you will accidentally deselect them. While they are still highlighted, simply click the Fill Color bucket icon in the Home tab and choose a bright red color. Instantly, every single missing piece of data in your 5,000-row database will be painted red, allowing your team to easily find and fix the errors.