As part of the training at The Data School, we're often tasked by clients to build industry specific dashboards and/or dashboards that can be used as templates for their organization. Of course what the client can ask for is often much more broad. From a dashboarding perspective, we tend to have the freedom to create what we think works best for their data. This them, in turn, helps create a sort of "brand" for their dashboards internally.

That got me thinking about common use cases for dashboards, dashboards that would likely span industries and companies and could serve as templates for others. In this series, I'll be posting templates that I have been building based on the data and sample dashboards from Excel Dashboard School. 

Let me make it clear that I am in no way criticizing the work they have created. My intent is to build an alternative method for displaying the data as a template in Tableau. The data and templates they have provided are the starting point for my work. I want to thank them for being so kind and sharing their work.

TOPIC

Customer Service Dashboard

RESOURCES

1. Dashboard Overview - https://exceldashboardschool.com/customer-service-dashboard/
2. Data and Interactive Dashboard - https://cdnspeed-exceldashboardsc.netdna-ssl.com/wp-content/uploads/2017/08/080_CSD_final.zip

DASHBOARD TEMPLATE

I love Jon Peltier’s Tech Blog.  He always has great tips for getting things done in Excel and his recent post “Easy Two-Panel Column Chart in Excel” is no exception.  In this post Jon walks you through how to create this chart in Excel:

Seems pretty straight forward, right?  Well not exactly.  I wouldn’t exactly call this technique easy.  In Jon’s post, I quickly counted about 13 steps (I think there are more; I lost count as I recreated the chart), but there are many clicks for each of these steps.  It took me a bit over five minutes to create it the first time, but this included flipping back and forth between Excel and the instructions.  I suspect it would take two minutes or less once you use this technique a few times.  Again, let me emphasize that this is a great technique for Excel.

One big drawback for this technique though is that you are forced to hard code the scales.  What happens if your data changes?  You’d need to go back into each axis and reset them.  Kind of a pain if you ask me.  I’m sure I’d forget at some point too.

Also, I don’t like how the axis scales are not on the same side of the chart.

How many clicks it would take to create the same chart in Tableau?  Step-by-step, here are the instructions (with click counts in parentheses):

1. Drag the Month dimension onto the Column shelf (1 click)

2. Hold the CTRL key and choose both the Primary and Secondary measures, then drag them onto the Rows shelf (3 clicks)

Two steps/Four clicks and we’re already 95% of the way done.

3. Drag the Measure Names dimension onto the Color shelf (1 click)

That’s it!  Five clicks and we’ve created a two-panel column chart in Tableau.  I timed myself and it took five seconds to create this chart.  Of course, you need to spend the money on a copy of Tableau.

In summary, let’s assume:

1. It takes two minutes to get the chart exactly as you want it in Excel (but you still have to fix the scales every time you change the data)
2. It take 30 seconds on Tableau (though I can’t see how it could take that long and you DON”T need to update the scales each time)
3. This gives you a 75% time reduction in creating just this one chart

Think about all of the time you would save if you did EVERY chart in Tableau.  This makes the ROI story for Tableau pretty simple if you ask me.

Quite often I listen to presentations from other manufacturers on an “Insights & Analytics” share group.  Much of the content presented is directly applicable to my work; the education and ideas are great.  Unfortunately, far too often the presentations are of poor quality.  One thing I need to learn is to look past these “mistakes” and focus more on the story, but I’m struggling to do that.

Here are couple examples from a recent presentation:

These pie charts took several minutes for the presenter to explain, which is an immediate sign of their weaknesses.  The only part that works well is that the slices are ordered by the amount of time.  Other than that there are problems throughout.  I’ve gone into these in plenty of detail in the past.

The second charts that took my breath away are these 3D cylinder column charts.  I don’t see too many of these, for which I’m thankful.

There was no explanation whatsoever for how to interpret these charts.  My eyes take me back and for between the charts in an effort to compare like colors.  I can’t be convinced that was the intent though without the background commentary. 

