Have you ever used the Find feature in Excel to locate instances of data/text? If you have, you have also used the “Find Next” button to find the next instance, and the next, and so on. What happens if you have a very large data set (i.e. hundreds or thousands of rows of data)? The Find feature is commonly used but is a very tedious method for larger instances of data. Also, you would have to engage the Find dialog box each time you want to find new text/data. In other words, it’s clunky.

A more efficient method is utilizing the Conditional Formatting feature in Excel, but with a Formula to make it more dynamic. It can be made even more efficient if it is equated to an entry cell, where you can just type the data/text you want to find, and let Excel highlight all the instances with a color so it can stand out. You can also highlight an adjacent but corresponding cell to clearly stand out and identify the data/text you are trying to “Find”. If the data set itself is constantly growing, (i.e. you are updating and adding new rows to the data), then converting the data (range) to a Table object will make the method more effective.

The following example shows how one can enter the text to find in cell F1 (yellow cell), and Excel will locate all instances of that data in column E (yellow). However, if you type the data found in column F, then the yellow highlight is applied to all items found in column F, along with the adjacent column E.

Screenshot A:
(Convert a Data Range to a Table Object – mentioned in an earlier Technology Tip)


Screenshot B:
(Convert a Data Range to a Table Object – mentioned in an earlier Technology Tip) – continued


Steps to Convert a Range to a Table (Object):

  1. Click in the data range (no blank rows or columns)
  2. Press CTRL + T to bring up the Create Table dialog box
  3. Make sure the Check Box has a check mark to indicate the table has headers;
    Click Ok (the Table has Blue/White alternating rows and filter arrows on the headers).


Screenshot C:
(Apply Conditional Formatting to data columns where data is found)


Before applying conditional formatting, select the cell where you will be entering Data/Text you want to find, and apply a Yellow color.  In our example below this is Cell F1.

Steps to Apply Conditional Formatting:

  1. Select the columns of data where you want to find the text (Column E & F in our example, E4 to F30)
  2. Click on Conditional Formatting ribbon button
  3. Click on New Rule (in the menu drop down)
  4. Select Use a Formula to determine which cells to format;
    Click OK.


Screenshot D:
(Apply Conditional Formatting to data columns where data is found) – continued


Step to Apply Conditional Formatting (cont’d):

  1. In the Format values where this formula is true field, type the following formula:
    =OR(E4=$F$1,F4=$F$1) where, E4 is the first data cell in column E, F4 is the first data cell in column F, and $F$1 is the absolute reference to the data that will be typed in cell F1 (the yellow colored cell)
    1. Click the Format button
    2. in the Fill tab, choose yellow
    3. Click OK

Additional Explanation for Formula Used:
In Excel formulas, you may use upper of lower case for functions or references, but no blanks.

Without a “$” sign, a cell reference is known as a relative reference, so when the reference goes down in selection, the row number changes automatically; if the selection goes to the right, the column letter changes.  In this example, cell E4 will change to E5, E6, E7 etc, as it goes downward in the data. The same with cell F4.

With a “$” sign, a cell reference is known as an “Absolute Reference”, so when the selection changes downward or to the right, the reference to the cell will not change. In this example, cell $F$1 is an absolute reference and does not change.

This concept is covered in our Excel Level 1 Introduction course.