Walk into most offices and you’re apt to find someone using an Excel spreadsheet. It’s one of those common business tools. The problem with many spreadsheets is they make the reader find the key information rather than highlight key items. Fortunately, Excel conditional formatting can make your data automatically 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 styling might include a different background color, font color, gradient, symbol or border. The goal is to make important information stand out so you can find them 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
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 3 popular examples.
How to Apply Excel Conditional Formatting
Adding these formatting rules is easy. The hardest part is finding out 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
- Highlight your cell or range of cells.
- From the Home tab, click Conditional Formattingbutton.
- From the drop-down menu, select Hightlight Cell Rules.
- From the side menu, select Greater Than…
- Adjust the value in the Format cells that are GREATER THAN: field.
- Click the down triangle to make your color selection.
- Click OK
Excel Conditional Formatting button
Greater Than…menu option
Note: Excel will pre-populute this field based on the existing values of your highlighted cells.
Excel Pre-fills the GREATER THAN value
Choosing a color scheme
Note: You can create a different scheme by selection Custom Format…
You should now see your results applied. In my example, I selected 40 as my value and used the red color scheme.
Highlighted Entry Great Than 40
Example 2: Highlight Top 10 Entries
One popular request is trying to find the top entries, whether they be a set number or percentage.
- Follow the starting 3 steps from Example 1.
- 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.
Highlighting the Top 10
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.
- Follow the starting 3 steps from Example 1.
- From the Conditional Formatting drop-down menu, select Data Bars
- From the side menu, select your color fill.
Color Fill and Gradient Options
In my example, I chose a solid green fill.
Excel Conditional Formatting with Data Bars
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”.
