Charts are easy to create until you need to create a chart that has a line chart and a column chart.  Percentages are used in pie charts and units as used in column charts.  You can COMBINE these two chart formats into one chart.

Year Expenses Sales Profit
2002

10000

11000

9%

2003

12000

12500

4%

2004

15000

16000

6%

2005

16000

17000

6%

  • Make sure that the year column is in text format.  You cannot choose number format and convert to text.  You need to place a single quote in front of each year to make the number into text. Notice that the numbers are left aligned.
  • Profit: =(sales-expenses)/sales
  • Highlight the entire table and Insert Column Chart and choose 2-D Column. It MUST be this format or the combination option (secondary axis) will not be available.
  • Move chart to a new sheet.

  • Profit series are visible (they are the thin lines on the chart).  They are right of the red bars. They are this way because the other numbers are larger than the percentages.
  • Right Click on Profit series and choose Format Data Series and choose Secondary Axis and Close.
  • Now the percentage numbers are on the right side of the chart and the flat lines are bars.  They are on the secondary axis now.  This chart sits on the top of the original chart.

  • Right Click on the Profit Series and change chart type to a line graph.

And there you have it–a COMBINATION chart!!!

Advertisements