Conditional formatting is a useful tool that formats your Excel data based upon certain criteria or conditions that you have set in Excel 2013. This includes (but is not limited to!) having coloured cells, icons or even data bars once the condition’s criteria is fulfilled. It’s particularly useful as a visual data validation tool to check data being entered is correct according to the criteria you require, and is also useful to demonstrate trends or highlight extremes within a data set. One particular example of a good use for Conditional formatting is to identify members of a sales team who are underperforming, or to work out commision that is due.
Note: This article focuses on Microsoft Excel 2013, if you are looking for details on earlier versions, click here to view an earlier article on the same topic.
In order to use conditional formatting, you need to have a conditional formatting rule created. In the sample below, we want to identify everyone who hit their sales target for the month. So one rule could be that the colour of a cell changes to red if the value is under £7000 to identify sales team who are underperforming, and highlight in green if they hit a target and are due commision or a bonus.
In this example, we are going to arrange sales commissions and identify the team members who hit the target of £10,000 per month. Since there are a number of columns and rows, it gets difficult and very time-consuming for us to manually check and highlight any number greater than £10,000 and so we will be applying the conditional formatting rule here. Let’s have a look at how this works:
1. Select the data for which you want to create conditional formatting options. This will usually be a range of columns and cells.
2. Click the Conditional Formatting tab from the Home tab. A drop down menu would be shown. Since we want to highlight cells having £10,000 or above value with green, we will hover the mouse on Highlight Cells Rules, then click Greater Than…
3. In the dialogue box, write in 10000 and select Green fill with dark green text. Then click ok.
4. You’ll be taken back to your data sheet where you now can easily see which cells have value over £10,000 as all of them have been highlighted green, making it easy for us to manage the data. Instead of counting manually, you can now see, identify and cross check the data yourself. Now the cells are highjlighted, you can also use the filter function to filters cells by their colour in order to pruce reports of under performing team member and commision reports.
5. If you want to remove formatting from cells, this is equally as simple to do. To remove conditional formatting rules, click the Conditional Formatting option, and then hover your mouse over Clear rules and then select Clear rules from Entire sheet
There are tonnes of possibilities for conditional formatting, and in exactly the same way, we can make rules such as less than, between, equal to.. etc. – it’s definitely worth a look next time you are running reports in Excel.