Series overlap

When analyzing sales, we will often come across data series that would be convenient to display using a Stacked Column or Stacked Bar chart, but due to their nature this is not a good solution. For example, if we have a production cost and a sales price, we need to calculate the price difference in order to show the production price and the price difference as two complementary series on the chart; their sum is, it is easy to see, the sales price. Can we create such chart without additional calculation?

Of course we can! In this particular case, it is possible to display both series using a Clustered chart, and then adjust the series to overlap.

For example, we will use a table showing the production cost and sales price for three items. Let’s select the data and create a Clustered Bar chart. We then could select one of the display styles according to our preferences. Next, right-click on one of the series and select the Format Data Series option. A menu will appear on the right. You can see two sliders in it:

  • Series Overlap (percent that shows overlapping of series)
  • Gap Width (gap between series)

The first slider needs to be moved until we get a value of 100%. If you see only a series with a higher data value (it switched a smaller one), you need to go to the Design ribbon, start the Select Data option, then just set the smaller series to be the first to be displayed. Easy task, isn’t it?