I did a quick Google search for cylinder chart and the first site that came back was from anychart.com.  My terror increased when I read their definition.

Cylinder charts are column (or bar chart) that use cylinder shaped items to show data. Although cylinder charts do not add any additional data, sometimes using this shape allows to achieve a better visual appearance of your data.

The best visualization of Cylinder charts can be seen in 3D mode, so we will present all examples of them in this mode.

Seriously?  They achieve a better visual appearance?  How?  Their best us is in 3D mode?  OMG!  You really should checkout their site.  The examples are frightening!

So how can the cylinder charts presented be improved?

1. Change the cylinder to plain old bars
2. Add a scale
3. Make it a clustered column chart.  This would allow you to compare the categories for each color to see the relationship between “Likelihood to Shop” and “Ease of Use”
4. Consider a scatter plot

These are incredibly simple changes to make, which is what frustrates me so much.  It actually takes more work to make it look this bad versus using Excel’s defaults.

I struggle with charts like this each and every day, but the culture needs to change.  Have you been able to influence change?  How?

This past week, I was watching a presentation on Q3 performance and up pop a bunch of charts that were clearly created in Excel with smoothed lines.  I hadn’t seen smoothed line charts in quite a while, so I was taken aback.  I almost, but thankfully didn’t, stand up and call out the junk.

It was incredibly clear to me that the smoothed lines were distorting the data, not much, but distorting it nonetheless. And I have a problem with THAT!

Let’s first take a look at some examples to see how badly the data can be distorted.  The first chart is obviously the smoothed lines.  Nice and pretty, I agree.  It makes me feel like I’m going up a chairlift then skiing down the slopes of Keystone, Colorado.

I added the gridlines, though I would never do this if I were presenting this for real, so that you can see where the points truly intersect.  It should be abundantly clear now that the line is trying to connect points that don’t exist.  Look between July & August 2009 or between August & September 2008.  In both of these instances, and many more across the chart, the lines go beyond where they should in an attempt to make the chart nice and smooth.

If I were to look at this quickly, I might think that my sales increased from July to August 2009, but in fact, there was a slight decrease.  In order for the line to connect smoothly to September 2009, the line has to go around August 2009.  Think about all of the people that don’t used zero-based axes.  Imagine how distorted their data could look.

Contrast the smoothed line chart to this standard line chart.

You now easily see that sales decreased from July to August 2009.  It’d be tough to interpret anything from this chart between the months because the lines clearly connect month to month.  The smoothed lines lead you to believe that there is more data being connected.

Now, let’s look at how the smoothed and straight lines look on the same chart.  For illustrative purposes, we’re only looking at 2008.  Now that dip after August really stands out. 

Jon Peltier of the Peltier Tech Blog sums it up best in his post about the charts to choose and avoid in Excel 2010:

Smoothed lines are abused. If you are plotting measured data, the only valid connecting curve between points is a straight line (or a line which is fitted to a function that comes from a physical model of the data). A smoothed curve implies that the data goes places where it has not been measured. Smoothed lines without points are even worse, because the person trying to interpret the chart doesn’t even know what points on the smoothed curve belong there.

My advise?  NEVER use smoothed lines.  The ONLY possible outcome is misinterpretation.

Let me wrap up with what I find to be a bit of a funny line from Microsoft’s help for creating smoothed lines:

When you use this procedure to soften the jagged edges of a line chart, your data is not affected.

This is very true.  Your data is not affected, it’s merely misrepresented.  Semantics?

I came across a challenge last week while working on a project.  I needed to create a dual-axis chart, with two bars on the primary axis and a line on the secondary axis.  Tableau makes this task incredibly easy, but I needed to do this in Excel.  Well, I really didn’t HAVE to create the chart in Excel, but others needed to be able to update the chart and they, gasp, don’t have a Tableau license.

