Solved

How to create one chart with two vertical scales

Posted on 2013-06-25
3
359 Views
Last Modified: 2013-06-25
I am looking ton try and overlay data with very different scope. For example I want to create a chart which shows me in the left vertical amount spent per customer, however in the right vertical scale I want to see the number of customers.

Question is ? Is this easy / possible in Excel. I have been looking high and low for a mechanism but it is clearly hiding in plain sight of me

cheers
0
Comment
Question by:aioej
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 33

Accepted Solution

by:
Rob Henson earned 250 total points
ID: 39274402
Once you have the two sets of data plotted on the graph, right click on the one that you want to show on the second scale and choose Format data series. In the window that comes up choose the Axis tab and select the radio button for Secondary axis.

Click OK.

Then right click on the data again and choose Chart type to change it to the type required, I am assuming line chart overlaid over a bar chart.

This is instructions for Excel 2003 but no doubt later versions will be similar.

Thanks
Rob H
0
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst
Ingeborg Hawighorst earned 250 total points
ID: 39274415
Hello,

create the chart as a clustered column chart with the two data series for spend and number of customers.

Select one of the columns, open the format dialog (how exactly depends on your version of Excel) and select "Secondary Axis". Now the chart will show two Y axes, but the columns will overlap.

You can make one series into a line instead of a column: select the column and change the series chart type to a line chart.

Or you can change your data layout to include one blank series of zero/empty data after the primary and before the secondary series.

In fact, the blank data series does not have to come from your data table. It can refer to any empty range in your sheet, as long as it has the same number of cells as the data series.

See attached file.

cheers, teylyn
two-axis-column-chart.xlsx
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39274435
An alternative solution:

Rather than showing the number of customers as a series on the chart, you could include the value from that series above the column for spend. To do this create the chart as a stacked chart and change the series order so that the number of customers is on top of the spend.

Select the series and right click and choose format options. Choose the data labels tab and tick the value box, OK. The value for that series will then appear within the column. Right click on one of the numbers and choose Format Data labels. On the Alignmnet tab there is a dropdown for Label position, one of these is Inside Base. This will push the numbers to the bottom of the series.

Then format the number of customers series such that it is not visible, eg no background with no borders.

Thanks
Rob H
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question