Show average value as a horizontal line in a column chart
 

Have created a column chart showing world GDP and World Merchandise
Exports(Annual percentage change) for the years 1995 to 2005. Want to show
the average GDP growth (1995-2005) and average export growth (1995-2005),
both as horizontal dotted lines on the chart. How do I do it? I am using
Microsoft office XP pro.

Show average value as a horizontal line in a column chart
 

Hi Fenil,

Two methods explained. First to start from scratch and second to add the
line to an existing chart.

Start from scratch method:

Create an additional column for average. Insert column header and formula in
first cell of range and copy down for the full length of the data.

Example of Formula: =AVERAGE($B$2:$B$30).
Note the use of absolute values with $ signs for the range.

Select all the data including the average and create the column chart.

Right click on the column representing the average.

Select Chart type.

Select Line Chart.

Select plain line (without place markers)

Click OK.

Right click on the straight line representing average series.

Click Format Data series

Select preferred Style, Color and Weight.

Click OK.




Alternatively Method if chart already exists.

Add the column of data for the average.

Right click on the plot area.

Select Source data.

Select Series tab.

Click Add.

Click little red arrow at right of Name box and another box opens.

Click on the column title on the worksheet for the Average. (One cell).

Click red arrow at right of box.

Click on the red arrow at right of Values box.

Select the average data range only (without column header).

Click on the red arrow at the right of box.

Click OK.

Right click on the straight line representing average series.

Follow the instructions from previous method to change to a line.

The above creates one average line. Repeat for additional average lines.

Regards,

OssieMac

Show average value as a horizontal line in a column chart
 

Here are some illustrated tutorials:

http://peltiertech.com/Excel/Charts/AddLine.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Fenil Shah" <Fenil wrote in message
...
Have created a column chart showing world GDP and World Merchandise
Exports(Annual percentage change) for the years 1995 to 2005. Want to
show
the average GDP growth (1995-2005) and average export growth (1995-2005),
both as horizontal dotted lines on the chart. How do I do it? I am using
Microsoft office XP pro.

Show average value as a horizontal line in a column chart
 

Hi,

Thank you so much for your prompt help! I had been really stuck without
knowing how to go about it since a lot many days now. You made my day! I
tried both the methods, the second in my existing chart and the first in a
new chart.

Regards,
Fenil.

"OssieMac" wrote:

Hi Fenil,

Two methods explained. First to start from scratch and second to add the
line to an existing chart.

Start from scratch method:

Create an additional column for average. Insert column header and formula in
first cell of range and copy down for the full length of the data.

Example of Formula: =AVERAGE($B$2:$B$30).
Note the use of absolute values with $ signs for the range.

Select all the data including the average and create the column chart.

Right click on the column representing the average.

Select Chart type.

Select Line Chart.

Select plain line (without place markers)

Click OK.

Right click on the straight line representing average series.

Click Format Data series

Select preferred Style, Color and Weight.

Click OK.




Alternatively Method if chart already exists.

Add the column of data for the average.

Right click on the plot area.

Select Source data.

Select Series tab.

Click Add.

Click little red arrow at right of Name box and another box opens.

Click on the column title on the worksheet for the Average. (One cell).

Click red arrow at right of box.

Click on the red arrow at right of Values box.

Select the average data range only (without column header).

Click on the red arrow at the right of box.

Click OK.

Right click on the straight line representing average series.

Follow the instructions from previous method to change to a line.

The above creates one average line. Repeat for additional average lines.

Regards,

OssieMac

Show average value as a horizontal line in a column chart
 

Hey Jon,

Thank you so much for your help. It worked like a dream! Thanks man

Fenil.

"Jon Peltier" wrote:

Here are some illustrated tutorials:

http://peltiertech.com/Excel/Charts/AddLine.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Fenil Shah" <Fenil wrote in message
...
Have created a column chart showing world GDP and World Merchandise
Exports(Annual percentage change) for the years 1995 to 2005. Want to
show
the average GDP growth (1995-2005) and average export growth (1995-2005),
both as horizontal dotted lines on the chart. How do I do it? I am using
Microsoft office XP pro.