How to use Conditional Formatting in Excel

In this lesson, you will learn how to use conditional formatting.

Excel is a powerful tool for capturing, analyzing, and visualizing data. With countless tools at your disposal, you can leverage Excel to tell accurate stories with the data you have available. Conditional formatting is a popular Excel tool that takes visualization one step further.

Rather than diving into the details of the data, conditional formatting allows you to efficiently understand data in relation to the rules and parameters you set. For example, you can set conditional formatting to visually identify values that either increase or decrease when compared to something else in the sheet. Let's take a look at how that happens.

Conditional formatting is a change in cell size provided that any condition is met. For example, Excel can change the color of all cells whose value is greater than 100. Conditional formatting is very useful for working with Excel, so now you'll learn how to use it.

Basic conditional formatting

I prepared a table with the results of the exam.

Excel Conditional Formatting Table Results Exam

You want to highlight the extreme results. The best results are above the 90% mark in green and below 30% in red. How to do it?

The conditional formatting button is at the center of the Excel ribbon.

Excel Conditional Formatting Button Ribbon

Start with the best students. All results with a score of 90% or higher want to paint the color green. Click on the button Conditional Formatting on the ribbon and select Greater Than.

Excel Conditional Formatting Format Rule Greater Than

A dialog box appears. The entry requirement is 90%. Select the format of a custom list to determine the green fill.

Excel Conditional Formatting Greater Than Rule Green Fill

Similarly, do so with the next conditions. Less than 30% of those polled chose to fill in the red. The results look as follows:

Excel Conditional Formatting Green Red Rules

Excel automatically formats all cells according to the given condition.

Advanced conditional formatting

Other (example) formatting options are presented in the following figure.

Excel Conditional Formatting Examples

I marked the first column with blue stripes. The higher the number in the cell, the longer the formatting toolbar. This option can be turned on in a way that I showed in the picture.

Excel Conditional Formatting Rule Solid Fill

The second column's formatting benefited from the color scale. On only the first scale, the highest values are green, and the lowest are red.

Excel Conditional Formatting Green Yellow Red Rule

Results in the third column are formatted sets of icons. I think this is a good way to spice up the sheet, but using it at work is not the best idea.

Excel Conditional Formatting Rule Icon Sets

Show an Increase or Decrease in Conditional Formatting

In this example, we'll take a look at sales numbers and how to use conditional formatting to identify increases or decreases compared to average sales volumes.

Before you set the conditional formatting rules, make sure your data is formatted correctly. In this example, we are going to compare monthly sales volumes against average sales volumes, as seen below.

Conditional Formatting Sales Volumes

We will use conditional formatting to easily identify the numbers in the Sales by Month column. The example shows the Average Sales Volume number that will allow us to set a conditional formatting to compare an increase or decrease.

In Excel's Home tab, select Conditional Formatting, then select New Rule. Select the range of cells you want to format (in this case, the cells in the Sales by Month column). For Rule Type, select Highlight Cells With, then select Cell Value Greater Than. Finally, select the Average Sales Volume as the target cell.

Conditional Formatting Rules

This rule will highlight any increases in the sales volume. To identify decreases compared to the average, simply create a second rule. Rather than selecting Greater Than, simply select Less Than. These two rules will quickly highlight increases and decreases, so you don't need to assess every line item.

Conditional Formatting Increase Decrease

Conditional Formatting for a row

You are going to learn, how to format whole row not only one cell. It can be useful for some reports or presentations.

Format row formatted

You have some sales report.

Format row sales report

You can format rows, which sales is more then 10 000$. First select your data without headings.

Format row select data

Next go to the ribbon and click Conditional Formatting and New Rule.

Format row new rule ribbon

On the dialog box choose Use a formula to determine which cells to format option.

Format row new rule

Write down =$B5>$C$2 formula.

Format row formula

Next click Format and choose how your rows will be formatted. I clicked Fill and chose yellow background.

Format row fill

Click OK and see you data formatted?Conditional Format row

This is how Conditional Formatting for a row is working. Isn't it easy?

There are many more possibilities. Try them all at home.

Further reading: 
Sorting
How to Format Date and Time?