What is Conditional Formatting?

Conditional formatting lets you apply cell formatting or built-in signs if a specific condition or critera are met. This lets you easily spot important values or records.

There are a few prebuilt you can choose from but the real fun starts when you create your own conditional formatting formulas.

This image shows color scale conditional formatting applied to a cell range containing degrees ranging from blue (cold) to red (hot).

1. Where is Conditional Formatting?

Go to tab "Home" and click the "Conditional Formatting" button to display the conditional formatting options.

A popup menu appears, here you have all the Conditional Formatting options.

I will below explain the different options you have and demonstrate what is possible with Conditional Formatting. Each category below contains links to articles showing all features.

Category names correspond to the options displayed in the image above. "New Rule.." button takes you to a dialog box with more granular control.

It also contains Conditional formatting formulas, they are really powerful and fun to use. Category 5 to 12 below contain mostly Conditional Formatting formulas.

Back to top

2. Highlight Cells Rules


test
Highlight cells equal to
Excel lets you easily highlight values based on a condition you specify, with a built-in formatting or a custom formatting. […]
test
Highlight cells containing string [Conditional formatting]
Excel allows you to quickly highlight cells containing a given text string. How to apply Conditional Formatting Select cell range. […]
test
Highlight a date occuring [Conditional formatting]
Excel has a built-in feature that allows you to highlight dates if a given condition is met. Section 1 below […]
test
Highlight unique/duplicates [Conditional formatting]
Excel has a few built-in conditional formatting features, one of them highlights values that have at least one duplicate. The […]
Back to top

3. Top/Bottom Rules


test
Highlight top 10 values
The image above shows top 10 values using conditional formatting applied to cell range B2:G13. How to format Select cell […]
test
Highlight top 10 % values
The image above shows top 10% values using conditional formatting applied to cell range B2:G13. Cell range B2:G13 contains 72 […]
test
Highlight above average values
The image above shows values above average using conditional formatting applied to cell range B2:G13. Cell range B2:G13 contains 72 […]
Back to top

4. Conditional Formatting features


test
How to add Data Bars to your worksheet
Data bars allows you to insert a data bar into each cell in the selection, the size is based on […]
test
How to create color scales [Conditional Formatting]
Color scales in conditional formatting applies a color to cells in a cell range based on their values, this lets you […]
test
How to insert icons representing cell values [Conditional formatting]
Conditional formatting lets you insert icons based on cell values, the idea is to make the data easier to read, […]
Back to top

5. Basic Conditional Formatting formulas


test
Working with Conditional Formatting formulas
In this post I am going to try to explain formula basics in conditional formatting. It is really good if […]
test
Highlight empty cells
Table of Contents Highlight empty cells Explaining CF formula How to apply conditional formatting Formulas returning nothing Cells with hidden […]
test
Highlight text values
The conditional formatting formula applied to cell range F3:F13 highlights all cells containing text. Here is how I did it: […]
test
Find numbers in close proximity to a given number
This article demonstrates how to apply Conditional Formatting formula to a cell range, it finds cells that are in close […]
Back to top

6. Conditional Formatting Partial match


test
Highlight lookup values
In this article, I will demonstrate how to search a table using conditional formatting. The criteria highlight matching column and […]
test
Highlight records [AND logic]
The picture above shows you conditional formatting formula that highlights matching records based on criteria in row 3 and 4. […]
test
Highlight records containing text strings (AND Logic)
The picture above shows you how to highlight rows containing text strings using conditional formatting. Example, continents criterion (cell B3) […]
test
Highlight records – multiple criteria [OR logic]
The image above shows you how to highlight rows with multiple criteria using OR logic. The criteria are found in […]
Back to top

7. Highlight unique distinct values/records


test
How to highlight unique distinct values
The image above shows conditional formatting highlighting unique distinct values, duplicates are not highlighted. Conditional Formatting Formula: =COUNTIF($B$3:B3, B3)=1 The […]
test
Highlight unique values and unique distinct values in a cell range
The following formula highlights cells that contain unique distinct values, in other words, all duplicate values except the first instance […]
test
Highlight unique distinct records
The image above demonstrates a Conditional Formatting formula that highlights unique distinct records. This means that the first instance of […]
test
Highlight unique values in a filtered Excel table
This article demonstrates a conditional formatting formula that allows you to highlight unique values based on a set of filtered […]
Back to top

8. Highlight duplicates


