Create visual dashboard metrics using Conditional Formatting

Instructions: You can type values or formulas into cells above but to access Excel functionality, or if it is easier to work directly in Excel, open the file by clicking on the Excel Icon at the bottom.

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

In Excel 2007-2010 the capability of Conditional formatting has been extended to include the ability to display graphics that show the relative value of each cell within a range. So for example, rather than just using conditional formatting to highlight numbers that are greater or less than a threshold, you can instead display an in-cell data bar to show how close to that threshold each value in the range is. Or alternatively, you can define a group of categories defined by a series of thresholds (such as Red/Amber/Green, Low/Medium/High) and then use conditional formatting (in particular icon sets) to show which category each value falls into.

Applying a Data bar to a selection of cells allows you to define a range (a minimum and maximum value) and for each cell in the selection an in-cell bar will show where in this range the value sits.

This is shown in the screen shot below – the range is set from 0-750, so values that are close to the maximum (e.g. the value of 600) have a cell that is nearly full with the bar, and all other values are proportionally sized.

To apply this, firstly select the range you want to apply the conditional formatting to and then select Conditional Formatting -> Data bars, and pick a data bar style/colour from the selection.

Once you have selected a data bar style, Excel will apply it to the range selected according to automatic default thresholds – the largest value in the range will be set as the maximum (full bar value) and 0 will be the minimum (empty bar value)

To change this to your own thresholds, highlight a cell with the rule applied and then click on Conditional Formatting -> Manage Rules. The rule should appear in the Rules Manager (see screen shot below). Note that the default is to only show the rules that apply to the cell(s) that are selected but you can see all the conditional formatting rules applicable in the worksheet by changing the ‘Show formatting rules for’ dropdown at the top.

To modify the data bar conditional formatting rule, highlight the ‘Data Bar’ rule and click ‘Edit Rule.’

You will then be presented with the below dialog box. The Minimum/Maximum type will be ‘Automatic’ – to change this to a static number or a cell reference change it to type ‘Number’ . In the example below our range is from 0 to the maximum threshold number in cell K4.

Other range types include Percent (range based on value relative to the maximum value) or percentile (range based on value relative to the total).

Applying an icon set to a range allows you to define multiple thresholds (e.g. low/medium/high) and then show a set of icons (e.g. red/amber/green) within each cell to indicate which category it falls into.

Like with all the conditional formatting rules, these thresholds can be static numbers, or can be made dynamic by linking to a cell. The thresholds can also be based on the relative value of the cells – for example putting the top 10% of the numbers (either by count or value) together into one band.

Applying an icon set works in a very similar way to Data bars. Firstly select the range you want to apply it to and then select Conditional Formatting -> Icon sets and pick an icon set from the selection (screen shot below).

Once you have selected an icon set Excel will apply it to your range based on automatic thresholds based on Percent. For example if you select a 3-colour traffic light, then the bandings will be based on values which are greater than 67% of the maximum value being Green, values falling between 33-67% of that value will be Amber, and values less than 33% of the highest value will be Red.

In the example above, the largest value in our range is 600 so any values less than or equal to 200 will be Red, any between 200 and 400 will be Amber and any values greater than 400 will be Green.

To change the thresholds, we select on a cell that has an icon, click on Conditional Formatting -> Manage Rules. Click on the rule we want to change thresholds for and click on ‘Edit Rule’.

To change the threshold to be based on a static number or cell value, change the Type from ‘Percent’ to ‘Number’ and type in the number or point to the cells where threshold values are stored.

The types of threshold you can choose here are Number (either static or linking to a cell), Percent, Percentile, or Formula (which allows you to define custom thresholds). The distinction between Percent and Percentile is quite important – Percent will rank numbers by count whereas Percentile ranks by value.

For example if you select a 3-colour traffic light with Percent bandings of 67% for Red and 33% for Amber, then the bandings will be such that the top third of numbers by count will be Green, the bottom third red and the middle third Amber. So if you have 6 values these will be split by having the lowest 2 in the Red category, the middle 2 in the Amber Category and the highest 2 in the Green category.

By contrast, Percentile ranks the top X% of numbers by value (as opposed to by count). For example to highlight the top 5% of values we select type ‘Percentile’ and put a value of 95.
Note that there is an Excel function which calculates percentiles and an alternative (perhaps more transparent) approach would be to calculate the threshold at which the required percentile occurs and then base conditional formatting on this. The PERCENTILE function =PERCENTILE(Range of cells, 1-Decimal Percentile required). E.g. to see the threshold relating to the top 5% of the numbers in the range D2:D8 we use the function =PERCENTILE(D2:D8,0.95) which returns 570, indicating that values which are greater than 570 fall into the top 5%.

For each of the following worksheets in the Topic Example Workbook, create conditional formatting rules on the ‘Quantity Sold’ figures (cells D2:D8):

• Data Bar based on Automatic – Apply as a data bar and leave the automatic thresholds
• Data Bar based on Thresholds – Apply a data bar and change the thresholds to point to cells L3/L5
• Icon Set based on Automatic – Apply an icon set and leave as the automatic thresholds
• Icon Set based on Thresholds – Apply an icon set and change the thresholds to point to cells M3/M5
• Icon Set-Fixed Percent – Apply an Icon set, change the threshold type to Percent, with values for >=67% for Red and >=33% for Amber
• Icon Set-Fixed Percentile – Apply an Icon set, change the threshold type to Percentile, with values for >=80 for Red and >=30 for Amber
• Icon Set-Dynamic Percentile – Use the PERCENTILE function in cells M5 and M11 to work out the threshold based on the percentile in cells M3 and M8 respectively. Then apply an icon set with thresholds based on the values in cells M5 and M11.

A demonstration of Data bars (based on Thresholds) and Icon sets (based on Percentile) is shown in the video clips below.

Resist the temptation to view the answers file below until you have tried this!

Data Bars



Icon Sets

Once you have completed the ‘Try it Out’ exercise, the completed Excel file should look like this:

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.