Spotlight Important Data with Excel Conditional Formatting

Walk into most offices and you’re apt to find someone using an Excel spreadsheet. It’s a common business analysis tools. The problem with many spreadsheets is they make the reader find the key information by scanning through countless cells. Fortunately, Excel conditional formatting can make your data stand out with a few steps. (See sample spreadsheet in the Resources section.)

I see it happen all the time. You get a spreadsheet from someone with rows of data and it all looks the same. But is the data the same? Are there cell values that are different from the rest? Is something outside the norm? These are the type of questions Excel users have when they see spreadsheets. The problem gets worse the larger your spreadsheet.

Formatting Excel Cells Based on Conditions

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

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

Excel already does some of this for you. As an example when you format numbers, there are options such as displaying negative numbers in red. This is an example of a predefined format.

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

Example of Excel Cell Rules

Some Cell Value examples include:

• Apply a red background if the value is less than 50
• Apply an italic bold font if the value is between 70 and 90
• Apply a green font color if the value is Montana
• Apply a yellow background 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.

Excel Conditional Formatting with a Formula

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 these formatting rules is easy. The hardest part is finding what you want to emphasize on your Excel spreadsheet. What are your audiences’ interests 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 cells if the state’s population exceeds or equals 2% of the US population.

We want to highlight values > 2%

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

Setting up a Greater Than Condition

7. 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%.

Excel prefill the GREATER THAN field

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.

Choosing new value and color

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.

Finding Top 10 States by US House count

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 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.

Example of Data Bar without text background

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 Conditional Formatting button.
5. From the Conditional Formatting drop-down menu, select Data Bars
6. From the side menu, select More Rules.

Select More Rules…

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

Turning off text background

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.

Highlighting entire rows

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 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.

Providing formula for True condition

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.

Find Conditional Rules on your sheets

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

Select your worksheet to see conditionals

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