Dynamic Range Names And Charts In Excel 2010 (The Right Way)

I have long been reluctant to post solutions on-line for the very reason that there’s always someone out there smarter than me with a better solutiuon that makes me look like a “Piker”.  Well I finally decided to post a solution to a problem I found recently, only to find once again that I missed something and that the actual solution to a percieved problem was actually much easier than the solution I found.  So here I go “Eating Crow” once again.

First I have to thank my friend Andy Pope (becoming friends after we stared at each other across a crowded bar near Covent Garden for nearly an hour before we had the courage to ask if we were the one who we were actually looking for – and then sharing a couple of great local beers on-tap with Mike Staunton and Bob Phillips as well – who I’m sure both knew about this) for providing me with the absolutely correct solution to the problem of Dynamic Ranges in Excel 2007 and 2010 Charts.

In my research on this topic I think I tried this technique without success but I must have done something wrong and thus went off on a tangent to use the old “Coded Solution” Method.

But , as Andy pointed out to me, to use a Dynamic Named Range as the source of a Series in a 2010 Chart, you need to enter it through the “Select Data” Button on the Design Tab of the Chart and for the Series as a Fully Qualified Reference to the Range:

Notice that the full name of the Host XLSX file is referenced in the reference to the CHT_DATA Dynamic Name Range, a Range that looks like:

=OFFSET(CHT_LABEL,0,1)

and is a truly Dynamic range (that takes advantage of the inherent Array capabilities of Excel in a cool way BTW – thanx again Andy).  Therefore it is based on a Range Name CHT_LABEL that looks like this:

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1)

that Dynamically changes the range and the countA of values in Column A increases or decreases.  Awesome !!

If you are using a Range Name with a Worksheet Scope (as opposed to Workbook Scope like these Names) you only need reference the Worksheet name as opposed to the Workbook name).  BUT YOU CANNOT ENTER ANY RANGE NAME IN AN EXCEL CHART UNLESS YOU USE A FULLY QUALIFIED REFERENCE.

So in conclusion you can pretty much ignore my previous post (which I will edit to refernce this Post instead).  I do think it’s a cool VBA solution though and I will probably use this technique in some other context somewhere, so I don’t consider it all a waste – just a BIG embarassment😦 …. Just kidding – just a small embarassment.

Thanx again and again Andy.

Dick

About Biggus Dickus

Dick is a consultant in London, ON Canada who specializes in Microsoft Excel and Microsoft Office Development.
This entry was posted in Uncategorized. Bookmark the permalink.

