Solutions and training for business users of Microsoft Excel.
Solutions and training for business users of Microsoft Excel.

 Excel User's Home
 ExcelUser Blog      
 Site Map              
 Contact              
 Excel for Business
 Excel Dashboards   
 Excel Solutions   
 Exploring Excel
 BI for Excel    
 Business Tools   
 Excel Catalog   
 Affiliate Program   
 Excel Help Portal  
 
   
     
   
     

Home > Exploring Excel

Changing Types and Axes
In Microsoft Excel Charts


"I have trouble to make a chart. The chart I want is a stack column chart. Series 1 and 2 should be stacked together like Figure 1. Series 3 should be a line with an axis on the right like Figure 2. The line and stacked column should be in the same figure. Would you please help me to build such a chart? Thank you." -- Gong H.

Gong,

You didn't tell me whether your sample was company confidential. So I changed the labels and data.

It isn't obvious in this chart, but as you said, it has three SERIES formulas. The first two show currency. The third series shows a percentage.

All three series are formatted as a stacked column chart.

The third series shouldn't be presented that way, for two reasons. First, its magnitude is so small that it's invisible in this figure. Second, the data doesn't fit; it's meaningless to stack a percentage on top of measures of currency.

This second chart solves both problems. It uses a line chart for the percentages. And it displays the line chart's values against a second axis at the right.

Your question is, how do you modify your first chart so that it looks somewhat like the second?

This is easy to do.

The first step is to select SERIES 3, which we can't see. To do so, first select one of the SERIES formulas that you can see. Then press your keyboard arrow keys up or down to cycle through the various SERIES formulas until you get to SERIES 3.

(You know it's SERIES 3 because the last argument in the SERIES function displays that number.)

Let's start by assigning this item to the secondary axis. To do so, make sure SERIES 3 is selected, and then...

...In Excel 2003, press Ctrl+1 or choose Format, Selected Data Series to launch the Format Data Series dialog. In the Axis tab, choose Secondary Axis. Then choose OK.

...In Excel 2007, press Ctrl+1 or choose Layout, Current Selection, Format Selection to launch the Format Data Series dialog. In the Series Options menu, choose Secondary Axis in the Plot Series On option box. Then choose OK.

After you do so, your stacked columns might change color. Don't worry about it for now.

The next step is to change the chart type to a line chart. With SERIES 3 still selected...

...In Excel 2003, choose Chart, Chart Type. In the Standard Tab, choose Line and the top-left Chart sub-type. Then choose OK. The line might seem to disappear. This is because the default color of the line is yellow, which is virtually invisible at this point. But we'll change the color shortly.

...In Excel 2007 and above, choose Design, Change Chart Type. Then in the Change Chart Type dialog, choose Line and the left-most type of line. Then choose OK.

Finally, you probably will need to change the line's color. To do so, press Ctrl+1 again. And then...

..in Excel 2003, in the Patterns tab, choose Custom in the Line section, and then choose a color. I chose red. Then choose OK.

This gave me the figure shown above.

...In Excel 2007, choose Line Color from the menu in Format Data Series dialog. Set the color you want.

Then choose Line Style in the menu of this dialog and make the line thicker than its default 1-pt thickness, if you want.

Then choose OK.

As you look at these two figures, you can see that you have more work to do. The value labels aren't very readable, and they intrude on the labels for the right axis.

Even so, you now can see your percentages in the chart, so you've made progress.

All the best,

Charley


 
 
 
 


ExcelUser, Inc.
http://www.ExcelUser.com

Copyright 2004 - 2012 by Charles W. Kyd, all rights reserved. Content, graphics, and HTML code are protected by US and International Copyright Laws, and may not be copied, reprinted, published, translated, hosted, or otherwise distributed by any means without explicit permission. Terms of Use | Privacy Policy | Earnings Policy.

Excel Dashboards

Create professional quality dashboard reports with Excel.