Pinterest Pixel

All You Need to Know About

Find and Select in Excel

There are a lot of creative ways to do Find and Select in Excel. Highlight, search, isolating errors, you name it!

Here are the top things on what you can do with 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.

Highlight all Formula Cells

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

Highlight all Formula Cells

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

Highlight all Formula Cells

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.

Highlight all Formula Cells

And now all your cells containing formulas are now highlighted!

Highlight all Formula Cells

You can practice this cool trick by downloading this 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.

Find Blank Cells

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

Find Blank Cells

Select Blanks. Click OK.

Find Blank Cells

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

Find Blank Cells

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

Find Blank Cells

Our filtering has worked to show the blank cells!

Find Blank Cells