h2g2 The Hitchhiker's Guide to the Galaxy: Earth Edition

Dear Excel gurus

I know there must be an answer but 3 of us in the office are stumped and can't get any further.

We want to make a bar chart showing qty sold in any one year and revenue in that year. With the bars next to each other. Because the qty is in K pcs and the amount is in USD Mio when you put them on a chart together with one vertical axis it looks totally mad - the qties look massive compared to the revenue.

So we thought, if we put a 2nd axis on the right hand side for the revenue, we can have the blue bars measured against the left hand axis for qty, and the red bars next to them measured against the right hand axis for revenue.

but when we do that as a 2d chart, the bars overlay each other so you can only really see the red ones with a bit of blue peeping over the top now and again.

Does anyone have any idea how we can have a 2d chart with this data, showing 2 axes but the bars next to each other?

I can give the numbers if anyone has any clues (we tried right clicking the bars and sliding the slidy thingamyjig to separate but it just made one set of bars fatter than the others)

No, I can't get the bars combined side-by-side as you say. I guess your options are to have one of the data series as a line chart, or to have two separate bar charts side by side.

Trying to visualise this. You essentially want the x axis to have two scales: quantity and revenue, each with their own units.

While I am sure you can do this in excel (I am sure you can do ANYTHING in excel) wouldn't you be better showing the ratio of quantity to revenue? That would ultimately tell you the same information as seeing them side by side (where you do your own ratios in your head) and would mean every graphed point on the page would relate meaningfully to every other graphed point.

But I may really not understand what you want. You could do the ratios as a line graph and overlay the quantity and revenue figures as scatters.

You can alternate the series with zeros:
20 0
0 400
30 0
0 450

etc and then play with the column widths

Horrid

I can't get bars to display this way, but I can get scatter plot with lines. On the data series you want on the right axis, you Format Data series, and tick the Secondary Axis.

Additionally, I put the labels above the diagram, that shows the second series to the right. I haven't been able to put labels on the two Y-axes directly, but I did put the unit in the Series labels.

Will send you an image on fb.

thanks everyone - I liked the line chart better than what we did in the end

we had rows of data something like:

qty (Kpcs) 400 57 526 78
amount ($mio) 27 3 75 4

in the end we inserted another row of data called "blank" and left all the cells blank, when we made the chart we had the vertical axes showing qty (left) and amount (right) and then told it not to show the blank row. And, hey presto, suddenly the bars were side by side