1

I'm trying to make a chart always take the range from the first 8 cells within a column without updating the range when a new row is added above the range. You could use something like "=INDIRECT("A1:A8")" in other circumstance, but within the chart editor it keeps telling me the reference is invalid.

This is also true for other formulated reference methods I tried to implement for earlier things.

Thank you in advance.

Improve this question

1 Answer 1

Reset to default
1

Google Sheets chart data range can currently (November 2021) only be a range reference such as Sheet2!A1:A8 or a list of range references such as A1:A8, C1:C8. It cannot be a calculated array such as a formula result, nor a named range.

One consequence of this limitation is that the data range will always get adjusted when you insert or delete rows within the data range.

To plot just the first 8 values in column A, put this formula in the first row of a free column:

=filter(Sheet1!A:A, row(A:A) <= 8)

Then use the new column as the data range for your chart. If you place the formula in a new tab where no rows get inserted or deleted, the chart's data range will no longer get adjusted when you insert or delete rows in the source sheet.

If you place the formula in the same tab as the source data, the data range will still get adjusted when you insert or delete rows within the first eight rows, but it should not matter, since the rest of the rows will be truly blank and thus get ignored by the chart.

Your indirect() formula will work the same way, but it is a volatile function that gets recalculated every time any value in the spreadsheet changes. It is better to use filter() or another non-volatile when possible. See these optimization tips.

Improve this answer
6
  • Thanks, it works as a solution for the specific issue I was currently having (if I'm creating the reference in another sheet, that is; in the same sheet it still means the chart would automatically adjust its range if I'm adding a new row no. 1); actually now thinking about it, creating a mirroring column using "=INDIRECT("1:8")" also works. But that still means a workaround without using a formula within the chart. That means this is impossible?
    – TLSO
    Nov 20, 2021 at 16:52
  • I need to reiterate that this solution requires the referenced values be placed on a different sheet where additional rows aren't added, as writing it on the same sheet would still keep changing the range used in the chart. And by INDIRECT() being a volatile function you mean it recalculates itself whenever something changes on the sheet rather than directly referencing the range pf cells more efficiently?
    – TLSO
    Nov 20, 2021 at 22:58
  • Thanks for updating the answer, but it would still matter putting the FILTER() formula in the same sheet when the new row is added at the very top, meaning that the previous row no. 1 is now no. 2 and the FILTER() formula thus automatically adjusts itself, as it will then miss a row of values with each iteration.
    – TLSO
    Nov 21, 2021 at 9:22
  • Edited the answer.
    – doubleunary
    Nov 21, 2021 at 10:17
  • Ah, I didn't know you can just reference the column in this manner. Does it make it less efficient of you refer to the entirety of the column like this?
    – TLSO
    Nov 21, 2021 at 11:09

Your Answer

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

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