Let's Talk About Excel Conditional Formatting
We often hear people saying they use conditional formatting already, but usually they have only used the rules which apply to number cells, e.g. all the numbers over £350 should be in blue, all the figures below 10 are in red. This is great to draw attention to dwindling stock levels or budgets that have been exceeded, or sales staff who have beaten their target.
There are lots of other rules too such as highlighting all your similar products in a specific colour using keywords e.g. all your products which contain the word ‘nail’ could be shown in purple, whereas ‘screws’ could be in orange. This can help you determine which products are suitable for your customer’s order.
Did you also know the following are possible too using conditional formatting rules?
Show a symbol against the figures which are in the top third, middle third or bottom third of the range.
See all the duplicated values in a chosen colour so you can investigate further
Show all the unique values in a specific colour
Highlight all the blank fields in yellow
All the dates from last year to be highlighted in red
All the dates which are within 2 months of today’s date in yellow eg MOTs which are almost due, First Aid Certificates which need to be rebooked.
All the staff who have worked for the organisation more than 10 years
Change all the amounts which are in sterling to have a pound sign in front of the number, or a euro symbol if not
Highlight more of the column in green to show to highest and lowest amounts within a range.
So you see there is a lot more to conditional formatting than just highlighting figures over/under a given amount!