Category Archives: Dashboards

Effective means of display in Excel

There are many great articles and tutorials on how to build interactive data displays in Excel. However, there are few articles on how best to effectively display data in Excel. I think this is an important subject that every Excel user should at least think about.

On the other hand, I personally don’t like any set of rules that say this key performance indicator or metric or chart must always be used. If we think about the best books on English, say The Elements of Style, or even the best books on data visualization, say The Visual Display of Quantitative Information, we find neither author posits their principles as hard and fast rules. Rather, they submit a set of guidelines, heuristics to help us test and decide. The Excel developer world should have such guidelines, to which I present the following for effective means of display in Excel [1]:

– Mutual Exclusivity

– Common Interpretation

– Sufficiency

Mutual Exclusivity

Mutual exclusivity speaks to presenting data and metrics whose different meanings do not overlap. I say “meanings,” here, because there may be instances in which metrics overlap, but their meanings do not. Consider a simple income statement. We all know Gross Profit = Revenue – Costs. If we were to ignore meaning when applying the test of mutual exclusivity, we might venture to only include two of these measures, since the third measure is only one algebraic calculation away. (For instance, we could divine Gross Profit in our heads by only present Revenue and Costs.) In this sense, there is surely overlap in all three of these metrics. But in our understanding of business performance, showing all three is the best option because the meaning they impart is separate and independent.

On the other hand, take a look at the following chart.

clip_image001

The addition of a total measure into the Unit Sales chart does little help us divine regional sales differences. Indeed, the total is already plotted to a certain extent – it’s the aggregation of the other regions. Now consider the average measure. The data variation between regions is such that we can gauge the average with our eyes (perhaps not with granularity, but the additional metric does not help us with this either). I argue these two metrics provide an unjustifiable overlap already inherent within the presented regional sales differences. (But you may want to include these metrics for good reason, and we’ll deal with a way to do that by the end of this article.)

clip_image002

Common Interpretation

Common interpretation refers the organizational, institutional, and even societal paradigm through which we understand and interpret data. That may have been a mouthful, but it simply means this: all the people looking at the same metrics must understand them in the same way.

This is especially important for metrics created by and for a specific organization or market. Most of us agree on how to calculate the Current Ratio (we should, there’s really only one way to do it). On the hand, a single organization could come up with its own metric. Consider a performance metrics that results in a score within the boundaries of zero and one. For those familiar with the metric, the results are easy to interpret. But for those on the outside, there may be puzzlement. And unless everyone agrees, placing it on your dashboard or report is sure to generate confusion.

I speak from experience. I’ve watched organizations argue over such metrics and what they mean. When this happens it’s time to take a second look at the measure. Or, consider this scenario: an organization rates its employees once a year. One underperformer receives a 3 out of 5 each year, which according to the company definition of its own metrics is a “good” score. However, compared to his 5 out of 5 peers, he is an underperformer. The employee is fired for underperforming but sues the organization since a 3 out of 5 is considered good on paper. Who’s right? This is an example of an uncommon interpretation.

Sufficiency

Sufficiency speaks to whether there are too many or too few metrics to sufficiently deliver the message required. Sufficiency is related to mutual exclusivity. However, sufficiency deals with the extraneous and insufficient inclusion of metrics. For example, you may find some white space on your dashboard that could be filled in with even more information. If that information doesn’t aid in the understanding of that data already included therein—don’t add it! The whitespace might feel like empty real estate, but not every metric needs a neighbor.

Let’s go back to the first chart presented. Here again, average and total are the problem, but removing them might also present an insufficiency in what information should be delivered. If the aim of the chart is to present the intra performance of regional sales, then the inclusion of these metrics certainly inhibit our understanding. For example, the inclusion of the total requires we stretch the chart range out considerably, thus dampening the variation between each region. But we can still present this information in a way that doesn’t compete with its message while presenting a sufficient amount of information. The chart below demonstrates one such way you could use in a pinch.

clip_image003

Conclusion

The most important thing to remember is that these aren’t hard and fast rules. Consider them as a guide as you continue your Excel journey. As we are now merely scratching of what Excel can do, effective means of data display will serve us well.

————————————————————-

[1] Adapted from the field Value Focused Thinking and quite specifically from the work of Gregory Parnell.

Parnell, G. S., Chapter 19, Value-Focused Thinking Using Multiple Objective Decision Analysis, Methods for Conducting Military Operational Analysis: Best Practices in Use Throughout the Department of Defense, Military Operations Research Society, Editors Andrew Loerch and Larry Rainey, 2007. link

