How to Use Conditional Formatting to Highlight Information in Excel

Excel’s conditional formatting is a powerful feature that allows you to change the format of cells based on a condition (or criteria). Conditional formatting helps highlight, emphasize, or differentiate data stored in an Excel spreadsheet.

All conditional formatting rules are based on simple logic: If the conditions are true, then specific formatting will be applied; if the conditions are false, then formatting will not be applied.

Conditional formatting helps you highlight important data in a dataset, emphasize anomalies, and visualize data using data bars, colors, and icon sets. In this tutorial, we’ll explore how to apply conditional formatting to highlight cells or ranges of cells based on a condition.

How to Apply Excel Conditional Formatting

For instance, You are in charge of managing a large inventory list with several items and their respective quantity in stock. And if the quantity in stock of a particular item goes below, let’s say 50, it’s important that you need to kow that, so you can restock it.

If that inventory list has a dataset with hundreds of rows, it’s not very effective to search row by row to see if there are any numbers in that column that are below ’50’. That’s where the Conditional Formatting feature comes in handy. With a few clicks with your mouse, you can highlight all the values in a column that is less than ’50’.

Conditional Formatting with Highlight Rules

In our example, we have a worksheet containing sales records of some products. we want to highlight the amount that is less than 500 in sales.

To do that, first select the cell range you want to apply a rule (condition) to. In our example, we want to highlight the amount that is less than 500 in the ‘Amount’ column, so select column D. You can highlight values in a range of cells or multiple cell ranges, or even an entire sheet.

Then go to the ‘Home’ tab and click ‘Conditional Formatting’. Select ‘Highlight Cells Rules’ from the drop-down and since we want to find less than 500 values, click the ‘Less Than’ option. Excel offers seven preset highlight rules. You can use any of them to highlight data.

Next, a ‘Less Than’ dialog box will appear. In that, enter ‘500’ in the ‘Format cells that are LESS THAN’ box and select formatting for the highlight in the drop-down next to it.

Now, the cells that contain less than 500 in their values will be highlighted in the chosen formatting.

Let’s see how to highlight values that contain a specific text string. In the following example, we want to highlight all the employees from New South Wales (NSW).

To do that, go to Home tab –>Conditional Formatting –>Highlight Cells Rules –>Text that Contains.

You can use other options in the ‘Highlight Cells Rules’ horizontal drop-down menu to highlight Duplicate Values, Dates, Greater than, Equal to, or Between values.

In the Text That Contains dialog box, enter ‘NSW’ in the box and choose the formatting, and click ‘OK’.

The result:

Conditional Formatting with Top/Bottom Rules

Top/Bottom rules are another helpful built-in preset conditional formatting rules available in Excel. These rules allow you to call attention to the top (n) number of items, the top (n) number of percent, the bottom (n) number of items, the bottom (n) number of percent, or cell values that are above average or below average.

Let’s say you have a student’s mark record in a spreadsheet and you want to find out the top 10 performers (top 10 ranks) from that list. With conditional formatting, you can highlight the top 10 marks or top 15, or any (n) number of top items. To highlight the Top 10 items in a range of cells, first, select the range (Total) in the table.

Then, go to ‘Conditional Formatting’, expand ‘Top/Bottom Rules’ and select ‘Top 10 items..’ option.

In the ‘Top 10 items’ dialog box, change the number of ranks using tiny arrows in the left field. If you want to highlight the top 20 ranks (total mark) in your mark list, then set the number to 20. The default is already 10, so we keep it. Choose the cell formatting in the right field and click ‘OK’

The top 10 marks from the ‘Total’ column are highlighted as shown below.

Let’s say you want to find below-average marks in the ‘Exam 1’ column in that same mark sheet. To do that, go to Home tab –>Conditional Formatting –>Top/Bottom Rules –>Below Average.

You can set up your own conditional formatting instead of choosing one of the predefined conditions. To create your own formatting, select ‘Custom Format’ option in the ‘Below Average’ window.

A new Format Cells window will open up, here you can customize your formatting cells. We’re selecting an orange color to highlight the below-average marks. Once, your finished click ‘OK’ twice to see the result.

The result:

Apply Data Bars

Data Bars are simply horizontal bars in your cells. The size of the bar is relative to the value of the cell respective to the value of other cells in the selected range. That means the value of the short bar is low compared to other cell values and a long bar means the value is high compared to other cell values.

First, select the range of cells that you want visualized with the data bars.

Next, go to ‘Conditional Formatting’ in the ‘Home’ tab, then expand ‘Data Bars’ in the drop-down and choose your choice of the data bar.

The difference between the Data Bar format and other formats is that it shows on all cells instead of meeting a specific condition.

Apply Color Scales

Color scales are very similar to data bars because they both correlate the value of the individual cell to the value of other cells in the selected range. However, data bars visualize the cell value by the length of a bar, while color scales do it with color gradients.

Each color scale option uses two or three-color gradient patterns. One color is allotted to the highest values, another color is allotted to the lowest values and all other values in between get a blend of those two colors.

For this, we’ll use the same example we used for data bars. Select the cell range, the go to Home –> Conditional Formatting –> Color Scales. Then, choose a color range from the Color Scales horizontal dropdown menu.

When we chose the first color range, red color is allotted to the lowest value, green color is allotted to the highest value, and all the values in between are allotted colors that are a blend of red and green colors (as shown below). Since yellow is made by combining red and green at equal intensity, the cells with average values are assigned with it.

Apply Icon Sets

Icon Sets are another method to visualize the data inside each cell and correlate cells to one another.

Select the cells and click Home –>Conditional Formatting –> Icon Sets. You can choose any of these icon sets that are more suitable for your need. For our example, we’ll choose the first option under Directional.

Red, yellow, and green arrows that respectively indicate low, middle, or high-priced items.

Remove Conditional formatting

To remove conditional formatting, go back to the ‘Conditional Formatting’ option in the ‘Home’ tab and click ‘Clear Rules’. Then, choose which rules you want to clear.