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

Resources