Dashboard Dynamic Data - Designing a simple user interface

I wanted to review in greater detail how to make your data dynamic for use in charts and dashboards.

You can review 2 previous examples of this check box application here and here.

In the below sample data I have some basic information that I would like to make dynamic for use in a graph. To accomplish this I will place my dynamic data in columns H – L and use Checkboxes that the user can either check or uncheck to select the data they want to see.

To stat I want to add a checkbox from the form Controls. You can select the Developer Tab >Insert > then Check Box

I then drag the curser while holding down the left mouse button to create my checkbox in the worksheet.

I right mouse click on the newly added checkbox and select Edit Text and rename it to read “Customers”. As it stands now, there is no association to the sales column and the check box with the name customers. That association will be created using an =IF formula later.

Next I right mouse click on the added checkbox and select Format Control. Here is where I want to assign the value of the check box to a specific cell. In this case I select cell I3.

Now when the box is checked, cell I3 displays “True”. If it’s not checked, it displays “False”. Here I display the same Customers checkbox in both the True and False state.

Next I repeat the above process for the other columns, Customers, Returns and Total Profit making sure that I update the Cell Link for each column.

Now I can create an association to the columns of data and the checkboxes by using a simple =IF formulas to return my data when the checkbox target cell = “True”.

Cell I5 formula =IF(I$3,C5,NA())

Cell J5 formula =IF(J$3,D5,NA())

Cell K5 formula =IF(K$3,E5,NA())

Cell L5 formula =IF(L$3,F5,NA())

I copy this formula down my column for each row that has data (H5 – L16).

When done my columns will display the appropriate value when a box is checked and the text “#N/A” when the box is unchecked. Well right about now your thinking that this looks terrible, and you would be correct. However this is just a demonstration. In a real dashboard design I would have the dynamic data on a different tab where people cannot see it and the checkboxes would be at the top of the dashboard. The chart that gets created from the dynamic data would show on the dashboard.

Now let’s examine a simple graph using the above logic.

I create a basic line chart. When each checkbox for the data in my line chart is checked, my data is represented.

If I uncheck Customers and Total Profit the chart changes and you will notice that only Sales and Returns are represented in the chart.

So how does the graph know to remove the columns of data. Well it goes back to the =IF(I$3,C5,NA()) formula. If I had written the formula as =IF(I$3,C5,0), then when the checkbox was unchecked, a 0 would populate in the cell instead of a #N/A. That would cause my graph to still show the column of data but it would flat line at the bottom since the value would be 0. In the next example I have changed the formula for total profit to return a zero when the checkbox is not checked. You can see on the graph that total profits shows at the bottom of the graph as 0. Compare this to the customers column which is not displayed (since I still used the =IF(I$3,C5,NA()) formula in that column.

By default, data that is hidden in rows and columns in the worksheet is not displayed in a chart, and empty cells are displayed as gaps (remember our cells are not empty). For most chart types, you can display the hidden data in a chart.

For line, scatter, and radar chart types, you can also change the way that the empty cells are displayed in the chart. Instead of displaying empty cells as gaps, you can display empty cells as zero values, or you can span the gaps with a line. You can change how Excel displays empty cells when you select the data source by clicking on the Hidden and Empty Cells button.

However I prefer returning the #N/A. Now of course I can continue to clean up my chart, placing the customer value on a secondary axis, and add other features to clean it up. Notice that I moved the checkboxes and made the dynamic data font white to match the background. I then moved the final graph over the dynamic data.

So how can you apply the above techniques in your dashboards and charts to transform your data from the ordinary to the extraordinary?

Best Dashboard

In a previous post I demonstrated a simple but effective dashboard but did not go into how to create it. 

Sales Dashboard

I will do so in the following post.

As with any dashboard, it all starts with the data you have and what you are trying to convey. A good dashboard will be easy to understand, (clear and concise). It will convey the data in as few steps as needed without losing key points. It will also not obfuscate key points by surrounding them with superfluous information.

For my demonstration, I will create almost the entire dashboard from one key data series (Sales per Month).

Month	Sales
Jan	$  20,947
Feb	$  45,494
Mar	$  35,173
Apr	$  20,249
May	$  47,792
Jun	$  42,511
Jul	$  28,343
Aug	$  28,343
Sep	$  28,000
Oct	$  27,161
Nov	$  17,864
Dec	$  29,987

Now to start my dashboard I first need to set a color theme. I personally don’t like dashboards that use dozens of colors. In my opinion they tend to end up looking like something from a circus. I prefer a sleek look and try to minimize my color pallet. For this example I will use variations on shades of blue.

So now that I have my data, know what my key points are, and color tablet; I can now begin.

