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

1. Navigate to the “Format” tab and select “Conditional formatting”.
2. A pop-up menu for Conditional Formatting shows up on the right side.
3. Select the “Single color” tab in the menu.
4. Define “Format rules” and “Formatting style”.

‍

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. 

‍

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.

‍

(i) Change cell colors based on their dates

1. Select the range. In this case, it should be C4:C13.
2. Choose “Date is before” at the first drop-down list at “Format cells if…”.
3. Then, select “exact date” on the second pull-down list.
4. Enter “5/30/2022” in the text box.
5. Change the fill color in the “Formatting style” section.
6. Click “Done” at the bottom right.

‍

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

1. Select the range. In this case, it should be D4:D13.
2. Choose “Text contains” at the first drop-down list at “Format cells if…”.
3. Then enter “XYZ” as a specific text in the text box.
4. Change the fill color to white and font color to blue, and make the font style bold in the “Formatting style” section.
5. Click “Done” at the bottom right.

‍

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

1. Select the range. In this case, it should be E4:E13.
2. Choose “Greater than or equal to” at the first drop-down list at “Format cells if…”.
3. Then enter “3000” as a specific text in the text box.
4. 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.
5. Click “Done” at the bottom right.

‍

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.

1. Select the range E4:E13.
2. Choose “Less than or equal to” at the first drop-down list at “Format cells if…”.
3. Then enter “1500” as a specific text in the text box.
4. Change the fill color to light yellow and make the font style bold and italic in the “Formatting style” section.
5. Click “Done” at the bottom right.

‍

How to remove Conditional Formatting from cells

1. Put your cursor on a cell or a range that contains a conditional formatting rule you want to remove.
2. Navigate to “Format” and go to “Conditional formatting”.
3. You have a list of rules applied to the cell.
4. Click the bin icon, which pops up when you put your cursor around.

‍

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.

‍

Other formulas related to Conditional Formatting

If you don’t get the right answer to your question in this article or you have other questions related to Conditional Formatting, you may find the answers in the following articles.

How to Highlight Duplicates in Google Sheets

How to Alternate Row Colors in Google Sheets

Alternate Column Colors in Google Sheets: Explained

You can learn about other Google Sheets formulas and tips that are not mentioned here on this page: LiveFlow‘s How to Guides

‍

Analyze your live financial data in a snap

Are you learning this formula to visualize financial data, build a financial model, or conduct financial analysis? In that case, LiveFlow may help you automate manual workflows and update numbers in real-time. You can access various financial templates on our website, from the simple Income Statement to Multi-Currency Consolidated Financial Statement. You can also customize these templates as you want without breaking the automated data inflow.

To learn more about LiveFlow, book a demo.