Solved

How to create one chart with two vertical scales

Posted on 2013-06-25
3
344 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 32

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 32

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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 a scrolling table in Microsoft Excel using the INDEX function.

773 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