Add single value as constant line to diagram

Question

I have a range A1:B of which I make a simple line diagram. I also have a single value in another cell, say C1, which I want to use as a constant reference line in the diagram.

I know I could populate the whole C column with C1 and then extend the diagram to the range A1:C. But I was wondering if there is a better way to add a value as a constant line in a diagram without wasting a whole column.

Example data

Year        Expenses    My Goal
2007        500$        500$
2008        400$
2009        550$
2010        450$
2011        300$

Answer 1

The only option in charts is to use one of the many trendline options.

Since your column is just a single arbitrary value and you want to plot it the best thing you can do is put it all in one formula. Assuming "My Goal" is in cell C1 replace it with the following formula:

={"My Goal";arrayformula(if(A2:A="","",500))}

You can also replace 500 with a reference somewhere else in your spreadsheet if you want to easily change the goal without needing to change the formula.