The tutorial explains what an Excel name is and shows how to define a name for a cell, range, constant or formula. You will also learn how to edit, filter and delete defined names in Excel. Names in Excel are a paradoxical thing: being one of the most useful features, they are often considered pointless or nerdy. The reason is that very few users understand the essence of Excel names. This tutorial will not only teach you how to create a named range in Excel, but will also show how to leverage this feature to make your formulas much easier to write, read, and re-use.
There's also a way in Excel to get a list of all defined names in a workbook directly in a worksheet. First select an empty cell on the worksheet. Next, click the 'Use in Formula' menu, and select Paste Names. When the Paste Names dialog appears, click the Paste List button. Excel will generate a full list of all names and references. Using Tabs in Excel like Firefox, Chrome, Internet Explore 10! Usually we can edit defined names with Name Manager command. Of course we can view all defined names within the Named Manager dialog box. After clicking the Name Manger button under Formulas tab, it displays the Name Manger dialog box.
What does name mean in Excel? In everyday life names are widely used to refer to people, objects and geographical locations. For example, instead of saying 'the city lying at latitude 40.7128° N and longitude 74.0059° W, you simply say 'New York City'. Similarly, in Microsoft Excel, you can give a human-readable name to a single cell or a range of cells, and refer to those cells by name rather than by reference. For instance, to find the total of sales (B2:B10) for a specific item (E1), you can use the following formula: =SUMIF($A$2:$A$10, $E$1, $B$2:$B$10) Or, you can give meaningful names to the ranges and individual cells and supply those names to the formula: =SUMIF(itemslist, item, sales) Looking at the screenshot below, which of the two formulas are easier for you to understand?
Excel name types In Microsoft Excel, you can create and use two types of names: Defined name - a name that refers to a single cell, range of cells, constant value, or formula. For example, when you define a name for a range of cells, it's called a named range, or defined range. These names are subject of today's tutorial.
Table name - a name of an Excel table that is created automatically when you insert a table in a worksheet (Ctrl + T). For more information about Excel tables, please see. How to create an Excel named range Overall, there are 3 ways to define a name in Excel: Name Box, Define Name button, and Excel Name Manager. Type a name in the Name Box The Name Box in Excel is fastest way to create a named range:. Select a cell or a range of cells that you want to name.
Type a name into the Name Box. Press the Enter key.
Voila, a new Excel named range is created! Create a name by using the Define Name option Another way to make a named range in Excel is this:. Select the cell(s). On the Formulas tab, in the Define Names group, click the Define Name button. In the New Name dialog box, specify three things:. In the Name box, type the range name. In the Scope dropdown, set the ( Workbook by default).
In the Refers to box, check the reference and correct it if needed. Click OK to save the changes and close the dialog box.
By default, Excel creates a name with absolute references. If you'd rather have a relative named range, remove the $ sign from the reference (before you do this, make sure you fully understand how behave in worksheets). Compared to the previous method, using Define Name in Excel takes a few extra clicks, but it also provides a couple more options such as setting the name's and adding a comment that explains something about the name.
Additionally, Excel's Define Name feature allows you to create a name for a. Make a named range by using Excel Name Manager Usually, the in Excel is used to work with existing names. However, it can help you build a new name too. Here's how:. Go to the Formulas tab Defined Names group, and click the Name Manager Or, just press Ctrl + F3 (my preferred way).
In the top left hand corner of the Name Manager dialog window, click the New button:. This will open the New Name dialog box where you configure a name as demonstrated in the previous section.
To quickly test the newly created name, select it in the Name Box dropdown list. As soon as you release the mouse, the range on the worksheet will be selected.
How to create an Excel name for a constant In addition to named ranges, Microsoft Excel allows you to define a name without cell reference that will work as a named constant. To create such a name, use either the feature or as explained above. For instance, you can make a name like USDEUR (USD - EUR conversion rate) and assign a fixed value to it. For this, type the value preceded by an equal sign (=) in the Refers to field, e.g. =0.93: And now, you can use this name anywhere in your formulas to convert USD to EUR: As soon as the exchange rate changes, you update the value only in one central location, and all of your formulas will get recalculated in a single step! How to define a name for a formula In a similar manner, you can give a name to an Excel formula, for example, the one that returns the count of non-empty cells in column A, excluding the header row (-1): =COUNTA(Sheet5!$A:$A)-1. If your formula refers to any cells on the current sheet, you do not need to include the sheet name in the references, Excel will do it for you automatically.
If you are referencing a cell or range on another worksheet, add the sheet's name followed by the exclamation point before the cell/range reference (like in the formula example above). Now, whenever you want to know how many items there are in column A on Sheet5, not including the column header, just type the equality sign followed by the name of your formula in any cell, like this: =Itemscount How to name columns in Excel (names from selection) If your data is arranged in a tabular form, you can quickly create names for each column and/or row based on their labels:.
Select the entire table including the column and row headers. Go to the Formulas tab Define Names group, and click the Create from Selection button. Or, press the keyboard shortcut Ctrl + Shift + F3. Either way, the Create Names from Selection dialogue box will open. You select the column or row with headers, or both, and click OK. In this example, we have headers in the top row and left column, so we select these two options: As the result, Excel will create 7 named ranges, picking up names from the headers automatically:. Apples, Bananas, Lemons and Oranges for rows, and.
Jan, Feb and Mar for columns. If there are any spaces between words in the header labels, the spaces will be replaced with underscores. Excel dynamic named range In all previous examples, we have been dealing with static named ranges that always refer to the same cells, meaning you would have to update the range reference manually whenever you want to add new data to the named range. If you are working with expandable data sets, it stands to reason to create a that accommodates newly added data automatically. The detailed guidance on how to create a dynamic named range in Excel can be found here:. Excel naming rules When creating a name in Excel, there are a few rules to remember:.
An Excel name should be under 255 characters long. Excel names cannot contain spaces and most punctuation characters. A name must begin with a letter, underscore , or backslash ( ).
If a name begins with anything else, Excel will throw an error. Excel names are case-insensitive. For example, 'Apples', 'apples' and 'APPLES' will be treated as the same name.
You cannot name ranges like cell references. That is, you can't give the name 'A1' or 'AA1' to a range. You can use a single letter to name a range like 'a', 'b', 'D', etc. Except for the letters 'r' 'R', 'c', and 'C' (these characters are used as shortcuts for selecting a row or column for the currently selected cell when you type them in the Name Box). Excel name scope In terms of Excel names, scope is the location, or level, within which the name is recognized. It can be either:.
Specific worksheet - the local worksheet level. Workbook - the global workbook level Worksheet level names A worksheet-level name is recognized within the worksheet where it is located. For example, if you create a named range and set its scope to Sheet1, it will be recognized only in Sheet1. To edit a name reference, you do not need to open the Edit Name dialog box. Just select the name of interest in the Excel Name Manager, and type a new reference directly in the Refers to box, or click the button at the right and select the desired range on the sheet. After you click the Close button, Excel will ask if you want to save the changes, and you click Yes.
How to filter names in Excel If you have a lot of names in a certain workbook, click the Filter button at the top right corner of the Excel Name Manager window to view only the names relevant at a given time. The following filters are available:. Names scoped to worksheet or workbook.
Names with or without errors. Defined names or table names How to delete named range in Excel To delete a named range, select it in the Name Manager and click the Delete button at the top. To delete several names, click the first name, then press the Ctrl key and hold it while clicking other names you want to remove.
![Manager Manager](/uploads/1/2/5/4/125424351/978338168.jpg)
Then click the Delete button, and all selected names will be deleted in one go. To delete all defined names in a workbook, select the first name in the list, press and hold the Shift key, and then click the last name. Release the Shift key and click Delete. How to delete defined names with errors If you have a number of invalid names with, click the Filter button Names with Errors to filter them: After that, select all filtered names as explained above (by using the Shift key), and click the Delete button. If any of your Excel names are used in formulas, be sure to update the formulas before deleting names, otherwise your formulas will return #NAME? Top 5 benefits of using names in Excel So far in this tutorial, we have been focusing mostly on how-to things that cover different aspects of creating and using named ranges in Excel. But you may be curious to know what is so special about Excel names that makes them worth the effort?
The top five advantages of using defined names in Excel follow below. Excel names make formulas easier to make and read You don't have to type complex references or go back and forth selecting ranges on the sheet. Just start typing the name you want to use in the formula, and Excel will show a list of matching names for you to choose from.
Double click the desired name, and Excel will insert it in the formula straight away: 2. Excel names allow creating expandable formulas By using, you can create a 'dynamic' formula that automatically includes new data in calculations without you having to update every reference manually. Excel names make formulas easier to re-use Excel names make it a lot easier to copy a formula to another sheet or port a formula into a different workbook.
All you have to do is create the same names in the destination workbook, copy/paste the formula as is, and you will get it working immediately. Dynamic named ranges do not show up in the Name box in Excel.
To see dynamic ranges, open the ( Ctrl + F3) that shows full details about all names in the workbook, including their scope and references. Named ranges allow creating dynamic drop-down lists To build an expandable and updatable drop down list, make a first, and then create a data validation list based on that range. The detailed step-by-step instructions can be found here:. Excel named range - tips and tricks Now that you know the basics of creating and using names in Excel, let me share a few more tips that may prove helpful in your work. How to get a list of all names in the workbook To get a more tangible list of all names in a current workbook, do the following:. Select the topmost cell of the range where you want the names to appear. Go to the Formulas tab Define Names group, click Use in Formulas, and then click Paste Names Or, simply press the F3 key.
In the Paste Names dialog box, click Paste List. This will insert all Excel names along with their references in the current worksheet, beginning in the selected cell.
![Name Manager In Excel Name Manager In Excel](/uploads/1/2/5/4/125424351/409799602.png)
Absolute Excel names vs. Relative Excel names By default, Excel names behave like - locked to specific cells. However, it is possible to make a named range relative to the position of the active cell at the time the name is defined.
Relative names behave like - get changed when the formula is moved or copied to another cell. In fact, I cannot think of any reason why one would want to make a relative named range, except maybe when a range consists of a single cell.
As an example, let's create a relative name that refers to a cell one column to the left of the current cell, in the same row:. Select cell B1. Press Ctrl + F3 to open Excel Name Manager, and click the New. In the Name box, type the desired name, say, itemleft. In the Refers to box, type =A1. Now, let's see what happens when we use the itemleft name in a formula, for example.
=SUMIF(itemslist, itemleft, sales) Where itemslist refers to $A$2:$A$10 and sales refers to $B$2:$B$10 in the table below. When you enter the formula in cell E2, and then copy it down the column, it will calculate the total sales for each product individually because itemleft is a relative name and its reference adjusts based on the relative position of the column and row where the formula is copied: How to apply Excel names to existing formulas If you've defined the ranges that are already used in your formulas, Excel won't change the references to the appropriate names automatically. Though, instead of replacing references with names by hand, you can have Excel do the work for you. Here's how:. Select one or more formula cells that you want to update.
Go to the Formulas tab Define Names group, and click Define Name Apply Names. In the Apply Names dialog box, click on the names you want applied, and then click OK. If Excel is able to match any of the existing names with the references used in your formulas, the names will be selected for you automatically: Additionally, two more options are available (selected by default):. Ignore Relative/Absolute - keep this box checked if you want Excel to apply only the names with the same reference type: replace relative references with relative names and absolute references with absolute names. Use row and column names - if selected, Excel will rename all cell references that can be identified as the intersection of a named row and named column. For more choices, click the Options Excel name shortcuts As is often the case in Excel, the most popular features can be accessed in several ways: via the ribbon, right-click menu, and keyboard shortcuts. Excel named ranges are no exception.
Here are three useful shortcuts to work with names in Excel:. Ctrl + F3 to open the.
Ctrl + Shift + F3 to create. F3 to get a in a workbook.
Excel name errors (#REF and #NAME) By default, Microsoft Excel does its best to keep your defined names consistent and valid by adjusting range references automatically when you insert or delete cells within an existing named range. For example, if you have created a named range for cells A1:A10, and then you insert a new row anywhere between rows 1 and 10, the range reference will change to A1:A11. Similarly, if you delete any cells between A1 and A10, your named range will contract accordingly. However, if you delete all cells that make up an Excel named range, the name becomes invalid and displays a #REF!
Error in the Name Manager. The same error will show up in a formula referencing that name: If a formula refers to a non-existing name (mistyped or deleted), the #NAME? Error will show up. In either case, open the and check the validity of your defined names (the fastest way is to ). This is how you create and use names in Excel. I thank you for reading and hope to see you on our blog next week! You may also be interested in:.
Hello I am looking to use VLOOKUP to return certain column data out of an array. Outside of a table this is what I have been using: =VLOOKUP($B2,Sheet2!($A$8:$H$48,Column(B8),FALSE) and then dragged the formula across and down the page. This is fine, as the lookup locks to B. However, when in a table with named ranges, I cannot drag the formula across as the initial reference keep changing as I drag across. =VLOOKUP(@sampleid,Book1Sheet1!$A$8:$H$48,COLUMN(B8),FALSE) The initial @sampleid needs to be locked, I assumed with $ but nowhere i put it allows the formula to function. Hi there, I would like to define a Name that references the name's calories. However, when I list the name without an =, (so that it just says, WholeWheatBread) I would like to reference its value in another column titled Calories.
Column A: Row 1: Food Row 2: WholeWheatBread Column B: Row 1: Calories Row 2: =A2 Assigned Value for WholeWheatBread is 78 However, when I sype =A2 it enters 'WholeWheatBread' in the cell; I would like it to enter the Value associated with WholeWheatBread (which is 78) This is so when I enter food items for a daily diet, I can just enter the titled food item and let the caloric section autofill with the selected values so I do not have to type ='adjusted new food' in the calories column. Hello, would it be possible to include in a formula a text from a cell that refers to a given 'name' that identify a column of values? I have the closing prices of a stock in column E that I named 'Close', I had the cell N1 with the text 'close', I would like to reference to cell N1 in the formula contained in the cell O8 (=min(Close)) as cell, so I would use, in O8 =min(N1).
Changing the text in N1 would report the value calculated for the same function applied to different set of values (columns). Thanks for the support you can give me.
Excel for Office 365 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Excel Starter 2010 Use the Name Manager dialog box to work with all the defined names and table names in a workbook. For example, you may want to find names with errors, confirm the value and reference of a name, view or edit descriptive comments, or determine the scope. You can also sort and filter the list of names, and easily add, change, or delete names from one location. To open the Name Manager dialog box, on the Formulas tab, in the Defined Names group, click Name Manager. The Name Manager dialog box displays the following information about each name in a list box: Column Name Description Name One of the following:.
A defined name, which is indicated by a defined name icon. A table name, which is indicated by a table name icon. Note: Names can be up to 255 characters in length. The scope automatically defaults to Workbook. To change the name’s scope, in the Scope drop-down list box, select the name of a worksheet. Optionally, in the Comment box, enter a descriptive comment up to 255 characters. In the Refers to box, do one of the following:.
Click Collapse Dialog (which temporarily shrinks the dialog box), select the cells on the worksheet, and then click Expand Dialog. To enter a constant, type = (equal sign) and then type the constant value. To enter a formula, type = and then type the formula. To finish and return to the worksheet, click OK. If you modify a defined name or table name, all uses of that name in the workbook are also changed. On the Formulas tab, in the Defined Names group, click Name Manager.
In the Name Manager dialog box, double-click the name you want to edit, or, click the name that you want to change, and then click Edit. In the Edit Name dialog box, in the Name box, type the new name for the reference. In the Refers to box, change the reference, and then click OK. In the Name Manager dialog box, in the Refers to box, change the cell, formula, or constant represented by the name. On the Formulas tab, in the Defined Names group, click Name Manager. In the Name Manager dialog box, click the name that you want to change. Select one or more names by doing one of the following:.
To select a name, click it. To select more than one name in a contiguous group, click and drag the names, or press SHIFT and click the mouse button for each name in the group. To select more than one name in a noncontiguous group, press CTRL and click the mouse button for each name in the group. Click Delete. Click OK to confirm the deletion. Use the commands in the Filter drop-down list to quickly display a subset of names.
Selecting each command toggles the filter operation on or off, making it easy to combine or remove different filter operations to get the results you want. You can filter from the following options: Select To Names Scoped To Worksheet Display only those names that are local to a worksheet. Names Scoped To Workbook Display only those names that are global to a workbook. Names With Errors Display only those names with values containing errors (such as #REF, #VALUE, or #NAME). Names Without Errors Display only those names with values that do not contain errors. Defined Names Display only names defined by you or by Excel, such as a print area. Table Names Display only table names.