Conditional formatting - Get Digital Help

Conditional Formatting archive

Built-in conditional formatting

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).

Where is Conditional Formatting?
Highlight Cells Rules
Top/Bottom Rules
Conditional Formatting features
Basic Conditional Formatting formulas
Conditional Formatting Partial match
Highlight unique distinct values/records
Highlight duplicates
Comparing lists
Highlight largest/smallest number
Highlight dates
Conditional Formatting examples
What is the downside with Conditional Formatting?
How to sort formatted cells?

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.

2. Highlight Cells Rules

Conditional Formatting Basics
Table of Contents How to add Data Bars to your worksheet How to create color scales How to insert icons […]

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. […]

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 […]

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 […]

3. Top/Bottom Rules

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 […]

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 […]

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 […]

4. Conditional Formatting features

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 […]

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 […]

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, […]

5. Basic Conditional Formatting formulas

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 […]

Highlight empty cells
Table of Contents Highlight empty cells Explaining CF formula How to apply conditional formatting Formulas returning nothing Cells with hidden […]

Highlight text values
The conditional formatting formula applied to cell range F3:F13 highlights all cells containing text. Here is how I did it: […]

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 […]

6. Conditional Formatting Partial match

Highlight lookup values
In this article, I will demonstrate how to search a table using conditional formatting. The criteria highlight matching column and […]

Highlight records [AND logic]
The picture above shows you conditional formatting formula that highlights matching records based on criteria in row 3 and 4. […]

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) […]

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 […]

7. Highlight unique distinct values/records

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 […]

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 […]

Highlight unique distinct records
The image above demonstrates a Conditional Formatting formula that highlights unique distinct records. This means that the first instance of […]

Highlight unique values in a filtered Excel table
The image above demonstrates unique values highlighted in a filtered Excel Table. I will in this article show how to […]

8. Highlight duplicates

How to highlight duplicate values
Table of Contents How to highlight duplicate values Highlight the smallest duplicate number Highlight more than once taken course in […]

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 […]

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 […]

Highlight duplicate records
This article shows you how to easily identify duplicate rows or records in a list. What's on this webpage Highlight […]

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 […]

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, […]

Highlight duplicate columns
This article describes how to highlight duplicate records arranged into a column each, if you are looking for records entered […]

Highlight smallest duplicate number
Question: How do I highlight the smallest duplicate value in a column using conditional formatting? Answer: Conditional formatting formula in […]

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 […]

9. Comparing lists

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 […]

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 […]

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? […]

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 […]

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 […]

How to highlight differences and common values in lists
This article demonstrates techniques to highlight differences and common values across lists. What's on this page How to highlight differences […]

10. Highlight largest/smallest number

Highlight closest number
This post demonstrates how to highlight records with the closest value to a criterion, you can also choose to highlight […]

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 […]

Highlight smallest duplicate number
Question: How do I highlight the smallest duplicate value in a column using conditional formatting? Answer: Conditional formatting formula in […]

11. Highlight dates

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 […]

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 […]

Advanced Date Highlighting Techniques in Excel
This article shows how to highlight dates using Conditional Formatting (CF) techniques. Most of these examples use a CF formula […]

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 […]

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 […]

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 […]

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 […]

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 […]

12. Conditional Formatting examples

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 […]

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 […]

Dynamic formatting
Question: I have a list that I keep adding rows to. How do i create a border that expands as […]

Highlight opposite numbers
Pamela asks: I would like to ask you how to identify PAIR of same numbers, but with a different sign. […]

Advanced Techniques for Conditional Formatting
Table of contents How to change cell formatting using a Drop Down list Highlight cells based on coordinates Highlight every […]

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 […]

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.