Want to learn how to make your Excel spreadsheets more appealing and impactful? The answer lies in mastering how to highlight cells in Excel with conditional formatting. The possibilities are endless, from color-coding cells based on sales values to dynamic heat maps. This guide shows you techniques to showcase cells and reveal data insights effectively. (Includes Excel practice file.)
Knowledge You’ll Gain:
- Quickly identify key data points with basic highlighting techniques.
- Visualize data patterns using color scales and data bars.
- Create custom formatting rules with formulas for advanced analysis.
- Highlight duplicates and unique values efficiently.
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.
What is Conditional Formatting in Excel?
Instead of having the reader scan each cell, you can have Microsoft Excel do some legwork using some rules. This allows Excel to apply a defined format to a range of cells that meet specific criteria or conditions. These defined rules evaluate a cell value to see if it meets specific criteria. If the condition is met, certain formatting is applied to the entire cell.
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, rows, or range of cells. The simplest method is to have Excel apply conditional formatting if it meets certain conditions or locations. This uses the “Cell Value Is” method.
Cell Value Highlight Examples
Below is a small sampling of ideas for using conditional formatting:
- Apply a red background fill if the cell value is less than 50
- Apply an italic, bold font style if the cell value is between 70 and 90
- Apply a green font color if the cell text contains “Montana.”
- Highlight cells that are equal to 15 with a red border
- Apply a yellow background fill to duplicate values
- Add an Up arrow icon to cell values above 10%
- Highlight all blank cells
Excel also allows you to use formulas for conditional formatting. One benefit to Excel formulas is that you can reference the values elsewhere in your spreadsheet. In the example below, I’m using an Excel IF formula to test if the cell value in B2 is greater than the value in C2. If the formula is TRUE, apply a green background color.
Microsoft has greatly enhanced this feature over the years, 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 format rule is easy. The hardest part is finding what you want to highlight on your spreadsheet. It helps to think about your audience’s needs and what actions they might take.
How to Highlight Cells Above a Specific Number
If you wish to follow along, this is Example 1 from the sample worksheet below. Again, I’m going to highlight a range of cells to see 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.
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.
You can create a different color scheme by selecting a Custom Format.
- Click OK.
You should now see your results applied.
How to Highlight Top 10 Items
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 that 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. Additionally, you can also SUM or COUNT colored cells.
How to Use Data Bars Instead of Background Color
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 all cells instead of those meeting a specific condition.
By default, Excel will keep the text value background. I find that this can be distracting. I duplicated the % Change column in this example 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 list, select Data Bars.
- From the side menu, select More Rules…
- In the Edit the Rule Description: section, check Show Bar Only.
- Click OK.
How to 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 dialog box, 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 the Fill tab.
- Select your color.
- Click OK to accept your color.
- Click OK to close the dialog box.
In the example above, I want 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.
- Click OK.
Find Existing Conditional Formatting Rules
Sometimes we’re given a spreadsheet and 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 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 list.
- In the Conditional Formatting Rules Manager dialog box, 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.
Excel Highlight Tips & Tricks
This tutorial touched on some of the ways you can use conditional formatting to highlight cell values. 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 rule 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.
Mastering Excel’s conditional formatting lets you transform your spreadsheets from static data displays into actionable insights. You can find patterns, streamline data analysis, and effectively communicate critical information by strategically highlighting cells. Remember, the key lies in experimentation. So, explore the various techniques discussed, download the practice file, and don’t forget to watch the accompanying video. With a little practice, you’ll be highlighting cells like a pro.
Show Me How Video
To see the above examples, click the image below to be taken to the accompanying video and transcript.