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

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

Resources