How To Use Conditional Formatting in Excel in 5 Steps

By Indeed Editorial Team

Published December 14, 2021

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

Using Excel to maximize efficiency at work is something many professionals do. One Excel tool they may use is conditional formatting. Knowing what conditional formatting is and how to use it can help you find information faster and create visually appealing presentations. In this article, we discuss how to use conditional formatting in Excel, including what it is and four steps you can follow to apply it to your Excel spreadsheets.

Related: Advanced Excel Skills: Definition and Examples

What is conditional formatting in Excel?

Conditional formatting in Excel is a way for you to alter how the data inside a cell looks based on the rules you choose. There are several main options in conditional formatting, including:

Highlight Cells Rules

This option allows you to highlight specific cells based on the rules you choose. There are several options included in the basic Excel program, including:

  • Greater than: This option allows you to highlight cells that contain values larger than a number you designate. For example, you can highlight cells with a value greater than 10, which Excel highlights as the numbers 11 and higher.

  • Less than: This option allows you to highlight cells that contain values smaller than a number you designate. For example, you can highlight cells with a value less than 10, which Excel highlights as the numbers nine and lower.

  • Between: This option allows you to highlight cells that contain values in between two values you designate. For example, you can highlight cells between the values 10 and 20, which Excel highlights as the numbers 11 through 19.

  • Equal to: This option allows you to highlight cells that contain values that exactly match a number you designate. For example, you can designate the value 15, which Excel highlights any number or mathematical formula that equals 15.

  • Text that contains: This option allows you to highlight cells that contain values that include a series of characters you designate. For example, you can designate the term "car", which Excel highlights any term with the letters car in it such as "supercar," "racecar," "carriage" or "career."

  • A date occurring: This option allows you to highlight cells that contain values that include a calendar date you designate. For example, you can designate "A date Next Month" which Excel then highlights that specific date.

  • Duplicate values: This option allows you to highlight cells that contain values that exactly match each other. For example, you can designate a number such as "20" which Excel then highlights every cell within the range that matches the value of 20 or you can designate a word such as "Subscription" which Excel then highlights any cell in the range with that exact value.

Related: How To Remove a Duplicate From Excel (With Helpful Instructions)

Top/Bottom Rules

This option allows you to designate specific rules to choose a top or bottom number of cells within a range. Excel includes several options that you can choose, including:

  • Top 10 items: This option allows you to designate the top 10 items within a larger range of cells. For example, you can choose this option to highlight the top 10 numerical values in a range of 11 or more cells.

  • Top 10%: This option allows you to designate the top 10% of items within a larger range of cells. For example, you can choose this option to highlight the top 100 items in a range of 1,000 cells.

  • Bottom 10 items: This option allows you to designate the bottom 10 items within a larger range of cells. For example, you can choose this option to highlight the lowest 10 numerical values in a range of at least 11 cells.

  • Bottom 10%: This option allows you to designate the bottom 10% of items within a larger range of cells. For example, you can choose this option to highlight the lowest 10 items in a range of 100 cells.

  • Above average: This option allows you to designate any value in a larger range of cells that is higher than the average value within that range. For example, Excel highlights all values greater than three if three is the average value for that entire range of cells.

  • Below average: This option allows you to designate any value in a larger range of cells that is lower than the average value within that range. For example, if you have 100 cells with an average of 11.5, Excel highlights all values in that range below 11.5.

Data Bars

This option allows you to input data bars, such as those used in bar graphs, into your Excel spreadsheet within the range of cells you choose. For example, you can highlight a column of 10 cells and choose this option to create a miniature bar graph within the cells themselves. This can help you quickly convey information without having to specify exact numbers.

Color Scales

This option allows you to insert a color gradient into your Excel spreadsheet within a specific range of data. For example, you can select a range of data with temperatures that range from freezing to hot and choose this option to represent those temperatures visually as colors ranging from purple to red. This can help you show differences in values over time or highlight the most important values in bright colors.

Icon Sets

This option allows you to insert specific symbols next to your data within an Excel spreadsheet. For example, you can select several ranges of data representing change over time and select directional icons to represent how they vary in each iteration of the data. This can help you present information more concisely than explaining the exact numerical changes within the data. This can also help you evaluate trends in your data and represent them visually for others who may use that data.

How to use conditional formatting in Excel

Below are five steps you can use to use conditional formatting for your own data in Excel:

1. Input your data into Excel

The first step is to take the data you want to format and input it into Excel. While you may have done this already, there are ways to let Excel change the formatting more easily. For example, placing your data in columns and rows that correspond to each other can help Excel use the data more effectively. Typically, Excel works better when you organize the ranges of data you want to work with into columns. Organizing your data this way can also help you locate data more easily, especially if you're working with smaller data sets.

Related: Basic Excel Formulas and How To Use Them

2. Select the data you want to format

Once you input and organize your data in an Excel spreadsheet, select the range of cells you want to format. There are two methods you can use to select your data. First, you can go to the top cell in the range that contains data and place your mouse near the bottom right corner of the cell until it turns into a plus symbol. Then you can click and drag down and across the entire range of cells you want to format. This is useful if you only plan on using the data once, or it is a small dataset.

The second method you can use is double-clicking the name of the column you want to format. The name is the letter at the very top of the Excel program such as "A," "B," "Z," or "AA" and others. This method is useful if you plan to add more data in the future or are working with large datasets. This is also a helpful option if you aren't the only person changing the Excel spreadsheet and want to apply the formatting to new data others input.

3. Find the conditional formatting button

Once you've selected your data, find the conditional formatting button. In modern versions of Excel, the "Conditional Formatting" button is in the "Styles" group in the "Home" tab of the program. To navigate to it, go to the top banner of Excel and select "Home." Then, find the "Styles" group. Excel separates each group with a vertical line and labels at the bottom of the group. Finally, click on the "Conditional Formatting" button.

4. Select an option from the drop-down menu

Once you select the "Conditional Formatting" button, Excel displays several options, starting with "Highlight Cells Rules" and ending with "Manage Rules." Selecting one option from the first five allows you to develop rules based on the option you choose. The last three options, "New Rule," "Clear Rules" and "Manage Rules" allow you to change rules you previously created, or to delete rules that are already present and may affect your conditional formatting.

5. Save your Excel spreadsheet

The last step is to save your Excel spreadsheet. There are two methods to do this. The first way is to go to the top left corner of the Excel program and click "Save" or "Save as" depending on the needs of the spreadsheet. If it is a new spreadsheet, "Save as" allows you to control where and how you save the document. If it is an old spreadsheet, clicking save updates the old file you have. The second method is by pressing "CTRL+S" on your keyboard, which is the same as clicking "Save" on the top left.

Related: Basic Computer Skills: Definition and Examples

Please note that none of the products mentioned in this article are affiliated with Indeed.

Explore more articles