Conditional formatting is a feature in Google Sheets in which a jail cell is formatted in a item manner when sure conditions are met. The formatting can include highlighting, bolding, italicizing – just about whatever visual changes to the cell.

Just as it tin can be done for the prison cell y'all'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 swoop into how to create this condition based on multiple criteria.

How Conditional Formatting Works

To larn how to set conditional formatting, let's utilize this workbook as an example.

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

It's a workbook showing website traffic twelvemonth over twelvemonth from Q4 2020 to Q4 2021, with the folio views along with the year-over-year pct change.

Here's what nosotros desire to accomplish hither: When the percentage change is positive YoY, the prison cell turns greenish. When it'south negative, the cell turns red. This makes it easy to get a quick operation overview earlier diving into the details.

Here are the steps to ready the provisional formatting.

i. Select the cell yous 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 "Unmarried color" tab, double-check that the cell under "Utilise to range" is the cell you desire to format. how to set the conditional formatting step 2


iii. Set your format rules.

how to set the conditional formatting step 3

Information technology may automatically default to a standard provisional formatting formula. In this case, open up the dropdown menu under "Format cells if…" to select your rules. Options will expect as follows:

four. Cull your formatting mode, then click "Done." how to set the conditional formatting step 4

5. Confirm the dominion was applied nether "Conditional Formatting Rules."

conditional formatting step 5

6. Add together another rule if needed.

conditional formatting step 6

7. Render to cell to view formatting, and so drag the cursor to apply to other cells, if needed.

how to set the conditional formatting step 7

Now that you empathize the basics, allow'south cover how to employ conditional formatting based on other cells.

Conditional Formatting Based on Another Cell Value

1. Select the prison cell you desire to format.

conditional formatting based on another cell value step 1

ii. 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, so click "Done."

conditional formatting based on another cell value step 4

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

conditional formatting based on another cell value step 5

Conditional Formatting Based on Some other Jail cell Range

To format based on some other cell range, you follow many of the same steps you would for a jail cell value. What changes is the formula y'all write.

1. Select the prison cell yous 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. Nether "Format Rules," select "Custom formula is."

conditional formatting based on another cell range step 3

4. Write your formula using the post-obit format: =value range < [value], select your formatting style, so click "Done."

conditional formatting based on another cell range step 4

v. Confirm your rule has been applied and bank check the cell.

conditional formatting based on another cell range step 5

Conditional Formatting Based on Another Jail cell Not Empty

  1. Select the cell you want to format.
  2. Click on 'Format' in the navigation bar, then select 'Provisional Formatting.'
  3. Under 'Format Rules,' select 'Custom formula is.'
  4. Write your formula using the following format: =Not(ISBLANK([jail cell#)), select your formatting style, and then click 'Washed.'
  5. Ostend your dominion has been applied and check the cell.

Google Sheets Conditional Formatting Based on Another Cell Color

Currently, Google Sheets does not offer a way to employ conditional formatting based on the color of another cell. You lot 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 subsequently, is exactly
  • Emptiness – is empty, is not empty

To reach your goal, yous'd have to use the condition of the cell to format the other.

Allow's apply 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 (-xx%). There's no formula that allows you to create a condition based on color. Yet, you can create a custom formula based on E2's values.

You can say that if cell E2'due south values are less than 0, cell A2 turns red. The formula is as follows: = [The other prison cell] < [value]. In this case, the formula would be =e2<0, equally it signifies that cell A2 should plow scarlet if E2'south value is less than 0.

google sheets conditional formatting based on another cell's values

With and so many functions to play with, Google Sheets can seem daunting. By following these uncomplicated steps, y'all can easily format your cells for quick scanning.

business google sheets templates

google sheets templates

Originally published Mar 10, 2022 vii:00:00 AM, updated March 10 2022