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. For example, you could flag certain cells with specific formatting, and use Find to locate all flagged cells. This also means you can use Find in creative ways. This means that you can use Find and Replace to update formats globally. Then, let's remove Ann and Anna.Įven without text to search for, Excel can still find and replace formats. I can 'Find' a amount (ie: 3.50) in cells ONLY if I change 'Look In' from 'Formulas' to 'Values.' BUT, I when I click on 'Replace' and the next screen comes up, the 'Look In' drop down menu disappears and if I try to 'Replace All' my 3.50 with 3.00 (for example) it says 'Microsoft Excel cannot find matching data to replace. Now let's adjust the Find and Replace options to work with the format only. In this case, it doesn't make any difference, because Excel is still able to find cells that contain only Ann. Let's undo those changes and see what happens if we remove formatting from the Find criteria. Enter the replacement word or phrase in the second field. Enter a word or phrase in the first field. We can now click Find Next, then click Replace. in the toolbar, then choose Show Find & Replace. We'll use Anna for 'Replace with,' and then set a format of a blue fill, with a green border. Let's also add a format to match the orange fill we've just applied. Let's look for Ann, and select 'Match entire cell contents.' Now let's open the Find and Replace dialog box and set up our Find options. To understand how find and replace works with formatting, let's use the name table, and the example of replacing Ann with Anna.īefore we replace any values, let's add an orange fill to all cells that contain Ann. You can even ignore values and just work with the format itself. One of Excel's really useful features is the ability to find and replace values based on formatting.

Where is find and replace in mac excel