Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Applied Excel: Building a traffic light KPI dashboard. Part 2: Conditional formats

by
21st Oct 2008
Save content
Have you found this content useful? Use the button above to save it to your profile.

ExcelZone tutorialIn the second part of this Applied Excel tutorial series, Simon Hurst explains how to translate numeric data into "traffic light" style indicators using Excel's Conditional Formatting commands.

In the initial episode of this two-part series, we linked Excel to our employee data and applied a very simple traffic light conditional format. In this, the second and final part, we are going to create a rating value based on the three factors, and then create a variable number of ticks which we'll colour automatically using a slightly more advanced application of conditional formatting.

Rating formulae

We are going to add some columns with rating calculations to the right of our linked data. Before we add the columns, we will set some of our External Data Range properties. Right click in our linked data and choose Data Range Properties. Here we have given our External Data Range a sensible name, set the refresh options to refresh every 30 minutes and turned on the option to 'Fill down formulas in columns adjacent to the data'. This last option will ensure that our ratings formulae are automatically copied to any new employees when the data is refreshed [Coloured text relates to the highlighted selection in the graphic above, except for blue text, which describes the relevant Excel 2007 menu option. Ed.:

Excel External Data Range Properties command

In Excel 2007 the properties are set via the Table Tools, Design ribbon. The Table Name is set via the Table Name button in the Properties section and the refresh control via the Refresh button, Connection Properties option in the External Table Data section. Because the data range is created as a table, adjacent cells automatically become part of the table and will be copied to new rows.

Now we can add our new columns of formulae. The columns will be headed Loyalty, Sales, Discount and Rating. The first three will allocate a value based on the values for HireDate, SalesTotal and DiscountTotal respectively. We will use the values in the Age Factor, Sales factor and Discount factor cells to calculate a rating number. In each case we will round the resulting value to zero decimal points. Our employees will receive a point for each year of employment, a point for each £20,000 worth of sales and lose a point for each £2,000 of discount allowed. All a bit unlikely and arbitrary, but it's the thought that counts. The formulae for the top row are as follows:
Loyalty: =DATEDIF(D11,TODAY(),"y")/Age_factor
Sales: =ROUND(E11/Sales_factor,0)
Discount: =-ROUND(F11/Discount_factor,0)
Note that there is no need to round the Loyalty formula, as the DateDif() function returns a whole number of complete years. Also, the DateDif() function is not one you are likely to find in the Excel, Insert Function dialog or even Excel help – although Microsoft do acknowledge its existence occasionally http://office.microsoft.com/en-us/help/HA011609811033.aspx .
We have used the range names we created for our factors in part 1 in the formulae, rather than direct cell references in order to (hopefully!) make the formulae easier to understand and avoid having to worry about explicit absolute references.
Finally we'll add a formula to the overall rating column to add our individual ratings and divide by our 'Tick factor' to generate a manageable number of ticks:
=INT(SUM(G11:I11)/Tick_factor)
Rather vindictively, we've used the INT() function to ignore any fractions, so an employee receiving 2.99 would get a rating of 2 while one earning 3.00 would get 3. A more fair-minded approach might be to use ROUND() rather than INT()!
We'll remove our example conditional formatting from the SalesTotal column and reset the limits to values appropriate to our ratings. Here is the result:

Northwind data for conditional formatting

Ticks
Now for the fun bit. We will add a column next to rating that will contain our visual traffic light indication of the rating. We'll do this by entering a number of ticks equal to the rating, and then colouring those ticks according to the values set in Green limit and Amber limit.

First of all the formula. This uses the REPT() function to repeat a character a given number of times. We haven't just typed a character but instead used the Insert-Symbol screen to find the character code for a tick using the Wingdings font, and then used the CHAR() function to enter that code. The cells containing ticks will need to be formatted to use the Wingdings font in a suitably large font size.

Wingding symbol menu

The formula itself is:
=REPT(CHAR(252),J11)

Conditional formatting using a formula

The conditional formatting we used in the last episode on the SalesTotal column just set a format for a cell based on the value in that cell. This time, we want to set our format based on the value in a different cell, so we select all the required cells (K11 to K19) and then use the 'Formula is' condition type. We compare the value in the cell to the left in the top row - J11- with our different limits. Note that the formula begins with the = sign and refers to the named range. Again we have used our maximum three conditions to create our three traffic light colours:

Conditional Formatting - formula dialogue

The actual rules are very similar in Excel 2007, but they are set up one at a time, and the limit of three no longer applies:

Excel 2007 Conditional Formatting Rules Manager

If we now select the columns holding the rating values and right click and choose 'Hide' we are left with our linked data and our ratings ticks. We can then experiment with the different factors and limits, and also add some employees to our Access database and refresh the data to see our ticks copied down to the new rows:

Northwind sales figures with conditional formatting

Thanks are due to Microsoft MVP Bob Phillips for the inspiration for much of this approach. Bob ran a session at the 2007 Excel User Conference which dealt with improving the visual impact of your Excel spreadsheets including the use of conditional formatting to create traffic lights. His website includes the further development of some of these ideas, including the creation of something that actually looks like a real traffic light. In addition, some of the links included in the comments to Dickey's original Any Answers post revealed some other very creative approaches to the use of conditional formatting including the use of the camera tool

Other ExcelZone management reporting tutorials
Excel 2007 - Get ahead with conditional formatting
Sales analysis at Northwind Traders
Improve your reporting skills with self-teach tutorials
Interested in Pivot Tables? Start here

Subscribe to the ExcelZone newswire
Subscribe to the ExcelZone NewswireTo ensure you don't miss any of AccountingWEB's management reporting tutorials, click the button below to subscribe to the free monthly ExcelZone newswire. The subscribe function will take you back to the AccountingWEB home page after it adds your name to the subscription list.

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.