will introduce you to the exciting new pivot table features in Excel 2007. Create quick summaries and pivot charts, add impact with traffic light icons, design calculated fields, group dates and numbers.
Quickly Create Named Ranges in Excel
In an Excel workbook you might have a worksheet that contains several lists that you use as the source for data validation dropdown lists. For example, this worksheet has a list of countries, and lists of regions within those countries.
If each list has a heading, you can quickly create named ranges from the lists.
Create the First Named Range
Select the heading and the items in the first list that you want to name.
On Excel’s Ribbon, click the Formulas tab
In the Defined Names group, click Create from Selection
(Note: In Excel 2003 and earlier versions, click Insert > Name > Create)
In the Create Names dialog box, add a check mark to Top row, remove any other check marks, then click OK.
Name the Remaining Ranges
To name the next range, select its heading and items
On the keyboard, press the F4 key.
Repeat for all the remaining ranges.
_______________
January 22nd, 2009 at 1:58 am
Deb, good tip. I'm often amazed at how I've missed some of these things. I've always gone straight for Define Name and never even tried to see what Create does. I'm a creature of habit, I guess.
January 22nd, 2009 at 11:20 am
Ctrl+Shif+F3 - Especially if you are on 007
January 22nd, 2009 at 8:16 pm
Tim, if we stopped to look at all the options in Excel, we'd never get any work done!
Sam, thanks, nice keyboard tip!
January 26th, 2009 at 7:48 am
[...] Quickly Create Named Ranges using F4 Key [...]
January 26th, 2009 at 2:55 pm
I use this even if I want to define a dynamic range. First, it lets me test formulas in dependent cells even before I have a chance to break the dynamic refers-to formula. Second, it gives me at least a starting point for defining the dynamic name.
January 26th, 2009 at 6:52 pm
Jon, that's a good idea. Thanks for the tip.
August 26th, 2009 at 1:05 am
It works except one time the dropdown list included the "title" of the range (it was a yes / no)???