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 to learn how to apply conditional formatting to a column according to values in another column.

‍

Analyze your live financial data in a snap

Are you learning conditional formatting to process financial data, build a financial model, or conduct financial analysis? If so, LiveFlow may help you to automate manual workflows and keep numbers updated in real time. For instance, you can easily compare your Budget versus Actual without any manual work. Furthermore, you can access other templates here, from the simple Income Statement and Operational KPI templates to Multi-Currency Consolidated Income Statement, Balance Sheet, and Cash Flow templates. You can also customize these templates as you want without breaking the automated data inflow.

‍

To learn more about LiveFlow, book a demo.