This is an excellent reference and tutorial on using dashboards within excel.

Kind of makes me wish I had lots of data and an audience for visualizing it.”

5/5 star Amazon review.

Order Now!

 

 

81ord-D3ajLThis book will change how you approach building models and applications in Excel. Jordan forces you to think outside the box, and makes some strong arguments against ‘standard’ spreadsheet development practices.”

Jon Acampora, Excel MVP.
Owner, ExcelCampus.com
Order Now!

computer-chart-800px

Three Common Dashboard Mistakes

I recently contributed to the article on MRC’s Cup of Joe Blog called 7 harmful business dashboard mistakes. I’ll excerpt a version of what I said below, but you should follow the link to read the whole thing. (I’ll use the original version I sent to blog so as not to compete for search keys.) I’ll also add two more. So here they are, three common dashboard mistakes that organizations make.

1. Data Dumping

This is the process of placing everything on a dashboard because you are either unsure of what stakeholders truly want or because you want to make everyone happy. Either way, the result is usually a confusing mess. Dashboards that attempt to be everything to everyone become nothing to anyone.

2. Bad Data Visualization

BI vendors have convinced us flashy, three-dimensional charts will save our businesses but data visualization research suggests they hurt more than help. Stick with the charts you know and avoid the stuff that looks complicated and self-indulgent. Dashboards aren’t marketing material; they don’t need to flashy, glitzy, over-the-top, or manipulative.

3. It’s Not a Technology Problem

So many business start dashboard projects only to spend 80% of their energy on researching and integrating a new BI platform and 20% on the dashboard itself. Those numbers ought to be the other way around. Dashboards are a business case, not a technology project. When we treat their development like a technology project, we face the budget and schedule overruns so familiar to IT departments.

—–

Going’s On…

It’s been a while since I last posted and there’s some interesting stuff going on.

Two-day Excel analytics short course in Cincinnati, Ohio

I’ll be teaching a two-day short course at the University of Cincinnati on analytics with Excel. This will be my third time teaching at the University of Cincinnati center for Business Analytics. These classes are among my favorite to teach, and I always have a great time. If you are in the area, make sure to sign up.

Click here to register!

PASS Business Analytics Conference Speaker

I’ll be speaking at the PASS Business Analytics Conference again this year. Click here to see my profile and register!

Excel Modeling and Analytics Video Course

I’m currently developing an online video course in Excel modeling and analytics. I haven’t really come up with a catchy name yet. But I’m very excited about this project. I expect to be complete sometime this summer, but if book writing is any indication of my ability to stick a schedule, it might be the end of the year!

Anyway, this will be a great course. Stay tuned!

Oh yeah, buy my new book, Dashboards for Excel.

Happy Holidays 2015

OK, so I know a few days ago I said I had written my last blog posted before the end of the holiday. But then I’ve released holiday cards in both 2013 and 2014, so this year should be no different. So I went with a topical Star Wars theme. The text being shown is  an abridged version of the introduction chapter in Dashboards for Excel.

Recording #3

Speaking of which, if you haven’t gotten your copy yet, you should. The book currently has a 4 out-of 5 on Amazon, with the most recent reviews being the most complimentary. (And I swear, I don’t know the people writing these reviews.) Aside from some complaints about the book not being in color, the book is doing well. For what it’s worth, I inquired about color printing with my publisher long before it was printed, but most tech-trade books are not published in color and the few that are already have a strong history of sales and success. (But also: don’t stop asking! If enough people ask for a color imprint, I can make a stronger case to my publisher for the second edition!) Remember the eBook versions are in color too, if that’s your thing. And, of course, I have all the print materials at the ready, so you can always ask me directly if you have trouble understanding anything.

Download File: Happy Holidays 2015.xlsm

image

Remember to hit the download icon from within Google Drive to get the file.

If you want to change the Star-Wars-text for your own application (aren’t there like a million business uses for this?!?!), simply click the worksheet tab called “Backend” and type new text into the textbox.

Thanks to all of you for a wonderful year. Next year will be even better!

Jordan

image_thumb.png

Enlarging Form Control Option Buttons

In a previous tip, I had complained about form control option buttons. I had argued they suffer because you can’t change the font size nor the “punch circle.” But then an idea dawned on me that builds off of that previous tip. We could use the same dynamic described in that earlier article and combine it with the camera tool.

Click here to learn more about the Camera tool.

Say Cheese!

Let’s take a look. On the left side, I’ve create a button list. This is just a place to store my option buttons. Notice I’ve placed them within a single cell each—this makes it easy to take a snapshot with the camera tool (since the camera will always only reference one cell).  The image below shows how  this works. Notice option buttons are all linked to cell B8, which I’ve named “SelectedIndex.”

image

Again, following my previous tip, we can assign each image created by the camera tool to a macro. That macro would change the Selected Index depending upon which button was selected

Click here to read that previous tip to understand it in full.

Let’s take a look at that macro:

Public Sub OptionButtonHandler()
    Dim Index As Integer
    Index = CInt(Replace(Application.Caller, "Button", ""))
    [SelectedIndex].Value = Index
    [SelectedIndex].Calculate
End Sub

You maybe wondering about that last line in the subroutine. Camera tool images can be a little funky. In the previous screenshot, you can see that these option buttons images are on the same tab as their form control counterparts. Presumably, you’ll place the option button images on a different worksheet tab and the original button list will live on a hidden tab. For whatever reason, whenever you place these form control images on another tab, the image won’t always sync with the right value. So making a call to recalculate will ensure that it does. The download file provided at the end of this post demonstrates a setup where the images are on a different tab.

Note: I’ve only noticed this problem in Excel 2010. Excel 2013+ does not seem to have this issue requiring a call to calculate. I’ve included it here so it’s backward compatible with previous versions of Excel. It’s also a bit slower, so feel free to remove it completely if it’s not an issue on your end. 

Discussion

Look, I’m probably not going to use these buttons anytime soon. I’m not saying you shouldn’t, but I don’t have a lot of use for them. I’ll still prefer my method of using shapes. However, the camera tool when combined with my earlier tip does allow you to create functionality not native to Excel. For instance, if you type a new font in one of the cells on the button list, you can even change the format of the radio button in the way it’s presented. In the image below, I’ve deleted the original caption to the option button. In its place I wrote another caption into the cell and picked a cheesy font. Yes, it doesn’t look great, but you can play with the formats and make it look better. This should give you an idea of what’s available.

image

My hope with this article is that we keep the creative juices flowing and see what other novel ways it can be applied.  Again, I probably won’t use this, but hey, maybe you will, and that’s enough for me.

I’m going to Dublin, Ireland

download

From December 23-30, I’ll be in Dublin, Ireland on holiday with my wife. If you are in the Dublin area and would like to say hi, let me know! Otherwise, this is likely my last blog post before the end of the year, so have happy holiday and terrific new year!

Download file: Large Option Buttons.xlsm

Also, buy my new book! Makes a great stocking stuffer!