Solved

How to create one chart with two vertical scales

Posted on 2013-06-25
3
324 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 31

Accepted Solution

by:
Rob Henson earned 250 total points
Comment Utility
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:teylyn
teylyn earned 250 total points
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now