It’s easy enough to create a dual-axis chart in Excel, if you want the bars side by side.  However, I needed overlapping bars.  There’s no standard chart design within Excel to accommodate this, which meant I had to come up with a workaround.  I perused the internet and didn’t find anyone else that had done this (I’m sure people have done it, but haven’t shared their work), so I wanted to share it with anyone that may need to do it in the future.

A sample Excel workbook can be found here (if your corporate network blocks Dropbox, send me an email and I’ll forward it to you).

The final product looks like this:

1. Highlight your data, insert a 2-D clustered column chart
   
   
   
   
2. Change the bar and line colors if desired
3. Right-click on one of the bars that you want on the secondary axis and choose Format Data Series
4. Change the Plot Series On option to Secondary Axis
5. With the bars on the secondary axis still highlighted, from the Chart Tools Design menu, change the Chart Type to a line
   
   
   
   
6. Right-click on one of the bars that are on the primary axis and choose Format Data Series
7. Change the Series Overlap to 100%
   
   
   
   
8. The two bars on the primary axis now completely overlap each other.  You’re almost done!
9. Remove the gridlines (important…I’ll explain in a bit) and add the axis labels (I hate it when people create dual-axis charts in Excel and don’t add the axis labels!)
   
   
   
   
10. Give the bars the “overlap” look (these are the most important steps to give the bars the proper) by right-clicking on the gray bar (i.e., the bar that’s in the first column of the table) and choosing Format Data Series
11. Choose the Border Color option, select Solid Line and set the color to the same color as the bar (in my case, light gray)
    
    
    
    
12. Choose the Border Styles option, change the Width to 10pt (or whatever floats your boat) and change the Cap type to Flat and the Join type to Miter
    
    
    
    
13. That’s it!  You’ve create a dual-axis chart in Excel with overlapping bars on the primary axis and a line on the secondary axis.
    
    
    

Now wait, I mentioned earlier that you should delete the gridlines.  This is critical because we’ve changed the height of the bar in the back by adding the border.  This leaves you with a couple of options:

1. Make the border of the bar in the back really thin, but this makes it challenging to see the overlap
2. Change the data to account for the thickness, but it’s kinda scary to me to alter the data because if you mouse over the point you see the original data
3. Leave the bars thick so that you can clearly see the overlap

I would go with option 3 because the naked eye won’t be able to discern the difference to the bar height anyway.  The purpose of the chart, after all, is to give the overall trends and comparisons anyway, not focus on the original data. 

If that “change” in the bar size really bothers you, then simply add the data table under the chart:



Don’t we all love challenges!  I know I do.

This morning, I was on a conference call and someone presented this chart (I've changed the data and the data reviewed to keep me from getting in trouble, but the format of the chart remains intact).



Some of the issues I see:

Does anything stand out to you in these tables?

Scott Raab wrote a fantastic article in Esquire in February when the allegations came out about A-Rod taking steroids in 2003 (and who knows how long before or after). In the article, Scott references a chart published by Esquire in October 2008 that projects A-Rod's stats and when he's projected to pass significant milestones.



There are some fundamental flaws with this chart:
(1) The vertical axis starts around 400 instead of zero; this distorts the chart (In fact, when you start the axis at 0, runs scored and RBI are nearly identical. This chart makes it look like there is a variance worth noting.)
(2) The colored background is unnecessary and distracting; keep it transparent so that the data stands out on its own
(3) The markers at each point are not needed; they take away from the natural trend of the data
(4) The total should be labeled 2018, not total

There are also a few details that make the chart work:
(1) Identifying the major milestones
(2) Labeling the projected totals
(3) The color choice is good; the colors are discernible and no one color sticks out more than another (though the red could be slightly softer)

Given the notes above, here is how I would present the data:



Good news for A-Rod, his projected stats are even better than when Scott Boras presented the data for the first graph to the Texas Rangers back in 2001. Bad news for Boras, the $252M contract you negotiated could have been higher if you presented your data more accurately (along with the 5% commission you lost).