6

I have a table of data in a Google spreadsheet that we want to use to create multiple charts. The issue we are facing is that some of these charts omit certain columns. We don't want to remake the table multiple times. Example:

  • Row 1 = Labels for Items 1, 2 and 3
  • Column A = Dates
  • Column B = Item 1
  • Column C = Item 2
  • Column D = Item 3

example of chart data

Items are in a specific order and the order cannot be changed. (Actual data has more columns than this and reordering it to satisfy all required charts would be impossible to do within the same table.)

Charts

  1. Data range for a chart with all the data: Sheet1!A1:D5
  2. Data range for a chart with Item 1 & 2 only: Sheet1!A1:D4
  3. Data range for a chart with Item 2 & 3 only: ????

It is situation 3. where we are having difficultly, since we need column A for the dates and column C & D for the values, but need to skip column B.

Is there a way to ignore specific columns within a dataset?

What is the proper reference to use in this situation?

5

In the chart's menu, go to Advanced Edit..., then in the Chart types tab you can enter the range. If you need to skip some columns, enter 2 ranges separated by a comma, one to left of the column you need to skip, and one to the right. This is how it looks like in a chart I maintain - skipping columns E to G and M to O:

C15:D948, H15:L948, N15:P948
  • Thank you, it worked. I was able to use Sheet1!A1:B5, Sheet1!D1:D5 to successfully skip a column in the example. Perfect. – Scott M. Stolz Apr 27 '17 at 13:16
1

Another hack I found was to set the line thickness of the series you want to hide to 0px. Big issue is that the legend will still contain the title of the series. So it's by far not ideal but works for my particular use case.

0

The easier way, is to have a specific table for the third chart.

Use a custom array to keep it up to date: ={A1:A5,C1:D5}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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