I’m seeing a lot of referral traffic to this post searching for Excel 2010.If you’re simply looking for where you define or modify named ranges in Excel 2010 (as one commenter indicated in response to an earlier version of this update), it’s on the Formulas tab in the Defined Names area — Name Manager.You can just drag a big area if you want, but this is a slightly more elegant approach.
My favorite on that front is Jon Peltier’s (if you get intrigued by this post, hop over and peruse a slew of other ways to have charts dynamically update).
This post describes (and includes a downloadable file of the example) a technique that we use extensively to make short work of updating recurring reports. I like to just have the first worksheet as the presentation layer — let’s name it Dashboard — and the second worksheets as the data layer — let’s call that Data.
We’ll name the cell in the first row of each metric column as the current value for that metric (the cells don’t to be named cells, but it makes for easier, safer updating of the dashboard as the complexity grows).
Name each cell by clicking on the cell, then clicking in the cell address at the top left and typing in the cell name.
Let’s call this Main Data and define it by going to Formulas » Name Manager and clicking on New (this is Excel 2007 — it’s somewhere else easier to find in Excel 2003).