Find and Select in Excel

Highlight All Excel Formula Cells

Have a lot of formulas and you thought find and select in Excel is just limited to cell values? We can quickly identify cells with formulas with this trick!
Whenever you are auditing an Excel worksheet and need to know where all the formulas are located, a great way is to highlight the formula cells in a distinctive color.  This is how it is done:
STEP 1: Select all the cells in your Excel worksheet by clicking on the top left hand corner of your worksheet.

STEP 2: Press the CTRL+G shortcut which will open up the Go To dialogue box and select the Special button.

STEP 3: Select the Formula radio button and press OK.

STEP 4: This will highlight all the formulas in your Excel worksheet and you can use the Fill Color to color in the formula cells.

And now all your cells containing formulas are now highlighted!

You can practice this cool trick by downloading this workbook:

DOWNLOAD EXCEL WORKBOOK

Find Errors with Go to Special Constants

Say you have a data set and want to make sure that each column contains what it is supposed to.
For example, say you have a column which contains Dates and you want to check that there are no cells which contain Text.
You can easily check this by highlighting that column and pressing CTRL+G to bring up the Go To dialogue box (or by choosing from the menu Home > Find & Select > Go To…)
Then you need to choose Special > Constants and select the constant that you want to find in your column.
In our example you will need to only select the Text box and de-select the other boxes and press OK.  This will highlight the cells that contain text and you can begin to format these cells.
See how this is done by watching the tutorial below.

Find Blank Cells with a Color

In Excel you can have a data set that comes from an external source which isn’t always formatted to your liking.
One of the most common things you may encounter are blank cells in your Excel data which can hinder your analysis, especially if you are using a Pivot Table to analyze the data.
To find these annoying blank cells in Excel you will need to highlight all your data set (CTRL+*) and bring up the Go To shortcut:  CTRL+G > Special > Blanks.
Then you can fill in the blank cells with a color red and filter by that same color to drill down to these blank cells and take some formatting action.
I show you how easy this can be done below.

STEP 1: Make sure your entire table is selected. We will select all the blank cells.

STEP 2: Press Ctrl + G to open the Go To Window. Click Special.

Select Blanks. Click OK.

STEP 3: The blank cells are now selected. Go to Home > Font > Fill > Color Red

STEP 4: Let us try it out! Try filtering the Customer column by selecting Filter by Color > Color Red

Our filtering has worked to show the blank cells!