Charts - how to limit x-axis to rows with populated values

**MCunningham** · 02-01-2013, 06:11 PM

[edit] SOLVED BY STEVE=TRUE (summary on my next post)
I have a lot of charts that I publish every week for budget meetings. The chart range for the first one is 'Hist Table'!$A$1:$R$100
The A column is a list of fiscal weeks (FY13-P2-W2, FY13-P2-W3, etc) and the rest of the columns make up the y-values. They are set to be "" (blank) in their formulas if there is no data matching that week.

What I would like to do is leave the chart range for the entire range through the end of the year, and have the x-axis only stretch up to the current week (for which there is data). Instead, I either have to have a big blank on the right side of the chart or keep updating the range for every chart every week.

Is there a solution to this? Thanks so much in advance!

**Steve=True** · 02-02-2013, 06:15 PM

Hi MCunningham,

You can do this in a few ways. I would suggest checking out this tutorial to see if it can help.

Case Study - Creating a Dynamic Chart in Excel Using Offset Formula

Let me know if it helps.

Steve=True

**MCunningham** · 02-02-2013, 07:33 PM

Steve, that's brilliant! I never thought using dynamic references for names, nor did I know that you could reference named ranges in a chart!

I appreciate that a lot. I'm even going to feel a little bad when I show this to my boss and don't give you credit!

**Palmetto** · 02-02-2013, 07:54 PM

They are set to be "" (blank) in their formulas if there is no data matching that week.

Rather than have your formulas return blanks, have them use the NA() function. Example: =IF(A1=100,A1,NA())
Excel does not plot N/A values.

You might also consider using Pivot Tables based on an Excel Table.
Tables are automatically treated as dynamic named ranges - no need to manually create them.

With Tables, you should learn to use Structured References

Dynamic and Interactive Charts

**MCunningham** · 02-02-2013, 07:58 PM

Originally Posted by Palmetto

Rather than have your formulas return blanks, have them use the NA() function. Example: =IF(A1=100,A1,NA())
Excel does not plot N/A values.

You might also consider using Pivot Tables based on an Excel Table.
Tables are automatically treated as dynamic named ranges - no need to manually create them.

With Tables, you should learn to use Structured References

Dynamic and Interactive Charts

I've already tried this. The problem is the x-axis values are already filled in, so the chart will just have a big blank on the right side where there are no y-values. Steve=true's solution works great, though.