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:

Select all the cells in your Excel worksheet by clicking on the top left hand corner of your worksheet.

Highlight all Formula Cells

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

Highlight all Formula Cells

Select the Formula radio button and press OK.

Highlight all Formula Cells

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:

DOWNLOAD EXCEL WORKBOOK

YouTube player

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.

DOWNLOAD EXCEL WORKBOOK

YouTube player

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.

DOWNLOAD EXCEL WORKBOOK

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

Find Blank Cells

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

Find Blank Cells
Select Blanks. Click OK.
Find Blank Cells

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

Find Blank Cells

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

YouTube player

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!