Dynamic Dashboards Using Dynamic Charts Part 2 of 3

In this second post for creating Dynamic Dashboards I will continue with the development of the Dynamic Dashboard. In my previous post I showed you how to create checkboxes and link the checkboxes to data.

I want to turn my attention to the Year column. In my data I have 3 years of data. Current Year, Prior Year and 2 years back (2012, 2011 and 2010).

Just as I did in my prior post, I create checkboxes (one for each year).

I Assign each checkbox to cell M3, M4 and M5. Just as with my data, True or False now display in these cells. I next add in column N the numbers 2010, 2011, and 2012. This is useful to show which year has a True value.

Now with my original formulas in columns I J and K, the data entire column would show or not show depending on the True or False in row 1. However I want the Year to also control if the data should show.  So I want to modify the formula to also show across the row.

I could accomplish this in a few ways but will add an extra step here to help you understand how to do it.

In cell O3 I add the following formula.  =IF(M3, N3) and copy it down for each year. This formula returns the year if the corresponding value in column M is True. Now I can compare this result to the year for each item.

I now change my formulas in columns H I J and K.

Column H            =IF(COUNTIF(O$2:O$11,B3) = 1,B3,"FALSE")

This takes the value in column B, and sees if it exists in column O, If it does, it displays the Year.

The rest key off of this formula and then check the True or False at the top of the columns.

Column I              =IF($H3 <> "FALSE",IF(I$1,C3),"FALSE")

Column J              =IF($H3 <> "FALSE",IF(J$1,D3),"FALSE")

Column K             =IF($H3 <> "FALSE",IF(K$1,E3),"FALSE")

Now my data is dynamic Vertical and horizontally.

Now for the fun part, creating the Dynamic Dashboard.

I insert a rounded rectangle shape and place it around the checkboxes giving it a light blue background. I have also made the entire worksheet background all white.

To create my Column Chart I select my data ( I highlight my data including the header for Item, Sale $, Profits and Units Sold. (I don’t want to include Year).  I can do this by holding down the CTRL key when selecting my columns.

I then create a 2D column chart.

I cut the chart and paste it under my checkboxes.

I remove the outline of the chart (shape outline) and change the design of the chart (Style 27).

Left mouse click on one of the Units Sold bars will select all the Units Sold bars. I change the chart type to be Line.

I format the data series by right mouse clicking on the line. I change the line color to red and the Width to 1. Now I go back to my data and add totals to each column.

I add a summary section on my dashboard and link the cell for each total to the totals I just added to my data.

I could continue to fine tune the data but you should have a good idea of how to improve this concept. So give it a try the next time you need to WOW your boss.

How would you use this technique to bring your dashboards and charts to life?

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.

Excel 2010 Should you upgrade?

There are only a few differences between Excel 2007 and Excel 2010.  So what is the determining factors in considering weather you should upgrade or not?

• First, Excel 2010 calculates a bit faster, but for most, this wont be noticeable. 
• Spark lines (mini charts) are now included in 2010 but you can still create them in 2007.
• Chart macro recording has been fixed in 2010.  Not something to be proud of since it should not have been broken when Excel 2007 was released, and should have been fixed in an SP release.  If you need to record chart macros, which is a need if writing such macros, then you need 2010.
• Fill patterns which were removed in 2007 are back.  There are free add ins for 2007 if you do a search on the internet.
• Excel 2010 handles conditional formatting much better then in 2007.
• Excel 2010 can be run as a 64 bit application but you need to have at least 5-10 gig of memory on your PC to significantly benefit from it.
• Microsoft improved statistical, financial, and math functions and the wizard interface in 2010.

So if you are pushing the limits of 2007 and your computer can handle the upgrade, then it might be a good idea. However if you are surviving well with 2007, save the cash.

Speedometer Chart / Gas Gauge Chart

A speedometer graph is comprised of multiple components to give the illusion of one chart. The background is an image or donut chart. The needle is created with a pie chart. The numbers and text are done with text boxes.

To start you want to lay out your preliminary data. The following 3 columns contain all the data I will need to create the chart. You will notice that I have formulas in 3 cells. I have displayed them for reference. You will also notice that in row 14 I sum the above data and each column (A, B and C) total is 360 (the same amount of degrees in a circle). The needle will represent any position on the chart based on the value entered in cell C2.

To create the chart, highlight cells A2 through A12 (don’t include the header row). Create a Donut Chart (shown below using Excel 2007)…

This creates the necessary components to create the first half of the Donut Chart.

You want to rotate the chart 90 degrees so the solid blue portion is positioned at the bottom. To do that, right mouse click on the donut ring and select Format Data. Under Series Options, select Angle of first slice and set it to 90. When you close the window you will see the chart has rotated.


Now we have the foundation for the chart. You won’t need the legend on the right so you can remove it by left mouse clicking on it then pressing the delete key.

Next I want to make the bottom ring of the circle transparent. I left double click on the bottom section to select the bottom portion of the circle and then right mouse click on it. I select Format Data Point to bring up the Format Data Point Dialog Box. I click the Fill option and select No Fill. If the boarder is visible, I also change the boarder color to no fill.

In the below example I am in the process of making the bottom circle transparent.

When Done I am left with the following.

Now I want to work on the needle of this chart. I first highlight cells C2 through C5 and create a Pie Chart.

Just as I did with the donut chart, I want to rotate the Pie Chart 90 degrees and remove the legend to the left.

I also want to make the Red, Blue and Purple sections of the Pie chart transparent without any boarders (just like the bottom of the donut). This will leave me with just the needle showing.

I make the whole pie chart background transparent. And the needle is shown on top of my donut chart.

You can also resize the needle by changing the pie chart size and then moving the Pie chart so it still is aligned correctly over my donut chart. In this example I have the needle slightly extending beyond the arch of the donut chart.

I also have changed the needle color to be black….

Well now you can test you chart by changing the value in cell C3. Set it to 0 and also 100. Check the position of the needle and readjust the pie chart as needed.

Now I want to add some numbers as indicators on the gauge. I simply insert a text box for each label I want to use. (In Excel 2007 from the Ribbon, Insert Tab >> Text Box). Make sure the background and outline of the text box is transparent.

I position by changing the value in cell C3 for each text box I have. This helps me position the text correctly.

Almost done! Now I want to add a heading to the gauge. This is where cell C2 come in. I insert another text box but instead of typing in text, I place an = in the formula bar and then select cell C2.

Now I was creating this chart to show a percent of holdings for an account so I used the word Share. You could just as easily put the word Percent, Total or any other text you want.

The last thing I want to do to complete this is to group all the components of this chart together so that I can resize the graph as needed.

From the Ribbon (Excel 2007) I select my items and then on the Format Tab I choose Group.
Depending on what you group first you may need to arrange the items on the chart again by bringing items forward or moving items backwards.

When done you have a fully functional speedometer graph or Gas Gauge chart. Used judicially, this little graphic can turn your ordinary dashboard into a “WOW did you see that dashboard”.

Now what if we wanted to dress the dial up some more.

Perhaps I wanted to do a gradient fill from left to right. Well to do that I would change my data in the dial column to be only two equal rows.

I then continue with the above procedure but when I select fill for the top section of the donut, I choose a Gradient fill and select the colors I want and direction of the angle.



Using the same technique, I can use 3 data points for my dial

Perhaps you are looking for a more realistic graphic. Just past in a picture and overlay the pie chart. Tweak the data to ensure the needle does not pass the E or F and viola!

So how would you use this technique in your dashboards?