Conditional Formatting

This is simply formatting that automatically adjusts depending on the contents of the cells in a worksheet. Typical uses are to highlight important trends in your data i.e. a rise in a stock price, a sudden spurt in colleague/employee expenses and highlighting upcoming deadlines as a few examples.

The most popular uses (from the people we asked) are for highlighting and removing duplicates from data. This is something we use a lot when we’re dealing with large data sets, in particular – where there could be duplicate rows of information for clients, projects etc. By using conditional formatting, we can tell Excel to highlight all duplicates in a certain column and from that we can decide which rows need deleting. If we’re working on a piece that should have just one unique identifier, we can select to remove all duplicates and Excel will do that for us, telling us how many it removed, which can be another great way to monitor mistakes/errors.

Task monitoring

We also use it for our tasks, all tasks are input into the spreadsheet with a RAG status and deadline date for when we should aim to complete the task by. Using conditional formatting we can set a rule to say highlight amber any tasks we have coming up next month, we also set a rule to highlight red any tasks that have a deadline date in the next week/month. This is great for us as we can see at a glance exactly which tasks we need to focus on first.

Another great thing about conditional formatting is that you can use it to add data bars, gradients, flags, arrows and other visuals to your data. These can be particularly good if you want to see how your business is doing, for example, you might want to see at a quick glance, which products have sold the most or are most popular. You could use it for invoice reconciliation and so much more.
You can also choose to see the top 10 items with largest numbers or bottom 10 as well.

Conditional formatting can also be used for highlighting important data in an Access form. It can be used in Outlook for making messages that meet defined conditions stand out in the message list by using different colours, fonts and styles, so you’re not just limited to using this feature within Excel.

Share This