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$

1 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.

  • Thanks, so it’ like I thought, I must fill the cells below C1.
    – mdcq
    Dec 17, 2019 at 15:47
  • @philmcole correct, but at least with my formula you will only have to enter it in one cell.
    – CodeCamper
    Dec 17, 2019 at 17:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.