- What is conditional formatting in Excel?
- How does conditional formatting work in Excel?
- Basic types of conditional formatting in Excel
- Advanced types of conditional formatting in Excel
Excel represents the go-to solution for data storage and representation for most business teams. Whether you work in finance, human resources, or marketing, the amount of data is definitely going to determine your analysis and visualization tasks. Thankfully, Excel offers a feature known as conditional formatting, which helps users organize and manage their whole data set in a few clicks. To make the most out of this rule-based feature, you must become familiar with how to use it correctly.
In this complete guide, you’ll learn what conditional formatting is and the many uses it offers in Excel. You’ll learn the basics of conditional formatting in Excel and also learn its more advanced uses when combined with IF or AND formulas. What is great about this feature in Excel is that it can be as basic or as complex as you need, depending on the level of customization your data requires. From setting multiple conditions to formatting rules to selecting which spreadsheet data it should apply to, conditional formatting is one of the most valuable features in Excel to boost productivity at all company levels.
What is conditional formatting in Excel?
Conditional formatting is a spreadsheet application feature that allows users to format data following a set of built-in or customized conditions. The most common use is to highlight key information through color-coding or remove duplicate content in one click.
The conditions or rules you set will apply to a cell or cell range, which can include columns; this means that the data in the cell will automatically change to the value specified in the rule. For example, you can ask Excel to highlight (for example, in the color green) all cells that contain a numeric value “greater than” the amount specified. This type of condition will change the color format, but you can add other formatting aspects, such as the font size or style.
How does conditional formatting work in Excel?
Conditional formatting changes the formatting of your data with conditions that act as “If/Then” statements. You set the condition of the rule to a specified cell range, and then determine the formatting style which will be applied to the cells that fulfill this rule. In other words, “If (data range) is (condition of rule), then (formatting style)” is applied.
Depending on the complexity of the rule you would like to apply, from basic to more advanced, you can create the following types of conditions in Excel:
- Built-in conditions: Excel offers a great variety of built-in conditions that range from highlighting cells to establishing top/bottom rules, which can be applied to different types of data, from numbers, text, to dates.
- Custom conditions: These can be used by combining Excel formulas with the built-in conditions. By selecting the “New Rule” option, you can include any formulae, such as =AND or =IF, to manage the data that you would like to apply built-in conditions to.
- Multiple conditions: In case you need to format-specific data simultaneously, Excel allows you to use formulae to apply various built-in conditions at the same time.
How to Use Conditional Formatting in Google Sheets?
Google Sheet allows you to make data more readable by changing the color of cells based on their value. Here's how to use conditional formatting in Google Sheets.READ MORE
Basic types of conditional formatting in Excel
We’ll start by showing the most popular conditional formatting types in Excel based on their ease of use and applicability. Note that the examples shown are based on the Excel desktop app, since you can only apply conditional formatting in this version. The cloud-based application in Office 365 will only let you view, but not edit or create, new rules.
How do I change cell color based on cell value using conditional formatting?
This type of conditional formatting is one of the most commonly used in Excel since it helps to highlight key information and to structure your data visually so that it becomes more digestible, especially when dealing with large datasets.
For example, let’s say I work in the sales department of an international car dealership, and I need to report which car models have yielded above the expected profit margin. This is how to apply conditional formatting to change the cell color based on the cell value:
- 1. Select the cell range you would like to apply the rule to. Here, we will select a cell range within the ‘Profit 5%’ column. Head to Conditional Formatting > Highlight Cells Rules > Greater Than.
- 2. Before setting the color, enter the condition that the data should meet. In this case, the cell value should be greater than 1,000. Now, add the formatting style. Here, let’s format all cells that meet the condition with the pre-set formatting “Green Fill with Dark Green Text”.
- 3. Click “OK”. All cells above the specified value will be color-formatted as specified.
As you will be able to see, Excel provides a full range of color formatting rules that can be applied to any data type, not only numbers. Moreover, the last highlighting rule applies to duplicate values, which is useful for later removal or simply as a way of quickly visualizing repeat information. You will find our article on How to Find and Remove Duplicates in Excel very useful.
How do I conditionally format a column in Excel?
In the same way that applying a conditional formatting rule to a cell or cell range, you can also apply it to a column - simply highlight the entire column before using the feature. Here’s how to conditionally format a column in Excel using the “Top/Bottom Rules”.
- 1. Highlight the column you would like to conditional format. Go to Conditional Formatting > Top/Bottom Rules > Below Average. In this case, I want to highlight the profit value that is below the average, based on the values provided in the column.
- 2. In the dialog box, simply change the highlighting format according to your preference. Since I’m going for a negative value, I will use “Light Red Fill with Dark Red”.
- 3. Excel will automatically highlight in red the profit values that are below the average.
Advanced types of conditional formatting in Excel
Now that we have seen the basic uses of conditional formatting in Excel, let’s explore more advanced options that will let you customize the rules according to your specific needs.
Can I use Excel conditional formatting with a formula?
So far, we have seen examples of using conditional formatting in Excel with built-in rules, such as “Highlight Cells Rules” or “Top/Bottom Rules”. But what if you want to apply more than one rule at the same time? Luckily, you are already using Excel and this means that you have a range of formulae to apply. Let’s see how the IF formula can be used with conditional formatting.
=COUNTIF formula to highlight duplicates
If you would like to conditionally format cells that contain duplicate values, you can use the built-in rule in Conditional formatting > Highlight Cells Rules > Duplicate Values…
However, you may also want to conditionally format the other columns or rows containing those duplicate values. The =COUNTIF formula in Excel is used to count the number of cells within a selected range that meet a specified criterion.
=COUNTIF formula syntax
Let’s see how the =COUNTIF formula works by explaining its parameters:
- Range: Refers to the cell range we are applying the formula and rule to.
- Criteria: Refers to the specifications that the range needs to meet to be counted or considered by the rule.
Conditional formatting using COUNTIF: how to remove duplicates
This is how you can highlight duplicate values using the =COUNTIF formula in Excel:
- 1. Select your cell range and go to Conditional Formatting > New Rule.
- 2. A dialog box will pop up. Select “Classic” in the “Style” field, and “Use a formula…” from the drop-down menu.
- 3. Below, in the formula field, enter your formula. In this example, I’ll enter =COUNTIF(A1, “2006”), where the range is A1, corresponding to the ‘YEAR’ column, and “2006” the value I would like to find the duplicates of.
- 4. Set the format to the color of your preference. Here I have set it to yellow.
Once you click “OK”, you should be able to see all 2006 duplicates in yellow.
How to use Excel conditional formatting when applying multiple conditions?
We have now used an Excel formula, =COUNTIF, with the “Duplicate Cells” conditional formatting rule. Let’s now illustrate how multiple conditions can be applied based on the type of data and the type of formatting we would like to apply.
AND Formula to apply multiple conditions
The AND formula is also a logical function that tests the conditions to return one of the true/false values, depending on whether they are met or not.
Following the use case, let’s say that I want to conditionally format the VIN (Vehicle Identification Number) values or registered cars from 2006 onwards and that have yielded a profit value above the minimum, $1,153.00.
- 1. Follow steps 1 & 2 shown in the previous section to create a new rule. Don’t forget to select the cell range you wish to apply the multiple conditions to.
- 2. Include the AND formula in the corresponding box. In this example, I’ll input the formula “=AND(C1>=2006, F1>=1,153).
- 3. Change the formatting style to your preference. Here, I have chosen the “Green Fill with Dark Green Text”.
- 4. Press “OK” to apply your conditions. As shown below, Excel highlights in green only the cell values that meet the multiple conditions set in the AND formula.
There are many ways to combine multiple columns into a single column in Excel. Here's how to do it without losing any dataREAD MORE
How do I conditionally format a cell based on another cell in Excel?
What if, instead of applying a rule to a given value, you want to base it on the value of another cell in the spreadsheet? This cell value will act as the fixed value when applying the highlighting rule. If we continue with the car profit margin example, the highlight was included in the formatting when a numeric value exceeded the expected profit value. However, since the profit margin value might change over time, we might prefer to create a rule that strictly depends on a controlled value. This is how you can conditionally format an Excel cell based on another cell.
- 1. In the same worksheet, add your controlled value to a cell. Here, I will add my controlled value (‘Min profit’) of $1,153.00 (Column H) to the right of my current table.
- 2. Go to Conditional Formatting > New Rule.
- 3. A dialog box will pop up, select “Classic” in the “Style” field, and “Use a formula to determine which cells to format” from the drop-down menu.
- 4. Below, in the formula field, select the cell containing your controlled value, here shown as “$H$2”.
Any value above the one specified will now be highlighted according to this conditional formatting rule. Now you know how to create a dynamic rule, as the minimum profit changes, the rule will adjust to the fixed value.
Conditional formatting in Excel has proven to be a versatile tool. As this guide has shown, the built-in rules already save you time on repetitive daily tasks by performing editing actions in bulk, such as color-coding or removing duplicate cells. If you would like Excel to perform more advanced actions, where multiple conditions or more sophisticated criteria need to be applied, it will also allow you to combine these with formulae.
You can now save time in cleaning and organizing large or smaller datasets. Hopefully, this guide has shown that an Excel spreadsheet goes beyond a data storage system and represents an all-around reference for business tasks related to planning, organizing, and decision-making.
If you would like to try conditional formatting functionality in Google Sheets, read our article on How to Use Conditional Formatting in Google Sheets.