How to find a formatted value in Excel

When you're looking for things in an Excel worksheet, you aren't restricted to looking for values only; you can also search for cells that are formatted in a certain way.

Let's take a look.

Here's the table of names we looked at previously. Let's add some formatting, then look at how to find this formatting. Let's fill several cells that contain the name Ann with yellow.

Let's also bold two of these cells.

Now let's bring up the Find and Replace dialog box and search for the name Ann, with the option "Match entire cell contents" selected.

As before, "Find Next" moves through all cells that contain Ann, one at a time, including the cells we just formatted.

Now let's add the yellow fill to our search criteria.

The Format button brings up the Format Cells dialog box, where we can select a yellow fill. When we click OK, the formatting is added to our search criteria.

Now when we click Find Next, Excel only finds cells that contain Ann, and have a yellow fill.

Note that Excel also finds the bolded version of the cells. If we like, we can add bold formatting to our search criteria. This time, let's take a different approach. Instead of entering the format manually, we can select "Choose Format From Cell" from the Format button menu.

Excel will temporarily close the Find dialog and wait for us to select a cell that contains the formatting we're after. When we click a cell, the Find window returns with the format set.

Now Excel will only find cells that contain Ann, and have a yellow fill, and are bolded.

To remove formatting from the search criteria, select "Clear Find Format" from the Format button menu.

Now, Find Next works like it did before we added the formatting.