Search Contextures Sites

Contextures Excel news by email Email: Name (optional): We respect your email privacy	Excel Names -- Named Ranges Video: Name a Range of Cells Name a Range -- Name Box  Video: Create Names from Worksheet Labels Create Names from Worksheet Labels Use Names   Video: Create a Dynamic Named Range Create a Dynamic Named Range More Names Tutorials You can create Excel names that refer to cells, a range of cells, a constant value, or a formula. After you define the names, you can use those names in formulas, to replace values or cell references. If Excel names refer to cells or a range of cells, you can use the names for navigation, to quickly select the named range. Video: Name a Range of Cells In Excel, you can create names that refer to a single cell, a group of cells on the worksheet, a specific value, or a formula. After you define Excel names, you can use the names in a formula, instead of using a constant value or cell references. For example, a cell that contains the tax rate could be named SalesTax. Then, other cells could multiply sales amounts by the named range, SalesTax. For Excel names that refer to a cell or a range of cells, you can use the names to quickly select the named range, and that makes navigation easier. Just select a range name from a drop down list, and you'll immediately go to that range. The written instructions are below the video. Name a Range - Name Box You can create a named range quickly by typing in the Name Box. Select the cell(s) to be named Click in the Name box, to the left of the formula bar Type a one-word name for the list, e.g. FruitList. Press the Enter key. Video: Create Names from Worksheet Labels To quickly name individual cells, or individual ranges, you can use worksheet labels as the names. Watch this video to see the steps. Written instructions are below the video. Create Names from Worksheet Labels A quick way to create names is to base them on worksheet labels. In the example shown below, the cells in column C will be named, based on the labels in the adjacent cells, in column B. To name cells, or ranges, based on worksheet labels: Select the labels and the cells that are to be named. The labels can be above, below, left or right of the cells to be named. In this example, the labels are in column B, to the left of the cells that will be named. On the Ribbon, click the Formulas tab, then click Create from Selection. In the Create From Selection window, add a check mark for the location of the labels, then click OK. In this example, the labels are in the left column of the selected cells. Click on a cell to see its name. In the screen shot below, cell C4 is selected, and you can see its name in the Name Box -- Full_Name. NOTE: 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. Use Names After creating names that refer to a range, you can select a name in the Name Box dropdown list, to select the named range on the worksheet. You can also use names in formulas. For example, you could have a group of cells with sales amounts for the month of January. Name those cells JanSales, then use this formula to calculate the total amount: =SUM(JanSales) In the following example, the names TotalSales and TaxRate have been defined. =TotalSales * TaxRate To view the steps in a short Named Range video, click here. Video: Create a Dynamic Named Range When you create a named range in Excel, it doesn't automatically include new items. If you plan to add new items to a list, you can use a dynamic formula to define an Excel named range. Then, as new items are added to the list, the named range will automatically expand to include them. The written instructions are below the video. Create a Dynamic Named Range Another way to create names is to use a dynamic formula to define a named range. As new items are added, the range will automatically expand. Note: Dynamic named ranges will not appear in the Name Box dropdown list. However, you can type the names in the Name Box, to select the range on the worksheet. Choose Insert>Name>Define Type a name for the range, e.g. NameList In the Refers To box, enter an Offset formula that defines the range size, based on the number of items in the column, e.g.: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) In this example, the list is on Sheet1, starting in cell A1 The arguments used in this Offset function are: Reference cell: Sheet1!$A$1 Rows to offset: 0 Columns to offset: 0 Number of Rows: COUNTA(Sheet1!$A:$A) Number of Columns: 1 Note: for a dynamic number of columns, replace the 1 with:             COUNTA(Sheet1!$1:$1) Click OK Names Tutorials 1. Excel Names -- Naming Ranges   2. Excel Names -- Using Names in Formulas   3. Excel Names -- Create Dynamic Ranges With a Macro

Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact
Privacy Policy		Contextures Inc., Copyright ©2013 All rights reserved.	Debra Dalgleish

Last updated: April 18, 2013 3:01 PM