Highlight Important Data with Excel Conditional Formatting

If you walk into any office,  you’ll likely find a Microsoft Excel user. It’s a common and useful business analysis tool if used properly. The problem with many spreadsheets is all the cells look the same. You have to hunt for key information by scanning through a range of cells. Fortunately,  Excel offers some conditional formatting rules that can highlight cells or apply color scales to make your data stand out.  (See example spreadsheet in the Resources section.)

I routinely see this scenario. You get a spreadsheet from someone with hundreds of data rows that look the same. Everything is formatted in the same boring way. But is the data the same? Are there cell values that are different from the rest? Is something outside the average? These are typical questions users have when they open spreadsheets.

And the problem gets worse with large spreadsheets.

Formatting Excel Cells Based on Conditions

Instead of having the reader scan each cell, you can have the software do some of the legwork by using conditional formatting. This allows Excel to apply a defined format to cells that meet specific criteria. These are rules you can define.

The goal is to make important information stand out so you can find it easier.

Excel already does some of this for you. For example, when you format numbers, there are options to display negative numbers in red. This is an example of a predefined format.

The program even allows you to use formatting options on individual cells or rows. The simplest method is to have Excel apply the conditional formatting if the cell meets certain conditions or location. This uses the “Cell Value Is” method.

Some Cell Value examples include:

• Apply a red fill if the value is less than 50
• Apply an italic bold font style if the value is between 70 and 90
• Apply a green font color if the text contains “Montana”
• Highlight values that are equal to 15
• Apply a yellow fill to duplicate values
• Add an Up arrow icon to cell values above 10%

Excel also allows you to use formulas for conditional formatting. One benefit to Excel formulas is you can reference the values elsewhere on your spreadsheet. In the example below, I’m requesting that if the value in B2 is greater than the value in C2, apply a green background color.

Microsoft has greatly enhanced this feature over the years and now predefines popular examples so you don’t need to rely on formulas as much. Below are 4 popular examples.

How to Apply Excel Conditional Formatting

Adding a new formatting rule is easy. The hardest part is finding what you want to emphasize on your spreadsheet. It helps to think about your audiences’ needs and what actions might they take.

Example 1: Highlight Cells Above a Specific Number

If you wish to follow along, this is Example 1 from the sample spreadsheet. I’m going to highlight selected cells if the state’s population exceeds or equals 2% of the US population.

1. Open the state-counts-cf.xlsx sample spreadsheet and click the Example 1 tab.
2. Click cell F2.
3. Select the whole column by pressing Ctrl +Shift + .
4. From the Home tab, click the Conditional Formatting button.
5. From the drop-down menu, select Hightlight Cell Rules.
6. From the side menu, select Greater Than…

1. Adjust the value in the Format cells that are GREATER THAN: field.

Note: Excel will pre-populate this field based on the existing values of your highlighted cells. If you look at the example spreadsheet, there is no cell that has 4.44%.

8. Enter your desired value. In our case, it’s 2.0%. Excel will start highlighting cells for you.
9. Click the down triangle to make your color selection.

Note: You can create a different scheme by selecting Custom Format…

10. Click OK.

You should now see your results applied.

Example 2: Highlight Top 10 Entries

One popular request is trying to find the top entries, whether they be a set number or percentage. This is an example of one of Excel’s preset or built-in formats. In other words, you don’t need to enter specific values as you did in Example 1. These presets are built around common items people look for such as Top 10 or below average performers. The advantage is you don’t have to do the math.

In this example, I want to highlight the top 10 states by US House Seats in Column D.

1. Open the state-counts-cf.xlsx sample spreadsheet and click Example 2 tab.
2. Click cell D2.
3. Select the whole column by pressing Ctrl +Shift + .
4. From the Home tab, click the Conditional Formatting button.
5. From the Conditional Formatting drop-down menu, select Top/Bottom Rules.
6. From the side menu, select Top 10 Items…
7. In the Top 10 Items dialog box, adjust your count or color scheme if necessary.
8. Click OK.

