Secondary vertical axis trouble

**JoeyMack** · 02-11-2014, 06:23 PM

Hello,

I am having trouble creating a secondary vertical axis with a stacked chart.

My data is like this:

Location / Revenue / Profit
Phoenix / 3,000 / 500
Dallas / 2,200 / 400
Boise / 900 / 125

I've created a stacked chart with one vertical axis showing a stack for Revenue and a second stack for Profit (each stack including each of the 3 locations).

Would like to have the Profit data plotted along a secondary vertical axis. Instructions on office.microsoft.com say to "click the arrow in the Chart Elements box, and then click the data series that you want to plot along a secondary vertical axis."

The problem is that the series are Phoenix, Dallas and Boise. I don't want one of them plotted on the secondary axis. I want the Profit number for each series (in other words, the second stack in my existing chart) to plot along the secondary vertical axis.

Would appreciate any advice on how to make this happen.

Thanks!
Joey

**Kaper** · 02-12-2014, 04:53 AM

In such case, I'd probably prepare helper table for graphing (or change original one) to look like:

PHP Code:


Location   Revenue  Profit 
Phoenix      3000     
Dallas       2200     
Boise        900     
Phoenix1               500 
Dallas1                400 
Boise1                 125

after that you end up with 6 series - 3 for primary and 3 for secondary axes)
I added 1 at end of names to make it easier to spot in Chart Elements list

After moving to secondary axis you probably will change bar colors to duplicate these from primary, and then remove unwanted legend entries (or whole legend?)

small trick - how to quickly remove single entry from legend:
- click on Phoenix1 in legend
whole legent will be selected
- click on it again (not doubleclick here!)
only this single legend entry will be selected
- press Delete (or rightclick and choose Delete)

See attached file
3 stages:
1) as created
2) 3 series moved to secondary axis
3) color formatting and legend correction.

Enjoy!