(613) 818-2848
Menu

Excel Tricks for Data Viz #1

Microsoft Excel is a handy piece of software, and like the Swiss army knife, it will do many things. It may, however, not do all of them well. Optimized data visualization is not a strength of Excel’s compared to dedicated packages like Tableau or Spotfire (noting that the presets in both those examples could themselves use a little cleaning up).

For whatever reason, Microsoft continues to provide chart layout choices that are full of gunk.  A lot has been written about how to do better charts with Excel, and everyone seems to think that they invented the methods for doing so, but it’s all been out there in various places for quite some time, and there are really only a few things you need to know anyway. I’d like to get this out of the way so that people could spend their time on more important and creative aspects of data visualization.(1)

Most of the trick to successfully presenting data with Excel is simply in knowing how to de-junk it, which is pretty straight forward if you know about the pre attentive attributes of visual perception and the data/ink ratio (see my previous blog on “Quick Tips”, and for more information, Stephen Few’s Show Me the Numbers (2)). There are, however, some key “custom” chart types and modifications for standard charts that require us to coerce Excel a little.

I will illustrate the things you need to know in the next few blogs. Sometime you just need a single “trick” to make a given chart and sometimes you need a combination of things. The upcoming blogs will cover:

  • Secondary axis – e.g. line chart, bar in bar chart
  • Gap width – e.g. bar in bar chart (also secondary axis)
  • Change chart type – e.g. reference lines and markers
  • Putting them together – e.g. bullet chart – secondary axis, gap width,change chart type
  • X and Y axes cross at () – e.g. slope chart and variance chart
  • Scatter plots using dot size and colour to add information

I will also talk about some other very useful Excel features, including:

  • Quick layout
  • Colour palettes – especially monochrome
  • Add chart element
  • Switch Row/Column or Select Data
  • Format axis, series, chart area, label series/values/location

All of the examples will be done using MS Excel 2016 for Windows. Older versions, unless very old, have the same features, although in a few cases you may need to access a given feature in a different menu or tab than I indicate here.

Today’s topic is using the secondary axis. Here’s a first application of this key feature:

A problem that arises frequently is the graphing of line charts, where there are too many data series involved for the chart to be useful, as in this example:

 

busy-time-series

 

The best way to fix this, other than reducing the number of series involved, is to highlight only the series that is of primary interest. When you do this, however you will usually find that at least part of your key line is obscured by other lines:

obscured-time-series

 

To fix this, as in the third diagram:

 

corrected-time-series

 

Control click on the highlighted series and elect “Format Data Series” under “Series Options”;

Select “Secondary Axis” and ensure that the second axis, on the right hand side of the chart, has the same scale as the primary axis. If so, delete the secondary axis in the chart area and you are done;

If the axes are not identical, control-click on the secondary axis, select “Format Axis” from the pop-up menu, and, under “Axis Options”, adjust the minimum and maximum of the primary axis in the minimum and maximum boxes under “Bounds”. Once this is done, remove the axis from the plot area and it’s done.

Note that I have also fattened the key line just a little, using the width adjustment widget under “Format Data Series”, “Line”, for even better visibility.

Pretty useful, huh? But that’s not all you can use the secondary axis for. In future blogs, I will show you how this is one of the tools you need to create things like bar-in-bar charts and bullet graphs:

 

bar-in-bar-chart bullet-graph

 

 

 

 

 

 

 

 

1) We might ask: “Are standard graphs like these data visualization”? That’s a question for another day, but my quick thought is “Yes, this is the ground floor, where you need to start in order to have a good foundation on which to build”. This is the stuff that you need to be able to build into reports and briefings, at minimum, and use as an anchoring component in infographics and other displays. Let’s call it “effective data presentation”.
2) Stephen Few, Show Me the Numbers, Second Edition (2012), Analytics Press.