Issue with creating a clustered column combo graph

**Lenny67** · 04-11-2021, 10:29 AM

Hi there, I’m looking to create a bar graph on 2 axis that has a separate column for 2 different variables, that sit side by side and are grouped by ‘time period’.

For example, I’m looking to produce a graph that shows the sales for apples 2 months pre- and post-pandemic in a column and have the percentage that this value is of the total value of sales of their category (fruit), in a column beside the one for sales.

So, I’m looking for fruit sales on the left y axis and % of average sales on the right y axis and for these to be grouped by pre- and post-pandemic. I have tried creating a combo graph, however, the only outcome I have managed to produce is a bar and line graph – but this doesn’t properly represent the values I want to display. When I’ve tried representing both variables as a bar, they sit on top of one another, and I’m wanting them to be side-by-side.

Screenshot 2021-04-11 at 15.08.26.png

I've played about with several different chart types, and the closest I've got to what I'm looking for is using a 3d bar graph - the only problem is I've not managed to plot the % values on a secondary axis:

Screenshot 2021-04-11 at 15.20.27.png

Can anyone point me in the right direction for creating this as required? If, for whatever reason, this isn’t possible, does anyone have any suggestions on how to display these figures in an aesthetically pleasing way that allows a quick and easy understanding of both variables?

Thanks

**MrShorty** · 04-11-2021, 11:07 AM

It can be difficult to debug or understand something like this based solely on pictures of charts. I find that a lot (often most) of chart design in Excel is how the data are arranged in the spreadsheet (which a picture of a chart cannot show).

I don't know if I have understood, but here's a wild guess. Assuming that you want something similar to the 2nd, but in 2D (because 3D is hard to read) and with the orange columns next to the blue columns, but having the orange columns on a secondary axis so they can be scaled to be visible.

1) As you've already discovered, when you move a clustered column data series to the secondary axis, Excel plots the columns in the same horizontal position -- losing the clustered effect. We need to arrange the data in the spreadsheet so that we can tell Excel where to place each column. I would use an arrangement like this:

time -- product -- s/p -- sales -- % of average
pre -- apple -- sales -- 200 -- blank
blank -- blank -- % -- blank -- 1.11
post -- apple -- sales -- 120 -- blank
blank -- blank -- % -- blank -- 1.20

In effect, instead of 2 categories for the horizontal axis, we are creating 4 categories. Then spacing the data for each category in the spreadsheet so that every other category is blank. Sometimes I will add a blank row/category before and/or after each grouping so that, when I format the column chart to have no gap, there is still a gap between groupings.
2) Select this table and insert a clustered column chart. Then format one of the data series to be on the secondary axis. The orange columns should stay adjacent to the blue columns (instead of behind/in front).

I'm not sure if that helps, because I'm not sure if I understood what you wanted. In any case, it should illustrate that most things are possible if you figure out the right arrangement in the spreadsheet then build the chart.