Contextures

Excel Conditional Formatting

See how to get started with Excel conditional formatting, or find new ways to use conditional formatting in your Excel workbooks. For example, highlight cells or an entire row, hide duplicate headings, use data bars, or shade alternate rows.

-- Conditional Formatting Basics

-- Video: Highlight Cells Based on Value

-- Data Bars and Icon Sets

-- Format Row Based on One Cell

-- Conditional Formatting Examples

-- More Conditional Formatting Info

Conditional Formatting Basics

With Excel conditional formatting, you can highlight one or cells, by setting rules (conditions) for those cells,

For example, set a rule to turn cells red if they have a number that is below 50

As you can see in this screen shot, conditional formatting makes it easy to identify months with low sales.

simple Conditional Formatting rule

Video: Highlight Cells Based on Value

To make it easy to find cells with high or low numbers, use conditional format the cells, based on their values. For example, in this video, two rules are set for cell formatting:

  • turn cell green if it has a number higher than 75
  • turn cell red if it has a number lower than 50.

For written steps, and a sample Excel workbook, go to the Excel Conditional Formatting Intro page.

Data Bars and Icon Sets

In addition to cell fill colour, you can use other types of conditional formatting on your Excel worksheet.

Conditional Formatting Icon Sets

For example, use an Icon Set with red, yellow and green arrows. That quickly shows if numbers are high, average, or low, compared to other numbers in the list.

conditional formatting icon sets applied

Conditional Formatting Data Bars

If cells contain numbers, you can add conditional formatting data bars, to show the differences among the amounts.

Watch this short video to see how to set up data bars in a cell, and the written instructions are on the Conditional Formatting Data Bars page.

Format Row Based on One Cell

You can apply conditional formatting that checks the value in one cell, and applies formatting to other cells, based on that value. For example, if the values in column B greater than 75, make all data cells in the same row blue.

format row based on cell value

You can watch the steps in this video, and the written instructions are on the Format Row Based on One Cell page.

Conditional Formatting Examples

There are many ways to use conditional formatting, to help you highlight information in your Excel worksheet, or find problems quickly, or even hide information until it is needed.

There are two examples in the videos below, and many more on on the Conditional Formatting Examples page.

Hide Duplicate Headings

In this example, there is a region named entered in each row. With conditional formatting, the second (and subsequent) occurrences of each region name have white font colour, to make the report easier to read.

hide duplicate headings in list

You can watch the steps in this video, and the written instructions are on the Conditional Formatting Examples page.

Highlight Alternate Rows

To make a list easier to read, you can use Excel conditional formatting to shade alternating rows on the worksheet.

hide duplicate headings in list

You can watch the steps in this video, and the written instructions are on the Conditional Formatting Examples page.

More Conditional Format Tutorials

Here are links to more Excel conditional formatting tutorials from Contextures:

Conditional Formatting Documenter

Conditional Formatting Show List and Colors

Conditional Formatting Problem

Conditional Formatting Currency

Pivot Table Conditional Formatting

 

About Debra

 

Last updated: February 26, 2022 1:10 PM