Handling Excel Rollover Popups

Various people have written to me asking what they should do about popups generated by the rollover method that don’t go away on their own. Essentially, the rollover method provides for an “on mouse over” style routine but not for a “on mouse out.”

The “moat” method

One method to address this is to create a “hotspot moat” around the initial hotspot zone. In the moat, you would again use rollover technique; now, it would hide any popups initiated by the original hot spot zone.

image

The moat method has a rather significant drawback: if you move your mouse too quickly over the moat, you might not trip the hyperlink to remove the popup. When that happens, your pop-up remains no matter what.

The Selection Change Method

My friend Robert Mundigl of ClearlyAndSimply.Com in his Bruce Springsteen Discography in Excel Dashboard noticed this drawback as well. So, he employed another trick: use the Worksheet’s Selection_Change event like you would the moat. With this technique, when the user selects anything else on the spreadsheet, the popup is always hidden.

The Timer Method

The final method – the one for which I’ve received the most requests for instruction over email  – is a mechanism to hide a popup box after a few seconds. A screen cast of it appears below.

popup fade

This method uses the windows Timer API to begin fading the popup after a very small amount of time. One clear advantage to this method is that it takes care of any popups when your mouse leaves the hotspot zone. However, if you pay careful attention to the screencast, you’ll see that a popup might begin fading even if your mouse remains in the hotspot zone. (You can see this when my mouse hovers over “3”… wait for it.) If you keep your mouse still, the rollover won’t get re-tripped.

So you need to do something to “retrip” it. Here’s my hack. The numbers in the hotspots are actually a reference from in Column A. See below:

image 

So, in my rollover function, I’ve included this additional line:

   1: Sheet1.Range("a4:a6").Cells(index, 1).Value = index

 

This simple bit of code does nothing more than reassign the cells in Column A to the same values that they were. In the picture above, when your mouse is over the hotspot in C4, the code snippet instructs cell A4 to be reassigned the value of one. However, since cell C4 is also dependent on the value in A4, Excel treats the assignment as a change in its dependency chain. This causes the Hyperlink function to update – and this update retriggers the rollover action. Problem solved.

image

You can download the timer-based popup here:

Textbox fade example2.xlsm

Feel free to use it in your projects without attribution. But, if you really like it, I’d love to hear from you.

A Dynamically Linked Checkbox Matrix with Microsoft Excel

Edit: sorry for some of the squashed images you see in this and other posts. I’m still working on my layout. In the meantime, click on an image to view it in full if you’re having trouble.

Today, I want to show you what I liked to call a “ dynamically linked matrix.” I’ll post an animated gif of the final product at the end of this discussion. I actually started with the gif at the top, but as I began writing, I found the gif nauseating! So if you want to skip ahead to see the final product, scroll down to the bottom of this post.

Checkbox Matrices

I recently had a project that required the user to toggle a list of features using a matrix similar to the one below. Here, I’ve replaced the grid headings with numbers and letters, but hopefully you can think of a few use-case examples where such a matrix would prove useful.

image

I like how form control checkboxes can be linked to other cells on a spreadsheet. One advantage of this linkage is that I don’t have to handle each click through the VBA. No code is required to allow the user to toggle between two different states; updating the spreadsheet and checkbox is taken care of for me.

In the matrix above, I wanted each cell to be a clickable area, so even if the checkbox itself was not clicked, I could still capture the same click event if there was a click anywhere in the cell. To achieve this, I created a named range out of the matrix above and called it Main.CheckboxMatrix. Then in Main.CheckboxMatrix, I went through each cell and created a checkbox to be placed directly on top of it. I set the top, left, width, and height values for each checkbox to the same as the cell. I also removed the caption to give the effect above. Here’s the relevant code from the project.

For Each rngCurrent In [Main.CheckboxMatrix]
    Dim chkbxTemp As CheckBox
    Set chkbxTemp = Me.CheckBoxes.Add(1, 1, 1, 1)
   
    itr = itr + 1
    Dim rowIndex As Integer
    Dim colIndex As Integer
   
    rowIndex = (itr - 1) \ 5 + 1
    colIndex = (itr - 1) Mod 5 + 1
   
    With chkbxTemp
        .Caption = ""
        .Display3DShading = False
        .Width = rngCurrent.Width
        .Height = rngCurrent.Height
        .Left = rngCurrent.Left
        .Top = rngCurrent.Top
        .Name = "Main.chbx" & itr
        .Value = Me.Range("link" & itr)

        .OnAction = "Main.RespondToShift"
   End With
