Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 1
Default 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.
  #2   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 2,510
Default 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


  #3   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 6,582
Default 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.



  #4   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 17
Default 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


  #5   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 17
Default 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.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Add a Horizontal Line to a Column Chart in XL 2007 Fabio Coatis Charts and Charting in Excel 6 September 21st 07 06:38 PM
How do I add a horizontal data line to a column chart? soconfused Charts and Charting in Excel 4 September 6th 07 04:26 PM
how to add a horizontal benchmark value line in a column chart? Mike Chro Charts and Charting in Excel 1 July 5th 07 11:26 AM
need to add colored horizontal line on chart to show cutoff level CoachZ Charts and Charting in Excel 1 March 1st 06 01:31 AM
Horizontal Line Chart Volsfan Charts and Charting in Excel 0 September 8th 05 12:44 PM


All times are GMT +1. The time now is 10:33 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"