How to use Conditional Formatting in Excel

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

Conditional Formatting in Excel

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.

Conditional formatting is a powerful Excel tool that allows you to automatically format cells based on certain conditions. This can be used to highlight important data, identify trends, and make your spreadsheets more visually appealing.

To use conditional formatting, first select the cells you want to format. Then, click on the Home tab and select Conditional Formatting. You can choose from a variety of formatting options, such as changing the font color, background color, or cell borders. You can also use conditional formatting to create icons or sparklines.

See also  How To Use Excel Formulas

Here are some examples of how you can use conditional formatting:

  • Highlight cells that contain values above or below a certain threshold.
  • Identify duplicate values.
  • Show trends over time.
  • Flag important data.

Conditional formatting is a versatile tool that can be used to improve the readability and usability of your spreadsheets. With a little practice, you can learn to use it to create effective and visually appealing visualizations of your data.

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 highlighted in green if they are above 90% and in red if they are below 30%. 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.

Excel Conditional Formatting Green Red Rules

Excel automatically applies the formatting you specify to the cells that meet the condition.

Advanced conditional formatting

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

Excel Conditional Formatting Examples

I used blue stripes to format the first column. The length of the stripes increases as the value in the cell increases. This option can be turned on in a way that I showed in the picture.

See also  How to Adjust the Status Bar in Excel

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

The results in the third column are formatted using icon sets. Icon sets can be a fun way to visualize data, but they may not be appropriate for all workplaces.

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.

See also  How to Use Excel Fast: A Guide for Beginners and Advanced Users

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?