?
Solved

How to create one chart with two vertical scales

Posted on 2013-06-25
3
Medium Priority
?
374 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 1000 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 (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 1000 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

719 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