How can I skip columns in a Google Spreadsheets Chart?

Question

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

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?

Answer 1

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

Answer 2

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.

Answer 3

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}