Learn how to utilize Excel's Conditional Formatting to make certain data stand out effortlessly. Discover how to set numerical thresholds, distinguish numeric values, and create custom responses to changing numbers on your Excel worksheets.
Key Insights
- Conditional Formatting in Excel is used to highlight data that meets specific conditions.
- You can set numerical thresholds and choose from multiple presets for the way the formatting will appear when the conditions are met.
- Excel has options for gradients, solid color fills, or the insertion of shapes to further distinguish numeric values.
- Conditional Formatting can be applied to text, such as a product or person's name, within a selected range of cells.
- Excel allows for customized rules in Conditional Formatting, where you can choose the specific condition and apply very specific formatting in response.
- As your worksheet content evolves, the formatting applied through Conditional Formatting will continuously be reapplied based on the cell value and your rules.
Conditional Formatting Types
-
Highlight Cells Rules
Greater than, less than, between, equal to specific values.
-
Top/Bottom Rules
Top 10, bottom 10%, above average, below average.
-
Data Bars
In-cell bars showing relative magnitudes.
-
Color Scales & Icons
Heatmap-style or icon overlays for at-a-glance visualization.
Applying Conditional Formatting
Conditional Formatting is formatting applied when certain conditions are met – thus the name.
For example, if you want to be alerted to low levels of stock on a given product, you can set a formatting rule to help you spot things you need to reorder.
Here, in our Inventory sheet, in the Quantity in Stock column, I can use a Conditional Formatting preset, found through the Conditional Formatting button on the Home tab – which applies a light red fill with dark red text to flag products dipping below 50 items in stock. I can set the numeric threshold and choose from multiple presets for the way the formatting will appear when the conditions are met.
I can also choose the Custom Format command from that same menu to apply whatever formatting I think will catch my eye, given the rest of the sheet’s existing formatting – creating a totally custom response to changing numbers on my worksheet.
You can also ask Excel to rank or otherwise distinguish your numeric values in a range of cells by applying shading, solid color fills, or inserting a shape at the left end of the cells in question.
Here, I’ll apply each one of those options to the same range of cells on my Inventory sheet. Any one of them will make me take note of numbers that are too high, too low, or just right.
On my Inventory Sheet, using the gradient and then solid color fills make my Over/Under numbers – indicating items we’re really low on or have way too much of – really stand out. So things we’re about to run out of appear in a dramatic red and things we need to sell to get rid of excess inventory appear in green.
Applying Icon Sets also creates a very helpful visual.
You can apply these preset graphics to sales numbers, test results, ratings, rankings – any numbers that you want to stand out.
Switching to my Widget Product list, we can see that you can also apply Conditional Formatting for text such as a product or person’s name wherever it appears in a selected range of cells. Here, I’ll use the Text That Contains option, and choose to highlight all the “Clear” widgets.
In addition to the presets for greater-than and less-than options and for top and bottom-ranked numeric items, you can set customized rules, where you choose the specific condition and apply very specific formatting in response.
I’ll use this to format the vendor costs – choosing to format the cells that contain values Less than $3 in green – so I know at a glance that these may be the most profitable widgets for my business.
Note that as your worksheet content evolves over time, the formatting applied through Conditional Formatting will be continuously reapplied, based on the cell value and your rules.
For example, when I switch back to my Inventory sheet and update the quantity-on-hand numbers for a previously low-stock item, the light red fill and the red text goes away, allowing me to focus on more urgent data in the worksheet.
Video Transcription:
Full Video Transcript
Conditional formatting is formatting applied when certain conditions are met, thus the name. For example, if you want to be alerted to low levels of stock in your inventory, you can set a formatting rule to help you spot things you need to reorder.
Applying Basic Conditional Formatting Rules
Here in my inventory sheet, in the quantity in stock column, I can use a conditional formatting preset found through the Conditional Formatting button on the Home tab. This applies a light red fill with dark red text to flag products dipping below 50 items in stock. I can set the numeric threshold and choose from multiple presets for the way the formatting will appear when the conditions are met.
I can also choose the Custom Format command from that same menu to apply whatever formatting I think will catch my eye, given the rest of the sheet's existing formatting. This creates a totally custom response to changing numbers in my worksheet.
Visual Indicators with Data Bars and Icon Sets
You can also ask Excel to rank or otherwise distinguish your numeric values in a range of cells by applying shading, solid color fills, or inserting a shape at the left end of the cells in question. Here I'll apply each one of those options to the same range of cells on my inventory sheet. Any one of them will make me take note of the numbers that are too high, too low, or just right on my inventory sheet.
I'll use the gradient and then solid color fills to make my over/under numbers, which indicate items we're really low on or have way too much of, really stand out. So things we're about to run out of appear in a dramatic red, and things we need to sell to get rid of excess inventory appear in green. Applying icon sets also creates a very helpful visual. You can apply these preset graphics to sales numbers, test results, ratings, rankings—any numbers that you want to stand out.
Conditional Formatting for Text Values
Switching to my widget product list, we see that we can also apply conditional formatting to text, such as a product or person's name, wherever it appears in a selected range of cells. Here I'll use the "Text That Contains" option and choose to highlight all the clear widgets.
Creating Customized Formatting Rules
In addition to the presets for greater than and less than options and for top and bottom ranked numeric items, you can set customized rules where you choose the specific condition and apply very specific formatting in response. I'll use this to format the vendor costs, choosing to format the cells that contain values less than three dollars in green so that I know at a glance that these may be the most profitable widgets for my business.
Dynamic Updates and Real-Time Formatting
Note that as your worksheet content evolves over time, the formatting applied through conditional formatting will be continuously reapplied based on the cell values and your rules. For example, when I switch back to the inventory sheet and update the quantity on hand numbers for previously low stock items, the light red fill and red text goes away, allowing me to focus on more urgent data in the worksheet.