Example 3: Show Data Bars Instead of Color Shading

Another useful option is to use “Data bars”. This is another type of visualization that you can apply. A key difference with the Data Bar format is it shows on all cells as opposed to ones that meet a specific condition.

By default, Excel will keep the text value background. I find that this can be distracting. So, in this example, I duplicated the % Change column but told Excel not to put the data value in Column I. The end result is just the data bar.

The data bar format doesn’t work well with all data types. This is a good example of trying different formats to see what works best for your data. This is especially true if you have negative values or a tight range.

1. Open the state-counts-cf.xlsx sample spreadsheet and click Example 3 tab.
2. Click cell I2.
3. Select the whole column by pressing Ctrl +Shift + .
4. From the Home tab, click the Conditional Formatting button.
5. From the Conditional Formatting drop-down menu, select Data Bars
6. From the side menu, select More Rules…

7. In the Edit the Rule Description: section, check Show Bar Only.

8. Click OK.

Example 4: Highlighting a Row

Highlighting outlier cell is great, but sometimes if you have a large spreadsheet, you may not see the colored cells because they’re off-screen. In these situations, it helps to highlight the entire row. That way, regardless of which columns you’re viewing, you know which rows are important. This works best when you have 1 conditional.

A key difference here is we won’t be highlighting a column, but the entire spreadsheet. That’s how we get the row effect.

1. Open the state-counts-cf.xlsx sample spreadsheet and click Example 4 tab.
2. Click cell I2.
3. Select all rows by pressing Ctrl +Shift + + .
4. From the Home tab, click the Conditional Formatting button.
5. Select New Rule…
6. In the Edit Formatting Rule module, select Use a formula to determine which cells to format.
7. In the Format values where this formula is true: type your criteria.

8. Click the Format… button to apply your highlight color.
9. Click OK to accept your color.

In the example above, I’m asking to highlight any row where the value in column F (Percent total of US population) is greater than 2%. The highlight color is green. I’m also using a mixed cell reference by placing a $ sign before F2 in the formula.

Tip: If you’re not sure you have the correct formula, you can always test it in another column. For example, I could enter in cell H2, this formula =$F2>20%. The H2 cell would evaluate as either TRUE or FALSE. You could then copy the formula down the column to make sure you got your expected results.

10. Click OK.

Finding Your Conditional Formating Rules

Sometimes we’re given a spreadsheet and you see highlighted cells or rows. But…where are the conditional rules or did someone manually format these cells? Fortunately, Excel provides a way to see the conditional formatting rules. You can use the example spreadsheet to follow along or use your own.

1. Open up your spreadsheet.
2. From the Home tab, click Conditional Formatting.
3. Select Manage Rules… from the drop-down menu.

4. In the Conditional Formatting Rules Manager, select your worksheet.
5. Click OK.

In the example above, you can see the rule I applied to highlight the row. From here, you could add a rule, edit the rule or delete it.

Tips & Tricks

This article just touched on some of the ways you can use conditional formatting. The best way to learn is to go experiment with some data whether it’s your own or the sample spreadsheet. Here are some other tips for you:

• You can have multiple rules in the same column. For example, you might want to have different range values be different colors.
• Play around with different formats to see which type works best for your data.
• If you get unexpected results, check to see if multiple conditions are being run and the order. You may need to change the order.
• You can set ranges on data bars. For example, if you have a % Complete column, you may want to set a range of 0 to 1.
• You can remove conditional formatting rules with the Clear Rules option.
• Apply your rules after you’ve entered data. Sometimes duplicate rules are created when you insert rows.

Excel’s conditional formatting can make your spreadsheets more useful if used appropriately. People won’t waste time looking for important changes or that “needle in the haystack”.

Additional Resource

Sample Excel Spreadsheet