Marimekko Replacement – Overlapping Bars (Easy)

I showed an overlapping bar chart alternative to a Marimekko chart in my recent post The Problem with Marimekkos. I gave very few clues about how to construct such a chart, so now I’m showing the protocol.

The approach shown here is somewhat simplified, and works only when the grid is symmetrical, with the same number of rows and columns.

Like most advanced charts, you need to rearrange the data in order to make it work. Here is the original data and a Marimekko chart made from it.

The first step is to compile the row and column subtotals into columns, with row and column headers in the first column, and the data staggered so each set of data appears in its half of the chart (in this case, Company to the left and Segment to the right).

Make a clustered column chart with these three columns of data.

Set the Gap Width of these series to 25, so the gaps between clusters are 25% as wide as the bars. This 25% comes from the final chart, which will have four narrow columns per wide column, with a narrow-column-width gap between.

Set the Overlap to 100, so the bars of one series overlap with the blanks in the other.

Now rearrange the rest of the data into the following sparse array. The gray rows are blank; I’ve only colored them for my own benefit in my working file.

Copy this range, select the chart, and use Paste Special to add the data to the chart as new series. Because the Overlap was set to 100, these new bars overlap the existing ones. For now I’ve colored the bars in contrasting colors with a custom palette.

Here’s a glimpse of the custom Excel 2003 color palette I used to format these bars. Bar_Chart_Graded_Colors.xls is a small downloadable Excel 2003 workbook that uses this palette.

One by one, move each of the series you’ve just added to the secondary axis. Change the Gap Width of one of the added series to 0 (zero). This is as good a time as any to remove the legend.

Excel has added the secondary Y axis, but we want the secondary X axis. In Excel 2003 and earlier, go to Chart menu > Chart Options > Axes tab, uncheck the Secondary Value (Y) Axis box and check the Secondary Category (X) Axis box. In Excel 2007 and later, go to Chart Tools > Layout tab > Axes > Secondary Vertical Axis > None, then Chart Tools > Layout tab > Axes > Secondary Horizontal Axis > Show Left To Right Axis. The bars almost line up.

Format the secondary category axis (top of the chart) and uncheck the Value Y Axis Crosses Between Categories in Format Axis dialog > Scale tab in Excel 2003, or select Position Axis > On Tick Marks in Format Axis dialog > Axis Options in Excel 2007. The alignment of columns in Excel 2007 is precise, but the alignment in Excel 2003 needs a little adjustment. Tweaking the width of the chart by a couple pixels may make the alignment slightly better, or slightly worse.

Changing to a matching color scheme completes the chart, and makes the misalignment almost vanish.

Changing the gap width of the secondary axis bars to 10 minimizes the misalignment and provides a border between the small bars.

The more complicated approach allows for asymmetric data arrays (different numbers of rows and columns) and also eliminates this slight misalignment. The next tutorial, Marimekko Replacement – Overlapping Bars (Hard), will describe that method for making overlapping column charts.

To construct a 2×2 panel chart that shows the same data, perhaps somewhat more clearly, see Marimekko Replacement – 2 by 2 Panel.

IIf you still want to make a Marimekko chart, you might consider the PTS Marimekko Chart Utility. Like all of my commercial utilities, it reduces the manual process of constructing a complex chart to simply selecting a range, choosing a couple of options, and admiring the result.