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
Louwrensjfourie's avatar
Louwrensjfourie
Copper Contributor
Mar 04, 2021

SergeiBaklan Thanks for the swift response. The problem is with this is that based on start and end dates the columns will be populated. I've updated your sheet to illustrate this point, see attached. Then the issue is that the formulas in the cells does not work with COUNTA as COUNTA does not see them as empty cells.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Mar 04, 2021

Louwrensjfourie 

To ignore empty cells and cells with spaces you may use for dynamic ranges formulas

Dates:
=Sheet2!$F$7:INDEX(Sheet2!$F$7:$AE$7,SUMPRODUCT(1*(LEN( TRIM( Sheet2!$F$7:$AE$7)) >0)))

Totals:
=Sheet2!$F$35:INDEX(Sheet2!$F$35:$AE$35,SUMPRODUCT(1*(LEN( TRIM( Sheet2!$F$7:$AE$7)) >0)))

Please check attached. 

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

    SergeiBaklan that works perfectly. Thanks for your assistance. It is greatly appreciated.

Resources