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 1: Data set-up

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

ExcelZone tutorialPrompted by a query in Any Answers, Simon Hurst sets out to show readers how they can build their own "traffic light" monitoring system using conditional formatting in Excel. But to get it right, you need to plug your spreadsheet into a suitable data source.

In a recent Any Answers question Dickey asked for help with setting up a traffic light system to indicate the health of some reporting entities by evaluating indicators such as timeliness and accuracy of reports. The resulting answers included links to some very creative uses of Excel, but in general the prevailing advice was to use Excel's conditional formatting feature.

Excel Zone has covered conditional formatting before, most recently looking at the significant changes in the latest version: Excel 2007 - Get ahead with conditional formatting. Dickey's question provides the ideal excuse to look at conditional formatting in a bit more detail, and also to examine some methods of getting at the data to be analysed. This animated image shows what we'll be aiming to end up with:

Access Query result for Northwind Traders

Accessing the data from Excel

Now we have our query set up in Access we can close our new database and set up our Excel spreadsheet. In order to leave room for some information at the top of our sheet, we'll position our linked data to start at cell A10. We can then choose Data-Import External Data (Excel 2007: Data ribbon, Get External Data section, From Access), New Database Query and, using an MS Access Database driver, find our EmployeeRating database and choose to use all the fields from our EmployeeRating query:

Excel Query Wizard

We can then go through the stages of the wizard accepting the defaults until we choose to place the data in cell A10 of the Existing worksheet. After using Tools Options, View, Gridlines (Excel 2007: Excel Options, Advanced, Display options for this worksheet, Show gridlines) to turn off the gridlines and tidying up the formatting a bit, and adding a heading and some data constants we should have something similar to the following:

Excel data range from Access Northwind Query

In Excel 2007 the External Data Range will be created as a table, and the editing tools will be found in the Table Tools, Design contextual tab.

Simple conditional formatting

In the next episode we'll look at creating a value rating and then using some advanced aspects of conditional formatting to translate it into a more visual indicator, but for now we'll use basic conditional formatting to highlight the SalesTotal figures in red, amber and green.

First of all, it's a good idea to allocate range names to the constants we have added. To do this, select the constants together with their labels (A3 to B8) and then use Insert-Name-Create (Excel 2007: Formulas ribbon, Defined Names section, Create from Selection). This will allocate names to the 6 value cells based on the text in the column to their left. Because range names cannot include spaces, the spaces will automatically be replaced by underscores.

Next select the SalesTotal figures and choose Format-Conditional Formatting. We will use the maximum of three formats and refer to our named cells, preceding the names with '=' in order to stop Excel assuming we want to compare to a text string rather than a range name. We have clicked on the Format button each time to set the Font colours to green, amber and red respectively:

Excel Conditional Formatting command dialogue

Conditional formatting is very different in Excel 2007 as detailed in Excel 2007 - Get ahead with conditional formatting. You can more easily achieve an improved result by using one of the Conditional Formatting, Colour Scales options, or the traffic lights Icon Set rather than setting up individual rules.

After setting sensible Green and Amber limits, our table of data should like similar to this:

Northwind sales figures with conditional formatting

Next time we'll add the multicoloured ticks.

Other ExcelZone management reporting tutorials
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 (1)

Please login or register to join the discussion.

avatar
By josephdobeng
04th Dec 2013 13:31

great

Thanks (0)