Excel Find based on cell formatting – When appearance matters

June 05, 2020 Excel tips,

Excel Find based on cell formatting – When appearance matters

Most probably you might have not found this little find option in Excel i.e. find by cell formatting. Basically it let you include additional condition to find only such results that hold specific format of the cell.

Remember format includes:

Text format that includes:

Font size, font color, font type, font style, font effects i.e. strikethrough, superscript, subscript

Cell format that includes:

text format (i.e. above is part of cell format), cell color/fill, text alignment, text controls, border styles, number format

You can specify the format which you are eyeing for using find format or you can use the format of specific cell by using choose format from cell option. The preview box gives an idea of what formatting conditions will be used to find results.

After you specify the format, excel will only consider the results that fulfill format specified even if the value provided in find what is present in other cells with different format.

Step-by-Step

Following steps help you make understand how this works:

Step 1: Go to Home Tab>Editing group>Find and select>Find or simply hit shortcut combo Ctrl+F

Step 2: In the dialogue box click options button and additional options will be enabled.

Step 3: Just at the right of Find what field you have preview box and on the right of it you can find format button. Click on the downward pointing arrow

Step 4: If you already have a cell that contains the format that you want to include in find results then click choose format from cell. The dialogue box will disappear and cursor will change to dropper. Click on the cell from which you want to inherit the formatting. Once clicked, find dialogue reappears with preview box displaying the formatting inherited.

In case if you do not have formatting in any cell then you can specify your own by clicking format button itself instead of clicking drop down arrow.

Step 5: Add in the text, number or value you want to find within those cells that has format specified and click find all or find next button. If you left find what field empty then excel will find all such boxes that has specified formatting.

Examples

Following are examples of use of find by formatting option

Find: Just value with no format

Only value is mentioned in find what field with no formatting defined therefore, it will consider all the cells that has value same as specified ignoring the format.

Find: Just format with no value

As no value is mentioned however, format is provided therefore, excel will consider only such cells that meet specified format.

Find: Just format with no value 2

In this example if you look closely, the format of cell used to identify format in the find is different from other orange boxes. Because the text is bold and italic therefore, only one cell is found as only one cell meet that format.

Find: With format and value

With value and format both specified excel will consider only those cells that are of format specified and also bear the value mentioned in find what field.

Find: Format obeys number formatting

Although I have specified what is considered as a cell format above and it includes number formatting as well but to be more clear consider following illustration. All the boxes have same date i.e. August 6, 2013. However the date format is different. Find will consider only those cells that has the same date format and ignores other values.

Find: Just format – interesting find

One thing I found when finding just with format with no values is when we select a cell with no specific format and then try to run find command then it stays within the range.

In the following animation I selected Item 3. This cell had no formatting done whatsoever and was squeaky default. I was expecting that excel will find all of the worksheet’s cells as a result but that didn’t happen. Instead it only considered cells that were within the range with same default format. I don’t know how Excel made it happen but there must be some reason. But anyways its Excel intelligence at work.