Forum Discussion
Excel Chart - x axis won't automatically resize when there is no data to display
Abiola1 Thanks for the reply. I can easily change the data to appearing blank rather than #N/A. It's the graphing that I can't work out what to do. Would love some advice.
Is there someone that can show me how to do this? I'm having no success working out how to automatically ignore for this graphing.
You may use dynamic ranges both for X and Y values
with OFFSET or INDEX
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A)-COUNTIF(Sheet1!$A:$A,NA()))or
=Sheet1!$B$1:INDEX(Sheet1!$B:$B,COUNTA(Sheet1!$B:$B)-COUNTIF(Sheet1!$B:$B,NA()))and use them in chart like Sheet1!XRange; Sheet1!YRange
SergeiBaklan I have a similar problem, with the main difference being that my chart data is in rows and not columns. Also, the chart data does not begin in the first column, but in column F and then needs to end at the last column which has data in it. Any suggestions will be welcome.
I want to plot row 7 (dates) and row 35 (currency values) starting from column F up to the last column with data in it with the graph scaling automatically updated as the data range changes.
- SergeiBaklan also note that the cells in row 7 and 35 contains formulas, so COUNTA does not work.
SergeiBaklan This was looking fantastic but I can't get it to work. No doubt this is just beyond me. I have plenty of other data in the columns so I adjusted your formulas to make a specific range (o33:p48). I also attempted to use the Name Manager to name the ranges, as you did. Then I attempted to edit the series within 'Select Data'. Where am I going wrong? It's just not working. I can see that yours functions perfectly.
I do apologise, but I only dabble in this stuff!
That's hard to say what exactly is wrong without seeing the file. You may incorrectly calculate dynamic range. You may use relative references in named formulas instead of absolute ones.
Check first how your dynamic range and named formula work within the sheet on formulas, after that apply to the chart.
