Dual axis Column Chart

cjc155

Board Regular
Joined
Apr 21, 2005
Messages
70
Hi all,

I have a difficult problem to describe. Basically, I have 6 categories of data (let's call them a,b,c,d,e,f) for which I want to grab the last 3 weeks of values for each category.

Categories a,b,c are all numeric

Categories d,e,f are all monetary

I am trying to have each category have the bar graphs per each week. So, category A would have 3 bars, B 3 bars and so on.

I want to have the primary axis for a,b,c and have secondary (monetary) axis for d,e,f.


Any help you could give would be very much appreciated
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
1. Highlight a,b and c and use the chart wizard. This will give you a column graph.
2. The highlight d, e and f and drag them onto the graph.
3. On the column that represents "d" right-click and choose "format Data Series"
4. Click on "Axis", then choose secondary axis.
5. Repeat 3 and 4 for "e" and "f"
6. Change the scale of the secondary axis as appropriate (right-click on the right axis and goto the "Scale" tab)
7. If you need to change the overlap on series d,e and f by right clicking on each one and choosing "Format Data Series" then "options"

hope this helps.
 
Upvote 0
Almost there

This is great, now I just need them not to overlap. I have tried the gap/overlap, but still can't get them to be side by side.

Or actually, I want to have all category A group together, all B's, all C's and so on.


I have a bad feeling there may not be a way to do this
 
Upvote 0
It sounds like you may want to group by row, not column.

Play around with the settings - you'll get there.
 
Upvote 0
Thanks

Thanks all for the help. I printed out the last example, my quesion is how could I graph all Market A's together, Market B's together on one axis, and then have Share's be grouped together on the secondary axis.

I have tried different ways;

Rows vs. columns
inserting dummy values

but just can't get this to look right. Thanks again for the help
 
Upvote 0
Has your problem statement changed? What exactly does the data look like? What exaclty should the chart look like?
 
Upvote 0
Problem Statement

Hi Jon,

My problem statment is the same. I checked out your example, so on the final page, instead of having time periods, I would have the graph grouped by Market Category (time periods would have to be inferred).

So, the past 4 months of market A would all be grouped together. Next to that would be the past 4 months of Market B, and after that the past 4 months of Market C. These would all be referenced to the primary y axis (sorry I think I mis-said this in my last post).

Finally, the group of Share information would be grouped together and reference the secondary y axis.

Does that help explain what I am trying to do? Thanks a lot for your help.
 
Upvote 0
I think I have it now. Set up the data like this:

Code:
          Jan  Feb  Mar  Apr  Jan  Feb  Mar  Apr
Market A   15   25   21   24
Market B   20   17   20   23
Market C   26   23   19   23
Share                         55%  33%  40%  32%
Make a clustered column chart by columns, then switch each of the share series to the secondary axis.
 
Upvote 0
THANK YOU VERY MUCH

Jon,

This is perfect. Thank you very much for your help with this. I appreciate it.-C
 
Upvote 0

Forum statistics

Threads
1,226,618
Messages
6,192,046
Members
453,693
Latest member
maverick688

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top