October 5, 2010

It is often useful to format cells based on the content of the value or formula in a cell. By using the ‘find’ feature (ctrl + ‘f’)this can be done very swiftly.

1) Select the area to analyze

2) Hit ctrl + ‘f’ and in the ‘find’ textbox type the value to be found

3) Click the ‘Find All’ button and you will see a list of cell references appearing where the desired value was found

4) Within the list of references select the first  and while holding down the ‘shift’ key, select the last reference

5) Now all the cells containing the matching text will be selected

Format Found

Format Found

6) Close the ‘find and replace’ dialog box and apply a formatting to the selected cells

Format Found

Format Found

You are now in a position to isolate these cells by using the sort or filter by color features in Excel.

This technique could also be used for finding cell references in formulas for the purposes of formula auditing.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: