auto adjusting axis based on cell ranges

Brass Contributor


I have this cluster stacked bar graph that shows infectious period over a course of time, this graph will auto populate as the data is entered over time,  the patient names axis pulls from a column in another workbook, but it looks like it includes blank cells, is there a way to avoid that so the height of the graph is relevant to the actual data?

8 Replies


Let's say the source data are on a sheet named Data Sheet.

x-values in A2 and down.

y-values of the first series in B2 and down.

y-values of the second series in C2 and down.

y-values of the third series in D2 and down.


Create the following named ranges - click Define Name on the Formulas tab of the ribbon:


XValues refers to =OFFSET('Data Sheet'!$A$2,0,0,COUNTA('Data Sheet'!$A$2:$A$10000),1)

Series1 refers to =OFFSET('Data Sheet'!$B$2,0,0,COUNTA('Data Sheet'!$A$2:$A$10000),1)

Series2 refers to =OFFSET('Data Sheet'!$C$2,0,0,COUNTA('Data Sheet'!$A$2:$A$10000),1)

Series3 refers to =OFFSET('Data Sheet'!$D$2,0,0,COUNTA('Data Sheet'!$A$2:$A$10000),1)


Click anywhere on the chart.

Click Select Data on the Chart Design tab of the ribbon.


Click Edit under 'Horizontal (Category) Axis Labels'.

Clear the current range, then insert


='Data Sheet'!XValues


Click OK.


Select the first series under 'Legend Entries (Series)', then click Edit.

Clear the current range, then insert


='Data Sheet'!Series1


Click OK.


Repeat these steps for the other series, with ='Data Sheet'!Series2, etc.


Finally click OK.

The chart should now be dynamic.

I uploaded the excel file because I got stuck at the defining portion, the two worksheets are titled "person diagram" and "person graph"

@Hans Vogelaar 


I don't see an attachment or a link.


Thanks. It was a bit more complicated than I thought, but here is the modified version.

You can see the defined names in Formulas > Name Manager.

thank you! that worked!

do you know if theres a way to auto set the minimum and maximum bounds based off the dates, currently I have to manually adjust them so that all periods are shown, when entering new data the axis bounds have to be reset each time@Hans Vogelaar 




In the attached version (a macro-enabled workbook again), I have added code to the worksheet module that dynamically adjusts the minimum of the value axis when the Dashboard sheet is activated.