27 Responses to Dynamic Range Names And Charts In Excel 2010 (The Right Way)

  1. Ramesh says:

    Thanks for the info. Dynamic ranges are also possible through indirect and Index functions. the problem with the offset function is that it is volatile

    • Biggus Dickus says:

      True … but I like it that way if it means I don’t have to worry about it😉 …. Call me lazy …

      Thanx for your post.
      Dick

    • Bob Phillips says:

      So is INDIRECT!

      • sam says:

        So is Index in special circumstances.

        Index:Index is quasi volatile – so it will recalculate only on file open and close

    • None of your Buisnes says:

      Hi, how about once more for those who don’t know what the hell you are talking about, who have no idea how to use Excel, but have to use it due to circumstances.

      • Biggus Dickus says:

        Not sure what you mean? You have a strange way of asking for free advice … (??)

        I dont think I’ll be going any further with this issue I’m afraid. Good luck.

        Dick

  2. Peter Carr says:

    Hi Dick,
    I fund your articles really good.
    I hate to be the one who tells you this, but there is an even easier way to do Dynamic Charts:- Base your chart on and Excel 2010 table! No range names required at all.
    Interestingly, the chart dynamically picks up changes in the number of rows in the table, even the the series formula does not reference the table by name.
    Give it a try, just define the example you showed as a table, create a new chart and voila, it’s dynamic.

    cheers

    Peter

    • Biggus Dickus says:

      Peter:

      Oh yes I am a BIG fan of Excel “Tables” and you are right that this would be MUCH easier if you use “Tables” and the references end up as Structured References to Fields in the “List” – absolutely !!

      But sometimes implementing Lists is not the preferred solution and also there is the issue of moving “legacy” spreadsheets forward to 2010 where this would be an issue (unless you have the luxury of being able to rewrite the spreadsheet).

      Thanx for your point, well taken.

      Dick

  3. andy pope says:

    Never be reluctent to post, there is always something to be learnt from what others post.

    Peter, tables/lists are great if you only want the data to continually expand. DRs are needed to do subsets of data and switching of data areas used.

  4. Bob Phillips says:

    That’s the great thing about knowing Andy, as well as being a good drinking mate (never makes me feel I drink too much :)), he is GOOD!

    And aren’t we all great fans of tables? Well, no, not completely. Don’t you just hate how you drag a structured reference formula to the next column and the column you reference in the table changes, no way to lock as with absolute columns that I can see. And as for trying to use structured references in conditional formatting, forget it.

    • Biggus Dickus says:

      Yeah Bob … Andy is my new hero and a good beer-drinking mate as well.

      Speaking of which …… It looks like I might be “Over ‘ome” sometime in mid-June. Maybe we should have another beer (and hopefully I won’t be so sick this time😦 ).

      Will keep all posted.

      • Bob Phillips says:

        Depends when for me. I am away until June 10th, up in London to see a play on 17th, and a couple of other things that escape my fading memory. I am sure Marcus will be there though!

  5. You’re truly right on this blog

  6. Blackmagic says:

    Extremely well executed writing

  7. Mawdo says:

    Wow! I’ve been struggling with dynamic charts in 2010 for what seems like an age! I’ve finally found your blog and realised I need to fully qualify the global names!

    Thanks

    • Biggus Dickus says:

      You’re welcome🙂 … Too bad you had to waste so much time with this because nobody at Microsoft thought it was important enough to tell anybody about it ….

      Dick

  8. JD says:

    Hate to dig up an old post, but thought you might be interested to know that this solution does not work on my version of Excel 2010 (32 bit). Maybe they REALLY don’t want us to use dynamic ranges in charts and “fixed” it in a recent upgrade. Whatever the cause, it is incredibly frustrating.

    • Biggus Dickus says:

      I don;t want to dig up an old post either but I have to say that the testing I did was on 2010 so I think (with all due respect) you musta done something wrong?;-)

      I’m not blogging anymore (for various reasons) so I’m not interested in starting a big thread on this but I suggest you try again.

      Dick

  9. Nicky says:

    Hi. I’m trying to design a template of sorts for the first time, and I need to create multiple charts in the same format…but with different data sets. How do I do this?

    • Biggus Dickus says:

      Hey Nicky …

      I suggest you create your master chart and then copy and paste it as many times as necessary. Then you go in a change the data source of each chart, pointing it to your other data sources in the model.

      It’s actually easier than you think.

      Dick
      p.s I watched “Little Nicky” last week again and I’m still laughing, especially at the pineapple/Hitler bit. I assume you’ve been forced to watch it somewhere along the way😉 ?

  10. Everything is very open with a clear clarification of the issues.
    It was truly informative. Your site is useful.
    Thanks for sharing!

  11. wwwww says:

    First off I would like to say terrific blog! I had a quick question that I’d like to
    ask if you don’t mind. I was interested to know how you center yourself and clear your
    head before writing. I have had a difficult time clearing my thoughts in getting
    my ideas out there. I do enjoy writing but it just
    seems like the first 10 to 15 minutes are usually lost just trying to
    figure out how to begin. Any recommendations or tips?
    Cheers!

    • Biggus Dickus says:

      No answer to that one … I’ve never had writer’s block like that … probably because I only write when something’s on my mind🙂 ..

      Dick

  12. I’m impressed, I must say. Seldom do I encounter a blog that’s both educative and amusing, and let me tell you, you
    have hit the nail on the head. The issue is something that too few folks are
    speaking intelligently about. Now i’m very happy that I found
    this in my search for something concerning this.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s