Conditional Formatting Based on Another Cell – Excel & Google Sheets

This tutorial will demonstrate how to highlight cells based on another cell value using Conditional Formatting in Excel and Google Sheets.

 

conditional formatting based on another cell master

 

Highlight Cells Based on Another Cell

To highlight cells based on another cell’s value, you can create a custom formula within a conditional formatting rule.

  1. Select the range you want to apply formatting to.
  2. In the Ribbon, select HomeĀ > Conditional FormattingĀ > New Rule.

 

conditional formatting based on another cell menu

 

  1. Select Use a formula to determine which cells to format, and enter the following formula:
=B4>$G$7
  1. Cell G6 needs to be locked by making it an absolute reference. You can do this by using the $ sign around the row and column indicators, or by pressing F4 on the keyboard.
  2. Click Format.

 

Conditional formatting based on another cell formula

 

  1. Choose the formatting for cells that meet the conditions.

 

Conditional formatting based on another cell formatting

 

  1. Click OK, then OK again to return to the Conditional Formatting Rules Manager.

 

conditional formatting based on another cell rule manager

 

  1. Now your range is conditionally formatted if the cell value is greater than the value contained in cell G6.

 

conditional formatting based on another cell master final
The rule is based on the cell value in G6, so you can then change the value in G6 to see number of formatted cells change!

 

conditional formatting based on another cell master adjusted

 

Highlight Cells Based on Another Cell in Google Sheets

The process to highlight cells based on another cell in Google Sheets is similar to the process in Excel.

  1. Highlight the cells you wish to format, and then click on Format, Conditional Formatting.

 

google sheets conditional formatting menu

 

  1. The Apply to Range section will already be filled in.

 

google docs conditional formatting based on another cell apply to range

 

  1. From the Format Rules section, select Custom Formula.

 

google docs conditional formatting based on another cell custom formula

 

  1. Select the fill style for the cells that meet the criteria.

 

google sheets conditional formatting format

 

  1. Click Done to apply the rule.

 

google docs conditional formatting based on another cell master

 

  1. Change the value in G7 to see the format of the cells change.

 

google docs cond formatting based on another cell master adjusted