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.
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.
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.
A dialog box appears. The entry requirement is 90%. Select the format of a custom list to determine the 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 automatically formats all cells according to the given condition.
Advanced conditional formatting
Other (example) formatting options are presented in the following figure.
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.
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.
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.
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.
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.
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 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.
You have some sales report.
You can format rows, which sales is more then 10 000$. First select your data without headings.
Next go to the ribbon and click Conditional Formatting and New Rule.
On the dialog box choose Use a formula to determine which cells to format option.
Write down =$B5>$C$2 formula.
Next click Format and choose how your rows will be formatted. I clicked Fill and chose yellow background.
Click OK and see you data formatted?
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?