In Excel, you can use conditional formatting based on another cell. For this, you need to use a custom formula within the rule.
In this example, we have month and year-wise data, and you need to highlight all the values above a specific value, which need to be in another cell.
Apply Conditional Formatting using Another Cell.
- First, select the range where you have data.
- Afterward, go to the Home Tab > Conditional Formatting > New Rule.
- In the New Rule dialog box, click on “Use a formula to determine which cells to format” and enter the formula (=B2>=$O$1) in the formula input bar.
- Next, click the Format button to specify the format you want to apply.
- Now, once you specify the format click OK to apply the conditional formatting. And then again, OK close Rules Manager.
In the snapshot below, you can see in cell O1 we have the number 150 to highlight all the numbers equal to or greater than 150.
And when you change the number from cell O1, it changes the highlight values in the data.
Get the Excel File
Download Learn Excel Basics
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 Column
- 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
- How to Add Data Bars in Excel
- Stop IF True in Conditional Formatting
- Find and Highlight Duplicates with Conditional Formatting
- Icon Sets in Excel