In Excel, you can apply conditional formatting based on another column. For example, below, you have a list of students with their scores, and you want to highlight the names of students who scored above 75.
Now here, you need to apply conditional formatting, which applies color to column A (Names) based on the values in column B (Score).
Steps to Apply Conditional Formatting Based on Another Column
- First, select the names column by leaving the column name.
- Afterward, go to the Home Tab > Conditional Formatting > New Rule.
- Next, in the new rule dialog box, click “Use a Formula to determine which cell to format”.
- Now, in the ‘Format value where this formula is true” dialog box, enter this formula =$B2>=75.
- From here, click on the “Format…” button to specify the cell formatting you want to apply to the student’s name cell, and in the end, click OK to save the rule.
- In the end, click OK to close the conditional formatting rules manager.
And the moment you click OK, it will highlight all the names of the students whose score is greater than 75 in the score column.
Using an Excel Table
Once you apply the conditional formatting and when you update new data in the list, Excel will not extend the conditional formatting to the new entry.
The best solution to this problem is to use an Excel table on the data. Once you apply the table (Ctrl + T) and then enter a new data entry to the list with the score, apply conditional formatting to it.
Using an Excel table make your conditional formatting rule dynamic, which extends itself when you enter a new value in the data.
Get the Excel FileDownload
More on Conditional Formatting
- Apply Conditional Formatting to a Pivot Table
- Remove Conditional Formatting
- Apply Conditional Formatting on Blank Cells in Excel
- Compare Two Columns using Conditional Formatting
- Apply Conditional Formatting Based on Another Cell
- Copy Conditional Formatting
- Apply Conditional Formatting to an Entire Column in Excel
- Highlight Rows using Conditional Formatting in Excel
- Apply Multiple Conditions in Conditional Formatting in Excel
- Why Conditional Formatting Not Working in Excel
- Highlight IF a Cell Contains a Specific Text
- Apply Conditional Formatting Based on a Date
- Applying Color Scales using Conditional Formatting
- Data Bars in Excel using Conditional Formatting
- Stop IF True in Conditional Formatting
- Find and Highlight Duplicates with Conditional Formatting
- Icon Sets in Excel