Category Archives: Data Visualizations

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

Advertisements

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.

Book Updates

I figured I’d break hibernation to give everyone a small, quick update on the book. According to the original dates agreed upon in my contract with Apress, I should be close to finishing my book by now. Well, internet, I’m not close at all. I still have a lot more work ahead of me. The new release date is September 29th, and I do believe the book will be complete by then. As well, the name might change at least nine more times before it’s finally printed. How about Fifty Shades of Excel?

What to expect

Above all, this is a book about Excel development of dashboards and interactive reports. If you’re looking for a short book that gives you just enough to make something and move on, this isn’t the book for you; although, there are many good books that do this (see Bill Jelen’s Mr. Excel LibraryJohn Walkenbach’s Mr. Spreadsheet series, and Michael Alexander’s Excel 2007 Dashboards & Reports, for example). My book is aimed at filling what I believe is a dearth of quality printed material on the subject of Excel development for building modern tools. Professional Excel Development was the last latest and greatest comprehensive book on really developing tools with Excel. It is my favorite book on Excel development, and a lot of my work owes to what I’ve read. (There are however many terrific blogs on Excel development, which I believe is in response to the lack of print material.) 
I hope you enjoy the book, I really do. So, in the interest of full disclosure, I do want to highlight what won’t be in the book. As of right now, there won’t be any Pivot Tables. Sorry for all you Pivot Table lovers out there. Here’s the skinny: when you use Pivot Tables for dashboards, you give up a lot of control. I think I make a pretty good case in my book, but I welcome feedback if you disagree. By extension, that also means no slicers. Look, I think slicers are very, very promising concerning where Microsoft wants to take Excel, but I kind of hate them. (Oh no, I’ve said too much.) They take up a lot of screen real estate and encourage people to make dashboards that are 80% slicers and 20% actual information. That’s not good. Also, there will no pie charts. 
Most importantly, this book will be about empowering you as an Excel developer. A lot of people think that you can only do cool stuff in Excel by using VBA. I say, VBA is only part of the whole package. Formulas are a part of it, too. But your mind, your ability to think critically and creatively, will ultimately be the most important tool in your arsenal. Right now, there are a lot of companies who want us to pay big money for infrastructure and data visualization tools we don’t need. I argue that you and I can do a better job than many of these vendors by doing the work ourselves in Excel. That is my motivation to write this book.
And now I bet you just can’t wait to get your hands on a copy!

Pre-ordering

So if you’re really, really interested in the getting a copy as soon as possible, there are a few options available. 

Through Apress

First, if you’re into the e-book thing, you can go to my publisher’s website and order a copy through their Alpha Book program. The Alpha Book program allows you to see each chapter as they are approved by my editors. The chapters will still be in development, so if you’re the type who likes giving feedback, you could email me directly with your thoughts. If you do choose to do the Alpha Book program, and you do frequently email me with solid feedback about each chapter you’ve read, I can maybe refund your purchase or even send you a signed copy of a print book. 

Take the refund. I have a terrible signature. 

Through Amazon

You can also order the book through Amazon below its bookstore price. The discount seems to change every few weeks, but this is the lowest I’ve seen it so far. If you follow the link below, I will get an infinitesimal referral fee.  

***
More updates coming soon. Thanks again to Winston Snyder for writing his awesome articles while I’ve been away. If you want to contribute an article, drop me a line.

Are You Series(ous)?

Do you work with different series in your Excel charts?

Here is what I would like the final chart to loo like. Note the white dashed lines in front of the columns:

Setup:

I started with the chart I ended with in my last post, Points Of Interest.

I then added some data to a separate worksheet in my workbook to replicate the horizontal gridlines.

The horizotal gridline scale is in units of 10, so I will use the same scale for my series’ that I create for the “fake” gridlines.





Note in Col A that I used the exact same scale that I used for Series 1 which is what was used to plot the column charts (Really 1 chart)

Also note that I put data that is used for formatting on a separate worksheet from “Value Data”. This makes it easier to maintain data as it needs to updated overtime

1. Horizontal gridlines

I am going to add fake horizontal gridlines to the chart, so first I’l remove the current horizontal gridlines.

  • Click on the chart (This will activate the Chart Tools Group on the Ribbon)
  • Click on “Layout” in the chart tools group
  • Click on gridlines
  • Click on Primary Horizontal Gridlines
  • Click on “None”

2. Add “Fake” horizontal gridlines

I copied all data from the formatting tab including the horizontal axis column, clicked on my chart and pasted the new series.

 I could click on each of the new series on the chart and change the chart type from column to line and apply all of the formatting. But I would like to do it with VBA. So I’ll step through each part of the process separately
 

3. Change the chart type:

First I need to chage the chart type for each series that is not a value series which was series 1. So I’ll loop through the SeriesCollection starting at number 2

1:  Option Explicit
2: Sub SetChartType()
3: Dim wb As Workbook
4: Dim ws As Worksheet
5: Dim i As Integer
6: Set wb = ThisWorkbook
7: Set ws = wb.Worksheets("Sheet1")
8: With ws
9: .ChartObjects(1).Activate
10: For i = 2 To .ChartObjects(1).Chart.SeriesCollection.Count
11: .ChartObjects(1).Chart.SeriesCollection(i).ChartType = xlLine
12: Next i
13: End With
14: 'Tidy up
15: Set ws = Nothing
16: Set wb = Nothing
17: End Sub

4. Format the color of the lines

I would like each of the horizontal lines to be white

1:  Option Explicit
2: Sub SetChartColor()
3: Dim wb As Workbook
4: Dim ws As Worksheet
5: Dim i As Integer
6: Dim lWhite As Long
7: Set wb = ThisWorkbook
8: Set ws = wb.Worksheets("Sheet1")
9: lWhite = RGB(255, 255, 255)
10: With ws
11: .ChartObjects("Chart 1").Activate
12: With ActiveChart
13: For i = 2 To .SeriesCollection.Count
14: .SeriesCollection(i).Select
15: With Selection
16: .Format.Line.ForeColor.RGB = lWhite
17: End With
18: Next i
19: End With
20: End With
21: 'Tidy up
22: Set ws = Nothing
23: Set wb = Nothing
24: End Sub

5. Change the line type:

The chart is looking pretty good. But the lines are a little thick, I would like something more subdued. I played around a bit with different line weights and dash styles until I found values that I liked

1:  Option Explicit
2: Sub SetLineProperties()
3: Dim wb As Workbook
4: Dim ws As Worksheet
5: Dim i As Integer
6: Dim lWhite As Long
7: Set wb = ThisWorkbook
8: Set ws = wb.Worksheets("Chart")
9: lWhite = RGB(255, 255, 255)
10: With ws
11: .ChartObjects("Chart 1").Activate
12: With ActiveChart
13: For i = 2 To .SeriesCollection.Count
14: .SeriesCollection(i).Select
15: With Selection
16: .Format.Line.Weight = 1.5
17: .Format.Line.DashStyle = msoLineRoundDot
18: End With
19: Next i
20: End With
21: End With
22: 'Tidy up
23: Set ws = Nothing
24: Set wb = Nothing
25: End Sub

I added the major horizontal gridlines back to the chart and made them very thin and light grey

The final chart:

The lines may be a little thin for your tastes, just adjust the line weight until you get the effect you are trying to achieve.

I hope you enjoy the post and that you find something of value in it.

Additional resources:

How do you use the SeriesCollection in your VBA or work with series in your charts? Let us know in the comments below.