How do I add a reference line to Excel charts?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to add a simple horizontal line across a chart that represents the
average of the lines of plotted data, in my case monthly grocery
expenditures. The data is graphed in a simple bar format, and I'd just like
an average line that stands out.
 
That is a cool tutorial for sure. I went through it and got a reference, but
on my bar chart the reference is not a line but bars of equal height next to
each data bar. Is there a way to add a horizontal reference line to a bar
graph?

Thanks,
Michael
 
can be done. do you want the second chart as a line chart or bar chart. of
it is line chart follow the turorial. if you want the second series also a
baar graph by the side of each bar of the first series then
create another series with the same xvalues but all the Y values as
that praticular value. chart this as series 2 in the same chartsheet. if
necessary change data type of the series 2.
 
Roswellmike,

In this simple example, there are two sets of data. The first set of data
is grocery store expense and the second set of data is the average grocery
store expense.

Expense Avg. Expense
meat $152 $240
lettuce $242 $240
carrots $321 $240
apples $245 $240

Now you can build a regular clustered column chart first and create the
average line from that chart second.

Start column 1 in cell A2, column 2 in cell B1 (Expense), and column 3 in
cell C1 (Avg. Expense). Cell A1 should be blank. Add the data into your
spreadsheet. When complete, the data should span the range A1:C5.

Put your curser in cell A1 and go to the chart wizard. In the standard
types tab, choose the Column -> Clustered Column option. Hit the Next key
and go through the prompts until finished. When complete, you should have a
chart with two series consisting of bars - your original data and the average
of that data.

To create the average line, click once on any one of the average bars
(clicking once on any one of the average bars should select the entire
average series). Right-click your mouse, select "Chart type . . ." in the
menu, choose a regular Line chart, and then hit the OK button. You should
get a line consisting of the average values going across the first series of
bars.
 
Back
Top