LiveFlow raises $13.5 million Series A led by New York-based Valar Ventures and launches LiveFlow Next to revolutionize accounting!
Read More
Arrow
Back to guides
Google Sheets Tips

Conditional Formatting in Google Sheets: Explained

In this article, you will learn how to use essential functions of Conditional Formatting in Google Sheets.

What is Conditional Formatting?

Conditional Formatting is Google Sheets’ feature that allows you to change the formatting of cells and the style of their values based on the standards you set up.

This function is beneficial when you want to highlight cells or apply a color scale to cells in a particular range under specific conditions.

How to use Conditional Formatting in Google Sheets

  • Navigate to the “Format” tab and select “Conditional formatting”.

  • A pop-up menu for Conditional Formatting shows up on the right side.

  • Select the “Single color” tab in the menu.

  • Define “Format rules” and “Formatting style”.

How to show the menu bar for Conditional Formatting

Imagine that you are a sales planning manager and need to analyze your client list. You want to highlight the cells based on a particular criterion in each column for the data set below. 

How to apply Conditional Formatting to a data set

Assume you set up the following standards for the data:

(i)  “Contract Date”: Highlight a cell if it contains a date before “5/30/2022”.

(ii) “Product Type”: Change font color without coloring cells for cells containing a text of “XYZ”.

(iii) “Sale Amount”: Color a cell if its value is greater than or equal to 3000.

Save hours formatting your next financial spreadsheet!
100+ templates, all free to use.
Free Spreadsheet Templates

(i) Change cell colors based on their dates

  • Select the range. In this case, it should be C4:C13.

  • Choose “Date is before” at the first drop-down list at “Format cells if…”.

  • Then, select “exact date” on the second pull-down list.

  • Enter “5/30/2022” in the text box.

  • Change the fill color in the “Formatting style” section.

  • Click “Done” at the bottom right.

How to change cell color based on its value in Google Sheets

We chose “Date is before” at the first drop-down list and “exact date” at the second one as an example, but there are more basic options as you see. Explore and select the most appropriate one for your practical situation. 

(ii) Change font colors of cells based on text in the cells

  • Select the range. In this case, it should be D4:D13.

  • Choose “Text contains” at the first drop-down list at “Format cells if…”.

  • Then enter “XYZ” as a specific text in the text box.

  • Change the fill color to white and font color to blue, and make the font style bold in the “Formatting style” section.

  • Click “Done” at the bottom right.

How to change cell and font style depending on the text in a cell

You have many basic options for conditions related to texts as well. So, think about a requirement to differentiate cells carefully and set up the proper rule for Conditional Formatting.

(iii) Change font and cell colors depending on cell values

  • Select the range. In this case, it should be E4:E13.

  • Choose “Greater than or equal to” at the first drop-down list at “Format cells if…”.

  • Then enter “3000” as a specific text in the text box.

  • Change the fill color to light red and font color to red, and make the font style bold and italic in the “Formatting style” section.

  • Click “Done” at the bottom right.

How to change cell and font formatting based on cell values

Similar to conditions on dates and texts, there are several basic options for rules on numbers. Check, try, and find the correct standards that fit the criteria you have in mind.

How do I apply conditional formatting with multiple criteria?

Select an array one by one and apply various criteria, respectively. Assume you want to add one more standard - highlighting cells in light yellow if a cell value is less than or equal to 1500 and changing the font style for the “Sale Amount” column in the table shown in the previous section.

You can apply these protocols to dates and texts as well.

  • Select the range E4:E13.

  • Choose “Less than or equal to” at the first drop-down list at “Format cells if…”.

  • Then enter “1500” as a specific text in the text box.

  • Change the fill color to light yellow and make the font style bold and italic in the “Formatting style” section.

  • Click “Done” at the bottom right.

How to apply multiple rules of Conditional Formatting in a column

Level up your Google Sheets skills with our FREE LiveFlow Academy
Basic and intermediate classes live now! Earn your certificate today.
Enroll in LiveFlow Academy

How to remove Conditional Formatting from cells

  • Put your cursor on a cell or a range that contains a conditional formatting rule you want to remove.

  • Navigate to “Format” and go to “Conditional formatting”.

  • You have a list of rules applied to the cell.

  • Click the bin icon, which pops up when you put your cursor around.

How to remove a conditional formatting rule in Google Sheets

If you want to remove a conditional formatting standard from a part of the range defined, we recommend you change the selected field in each rule as it allows the rule to be applied to the rest of the area.

How do I apply conditional formatting to a column based on another column?

Check this article: Conditional Formatting Based on Another Cell Value in Google Sheets: Explained to learn how to apply conditional formatting to a column according to values in another column.

Turn Quickbooks Online data into a Google Sheets dashboard
100+ templates, update automatically, totally secure!
Book a Demo

Learn how to do this step-by-step in the video below 👇

Automate financial reporting with LiveFlow

Cta Photo

Want to eliminate manual updates of your Excel & Google Sheets models?

Yes, show me how!

Get personal help

We guarantee you personal help on chat or Zoom within maximum 6 hours between 9am and 10pm EST.
Blue Tick
Email us at: [email protected]

Liked this article? Then you'll love the ones below

Supercharge your financial reporting today

See LiveFlow in action and discover how to streamline your workflows.