Suppose Sheet1!A1:A11 contains...
{"Mar","Apr","May"
;991,925,267
;633,720,538
;38,651,296
;904,814,326
;784,216,558
;918,372,876
;842,596,43
;862,607,708
;61,653,206
;758,880,489}
and Sheet1!C1:J1 contains...
{"Jun","Jul","Aug","Sep","Oct","Nov","Dec"}
Insert a Defined Name, "Sheet1!X_Axis_Labels", that refers to...
=OFFSET(Sheet1!$A$1,,,,MAX(COLUMN(Sheet1!$A$2:$J$11)*ISNUMBER(Sheet1!$A$2:$J$11)))
...and insert a Defined Name, "Sheet1!Series1", that refers to....
=OFFSET(Sheet1!X_Axis_Labels,1,)
...a Defined Name, "Sheet1!Series2", that refers to...
=OFFSET(Sheet1!X_Axis_Labels,2,)
Repeat this pattern of defined names for...
=OFFSET(Sheet1!X_Axis_Labels,3,)
=OFFSET(Sheet1!X_Axis_Labels,4,)
=OFFSET(Sheet1!X_Axis_Labels,5,)
=OFFSET(Sheet1!X_Axis_Labels,6,)
=OFFSET(Sheet1!X_Axis_Labels,7,)
=OFFSET(Sheet1!X_Axis_Labels,8,)
=OFFSET(Sheet1!X_Axis_Labels,9,)
=OFFSET(Sheet1!X_Axis_Labels,10,)
Create blank Bar (or Column?) chart by choosing the Chart Wizard with an empty cell selected. Paste (or type) each the following SERIES formulas into the formula bar...
=SERIES(,Book1!X_Axis_Labels,Sheet1!Series1,1)
=SERIES(,Book1!X_Axis_Labels,Sheet1!Series2,2)
=SERIES(,Book1!X_Axis_Labels,Sheet1!Series3,3)
=SERIES(,Book1!X_Axis_Labels,Sheet1!Series4,4)
=SERIES(,Book1!X_Axis_Labels,Sheet1!Series5,5)
=SERIES(,Book1!X_Axis_Labels,Sheet1!Series6,6)
=SERIES(,Book1!X_Axis_Labels,Sheet1!Series7,7)
=SERIES(,Book1!X_Axis_Labels,Sheet1!Series8,8)
=SERIES(,Book1!X_Axis_Labels,Sheet1!Series9,9)
=SERIES(,Book1!X_Axis_Labels,Sheet1!Series10,10)
Now as you add data your chart's X-axis will automatically expand to accommodate the new data.
Like this thread? Share it with others