Google Sheets Features

Upsides and Downsides of Google Sheets Conditional Formatting

Format your data based on certain conditions. Learn how to use Conditional formatting to quickly identify patterns and trends in your data. But there are downsides to Conditional formatting too.

Google Sheets Conditional Formatting allows you to format your data based on certain conditions. Learn how to use it to quickly identify patterns and trends in your data and take your sheets to new heights. Discover it now!

Conditional formatting is a useful tool in Google Sheets for highlighting and identifying patterns and trends in data. Some of the best use cases for conditional formatting include:

Highlighting cells that meet certain conditions: For example, you can use conditional formatting to highlight cells that contain values above or below a certain threshold, or that match a certain text string.
Identifying duplicates: You can use conditional formatting to quickly identify duplicate values in a range of cells.
Color-coding data: You can use conditional formatting to color-code cells based on their values, making it easier to identify patterns and trends in the data.
Comparing data: You can use conditional formatting to compare data across different columns or rows, such as comparing actual sales to projected sales.
Identifying errors: You can use conditional formatting to highlight cells that contain errors, such as invalid data or formulas that return errors.
Highlighting specific values: You can use conditional formatting to highlight cells that contain specific values, such as important dates or keywords.
Highlighting data that meet multiple conditions: You can use nested conditional formatting to highlight cells that meet multiple conditions.
Data visualization: Conditional formatting can be used to create simple yet effective data visualization, such as heat maps, color-coded maps, etc.

It's important to keep in mind that while conditional formatting can be a useful tool, it should be used in a meaningful way that supports the data analysis and not just for the sake of using it.

Conditional Formatting Downsides

The dark side of this awesome Google Sheets tool

What are the downsides to using conditional formatting in Google sheets?

Complexity can get overwhelming
Performance may slow down your sheet
Limited Options
Color Blindness
Incorrect usage

Let's go a bit deeper.

Conditional Formatting May Get Too Complex

Conditional formatting can become complex when trying to apply multiple rules or nested conditions. This can make it difficult to understand and manage the formatting rules. One of my favorite things to do with conditional formatting is to go beyond the clickable options and use the Custom Formula section to create conditional formatting. But for some this can be overwhelming. And for those who might have to manage your sheets after you've left an organization can be daunting to change, or update.

Your Sheets' Performance Could Degrade

When working with large datasets, conditional formatting can slow down the performance of the sheet. This is because the rules are constantly being reapplied as the data is updated.

And it's reapplied each and every time you edit a cell. The entire conditional formatting rule will check. Even with a few thousand rows, perhaps well under 1 million cells, you'll see the progress bar appear for condional formatting.

While a sheet technically has a 10,000,000 cell limit, the performance of your sheets along with Conditional formatting will degrade far before that limit is reached.

Conditional Formatting has Limited Options

While conditional formatting is useful for highlighting certain data, it may not be suitable for more advanced data visualization needs. There may be limited options for customizing the formatting or styling.

Color Blindness

Conditional formatting uses color as the main way to differentiate the data, if any of your sheets' users are color blind, they may have difficulty understanding the data. But not just color blind. There are shades of color blindness. Some that even we may not even be aware of.

Color blindness could mean that someone can't tell two different colors apart, or see slight variations in shade. A common usage of Conditional formatting is to create a gradient from high to low or vice versa. This could cause issues if you really do want to see some kind of rank.

Use the RANK() or SORT() formulas to distinguish this. Those formulas are better than using a gradient with Conditional Formatting.

💡

Discover more formulas at BetterSheets.co/formulas

Incorrectly Using Conditional Formatting

If the conditional formatting is not used correctly, it can lead to confusion and misinterpretation of the data. It's important to use it in a meaningful way that supports the data analysis and not to use it just for the sake of using it.

In Conclusion

Conditional formatting is a powerful tool in Google Sheets that allows you to quickly identify patterns and trends in your data.

By using a combination of conditions and formatting styles, you can make your data more meaningful and easier to understand.

From highlighting cells that meet certain conditions, to comparing data, to creating simple yet effective data visualization, the possibilities are endless.

With a little bit of practice, you'll be able to master the art of conditional formatting in no time.

Remember to use it in a meaningful way that supports the data analysis, and not just for the sake of using it.

And don't be afraid to experiment and try out new things, you never know what insights you might uncover.

Happy Google Sheeting!