Next rngCurrent

 

Note: I create the checkbox first with dummy top, left, width, and height values (in this case, I set everything equal to one). I change them later.

Dynamic Linking

To create the effect of “dynamic linking,” I had to go through each checkbox and set a reference to the backend data it represents. I find that making a named reference for each checkbox works best. In my example, each reference is named linkXX where XX is a number. If you look at the code above, I also name each checkbox Main.chbxXX where XX is a number. The numbers at the end of each name allow me to easily associate a checkbox with a link by using a common index.

The following code is found in the worksheet’s change event. When another entry of the dropdown is chosen, I go through link reference to update where it points. 

For Each curName In ThisWorkbook.Names
    Dim strName As String
    Dim index As Integer
    Dim chkbx As CheckBox
   
    strName = curName.Name
    If InStr(1, strName, "link") > 0 Then

        index = Replace(curName.NameLocal, "Main!link", "")

        Dim rowIndex As Integer
        Dim colIndex As Integer
       
        rowIndex = (index - 1) \ 5 + 1
        colIndex = (index - 1) Mod 5 + 1

        curName.RefersTo = Data.Range("Data." & Target.Value).Cells( _
            rowIndex, colIndex)
       
    End If
Next

Note that I do no need to reassign the value of each checkbox. Because each checkbox is assign to a link, only updating the links is necessary.

Conditional Formatting

To help provide visual cues about what items have been toggled, I’ve employed conditional formatting on the cells behind the checkboxes. You can reach these cells by clicking on a cell outside of the matrix and using your arrow keys to traverse your way in. On a much larger matrix, you’d probably want to using the Selection Pane to hide the checkboxes. 

image

The table on the right helps define the conditional formats for each cell (see the image below). In a final project, you’d want to hide this table by hiding its columns or by setting the font and border colors to white (to blend in with the background).

image

As you might have guessed, the conditional format table is also linked to the backend data. Note that this parallels how the checkboxes are linked. 

image

Flagging

You might run into a situation where you just want to flag a feature to think about it later. To achieve this, you’ll need to create a third state. Checkboxes will show a check or unchecked boxes for TRUE/1 and FALSE/0 values. However, they will also show a “mixed” state for NA() values. 

To flag a feature, the user holds down SHIFT as they click on a checkbox. Using code I’ve borrowed code from Chip Pearson, each checkbox has been assigned the same macro to test if the SHIFT button is held down. That macro is shown below.

Public Sub RespondToShift()
    Dim index As Integer
   
    index = Replace(Application.Caller, "Main.chbx", "")
   
    If IsShiftKeyDown Then
        ThisWorkbook.Names("link" & index).RefersToRange.Formula = "=NA()"
    End If
End Sub

Again, following the index numbering convention, we only need to use one macro to handle everything verses using creating a dependent macro for each checkbox. The reason we can do this because the name of the CheckBox is passed into the macro through the Application.Caller variable. If the SHIFT key is held down, we can tell Excel to set the backend data to NA().

What I like about dynamic linking

One great feature of this example is that all information has been linked. When you toggle a feature, the backend data is changed automatically. Very little VBA is used except to set up the spreadsheet (only performed once, ideally) and to help us relink the different elements together.

Dynamic Links

Get the download file

Checkbox Matrix Example.xlsm

Another way to do bullet graphs in Excel

Those of you who know me, know I’m a huge fan of Stephen Few. (I mean, I had him autograph my copy of Information Dashboard Design!) Few is highly regarded as an expert on data visualization and dashboard design. He is the original designer of bullet graphs.

Bullet graphs are not native to Excel’s chart library. If you want to create a bullet graph, be prepared for some finagling with chart features, layouts, and color. Several Excel blogs have articles about creating bullet graphs. Here’s a list of my favorites:

How to Make Horizontal Bullet Graphs in Excel and  How to Make Vertical Bullet Graphs in Excel by Jon Peltier

How to Create Bullet Graphs To Replace Gauges in Excel by Charley Kyd

Excel Bullet Graphs by Chandoo

Bullet Graphs for Excel: A Simple Way? by Matt Grams

In addition, Michael Alexander’s book Excel 2007 Dashboards & Reports for Dummies has several great suggestions regarding bullet graphs. And you might also consider Sparklines in Excel, a free tool that can create bullet graphs (and much more) for you automatically. Finally, if you haven’t attempted making these graphs on your own before, I’d start with Jon Peltier’s tutorials. In my opinion, his are the best. Or, alternatively, you could just wait for my book to come out. (Great idea!) But I don’t blame you if you don’t feel like waiting. 

