The Ultimate Guide to Conditional Formatting in Google Sheets

Conditional Formatting is one of the handiest tools in spreadsheet software that can boost your efficiency and productivity. It helps your data become more visual and intuitive without requiring a lot of input.

Here at FileDrop Blog, we are obsessed with how much time we can save using software. So if you want to know how to use conditional formatting plus the best practices for implementation, scroll down below to learn more.

An Introduction to Conditional Formatting in Google Sheets

What is conditional formatting? Let’s start with the textbook fundamentals.

Conditional formatting is an automated system that formats your cells, columns, or rows to change formatting when your specified conditions have been met.

There are three components to your conditional formatting rules.

1. Range. This refers to the selection of cells, columns, or rows where custom formatting can be applied to.
2. Formatting Rules. This refers to logical conditions that trigger your automatic styles.
3. Formatting Style. These are styles involving typography formats and color that will be applied to the cell, column, or row.

Any or a combination of the following formatting can be applied:

• 1. Bold
  2. Italic
  3. Underline
  4. Strikethrough
  5. Text color
  6. Fill color
  7. Color scale

Format Rules in Google Sheets

The following are preset rules that can be used on your selected range for conditional formatting:

1. Cell
   1. is not empty.
   2. is empty.
2. Text
   1. If text contains [specified value or formula].
   2. If text does not contain
   3. If text starts with
   4. If text ends with
3. Date
   1. If date is [exact date; today, tomorrow, or yesterday; within the past week, month, year]
   2. If date is before
   3. If date is after
4. Value
   1. Greater than [specified value or formula]
   2. Greater than or equal to
   3. Less than
   4. Less than or equal to
   5. Is equal to
   6. Is not equal to
   7. Is between [two specified values or formulas]
   8. Is not between [two specified values or formulas]

Getting Started With Conditional Formatting

To find Conditional Formatting, follow these steps:

Step 1. Go to Format on your top menu bar.

Step 2. Select Conditional formatting.

Applying Conditional Formatting to Your Range

To demonstrate how conditional formatting is configured, we’ll use an exercise sheet for a fictional apparel brand’s product inventory. We’ll use triggers to change cell fill color depending on the color of the shirt described on the Color column.

Step 1. Open the conditional format sidebar. We’re going to run the Conditional formatting menu by going to Format > Conditional formatting. Here you’ll see the conditional formatting sidebar.

Step 2. Selecting our cells. First, we’re going to select our Range. In this case, we’ll manually select the cells from our Color column. Click on the cell icon to the right of the range input box as highlighted below:

Confirm that the cells are selected by looking at your spreadsheet. Since we don’t want to apply conditional formatting to the column title, we’re going to select just the cells below it. The default formatting should immediately be previewed on your sheet.

Step 3. Create format rules. We’re going to configure our first trigger. In this exercise we want to change the cell color to orange if the cell content says exactly Orange.

3.1. To do this, go to the dropdown under Format rules.

3.2. Select format cells if… Text is exactly. For our example, we’ll type in Orange.

Why did we choose Text is exactly instead of Text contains? If the Apparel inventory sheet has other colors like Bright Orange or Yellow-Orange, we do not want our conditional formatting to be triggered. The same goes for Blue vs Light Blue, Navy Blue, Royal Blue, etc… you get the idea!

Step 4. Set the format style. For this demo, we want to be very visual. We want cells that say Orange to also look orange.

Select Fill Color and pick an orange value you like.

After making adjustments, your cells adjust automatically. Now we’ve created the first trigger for this exercise!

Bonus Tip: Work on Your Color Combinations for Better Contrast

Best practice for filling color styles. Change text color if you have to improve legibility when you use fill colors.

If your fill color is a dark shade of orange and the text is black, your spreadsheet will display poorly due to lack of contrast. Instead, if the fill is dark, choose a light text color like White.

You don’t have to stick with the preset color sets. You can choose a custom color using the built-in HSV (Hue-Saturation-Value) color picker.

You can find the HSV picker by going to Fill or Text color > Custom at the bottom of the menu.

Use More Than One Conditional Formatting Rule for the Same Cell Range in Your Spreadsheet

You can stack multiple rules for the same column, row, or cell. In our given example, we also need to create additional rules to automatically stylize other color names.

Create additional rules. To start adding more triggers, go to the Conditional format sidebar and click + Add another rule.

Tip: Copy and paste the range from your current rule to your new rule’s Apply to range box to save time.

Here’s what stacked rules look like when you click on an affected cell:

And this is how the column should look like if you applied your rules correctly:

Make Changes to Your Existing Conditional Formatting Rules

To edit your present rules, open the Conditional format rules sidebar by going to Format > Conditional format.

Click on a rule and make edits to your cell range, format rules, or formatting style as needed.

Use Color Scales Instead of Single Color Rules

Some spreadsheets can be better served by scaling colors rather than selecting colors one-by-one on multiple rules. An example of this is when visualizing numerical data.

To use Color scale, select it from your Conditional format sidebar beside Single color.

You are able to configure the midpoint, midpoint, or maxpoint to create your color scale gradient. The color will automatically shift from your selected midpoint and maxpoint. If you find that quite confusing, here’s an example of a scale moving from blue to red. A use case for this color set is temperature and intensity data.

You can also use the default configuration of moving from a dark color to a light color:

Another setup is using percentiles or percentage instead of minpoint and maxpoint.

Conclusion on The Ultimate Guide to Conditional Formatting in Google Sheets

Conditional formatting is a powerful and fun way to visualize your spreadsheet data on Google Sheets. It allows you to create triggers that automate your formatting styles so you can save a lot of time and make your data analysis in Sheets more efficient.

Today, we’ve learned how to apply single color and color scale rules along with concrete examples and a couple of best practices in implementing your conditional formatting to your data.

Supercharge your Google Sheets Productivity with FileDrop

Get even more productive and efficient on Google Sheets! Download FileDrop, a powerful Google Sheets and Docs add-on designed to integrate your data more tightly with your files on Google Drive. You can use it to insert PDFs, images, and other files to cells using drag and drop to give your spreadsheets more utility and customization.

Here are the core features of FileDrop:

✅Insert PDF, images, and other files into cells
✅Insert files from Drive, your local storage, or web links
✅Use powerful optical character recognition (OCR) tools to convert PDFs and Images to text
✅Extract table data from PDFs
✅Translate OCR text to over 50 languages