# Options for filtering a chart series

#### Celly

##### Board Regular
Greetings!

Say I have a sheet with data in range A001:AXXX. The XXX can vary in size based on another cell value (MAX_YEARS). This is a financial model. Odd rows have the data in nominal dollars, and even rows have the data in real dollars. I am looking for the cleanest solution to allow charting of only the nominal data or only the real data. Here are the solutions I am currently aware of:

Solution 1: Employ an auto filter on a dynamic range and use that range as the chart series. This works relatively well, however it visually filters the data on the sheet and couples the chart filtering to that visual filtering. I would like to decouple this so that potentially the data is visually filtered one way on the sheet, while the chart another.

Solution 2: I found this thread:

This potentially can work, however it employs INDIRECT ranges and is a bit brutal for a complex project with a lot of charting.

Solution 3: I can dynamically built a non-contiguous range through VBA, i.e. { A1,A3,A5,A7.. } This sort of works, but these non-contiguous ranges are unwieldy and I lose the ability to use OFFSET on them to get a fully dynamic size based on that MAX_YEARS cell value.

Solution 4: Excel 365 has a new FILTER function which seems to address this difficulty, however I need to maintain backwards compatibility to Excel 2007.

Solution 5: Refactor the nominal/real data into columns instead of rows. This would solve all my charting difficulties, but the row based approach has other significant advantages and this is not a good solution.

Is there a sixth solution I should be evaluating?

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Replies
0
Views
27
Replies
3
Views
71
Replies
0
Views
65
Replies
1
Views
246
Replies
0
Views
29

### Forum statistics

1,127,405
Messages
5,624,574
Members
416,036
Latest member
eloisa manzanarez

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back