Forum Discussion

jmant37's avatar
jmant37
Copper Contributor
Mar 02, 2020

Excel Chart - x axis won't automatically resize when there is no data to display

I'm charting very simple data from a set range - charting as a bar graph. This range will include blank/#N/A (I can adjust my formulas to whatever is needed) until new data comes in via...
Charting
excel
jmant37's avatar
jmant37
Copper Contributor
Mar 02, 2020

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.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Mar 02, 2020

jmant37 

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

  • Louwrensjfourie's avatar
    Louwrensjfourie
    Copper Contributor
    Mar 04, 2021

    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. 

    • Louwrensjfourie's avatar
      Louwrensjfourie
      Copper Contributor
      Mar 04, 2021
      SergeiBaklan also note that the cells in row 7 and 35 contains formulas, so COUNTA does not work.
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Mar 04, 2021

        Louwrensjfourie 

        I assume you have something like this

        In dates row we have formulas as

        =EOMONTH(F7,1)

        and in totals row formulas are

        =SUM(G8:G34)

        We add two named formula for the dynamic ranges

        Dates:
=Sheet2!$F$7:INDEX(Sheet2!$F$7:$AA$7,COUNTA(Sheet2!$F$7:$AA$7))

Totals:
=Sheet2!$F$35:INDEX(Sheet2!$F$35:$AA$35,COUNTA(Sheet2!$F$7:$AA$7))

        Use them in chart

        x-axis:
='Chart Dynamic ranges NA.xlsx'!Dates

Totals data series:
='Chart Dynamic ranges NA.xlsx'!Totals

        Please check in Sheet2 of the attached file. If you have another model it's better to illustrate on sample file.

  • jmant37's avatar
    jmant37
    Copper Contributor
    Mar 03, 2020

    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!

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Mar 03, 2020

      jmant37 

      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.

Resources