How-To: Conditional Formatting Based on Another Cell in Google Sheets

Download Now: Free Google Sheets Templates
Martina Bretous
Martina Bretous

Published:

Conditional formatting is a feature in Google Sheets in which a cell is formatted in a particular way when certain conditions are met. The formatting can include highlighting, bolding, italicizing – just about any visual changes to the cell.

Google Sheets user sets conditional formatting based on another cell

Just as it can be done for the cell you’re currently in, conditional formatting can also be set based on conditions met in another cell.

→ Access Now: Google Sheets Templates [Free Kit]

Let’s dive into how to create this condition based on multiple criteria.

How Conditional Formatting Works

To learn how to set conditional formatting, let’s use this workbook as an example.

example of a google sheets workbook to show how conditional formatting works

It’s a workbook showing website traffic year over year from Q4 2020 to Q4 2021, with the page views along with the year-over-year percentage change.

Here’s what we want to accomplish here: When the percentage change is positive YoY, the cell turns green. When it’s negative, the cell turns red. This makes it easy to get a quick performance overview before diving into the details.

Free Excel Marketing Templates

10 free templates to help you master marketing with Excel.

  • Marketing Reporting Template
  • Email Planning Template
  • SEO Template
  • And More!
Learn more

    Download Free

    All fields are required.

    You're all set!

    Click this link to access this resource at any time.

    Here are the steps to set the conditional formatting.

    1. Select the cell you want to format, click on "Format" from the navigation bar, then click on "Conditional Formatting."

    how to set the conditional formatting step 1


    2. While staying in the "Single color" tab, double-check that the cell under "Apply to range" is the cell you want to format. how to set the conditional formatting step 2


    3. Set your format rules.

    how to set the conditional formatting step 3

    It may automatically default to a standard conditional formatting formula. In this case, open the dropdown menu under "Format cells if…" to select your rules. Options will look as follows:

    4. Choose your formatting style, then click "Done."how to set the conditional formatting step 4

    5. Confirm the rule was applied under "Conditional Formatting Rules."

    conditional formatting step 5

    6. Add another rule if needed.

    conditional formatting step 6

    7. Return to cell to view formatting, then drag the cursor to apply to other cells, if needed.

    how to set the conditional formatting step 7

    Now that you understand the basics, let’s cover how to use conditional formatting based on other cells.

    Conditional Formatting Based on Another Cell Value

    1. Select the cell you want to format.

    conditional formatting based on another cell value step 1

    2. Click on "Format" in the navigation bar, then select "Conditional Formatting."

    conditional formatting based on another cell value step 2

    3. Under "Format Rules," select "Custom formula is."

    conditional formatting based on another cell value step 3

    4. Write your formula, then click "Done."

    conditional formatting based on another cell value step 4

    5. Confirm your rule has been applied and check the cell.

    conditional formatting based on another cell value step 5

    Conditional Formatting Based on Another Cell Range

    To format based on another cell range, you follow many of the same steps you would for a cell value. What changes is the formula you write.

    1. Select the cell you want to format.

    conditional formatting based on another cell range step 1

    2. Click on "Format" in the navigation bar, then select "Conditional Formatting."

    conditional formatting based on another cell range step 2

    3. Under "Format Rules," select "Custom formula is."

    conditional formatting based on another cell range step 3

    4. Write your formula using the following format: =value range < [value], select your formatting style, then click "Done."

    conditional formatting based on another cell range step 4

    5. Confirm your rule has been applied and check the cell.

    conditional formatting based on another cell range step 5

    Google Sheets Conditional Formatting Based on Another Cell Color

    Currently, Google Sheets does not offer a way to use conditional formatting based on the color of another cell. You can only use it based on:

    • Values – higher than, greater than, equal to, in between
    • Text – contains, starts with, ends with, matches
    • Dates – is before, is after, is exactly
    • Emptiness – is empty, is not empty

    To achieve your goal, you’d have to use the condition of the cell to format the other.

    Let’s use an example.

    Google Sheets Conditional Formatting Based on Another Cell Color

    Say you want to format cell A2 (September 2020) to be red and match the color of cell E2 (-20%). There’s no formula that allows you to create a condition based on color. However, you can create a custom formula based on E2’s values.

    You can say that if cell E2’s values are less than 0, cell A2 turns red. The formula is as follows: = [The other cell] < [value]. In this case, the formula would be =e2<0, as it signifies that cell A2 should turn red if E2’s value is less than 0.

    google sheets conditional formatting based on another cell's values

    With so many functions to play with, Google Sheets can seem daunting. By following these simple steps, you can easily format your cells for quick scanning.

    10 Free Google Sheets Templates

    10 Google Sheets templates for your marketing, sales, and customer service needs.

    • Budget templates
    • Goal-setting templates
    • SEO and email templates
    • Sales KPI templates
    Learn more

      Download Free

      All fields are required.

      You're all set!

      Click this link to access this resource at any time.

      Topics: Google Sheets

      Related Articles

      10 free Google Sheets templates for your business's marketing, sales, and customer service need

        Marketing software that helps you drive revenue, save time and resources, and measure and optimize your investments — all on one easy-to-use platform

        START FREE OR GET A DEMO