HOw to make a chart range dynamic

Hello. How can I make a chart range dynamic and have it include only the range where cells are active?

I actually found some relevant online content, but the problem is that I will have NA values and while my chart ignores these NA values (i.e. it doesn't extend the line) it still extends the axis. For example, if I have four points, and then two NA points, there will be six hash marks and I just want 4.

Working with your sample in Book3.xlsx.

Create a named range, for example call it Chart_Range note that I have used an underscore, not a space, there are a few restrictions on valid names.

Change the 'Scope' dropdown to 'Sheet1' (or the name of the sheet holding the source data). This step shouldn't be necessary but it can eliminate potential errors.

In the 'refers to' box enter this formula.

Formula:

Please Login or Register  to view this content.

Then edit your chart to use the data source =Sheet1!Chart_Range.

Note that this only exclude errors that appear after the last numeric value in the range, if you have n/a in the middle of your data then you will still see blank columns.

This is good. It still keeps the markers on the horizontal axis, which is strange. How can I eliminate that?

Is there any kind of numeric value after the NA markers in your sheet?

I tried it on your sample before posting and changed some of the values to N/A, I'm sure it resized the chart.

I'll double check it when I get home.

My bad, it doesn't resize if you enter the named range as the main chart range, (excel converts the dynamic named range back to a fixed range there).

However, it does work if you use it in the chart series.

Using your sample sheet from post #1. Go to the name manager and define the named range as before (skip this step if you already have a saved file with this done).

Select the chart. Then in the Excel ribbon, navigate to Chart Tools - Design - Select Data.

In the popup box, don't enter the named range in the 'Chart Data Range' box, instead, click on 'Edit' under the 'Legend Entries (Series)' heading, and enter the name reference =Sheet1!chart_range in the 'Series Values' box, then Ok / Accept until you get back to the sheet.

Now the markers on the axis should only show when there is a value to be displayed.

Note that on more complex charts, it might be necessary to use multiple named ranges.

Sweet. That worked. What's the difference between the two?

Also, what is the last part of the formula doing? The part with the match and the 1E =Sheet1!$B$3:INDEX(Sheet1!$B$3:$I$3,0,MATCH(1E+100,Sheet1!$B$3:$I$3))

The first lets you set a single range, such as a table to produce a simple chart. The second one lets you specify non-contiguous ranges for each axis.

1E+100 is a big number in scientific notation, 1 followed by 100 zeros. It is used to find the position in the last number of the match range.