It’s very easy to search for words or values (and replace them) in any excel-generated report by using the Find and Replace function. Do you also know that you can actually highlight the cell (i.e. fill in the cell colour) whenever that particular word or value is located?
So here’s the scenario…I have a report with 100 rows and 10 columns. I want to find the word “xtremio” which have multiple occurrences throughout the report, and I want to fill in the cell colour with yellow whenever that word is located. Follow these simple steps to do it:
1. Press the Ctrl and H buttons on your keyboard at the same time.
2. A Find and Replace dialog box appears. Click the Options>> button.
3. Type the word “xtremio” in the Find what and Replace with text boxes. Then, click the second Format button.
4. A Find Format dialog box appears. Click the Fill tab and choose yellow from the Background Color palette. Click the OK button.
5. You will return to the Find and Replace dialog box and you can see how your formatting will look like in the Preview section. Click the Replace All button.
6. Excel will notify you of the successful replacements. Click the OK button to return to your file and see the changes!
As you can see from the example below, any cell that contains the word “xtremio” will be filled with yellow colour. You can do a lot more with this method for various reporting purposes, so use it with creativity! Have fun!