test
How to highlight duplicate values
Table of Contents How to highlight duplicate values Highlight the smallest duplicate number 1. How to highlight duplicate values The […]
test
Highlight duplicates in two columns
Question: I need to delete duplicates in two different columns together. How do I highlight the second or more duplicates […]
test
Highlight duplicate values in a cell range
The following conditional formula highlights only the second instance or more of a value in a cell range. Conditional formatting […]
test
Highlight duplicate records
This article shows you how to easily identify duplicate rows or records in a list. What's on this webpage Highlight […]
test
Highlight more than once taken course in any given day
Question: My scenario is tracking employees who complete online training. I capture their name, id, class taken, and date. Any […]
test
Highlight duplicates with same date, week or month
The image above demonstrates a conditional formatting formula that highlights duplicate items based on date. The first instance is not highlighted, […]
test
Highlight duplicate columns
This article describes how to highlight duplicate records arranged into a column each, if you are looking for records entered […]
test
Highlight smallest duplicate number
Question: How do I highlight the smallest duplicate value in a column using conditional formatting? Answer: Conditional formatting formula in […]
test
Highlight duplicates in a filtered Excel Table
The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]
Back to top

9. Comparing lists


test
Compare two columns and highlight values in common
A conditional formatting formula highlights values in column B that also exist in column D. =COUNTIF($D$3:$D$7,B3) The same thing happens […]
test
Compare two columns and highlight differences
This article demonstrates a conditional formatting formula that will highlight the differences between two columns. The image above shows two […]
test
Highlight missing values between to columns
The picture above shows two lists. How do I highlight values in List 1 that are not in List 2? […]
test
Compare tables: Highlight records not in both tables
The image above demonstrates a conditional formatting formula that highlights records that only exist in one table. There are two […]
test
Compare two lists of data: Highlight common records
In this blog post I will demonstrate a conditional formatting formula that will highlight common records in two lists. The […]
test
How to highlight differences in price lists
Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]
Back to top

10. Highlight largest/smallest number


test
Highlight closest number
This post demonstrates how to highlight records with the closest value to a criterion, you can also choose to highlight […]
test
How to highlight MAX and MIN value based on month
The image above shows rows highlighted based on value in column C being the largest or smallest in that particular […]
test
Highlight smallest duplicate number
Question: How do I highlight the smallest duplicate value in a column using conditional formatting? Answer: Conditional formatting formula in […]
Back to top

11. Highlight dates


test
How to highlight weekends [Conditional Formatting]
The image above shows conditional formatting applied to cell range C8:C20, it highlights cells containing dates that fall on Saturdays […]
test
How to highlight dates based on day of week
The following conditional formatting formula highlights dates based on day of the week. =TEXT(B3,"DDDD")=$D$3 The TEXT function converts a value […]
test
Highlight dates in a date range
Question: How do I highlight dates that meet criteria using conditional formatting? Table of contents Highlight values in a column […]
test
Highlight overlapping date ranges using conditional formatting
The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]
test
Highlight records based on overlapping date ranges and a condition
adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]
test
Highlight current date
Cell range B3:B14 has conditional formatting applied, the formula checks if the date is today. Conditional formatting formula: =TODAY()=B3 A […]
test
How to highlight MAX and MIN value based on month
The image above shows rows highlighted based on value in column C being the largest or smallest in that particular […]
test
Highlight odd/even months
Color odd months Conditional formatting formula: =MOD(MONTH($B6),2) Explaining CF formula in cell B6 Step 1 - Calculate number of month The […]
Back to top

12. Conditional Formatting examples


test
Highlight every other row
Here is how to highlight every other row using conditional formatting. Conditional formatting formula: =ISEVEN(ROW())*OR($B3:$D3<>"") Alternative CF formula: =EVEN(ROW())=ROW() This […]
test
Highlight cells based on coordinates
The picture above shows Conditional Formatting highlighting cells in cell range F3:Y22 based on row and column values in column B […]
test
Dynamic formatting
Question: I have a list that I keep adding rows to. How do i create a border that expands as […]
test
Highlight opposite numbers
Pamela asks: I would like to ask you how to identify PAIR of same numbers, but with a different sign. […]
test
How to change cell formatting using a Drop Down list
This article demonstrates how to apply different cell formatting to a cell range based on a Drop Down list, column […]
test
Highlight cells based on ranges
This article demonstrates a Conditional Formatting formula that lets you highlight cells based on numerical ranges specified in an Excel […]
Back to top

13. What is the downside with Conditional Formatting?

Note: Conditional formatting is volatile meaning when the worksheet is recalculated all conditionally formatted cells are recalculated, this may slow down your worksheet considerably.

Back to top

14. How to sort formatted cells?

Did you know that you can sort formatted cells to the top of a list? Simply right click on a cell and click "Sort" -> "Put Selected Cell On Top".

Back to top


Excel categories


Home page
Latest updated articles.
Excel Functions
More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.
Excel Formulas
More than 1300 formulas organized in subcategories.
Excel Tables
Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.
Advanced Filter
Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.
Data Validation
Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.
Drop Down List
Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.
Named Ranges
Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.
Excel Solver
The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.
Charts
An Excel feature that lets you visualize data in a graph.
Conditonal Formatting
Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.
Pivot Tables
Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.
VBA
VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.
Macros
A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.
UDFs
UDF stands for User Defined Functions and is custom built functions anyone can create.
Archive
A list of all published articles.