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 are 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.
You can also set certain parameters, for example, you may want to have your customers categorised by age, you can tell Excel to search for certain age ranges and you may want to highlight them in a certain colour to help you identify specific customers to help you with your marketing. An example is you may sell beauty products and there are different products for different age groups, so if you decide to have a promotional offer on products for people over 65 you can use that as one of your search criteria in your spreadsheet, you can have many different colours as you select the custom colours, you may select blue for male and pink for female in a separate column so again you can easily identify who your target clients are for this promo offer you want to run.
There are lots of ways you can use conditional formatting to help you at work, in your business or just for your own personal tasks…
Task monitoring
One of the ways we use it is for our tasks, all tasks are input into the spreadsheet with a RAG (Red, Amber, Green) 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 the largest numbers or the 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.
I had a colleague who wanted to understand how to use ‘fancy formatting’ as he put it, to automatically colour cells for him. So we set up a video call, I got him to share his screen and I asked a few questions about what he was wanting the formatting to look for. So he decided he wanted drop-down arrows on certain columns of data that people could select. I showed him how to do this as it’s not something conditional formatting does for you – this is something I can do for you too.
He then decided that when one of the options was selected he wanted the cell to colour red, for another he wanted orange, for another it was green and the last one was blue. By telling Excel to colour these cells differently based on the criteria within them, he was able to see at a glance which lines of data needed action and which ones were completed.
This meant that he saved himself time reading through every single line trying to decide which needed his focus and because the formatting was done on the column when the drop-down selection was changed the colour automatically changed too.
We have a digital download on conditional formatting that walks you through some of the main ways to use it. If it is something that you’d like to learn how to use then you can buy the training for £9 here