I am going to stop for a moment and vent a bit… Quite often managers don’t know how long it actually takes to create a useful dashboard. A good dashboard is so simple to understand that the development side is often overlooked. A general rule of thumb is that the design and planning of the dashboard will take about 70 percent of the time it takes to create it. The other 30 percent is the actual creation. A good dashboard is almost always used over and over so it needs to be easily updated (so be sure to plan ahead).

So we plan what we are going to design and then design what we plan.

Now having said that, don’t allow yourself to be pigeonholed by your initial plan. Some times as you actually create the dashboard, inspiration strikes.  But I digress….

To start I first highlight the entire worksheet and change the background to white. This removes the gridlines. I then enter in my data In cells C3..D15 and then format the data to be more visual appealing. I then tweak the data by merging cells B2, C2, D2 and E2. I enter the Sales in this merged area. I also add the Quarter for each Month in column B. In my dashboard I am using the Calendar quarters so Jan Feb and March are Q1, Apr May and June are Q2, etc…

I use the Boarders tool to outline my cells and fill ever other cell so they are shaded light blue. I bold and shade the Header cells grey and am pleased with the following result. If your data has cents, you may want to remove them to help streamline the look.

.

So far the data looks nice but it does not easily tell a story. I want to quickly know how the data is trending. I don’t want to have to visually compare each row so I want to add trending arrow indicators in column E.

What’s a trend indicator? Well it’s just like it sounds. Some text or graphic to indicated a direction of increase or decrease for a particular metric.

To add these trend indicators, I use a technique I explained in a prior post. Click on the link for instructions on how to accomplish this.

Trend Indicators

Don’t want to click on the link??? The formula you need in cell E5 is:

=D3 & " " & IF(D2<D3,"▲", IF(D2=D3,"*","▼"))

Copy this formula down through E15. Use conditional formatting to change the color. What’s that, you don’t know how to do that? Click on the above link (go on, it won’t hurt)…

Next I want to work the second portion of my dashboard, Sales by Quarter. If you have followed my instructions for the above, then this is just a repeat only with a consolidated result.

Q1 Q2 Q3 and Q4 in cells B 18 – B 21. In D18 I sum the first three months of my above data (cells D4, D5 & D6). For cell D19 (Q2) I sum (D7, D8 and D9), etc…

I then merge Cells B17 and C17 adding the title “Quarter”.  I also merge cells D17 and D18 and add the title “Sales”. I format the same as the above (blue background fill, outlining the cells and bolding / shading my headers grey).

I again use my trend line technique to compare the Quarter results.

Now I have displayed sales by Month but also summarized by Quarter including trend indicators for quick reference on how sales are trending by month and quarter.

Next up is the Summary section of the Dashboard. This is where I place my key points. I group them in one area for easy data access. I also center these key points toward the top. Now perhaps I could apply some heat map logic used for websites to optimize this focal point but I feel they best are represented in this dash board where I placed them(above and to the left of the graphics)

To create the summary I just repeat the formatting of the first two parts of the dashboard and use simple formulas to create the lookups. You will also notice that to create the Summary, I have a row for Sale Target for Year. This is hard coded but could be a lookup to a budget worksheet.

Again all of the formulas use basic Excel functions….

=AVERAGE(Sheet1!D4:D15)

=MAX(Sheet1!D4:D15)

=MIN(Sheet1!D4:D15)

=MAX(Sheet1!D18:D21)

=MIN(Sheet1!D18:D21)

=SUM(D4:D15)

For the lookups I could have used a combination of Indirect and Match as described in the following post…

Vlookup To The Left

However I opted to use helper cells. In the below image I have changed the font color from White to dark red for the helper cells to help you better understand the formulas. Now all of the helper cells are done by a function. For example cell F4 has the formula =C4. This way if I sort my data, the helper cells also sort.

Next on the dashboard, I create the Sales By Quarter bar chart and size it to fit.

Same with the Sales by Month bar chart (with trend line). No great mystery on creating a bar chart. To add the trend line (Excel 2007) from the ribbon select the Layout tab and under Analysis, select Trendline.

I format the Trendline as Polynomial with Order 4 to give it a smooth look.

Now all that’s left to create is the sweet little Sales by Month gauge / summary board in the upper right corner.

WARNING HERE COMES THE CONTROVERSY!!!!

People either love or hate the “Fuel Chart” or “Speedometer Chart”. I will concede that it can be misused and is usually not my first choice in graphs. However, used correctly it can be a powerful tool. 

Besides a billion automobiles can’t be all wrong, if they were, we would all have bar charts on our car displays….

Now I won’t go into detail on how to get the Current, Trend, Goal info. That’s all covered above and done with simple lookups. But the speedometer graph is done by overlaying a donut chart with a pie chart.

To create this graphic, see my next post on creating a speedometer graph.