Transform Your Spreadsheets with Conditional Formatting

Hey guys! 👋

In this edition, we'll explore conditional formatting, a powerful feature that can make your data pop.

It's like giving your spreadsheet a pair of glasses, highlighting the most important details and helping you see your data in a new light. Whether you track expenses, manage a project, or analyse survey results, conditional formatting can help you quickly identify trends and outliers.

Let's get started!

❓ What is conditional formatting?

Conditional formatting is a feature that changes the appearance of cells in your spreadsheet based on the values they contain.

It's like setting up rules that automatically apply formatting - such as colours, icons, or data bars - when certain conditions are met.

Use cases for conditional formatting are almost endless; here are a few examples to get you thinking:

• Highlighting expenses over a certain amount

• Tracking project deadlines

• Analysing sales performance

• Grading or scoring results

• Monitoring inventory levels

• Identifying duplicate data

• Visualising data trends

🎓 How to use conditional formatting: 3 easy steps

Getting started with conditional formatting is easy. Here's a simple guide on how to do this in Microsoft Excel.

Imagine you have a list of monthly sales figures and want to highlight any sales greater than $10,000 in green to identify high-performing months quickly.

Also, feel free to download the example data to follow along.

Step 1: Select your cells

First, highlight the range of cells you want to format, e.g. B2:B13.

💡 Note that cells do not have to contain numeric data - you can also apply conditional formatting to cells that contain text!

Step 2: Access the conditional formatting tools

Head on over to the tools at Home > Conditional Formatting.

Step 3: Specify a rule

There are quite a few preset formatting rules that you can quickly choose from. There are further options for the Highlight Cells Rules or Top/Bottom Rules like Greater Than, Less Than, Between, or Text That Contains.

For something a bit different, you can apply Data Bars, Color Scales, or Icon Sets to identify patterns in your data quickly.

Or, if you need more customisation, you can create your own rule by selecting New Rule and using a custom formula to define specific conditions.

For this example, we want to highlight cells with a value greater than $10,000. So, let’s go with the Highlight Cells Rules > Greater Than… option.

Then, it’s a case of simply specifying the value and selecting a format in which cells should appear if they satisfy this criteria.

So now only those cells that contain values greater than $10,000 appear in green.

These rules are dynamic, meaning that if you change some of the values in the data, the formatting will update automatically—cool, eh?

💡 Pro tips for mastering conditional formatting

Create multiple rules

Did you know you can apply multiple conditional formatting rules to the same cell?

Layer different rules to emphasise various aspects of your data. For example, use one colour for high values and another for low values.

Paint formatting rules

Use the Format Painter tool (which looks like a paintbrush) in the Clipboard area to quickly apply the same conditional formatting rules to other ranges.

Click on the cell containing the conditional formatting you want to copy, hit the Format Painter tool, and then click on a new cell to copy the formatting onto it.

Experiment with custom formulas

Unlock advanced possibilities with custom formulas.

For example, suppose you have a dataset with sales targets in column B and actual sales in column C. To highlight rows where the actual sales (column C) are lower than the sales target (column B), use a custom formula like =C1<B1. Apply this rule to the range covering both columns (e.g., B2) to spot where targets weren’t met easily.

🎓 Want to learn more?

Check out my video tutorial below for more super easy conditional formatting examples!

I hope you found this article useful!

Until next time.

Stay sheet smart!

Steven