Let’s take a look. To understand how find and replace works with formatting, let’s use the name table, and the example of replacing Ann with Anna. Before we replace any values, let’s add an orange fill to all cells that contain Ann. Now let’s open the Find and Replace dialog box and set up our Find options. Let’s look for Ann, and select “Match entire cell contents.” Let’s also add a format to match the orange fill we’ve just applied. Now, let’s set up Replace options. We’ll use Anna for “Replace with,” and then set a format of a blue fill, with a green border. We can now click Find Next, then click Replace. We can also use Replace All. Let’s undo those changes and see what happens if we remove formatting from the Find criteria. In this case, it doesn’t make any difference, because Excel is still able to find cells that contain only Ann. Now let’s adjust the Find and Replace options to work with the format only. Let’s add the orange fill back to Find. Then, let’s remove Ann and Anna. Even without text to search for, Excel can still find and replace formats. This means that you can use Find and Replace to update formats globally. This also means you can use Find in creative ways. For example, you could flag certain cells with specific formatting, and use Find to locate all flagged cells. In a small table like this it’s not especially helpful, but in a large table, it’s a good way to quickly find cells marked with a specific format.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