My solution

I’m ready to submit my own solution to creating bullet graphs in Excel. I’ll tell you now it’s an imperfect solution – and there are tradeoffs depending upon what you want to display. You’ll see what I mean in a moment. Basically, my version is a combination of Excel’s Sparklines features and in-cell bar charts.

Here’s how it works. First, you create a series of numbers like I’ve done in the graphic below. These numbers will help form the shaded part of my bullet chart. The basic rule for now is that you need three different groups of three different numbers.

image

Next, select the entire range and insert a new Sparkline Column Chart (in the Sparklines Group on the Insert tab). Choose a location off to the side in which to place your new chart to take advantage of the full length cell. You should have something that looks like the image below.

image

If white spaces appear between the columns in your cell, adjust the cell’s column width to a smaller size until the white spaces disappear.

Next, click on the cell so that the Design tab appears in the tab menu at the top. Check both the High Point and Low Point checkboxes in the Show group. Now, staying on the Design tab, select Marker Color (in the Style Group), pull up the High Point color selector and choose a dark shade of green. Click Marker Color again and this time select the Low Point color and choose a very light shade of green. Finally, click Sparkline Color and select a middle-level shade of green. You now see something like this:

image

From here, we’ll do some axes adjustments. From the Design tab, press the Axis dropdown and select custom value for the minimum value. Type 0 and press ENTER. Now, again, select a custom value but this time click on the maximum value. Type 1 and press ENTER. You should now see a graph like this:

image

OK. With our shaded regions defined, we’re now ready to do some experimentation.

For a simple bar, we can use the ol’ pipe symbol repetition method, like this. One cool feature of the pipe symbol method is that I can make the bar go in the other direction by changing the alignment from left-aligned to right-aligned. Make sure to try it out in the enclosed example file.

image

We could also use various symbols as well (as a variation on the on the pipe symbol method). See this article by Jon Peltier for ideas.

image

If we wanted to insert a target into the chart, we could also insert a line shape like this (now with new colors!):

image

I’m not a huge fan of this method because it’s not easy to automate. If you have a series of bullet charts all of which have a target of 100%, the line isn’t such a bad idea. You could draw a straight line down across the series of them. But the pipe symbol repetition and shaded regions are all easily defined and changed with formulas. For example, I went back to our original data range and replaced several three’s with two’s to increase the range of the middle region.

image

One way to make the target-line automated is to create the line out of the Sparkline columns. If I go back to my original data set, I can change it to look like this:

image

Note that I’ve inserted a one into the series of twos and left the remainder blank. (You don’t need the red font, I just wanted to draw your attention to the difference.) My graph will now look like this:

image

But if I change the Low Point’s marker color to black, I can make it look like this:

image 
If I move that one in the range, I can make it look like this:

image

Obviously, I lose the final shaded region with this method. But that might not be a big deal in the end. With some scales and design, the last shade is still visually implied:

image

One last alternative (see edit!), also not really a favorite, is to use the the pipe symbol for the target and setting the font to a strikethrough style, like this:

image

I don’t like this method as much because the thinness of the strikethrough line bothers me. But of all the methods presented so far, strikethrough doesn’t require I give up any regions. Also, note, the last region hasn’t been shaded but the region still feels somewhat there.

EDIT!

I don’t know what I was thinking when I posted the strikethrough method. If you use strikethrough, you won’t ever get to a point where there’s distance between your performance measure and your target. I’ve kept it in the in the example file so you can see what I mean. However, you can still get something similar to the strikethrough method by using the dash (“-“) character with the pipe symbol instead. 

Thoughts

I really haven’t said anything about scaling – and there are scaling issues! You’ll have to play around with your REPT() formula to figure out how many pipes (or other desired character) can fit in the cell. You’ll then have to figure how much one character unit represents. Finally, you’ll have to ensure your character units align correctly with your Sparkline Column units. For now, I leave that up to you. In the future, I plan to have more applied examples. Until then, you can download this file of examples:

In Cell Chart Examples.xlsx

As a bonus, I’ve included this in-cell box and whisker chart:

image

One last, final point. I’d love to hear your feedback. I still have yet to play around with these charts in full, so I’m not entirely convinced of their usefulness. If you hate them, let me know. If you love them, let me know. Or, if you have other ideas for creating in cell charts using Excel Sparklines, share ‘em!

Also, Stephen Few recently released a new edition of Information Dashboard Design: Displaying Data for At-a-Glance Monitoring, which I highly recommend.