Dynamic Dashboards Using Dynamic Charts Part 1 of 3

Dashboards are a great way to display data in a concise way. However the person viewing your data may want to break out the data in a way more meaningfully to their own goals. One great way of doing this on a dashboard is to make the dashboard interactive.

Now in a previous post I showed how to create a static dashboard using a combination of bar charts, trend indicators, conditional formatting and fuel gauge graphics.

Dashboard

In the next few posts I will walk through setting up a dashboard for the ACME Supply Company.

Let’s start by looking at some raw data. Here are yearly sales for the two best selling products produced by the ACME Supply Company (Coyote Rocket Jets and Giant Springs).

I want to present this data in a dashboard used by the ACME Supply sales department. I am not much of a visual basic script developer and tend to fly by the seat of my pants (much like the coyote) so I don’t want to be locked into a PowerPoint presentation. I also want this report updatable so it can be used over and over. So my best solution is to use Excel.

My primary tool for creating the interactive dashboard interface will be check boxes. A check box allows for simple Boolean logic (checked or not checked).  To start I place my data in one tab and on a second tab I want to create my dashboard. On the dashboard tab I want to first add my checkboxes.

From the Developer tab, in the Controls group, click Insert and then, under Form Controls (top area), click the Check box .

Click the worksheet location where you want the upper-left corner of the control to appear. Hold down the left mouse button and drag the mouse to insert the checkbox.

When done you will want to modify the properties of your new check box by right mouse clicking on the Check box.  You will want to assign the value of the checkbox to a cell so we can use is in formulas. I assigned the value to the cell I1 in on my Data tab

I also change the word Check Box 1 to Sales $.

No whenever I check the Sales $ checkbox, the word True appears in cell I1.

 I repeat the process for Profits, and Units Sold. When done, I have 3 check boxes that will display True or False depending on if they are checked. Notice in the example below Profits are unchecked and displayed with FALSE in my data.

I can then reference these True or False values in formulas.

 In this sample my Data is in columns A through E. My formula to verify if the check boxes are checked or not checked are in columns G through K. Notice that since cell J1 is FALSE (the profits checkbox is unchecked), no value is passed into the fields.

Here are the formulas I use in my calculated fields, You can enter them in cells I3 J3 and K3 and then copy down for each row of your data…

=If(I$1$,C3,NA())    – If the value of cell I1 is True, then return the value in cell C3, else return FALSE.

Now my data is dynamic based on the checkboxes. I can use this dynamic data to create my chart.

I will show how to do that in my next post.