When you're writing a blog post, an email, or a Slack message, you use formatting to make it more readable. Simple things like bolding text and splitting up paragraphs goes a long way.
But what about when you're working in a spreadsheet? How can you highlight important or insightful sections of a large data set? How can you add visual flair to certain rows and columns that will not only make the spreadsheet more visually pleasing but also more useful?
The answer: conditional formatting.
Here, we'll walk through how to use conditional formatting in Excel Online. To follow along, use our demo sheet. Open the spreadsheet in your browser, and select Save to OneDrive. Then log in to your OneDrive account, and you'll be able to manipulate the data from there.
What Is Conditional Formatting?
Just as it sounds, Excel's conditional formatting feature lets you dynamically change the format of a cell (the background or the text), based on the rules that you've set. A rule in Excel Online works as an if this, then that statement.
Here's an example from our demo sheet. Let's say that we want to highlight all unit sales under 100 by changing the cell's background color to red. The conditional formatting rule would be something like "If any cell value for the selected cell range is less than 100, change the background color to red." Here's what that would look like:
Every conditional formatting rule is made up of three parts.
Range: You start out by selecting the cells to which the rule will apply. This might be your entire spreadsheet or just a selection of rows or columns.
Condition: This is the "if" part of the if/then clause. You can choose from more than a dozen options, including greater than, less than, between, and so on.
Formatting: This is the "then" part of the if/then clause. Excel Online has a default styling for every condition, but you are free to customize it.
How to Use Conditional Formatting in Excel
There are numerous combinations of the formatting condition and styles, but the process is the same every time. Here's an overview:
Select a range.
Go to the Home section, and click Conditional Formatting.
Select the rule (and, if you want, customize the condition).
Select the formatting style.
Click OK.
Follow along below for more detailed instructions using our demo sheet. In the steps below, we'll outline how to highlight employees that made more than 140 units of sale in 2019.
1. Select a Range
First, go to the top of the spreadsheet and disable the Simplified Ribbon feature. This will make the Conditional Formatting button easier to spot.
Then, before even opening the conditional formatting toolbar, highlight the range of data you're working with. This can be a row, a column, or even the entire sheet. In this case, it'll be column C.
2. Select the Rule
Once the range is selected, click the Conditional Formatting button from the Home section in the toolbar.
From the dropdown, you'll see the two basic rules at the top: Highlight Cell Rules and Top/Bottom Rules.
In the Highlight Cell Rules section, you'll find the following options:
Greater Than
Less Than
Between
Equal To
Text That Contains
A Date Occurring
Duplicate Values
In the Top/Bottom Rules section, you'll find the following options:
Top 10 Items
Top 10%
Bottom 10 Items
Bottom 10%
Above Average
Below Average
Pick the rule that suits your needs. In this example, we are using the Greater Than rule. So click Highlight Cell Rules > Greater Than.
3. Select Formatting
Selecting your condition will bring up a formatting box. On the left, enter a value in the Format cells that are Greater Than section. In this instance, we'll enter 140 since we want to highlight employees that made more than 140 units of sale.
Next to it, you'll see the dropdown for the formatting style. You can select among the following options:
Light Red Fill with Dark Red Text
Yellow Fill with Dark Yellow Text
Green Fill with Dark Green Text
Light Red Fill
Red Text
Red Border
These formatting options are common for all rules. In this example, we're highlighting a positive outcome (a sale), so we'll select Green Fill with Dark Green Text.
Click OK, and you'll see the relevant fields highlighted with the green fill and dark green text.
You can set unlimited conditional formatting rules in a spreadsheet; you can even set multiple rules for the same cell range. Excel Online prioritizes the newest rule, so if you apply different style rules to the same cells, the new rule will override the old one.
To clear all rules, go to Conditional Formatting > Clear Rules and select Clear Rules from Entire Sheet. If you only want to clear rules for the selected range, click Clear Rules from Selected Cells.
Types of Formatting Rules and Styles
Now that you know how to create conditional formatting rules, we'll explore all the available formatting options in Excel Online.
Highlight Cell Rules
The highlight cell rules like Greater Than, Less Than, Equal To, and Between are self-explanatory using the walkthrough we just provided. So let's look at the last three options.
Text That Contains: This rule will help you highlight cells that contain particular text. This can be useful if you want to see how many times a name comes up. In our demo sheet, we can use this rule to visually separate employees from Tampa.
A Date Occurring: This is a dynamic rule. You can highlight cells that contain the date from last month, last week, next month, and so on.
Duplicate Values: This rule is used to find both duplicate and unique values. Select the range and from the options box, select between Duplicate and Unique to apply the rule.
Top/Bottom Rules
Top/Bottom Rules help you quickly highlight the best and the worst performers from a selected range, no math required.
There are no customization options for the Above Average and Below Average rules, but the Top 10% and Top 10 rules can be customized.
When you click on Top 10 Items, you'll be able to change it to any number that you want—that is, you can highlight the top one item, top five items, or top 100 items. In our demo sheet, we can use this rule to highlight the top three performers based on unit sales.
It's the same story with the Top 10%, Bottom 10 Items, and Bottom 10% rules.
Data Bars, Color Scales, and Icon Sets
The last three options in the Conditional Formatting section are purely visual.
Data Bars adds a colorful bar graph view in the cell background. You can choose from a selection of gradient colors and solid colors. Highlight your cell range and apply a Data Bars rule to visually see the data in bar graph format. The longer the bar, the higher the number.
Color Scales is even more interesting. Once you pick your cell range and select a preset, it will automatically apply a color-based hierarchy system so you don't have to.
There are multiple scale options available. The first one is the most commonly used Green, Yellow, Red scale. When applied, it will add a green background to the cells with the highest cell values and red background for the lowest cell values. The numbers in between will be split using shades of orange and yellow, depending on the numeric values. Excel Online features twelve different formats for Color Scales.
Icon Sets is the last option in the conditional formatting section, and it's best used on top of an existing formatting rule. This feature adds small icons at the edge of the cell. Just like the Color Scales feature, the icons are added dynamically based on the numeric value and will change when the values are edited. You can pick between arrows, shapes, colorful circles, ratios, and indicator icons.
That covers the basics of Excel conditional formatting. Now that you know your options, use our demo sheet to play around with the feature. And if you run into problems, here's how to fix the most common errors in Excel.
