Change Chart Range

[Tarver]

I'm trying to graph a changing amount of data, and need to coax Excel into showing only the relevant data, not blanks or zeroes.

The amount of data changes on the fly (depending upon promotions, seasons, etc.) I have formulas that INDEX/MATCH the correct data, but there may be anywhere from two to 200 data points. Is there an easy way to change the range of data that my graph shows without having to manually select the ranges each time?

For instance, here's an extreme example, with only two data points. I'm trying to eliminate all of the empty space in the graph to the right.

Ideally, that graph should look like this, instead:



This is the same data as the graph above, but only showing these two non-zero (or non-null) data points. Is there a way to make that change (preferably) with settings in the chart? Or via formulas?

Thanks very much in advance for any help you can offer!

 

[Scott T]

excel will not chart an error. You need to have your formula or data have an error in the blank/zero cells.

 

[Repush]

apparently the INDEX/MATCH result in 0
instead of 0's try #N/A's . .

 

[Tarver]

Thanks, that's great info, but I've been unable to get it to do anything different by having either #N/A or #NULL ! errors as the results of my formula.

Here's what it charts if my result is an #N/A error.

[/URL][/IMG]

If I change it so that my axis has blanks ("") it's the same result, but without the #N/A errors across the bottom axis.

 

[Tarver]

Update: I found exactly what I needed, here:
https://trumpexcel.com/dynamic-chart-range/