Solved

How to create one chart with two vertical scales

Posted on 2013-06-25
3
352 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
  • 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to bypass VBA Project password 5 49
Web Query 1 20
EXCEL formula that pulls formatting as well 12 44
Need conditional formating when doubles 14 16
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

840 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