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
Abiola1's avatar
Abiola1
MVP
Mar 02, 2020
Since your range will include #N/A!.. I will suggest you deploy IFNA function to handle the error so that it doesn't impact your chart
jmant37's avatar
jmant37
Copper Contributor
Mar 02, 2020

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.

  • 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. 

Resources