A previous article outlined a simple method for creating a 'Last Ten Results' indicator panel in Excel (see link). This supplement shows how to add interactive drill down functionality (Excel skill level: advanced)
Another previous article had shown how to use Excel comments boxes dynamically to provide drill down details (see link). This method can be adapted to work with the last ten results indicator panel. There are a few technical challenges.
Reversing a linking formula
The cells coloured green, red and grey in the indicator panel read values from a separate data area. The way the data is structured (in this example) the drill-down needs to read from three adjacent rows in the data area, e.g. cells AF10, AF11, AF12. This is trickier than it first seems.
Some VBA code which can do this set out below. It is triggered by the Worksheet Selection Change event i.e.the code is run each time a different area on the worksheet is selected. So the code is run when any of the coloured bars are clicked on, displaying the appropriate details for the cell It is run again when any non bar cell is clicked on, removing the previously displayed details.
To make the code a little easier to follow it has been split into three blocks. The first block defines the memory variables that will be needed, clears any pre-existing comments from the worksheet, tests to see if the selected areas is withing the defined target area (i.e the whole indicator panel), and tests to see if it is a coloured results cell or simply the margins around the coloured results cell (requiring no drill down)
The next block of code records the coordinates of the cell where we want the drill down to be displayed and works out the coordinates of the topmost data cell relating to it
The final block collates the details from the data area into a cell comment and makes it visible. The details are produced by concatenating all the individual pieces, and using Chr(10) to force new lines within the comments box.
Inevitably, this VBA code could be written in a more compact and slick manner. But it works as is and runs quickly enough. Also note that the method of 'reversing' the link to identify the data cell is designed for cases where both sections are on the same worksheet. It would need further refinement if the data was located remotely
References
Excel: creating a 'Last Ten Results' indicator panel without using VBA by Edgar Bolton.
B I Dashboard Design. 31/01/2016 (link)
Excel: Using cell comments to provide "drill-down" details by Edgar Bolton
B I Dashboard Design. 05/08/2015 (link)
Excel: Using arrows to indicate direction of change in performance by Edgar Bolton
B I Dashboard Design. 26/07/2015 (link)
How to convert Excel column numbers into alphabetical characters. Microsoft. Article ID: 833402 - Last Review: 09/19/2011 00:01:00 - Revision: 5.0 (link)
The BBC website has many good examples of how to display information. The following device is used to summarise a series of results.
The BBC use it to display football results in a League Table (see link). The concept is used more widely in Business intelligence dashboards. There are various approaches to creating this effect. This short article shows a simple way of constructing it using Excel without the need for any VBA coding (Excel skill level: Intermediate). The main idea behind the approach is to use an entire spreadsheet cell for each coloured bar. The colours are controlled by Conditional Formatting.
Step One
Set up a data area. Here, a block of ten successive results by the football team Leicester City F.C. is used. In the illustration, the data is set up on the same worksheet as the dashboard panel. In a real application, the data would probably be held separately.
Step Two
Each set of bars requires three spreadsheet rows. The middle row is for the coloured bar; the rows either side are clear margins. Similarly, additional columns are needed between each of the bar cells. Mark out the grid area by filling each bar cell with a temporary grey background. Then link each bar cell to the corresponding results in the data area.
Step Three
Add Conditional Formatting to the bars. This can be done to the full range as a single block. Three rules will be needed, one for each colour: In the example, "Win" is to display as Green; "Draw" as Grey and "Loss" as Red. Set the formats so that the font and the background colours are identical, within each rule.
Step Four
If desired, add a light background tint to the whole panel (and set the font colour exactly the same)
Step Five
Shrink the grid by setting column width and row height properties. In the example the following values are used: Bar row 3, margin rows 6, bar columns 1, interval columns 0.17. It may help to zoom to 200% while doing this.
Step Six
To add normal rows of text alongside the bars, merge the three rows in the area adjacent to the bars. This re-creates a more normal row height into which text can be added. When all the row and column adjustments have been made, switch off the grid-lines on the worksheet
It might seem fiddly to set up, but once one line has been created it can be cloned easily. Simply copy and then paste blocks of three rows at a time.
Enhancements
An additional feature on the BBC website displays the details of each result when the cursor hovers over each coloured bar. A way of adding this type of functionality in Excel (using VBA) is set out in a supplementary article (see link)
References
Excel: adding interactive drill down to a 'Last Ten Results' indicator panel by Edgar Bolton. B I Dashboard Design. 31/01/2016 (link)
Excel: Using cell comments to provide "drill-down" details by Edgar Bolton
B I Dashboard Design. 05/08/2015 (link)
Excel: Using arrows to indicate direction of change in performance by Edgar Bolton
B I Dashboard Design. 26/07/2015 (link)
