If you walk into any office, you’ll likely find a Microsoft Excel user. It’s a useful business analysis tool if used properly. However, the problem with many spreadsheets is all the cells look the same. So you have to hunt for key information. In this tutorial, I’ll show how to highlight in Excel using several methods. (Includes downloadable practice file.)
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.
Article Contents
Formatting Excel Cells Based on Conditions
Instead of having the reader scan each cell, you can have the software do some legwork 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 it meets certain conditions or locations. This uses the “Cell Value Is” method.
Cell Value Highlight 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 some 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 they might take.
Example 1: Highlight Cells Above a Specific Number
If you wish to follow along, this is Example 1 from the sample spreadsheet below. Again, I’m going to highlight selected cells if the state’s population exceeds or equals 2% of the US population.
- Open the state-counts-cf.xlsx sample spreadsheet and click the Example 1 tab.
- Click cell F2.
- Select the whole column by pressing Ctrl +Shift + ↓.
- From the Home tab, click the Conditional Formatting button.
- From the drop-down menu, select Highlight Cell Rules.
- From the side menu, select Greater Than…
- 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.
- Enter your desired value. In our case, it’s 2.0%. Excel will start highlighting cells for you.
- Click the down triangle
to make your color selection.
✪ Note: You can create a different scheme by selecting a Custom Format…
- Click OK.
You should now see your results applied.
Example 2: Highlight Top 10 Entries
One popular request is 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. Instead, 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.
- Open the state-counts-cf.xlsx sample spreadsheet and click the Example 2 tab.
- Click cell D2.
- Select the whole column by pressing Ctrl +Shift + ↓.
- From the Home tab, click the Conditional Formatting button.
- From the Conditional Formatting drop-down menu, select Top/Bottom Rules.
- From the side menu, select Top 10 Items…
- In the Top 10 Items dialog box, adjust your count or color scheme if necessary.
- Click OK.
One advantage to using color is that Excel can sort by color too.
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 that it shows on all cells instead of meeting a specific condition.
By default, Excel will keep the text value background. I find that this can be distracting. In this example, I duplicated the % Change column but told Excel not to put the data value in Column I. The 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.
- Open the state-counts-cf.xlsx sample spreadsheet and click the Example 3 tab.
- Click cell I2.
- Select the whole column by pressing Ctrl +Shift + ↓.
- From the Home tab, click the Conditional Formatting button.
- From the Conditional Formatting drop-down menu, select Data Bars.
- From the side menu, select More Rules…
- In the Edit the Rule Description: section, check Show Bar Only.
- Click OK.
Example 4: Highlight a Row
Highlighting outlier cells 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.
- Open the state-counts-cf.xlsx sample spreadsheet and click the Example 4 tab.
- Click cell I2.
- Select all rows by pressing Ctrl +Shift + ↓ + →.
- From the Home tab, click the Conditional Formatting button.
- Select New Rule…
- In the Edit Formatting Rule module, select Use a formula to determine which cells to format.
- In the Format values where this formula is true: type your criteria.
- Click the Format… button to apply your highlight color.
- 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.
- Click OK.
Find 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.
- Open up your spreadsheet.
- From the Home tab, click Conditional Formatting.
- Select Manage Rules… from the drop-down menu.
- In the Conditional Formatting Rules Manager, select your worksheet.
- 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 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.”
