Apply Conditional Formatting Based on Another Cell in Excel

Last Updated: September 15, 2023
puneet-gogia-excel-champs

- Written by Puneet

In Excel, you can use conditional formatting based on another cell. For this, you need to use a custom formula within the rule.

conditional-formatting-based-on-another-cell

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.

  1. First, select the range where you have data.
    2-select-the-data-range
  2. Afterward, go to the Home Tab > Conditional Formatting > New Rule.
    3-click-on-new-rule
  3. 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.
    4-enter-the-formula
  4. Next, click the Format button to specify the format you want to apply.
    5-specify-the-format-to-apply
  5. Now, once you specify the format click OK to apply the conditional formatting. And then again, OK close Rules Manager.
    6-ok-to-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.

cells-with-value-more-than-given-reference-cells-highlighted

And when you change the number from cell O1, it changes the highlight values in the data.

highlighted-cells-changes-with-change-in-the-reference-cell-value

Get the Excel File

Download