Create Excel names that refer to cells, a constant value, or a formula. Use names in formulas, or quickly select a named range. Have fun with names too, and add strange characters, like a happy face! Show
Excel Names - IntroductionIn Microsoft Excel, you can create names that refer to:
After you define Excel names, you can:
The instructions below show how to create names and use names in your Excel files. Get the sample Excel workbook, to follow along with the instructions. NOTE: To create a quick list of all the names in a workbook, see the Quick List of Names - No Macro instructions. How to Name CellsWatch this short video to see how to name a group of cells. Then, go to that named group of cells, or use the name in a formula. The written instructions are below the video. TOP Name Cells - Name BoxYou quickly name the selected cells by typing in the Name Box. NOTE: There are a few rules for Excel names, shown in the section below.
Rules for Creating NamesThere are rules for Excel names on the Microsoft site, and I have briefly summarized those rules below. However, despite these strict rules, some unusual characters are allowed in Excel names. There ahe examples of what is allowed, in the Strange Characters in Excel Names section, further donw on this page..
See Names on WorksheetThe best way to see all the names that you've created is by using the Name Manager. The steps for that are in the next section, below. However, there are 2 ways that you can also see the names on the worksheet: --- 1) Create a List of Names --- 2) See Named Ranges by Zooming Create List of Names on WorksheetYou can create a list of names on a worksheet, with a few easy steps. This is a quick way to double-check the names in the Excel file, and to see their Refers To formulas To create the list, follow these steps:
A 2-column list of names will be inserted, starting in the selected cell, so make sure you have room for your list What’s in the List?The list of names will contain all the workbook level names, unless there’s a duplicate sheet level name on the sheet where the name list is pasted. In that case, the sheet level name appears in the list, instead of the workbook level name. See Named Ranges by ZoomingTo see some of the named ranges on a worksheet, use this quick trick:
The names of some ranges will appear on the worksheet, in blue text, like the MonthList in this screen shot.
See Names in Name ManagerTo see details on all the names in the entire workbook, use the built-in Excel Name Manager tool. To open the Name Manager, follow these steps
OR, open the Name Manager with the keyboard shortcut Ctrl + F3 Name Manager Dialog BoxThe Name Manager dialog box opens, showing a list of workbook level and worksheet level names.
Delete an Excel NameAfter you create a named range, you might need to delete that Excel name later. Sometimes, a name is no longer needed in a workbook. Follow these steps to delete a name in Excel:
Change a Named RangeAfter you create a named range, you might need to change the cells that it refers to. This short video shows the steps, and there are written steps below the video. Follow these steps to change the range reference:
Create Names from Cell TextTo quickly name individual cells, or individual ranges, you can use heading cell text as the names. Watch this video to see the steps. Written instructions are below the video. Create Names from Cell TextA quick way to create names is to base them on heading cell text (worksheet labels). In the example shown below, the cells in column E will be named, based on the labels in column D. Notes on Creating Names from Cell TextThe cell text might be altered slightly, when the name is created. For example:
For more information on invalid characters, see the section above - Rules for Excel Names Steps to Create Names from Cell TextTo name cells, or ranges, based on worksheet labels, follow these steps:
In the Create Names From Selection window, there is is heading, "Create Names From Values in the" Below that heading, there 4 check boxes:
To create the names:
One Location SelectedFor example, in the screen shot below:
Click on a cell to see its name.
Two Locations SelectedFor the next example, in the screen shot below, two locations are selected: When multiple locations are selected for the cell text, names are created for each selected location. In the example shown below, 4 names will be created:
All Four Locations SelectedFor the next example, in the screen shot below, all four of the locations are selected:
A name is created for all 8 of the label text cells:
Create Name for a ValueMost Excel names refer to ranges on the worksheet, but names can also be used to store a value. Frequently Used ValuesFor example, create a name to store a percentage amount that you use frequently, such as a retail tax rate:
Then, use that name in formulas, instead of typing in the value Special ValuesYou can also create names to store values that are difficult to enter. For example, some formulas use this strange-looking number. According to Excel specifications on the Microsoft site, that is the largest positive number that you can type into an Excel cell. Instead of typing that number into your formulas, you could define a name, using that value (copy the number from this page before you create the name):
Then, use the XL_Max name in formulas, like this LOOKUP formula that finds the last number in a column. =LOOKUP(9.99999999999999E+307, WeightData[Wt]) How to Use Excel NamesAfter creating names, you can use them: If a name refers to a range, you can select that name in the Name Box dropdown list, to select the named range on the worksheet. NOTE: If a name does not appear in the drop down list, you can type the name instead Use Names in FormulasYou can also use names in formulas. For example, you could have a group of cells with quantities sold. Name those cells Quantity, then use this formula to calculate the total amount: =SUM(Quantity) Name Box TricksIn addition to using the Name Box to create a named range, or to select a named range, here are a few other Name Box tricks. Resize the Name BoxIn old versions of Excel, the Name Box was a set width, and you couldn't change that. Here's how you can adjust the Name Box width in newer versions:
Select CellsAnother handy trick is that you can use the Name Box to select unnamed cells too. Here are a couple of ways that trick can be useful -- unhide columns, or fill a long range of cells. Unhide Columns Here's a quick way to unhide specific columns, and leave others hidden.
Fill Cells With Excel's AutoFill feature, you can create a list of dates, or numbers, or other sequences, very quickly. Just type one or two values as the starting sequence, select those cells, and double-click the Fill Handle to fill down to the last row of data. Sometimes though, there's no data in the adjacent column, so AutoFill won't work with a double-click. You could drag the Fill Handle down, but that's not very efficient if you need to create a long series. Here's how to create a list of 1000 numbers in column A:
Create a Dynamic Named RangeIf the list that you want to name will change frequently, having items added and removed, you should create a dynamic named range. A dynamic named range will automatically adjust in size, when the list changes. Here are two ways to create a dynamic named range: Use a Named Excel Table Use a Formula Use a Named Excel TableThe easiest way to create a dynamic named range is to start by creating a named Excel table. Then, define a range based on one or more columns in that table. In this example there is a list of parts on the worksheet, and a named table, and dynamic named ranges will be created. Later, if you add new items to the table, the named range will automatically expand. First, create the table:
Next, create a dynamic list of part IDs:
To see the name's definition, follow these steps:
Text the Dynamic RangeBecause the PartIDList named range is based on a named table, the list will automatically adjust in size if you add or remove part IDs in the list.
Dynamic Named Range - FormulaWhen 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. Dynamic Named Range Based on FormulaIf you don't want to use a named table, you can 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 that range on the worksheet.
Strange Characters Allowed in Excel NamesEven though Microsoft’s rules for Excel names say that you must use only letters, numbers, periods, underscores and backslashes, other characters are allowed. Based on testing, "letters" has a broad interpretation, and goes well beyond the basic letters of the alphabet. You can use a wide variety of Unicode characters too! For example, the screen shot below shows Excel names created by Peter B. He used Unicode text in his Excel names (shown below), to create arrows and subscript in Excel names, for a statistics workbook. More Fun with Excel NamesInspired by Peter’s examples, I did a few simple name tests, using characters created with the Alt key and Number keypad. For example:
In the screen shot below, you can see the list of unconventional names that I created. In-Depth Excel Name TestingFor an in-depth look at what characters are allowed in Excel names, see Martin Trummer's GitHub project excel-names. Martin has done an in-depth study of what’s allowed, beyond the basic letters and numbers. When you visit Martin's GitHub page, you'll find written examples, and an Excel file to download. Here is a screen shot from Martin's Excel file, with his test results. Get the Sample FileTo follow along with the instructions on this page, download the Excel Names Sample File. The zipped file is in xlsx format, and does not contain any macros. TOP How do I label text in Excel?On the Layout tab, in the Labels group, click Data Labels, and then click the option that you want. For additional data label options, click More Data Label Options, click Label Options if it's not selected, and then select the options that you want.
How do you set a cell name?On the Formulas tab, in the Defined Names group, click Define Name. In the New Name dialog box, in the Name box, type the name you want to use for your reference. Note: Names can be up to 255 characters in length. The scope automatically defaults to Workbook.
How do you add tags to a cell?Use the Tags button to insert a Tag into an empty cell, just like Word. If a cell already has a Tag, you can double-click on the cell, or select the cell and click on the Edit Tag button to bring up the Tag Editor on that Tag.
What is a label in a cell?The term label usually refers to text that you have typed in a cell. If the text appears next to a continuous. list of values, Excel sees the label as a 'tag' that represents that range of values. The label can then be. used in formulas instead of direct cell references.
|