Advertisement
  1. Business
  2. Microsoft Excel
Business

How to Use Conditional Formatting in Microsoft Excel

by
Difficulty:BeginnerLength:MediumLanguages:

Spreadsheets get a bad rap for being hard to read. Making spreadsheets user friendly is all about adding a bit of formatting—taking basic steps like adding column headers, adjusting your alignment, and using the right color in your spreadsheets.

Formatting is a must, but conditional formatting is even more powerful. Conditional formatting changes how a cell appears, based upon what's inside the cell.

Conditional Formating Excel
Basic conditional formatting can change the background color of a cell based upon the cell's value.

In the screenshot above, you can see the power of conditional formatting. I've setup a very basic rule that turns the background of a cell with a positive value green, while a negative value will turn red.The only thing that changes is the background color based on the value.

Other examples of conditional formatting could include:

  • Putting all negative numbers in a list of data in red.
  • Creating data bars to give a visual representation of the largest and smallest items in your data set.
  • Using icons to indicate the highest and lowest values in a list of data.
Sales Team Review conditional formatting in Excel
These easy-to-read flags are helpful to mark the highest values in green, mid range values in yellow, and low amounts in red.

When you write conditional formatting, the formatting adjusts to what's in the cell. In this tutorial, you'll find out how easy it is to use conditional formatting in Excel.

How to Apply Conditional Formatting in Excel (Watch & Learn)

In this video, I'll walk you through some of the easiest ways to apply conditional formatting. You'll get an over-the-shoulder look at how I use conditional formatting in Excel to highlight important data.

 

The rest of this tutorial will focus on adding conditional formats to your spreadsheets. Read on to find out how you can use them in your own spreadsheets.

1. Using Built-in Conditional Formatting

This tutorial includes an example spreadsheet that you can download for free and use for the steps below.

Spreadsheets can be overwhelming at times. The good news is that conditional formatting in Excel is easy to get started with thanks to Excel's built-in styles.

We're going to take a look at Excel's built-in styles for conditional formatting. For each of these styles, you can apply them by highlighting the list of data you want to format, and then finding the Conditional Formatting button on the Home tab of the ribbon.

Conditional Formatting menu
Apply conditional formatting by highlighting a list of data, and then choosConditional Formatting from Excel's ribbon on the Home tab.

Once you've opened the Excel conditional formatting menu, choose one of the options to begin applying conditional formatting.

Highlight Cells

Choosing the highlight cells option allows you to conditionally format cells on a variety of factors. Click on Conditional Formatting and choose one of the options from the Highlight Cells Rules menu to get started with conditional formatting.

Highlight cells
Highlight cells to apply presets.

Think of these options as presets for conditional formatting. Here's how each work:

  • Greater Than - Format cells greater than a value that you set.
  • Less Than -  Format cells lower than a value you set.
  • Between - Give conditional formatting to a range of values, and highlight cells between those values.
  • Equal To - Format a cell when it is exactly equal to a value you set.
  • Text that Contains - Format cells that contain strings of text.
  • A Date Occurring - Conditional formatting based upon the date in a cell.
  • Duplicate Values - Quick conditional formats for a list of data with duplicates.

Let's look at an example. I'll choose Greater Than for my example. Then, I'll set a value in the pop up window. In the screenshot below, I set my value to be $22,000, and used the "Green fill..." option.

Format Cells greater than
Format cells Greater Than in Excel.

As you can see, Excel applied conditional formatting in the preview, and all values greater than $22,000 were highlighted in green.

This menu is a great way to customize your conditional formatting rules. You can easily build rules for how to format your cells by starting with the "Highlight Cells" option.

Apply Top/Bottom Rules

Another great way to set Excel conditional formatting is with the Top/Bottom Rules option. You can easily choose to highlight the top and bottom 10 items in a list, or apply another option.

Click on the Conditional Formatting option, choose the Top/Bottom Rules panel, and then choose one of the formatting options shown.

TopBottom Rules
Top/Bottom Rules in Excel.

In my case, I'll choose the Above Average option to highlight all of the sales amounts that are above the average in the set.

The pop up window is very similar to the prior example, and we'll select Green Fill... from the dropdown.

Above Average highlight in Excel
I've selected the Green Fill option to highlight in green any of the cells that are above the average in the list.

Notice in the screenshot above that Excel automatically highlights in green any of the cells in green.

2. Use Built-In Styles in Excel

The options we've looked at so far require giving Excel some rules. If you want the fastest way to add conditional formatting, try out some of the options below.

Work With Data Bars

One of the quickest ways to add meaning to your spreadsheet is to choose the data bars option. Data bars will place bars inside of cells that represent the "size" of the data in your list.

Data bars option in Excel
Choose from either a gradient or solid from the data bars fill.

To apply a data bar conditional format, highlight your data first. Then, find the Data Bars option and choose from one of the gradient or solid fill options.

The screenshot below shows the power of data bars to illustrate data. In the January 2017 column, the green gradient bars correspond in length to the amount. The lowest amounts have the shortest bars, while higher amounts have longer bars.

Data bar example in Excel
The data bar conditional formatting option really illustrates your data, right inside of the cell.

Data bars are a great illustration of the power of conditional formatting in Excel. Notice how much easier it is to find the largest amounts than the February column, just by scanning the lengths of the bars.

Apply Color Scales

Color scales really draw attention to the highest and lowest values in a list of data. After you've highlighted your data, return to the Conditional Formatting option, and choose one of the options from the Color Scales menu.

Color Scales conditional formatting
Color Scales conditional formatting in Excel.

The color scales in the thumbnails represent the colors that will be applied to your data. I'll choose the first icon in the list, and you can check out the results below:

Color Conditional formatting in Excel
Color conditional formatting highlights cells with background colors based upon their values.

In the example above, the best sales result has a dark green background, while the lowest value is in red. All of the other values are in shades of yellow and green, to represent the salesperson's performance.

The color scales option has a variety of Excel conditional formatting rules. Try out several of the other thumbnails to apply other colors to your data.

Add Icon Sets

Icon sets are another great way to add some extra style to your spreadsheet. In my example, I'm going to highlight my data, and choose some arrows from the conditional formatting options.

The icon sets include arrows to show changes in data, as well as other icons like flags and bars to show data right inside of the cell.

Icon Set conditional formatting in Excel
Icon Set conditional formatting in Excel.

In the data below, I have a percent change column to show how the salesperson performed versus the prior month. This is a great way to visually represent your data.

Icon to show change in Excel
The percent change icons help you review your data quickly, and adapts automatically if the data changes.

Each of these built-in icon styles is designed to help you make judgments about your data by quickly glancing at it. 

All of these conditional formats adapt when the data changes. If the sales figures changed, Excel would automatically change the icon accordingly.

Usually, I start with one of the built-in styles and modify it to my liking. It's much easier to use a built in conditional formatting option and tweak it, instead of writing your own from scratch.

Recap and Keep Learning

This tutorial covered Excel conditional formatting. You learned how to add styles to your cells that change dynamically based upon what's inside of the cell. 

Here's the thing: using formatting makes your spreadsheets easier to read. But using conditional formatting in Excel helps you add insight directly to your cells. It allows you to glance through your data and understand it rapidly.

Here are more great tutorials to help you level up your Excel skills:

How are you using conditional formatting? What are your favorite styles to apply to a cell and how do you use it? Let me know in the comments.

Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.