3 axis chart with stacked bar graph and line graph above

**hefe_espada** · 06-24-2013, 12:04 PM

Hello again,

I just joined this forum and everyone has been extremely helpful! I am back with another question. I am trying to create a 3-axis graph, combining a stacked column graph with a line graph above. As for the column graph, the X-Axis is labeled "Year" with 2010-2013. The stacked bar graph represents the number of transactions that took place in each county for a given year (Counties stacked on top of one another, each represented by a different color.) The Y-Axis label is currently "number of transactions". I would like to plot total sales volume above the stacked column graph as a line. There will be one line for each county, each data point representing the total sales volume for that county in a given year. I would like to make the other Y-Axis label "sales volume". Does this make sense? I am having trouble uploading examples.

Below is the table I have used to create the stacked column graph, which works perfectly. I just can't figure out where to add the sales volume figures for each county to get it to plot above in a line.

County 2013 2012 2011 2010
Los Angeles 4 14 15 11
Orange 0 3 0 6
Riverside 1 0 0 0
San Bernardino 0 3 0 0
San Diego 0 3 1 0
Ventura 0 0 2 1
Totals 5 23 18 18

Below is the table I am using to create the line graph, this also works perfect. Any help would be much appreciated!

County 2013 2012 2011 2010
Los Angeles $314,550,000 $787,005,909 $932,985,440 $653,238,960
Orange $0 $139,900,000 $0 $375,600,000
San Bernardino $0 $213,475,000 $0 $0
San Diego $0 $253,850,000 $80,400,000 $0
Riverside $58,000,000 $0 $0 $0
Ventura $0 $0 $130,100,000 $81,500,000

**Steve=True** · 06-24-2013, 08:27 PM

Hi hefe_espada,

Welcome to the forums!

I think this chart meets your requirements. What you need to do is first create your column chart. Then you need to create one more series and change that chart type to XY Scatter with Line no markers. Once you have done this, you can add new series and change their refernces to create your lines on the 2nd axis. Notice that I added one column for the X's that are used for each line series.

Also, if you want to know how to do the Billions/Millions format as your numbers get really large on the right, check out this post:

How-to Format Chart Axis for Thousands or Millions

Please let me know if this helps and is what you were looking to do.

Steve=True

**hefe_espada** · 06-25-2013, 11:28 PM

Amazing! That is exactly what I was looking for. Thank you for your help, that was very kind! And my boss will be impressed

**Steve=True** · 06-26-2013, 12:53 PM

Hi Hefe_espada,

I am so glad that this worked for you. You are welcome.

thanks for the great comments and good luck on that raise you are sure to ask for now

Steve=True