Conditional formatting in Excel is a great way to highlight differences in your data. Sometimes when there are many figures on your Excel sheet, your eyes struggle to find meaning in the data. Conditional formatting is a technique in Excel that allows you to apply formats to a range of cells based off the value of the cell or the value of a formula. For example, you may want to highlight all cells where the value is more than 700.
I will take you through the most common techniques that will help you bring some clarity to your data. The two most useful techniques for business analytics are firstly, highlighting any cell based on a condition and secondly, highlighting entire rows.
1. Highlight all cells based on a condition
Let’s take a real example where we have inventory values by month for a set of products. It is difficult to put a filter on this data as we have so many data points. We want to show visually where the data is above 700 units. How the heck do we do that?
1.1 Step 1 – Highlight the cells
Highlight the cells to which you want to apply the conditional formatting. Next click the ‘Conditional Formatting’ icon under the ‘Home’ tab. We will use the ‘Highlight Cell Rules’ to start with. Select the ‘Greater Than’ option.
1.2 Step 2 – Set the condition
Now we want to add a condition. In the dialog box, set the value to be 700. You will see on the right side that there are some standard shading, or you can go the last option and add a custom format. In the custom format, you can change shading, font type, font colour and borders.
For hard coded values, the above 2 steps will work. However, if you want to enable some dynamic conditions, follow the below additional steps.
1.3 Step 3 – Create a variable for your condition (additional)
To make the above more dynamic, we can add variables to our condition. Say we wanted to highlight cells between 700 and 800 but we wanted to set it up so we can play with the ranges. To start with we add the variables ‘From’ and ‘To’ on the second row and add ‘700’ and ‘800’.
Next we click on ‘Conditional Formatting’ and select ‘Manage Rules.’
The subsequent step is to select ‘Edit Rule’ and use the last option ‘Use a formula to determine which cells to format.’ Type into the box ‘Format values where this formula is true:’ the below AND statement
Notice how I choose the top left corner cell of my range and I remove all $ signs, so the condition C6 can move across the entire selection. Also note the the fixed values $E$2 and $H$2 are locked in place with $ signs. We now have a cool conditional range that we can vary to see patterns in the data.
2. Highlight entire rows based off a condition
The second most common conditional formatting is to highlight entire rows based off a set of conditions. Let’s take an example with some ship to locations in the US. Our goal is to highlight all rows where more than 300 units were shipped and the transit time was less than 10 days.
2.1 Step 1 – Create new rule
Start by selecting the range you want the conditional format to apply. Remember to do this step, I often forget and it mucks up the solution.
Once your range is highlighted, click on ‘Conditional Formatting’ on the ‘Home’ tab. Next click on ‘New Rule.’ Go down to the last option once again ‘Use a formula to determine which cells to format.’
2.2 Step 2 – Add a condition to the rule
The trick to adding conditions when we want to highlight entire rows, is to lock the cell column range with a $. You will notice below, the first value of the ‘Units’ column is written as $F5 and the first value of the ‘TransitDays’ column is $I5. We do this to restrict the condition to one column. Where you place the $ sign is very important in getting your conditional formats w
2.3 Step 3 – Select a format
The next step is to set the format of the condition. Click on ‘Format’ in the above and select the appropriate shading colours.
After this has been done hit OK and you will get your result as per below.
That wraps up conditional formatting, you are now armed with some new techniques to give your data focus.