Peltier Technical Services, Inc.
 

Excel Chart Add-Ins | Training | Charts and Tutorials | Peltier Tech Blog


Peltier Tech Chart Utility

 

Excel Dashboards

 

Books at Amazon.com

 

2D or 3D Column Charts


 

A recent thread on the Mr Excel forum, Excel Chart - stacked column to chart progress, dealt with ways to show progress towards a goal, where the progress consists of a sum of items. One suggestion was to use a stacked column with 3D effects. Some sample data is shown below, next to my best reconstruction of the 3D chart offered as a solution.

  A B C
1   Sales Goal
2 Store A 180  
3 Store B 205  
4 Catalog 595  
5 Web 345  
6 Direct 415  
7 Goal   2000

I always tell people to avoid 3D effects, and they wonder why. The chart above helps to demonstrate my aversion to 3D charts. For example, I cannot do better than guess the values of any of the bars, except perhaps the top of the dark blue one, which is just below 1000. I cannot tell where to extrapolate the lines otherwise. Does the yellow bar stop midway between 1800 and 2000? No it's probably closer to 1800, or maybe lower, but I don't know where to draw the lines. The pink bar doesn't stop at 1400, either, probably a bit lower. Impossible to tell because of the difficulties of rendering perspective and parallax onto a 2D surface.

The simpler 2D chart at right is a major improvement on the 3D chart above. Making use of the 2D nature of the viewing surface (the screen or paper) means that everything is either parallel or perpendicular. The bars line up exactly with the axis, gridlines, and each other, without guesswork. There is no perspective, no parallax, no distortion. Of course, the colors could use a little work!

  A B
1   Sales
2 Store A 180
3 Store B 205
4 Catalog 595
5 Web 345
6 Direct 415
7 Goal 2000

The two charts at right show how to use a line instead of another column to indicate a goal. The data values are placed in a single column (above), because only one category is needed. After creating the chart, the "Goal" series is converted to an XY series (the red square), and X error bars are applied to the XY series, using positive and negative values of 0.4 (right). The marker (red square) is hidden, the error bars are formatted, and the series is given a data label "Goal". The colors have also been fixed up, though a graphics designer would be able to develop a better color scheme (far right).

If desired, the Goal series could still be represented as a column, as shown at right. In the first chart, the Goal series is on the Primary axis, while the individual sales outlet series are on the secondary axis, so they lie in front of the goal. The gap width for the Goal series is smaller (50) than for the other series (the default 150), and the secondary Y axis was deleted. This is pretty much what the original 3D behemoth was intended to show.

In the second chart, the sales outlet series remain on the Primary axis, while the Goal series was moved to the Secondary axis, given a border, and made transparent (no fill). The gap width for all series was set to 50, and the secondary Y axis was deleted.

 

 

Page copy protected against web site content infringement by Copyscape

 

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.


Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2014. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

Microsoft Most Valuable Professional

My MVP Profile