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.
Create Names From Excel Labels
This week I’m working on a giant Excel workbook, and my client wants all the data entry cells named. There are about 600 cells to name, so I’m looking for ways to same some time.
A quick way to name a cell is to click in the Name Box, type the name, the press the Enter key. In the screen shot below, cell C4 is being named as FullName.
Create From Selection
Even quicker is to use the Create Names feature, and name the cells based on the labels in adjacent cells .
Here, the cells with labels, and the data entry cells, are selected (B4:C8).
Then, on the Ribbon, click the Formulas tab, then click Create From Selection. Or, in Excel 2003, click Insert | Name | Create.
In the Create From Selection dialog box, select the location of the labels. In this example, the labels are in the left column of the selected cells. Then click OK to create the names.
Name a Range of Cells
In this example, I named a single cell with each label. You can select multiple cells adjacent to each label, and the label will name the range of cells.
Valid Characters in Names
If the labels contains spaces, they’re replaced with an underscore.
Other invalid characters, such as & and # will be removed or replaced by an underscore character.
For more information on naming cells, see the Learn about syntax rules for names section in the Microsoft article, Define and use Names in formulas.
_________________
September 23rd, 2009 at 12:20 am
How come I didn't know that? That is a real timesaver. Thanks. You might want to add in your article that it works for ranges too.
September 23rd, 2009 at 12:57 am
Thanks Jeff, and I've added a note about ranges.
September 23rd, 2009 at 3:53 pm
This is one of those great tips you always forget to use. I have known about this for some time, but I can never get in the habit of using it. Damned!
September 23rd, 2009 at 10:58 pm
All that blog reading finally paying off! This is something I actually do know and even practice sometimes.
September 25th, 2009 at 12:02 am
[...] Create Names From Excel Labels [...]
December 16th, 2009 at 12:02 am
[...] the Scenario Summary shown above, the changing cells are shown as addresses. If you name the value cells, the Scenario Summary will show those names, instead of the cell [...]