• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

excel axis query (dynamic oppose to static)


New Member
hi there,

I have a range of data (E6:BQ10 or 'year 1 to year 65') that needs to be charted.


there is an 'override' input that allows the user to curtail/wind-up the development before the 65 years. I need this value to be the upper limit on the x axis so that I avoid the blank space




Your links aren't working

but If the X Axis goes from E6:BQ6

setup a named range called XAxis and put the following formula in


Then on the Chart select the Data Range 1, which will be like

=SERIES(,Sheet1$E$6:$BQ$6,Sheet1!$E$7:$BQ$7,1) <- Note the 1 on one end

which is used to define the X-Axis

and change it to be like


Changing the Book1 reference to the name of your workbook

You could also make the Data Ranges Dynamic by adding more named ranges

Data1 = Offset(XAxis,1,0)

and change the series in the chart to be like



Then have a browse through

Thanks Hui,

you've got me pointed in the right direction but I've had a go this morning and can't apply it to my spreadsheet.

Those links again:



Any further help would be greatly received.


Because you have used a Column Chart (stacked in this case) you will need to set a Dynamic X-Axis and Individual Named Range for each of the 4 Ranges in your chart

So the Series formula for each Chart will end up looking like


=SERIES(,Book1!XAxis,Book1!Data2,2) etc
Apologies, I'm really struggling to understand your previous message, i'm not excel chart savvy at all - the finance model guy retired last week and i've been thrown into designing a dashboard by Friday!


This is the kinda of chart I'm looking for - i guess the rank would be based on the year in this instance?



You will need to define 7 Ranges

1 for the X-Axis and

6 more, 1 for each data Range you want in your chart

The XAxis named range will be be


The Named Data Ranges will be

for Data Range 1

Data1 = Offset(XAxis,1,0)

where the value 1 will be changed to represent the number of rows the Data Range is below the X-Axis Range

Repeat this for the 6 Ranges of Data you are graphing

Once you have your named Ranges Set-up, whilst still in the Named Ranges Dialogue, select each named ranges and click in the equation box, That will show you that it is selecting the correct data, Adjust the formula's if it isn't.

Now go back to your chart and in sequence select each Range by selecting the appropriate bar

The equation box will show something like


There may be a Range or Text in front of the First Comma

In the equation Box you need to edit the formula to be like


Where XAxis and Data1 are the named ranges you defined earlier

Adjust the Book1 name to be whatever your Spreadsheet is called

Leave whatever is in front of the first ,

Do the same to all the bars

The Chart won't change until you have done them all as the longest range will define the X-Axis length
Sorry Hui, spent the past 3 hours labouring over this with no success.

1.) Defining a range; do you mean insert->name->define then copy the formula above in the 'refers to:' field? (i.e, the relevant range doesn't need to be highlighted)

2.) 'equation box' = fields name, value and category axis label on the series tab.

Once again, sorry this is so painful!

What Hui suggests is using OFFSET to create the input ranges for your charts. Here is a tutorial, You can understand the concept thru this http://chandoo.org/wp/2009/10/15/dynamic-chart-data-series