Which of the following can be associated with a cell range? select all that apply.
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. Show
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. 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:
Or, you can give meaningful names to the ranges and individual cells and supply those names to the formula:
Looking at the screenshot below, which of the two formulas are easier for you to understand? Excel name typesIn 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 make and use a table in Excel. How to create an Excel named rangeOverall, 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 BoxThe Name Box in Excel is fastest way to create a named range:
Voila, a new Excel named range is created! Create a name by using the Define Name optionAnother way to make a named range in Excel is this:
Note. 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 relative names 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 scope and adding a comment that explains something about the name. Additionally, Excel's Define Name feature allows you to create a name for a constant or formula. Make a named range by using Excel Name ManagerUsually, the Name Manager in Excel is used to work with existing names. However, it can help you build a new name too. Here's how:
Tip. 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 constantIn 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 Excel Define Name feature or Name Manager as explained above. For instance, you can make a name like USD_EUR (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 formulaIn 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):
Note. 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: =Items_count 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:
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:
Note. If there are any spaces between words in the header labels, the spaces will be replaced with underscores (_). Excel dynamic named rangeIn 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 dynamic named range 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 rulesWhen creating a name in Excel, there are a few rules to remember:
Excel name scopeIn terms of Excel names, scope is the location, or level, within which the name is recognized. It can be either:
Worksheet level namesA 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 be able to use a worksheet-level name in another worksheet, you must prefix the worksheet's name followed by the exclamation point (!), like this: Sheet1!items_list To reference a worksheet-level name in another workbook, you should also include the workbook name enclosed in square brackets: [Sales.xlsx]Sheet1!items_list If either the sheet name or workbook name contains spaces, they should be enclosed in single quotation marks: '[Sales 2017.xlsx]Sheet1'!items_list Workbook level namesA workbook-level name is recognized within the entire workbook, and you can refer to it simply by name from any sheet in the same workbook. A use to a workbook-level name in another workbook, precede the name with the workbook name (including the extension) followed by the exclamation point: Book1.xlsx!items_list Scope precedenceA defined name must be unique within its scope. You can use the same name in different scopes, but this might create a name conflict. To prevent this from happening, by default, the worksheet level takes precedence over the workbook level. If there are a few identically named ranges with different scopes, and you want to use the workbook level name, prefix the name with the workbook name as if you were referring to a name in another workbook, e.g.: Book1.xlsx!data. This way, the name conflict can be overridden for all worksheets except for the first sheet, which always uses the local worksheet level name. Excel Name Manager - quick way to edit, delete and filter namesAs its name suggests, the Excel Name Manager is specially designed to manage names: change, filter, or delete existing names as well as create new ones. There are two ways to get to the Name Manager in Excel:
Either way, the Name Manager dialog window will open, letting you see all names in the current workbook at a glance. Now, you can select the name you want to work with, and click one of the 3 buttons at the
top of the window to perform the corresponding action: edit, delete or filter. How to edit named range in ExcelTo change an existing Excel name, open the Name Manager, select the name, and click the Edit… button. This will open the Edit Name dialog box where you can change the name and reference. The scope of the name cannot be
changed. 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. Tip. An attempt to navigate through a long reference or formula in the Refers to field with the arrow keys will most likely result in a very frustrating behavior. To move within this field without disrupting the reference, press the F2 key to switch from Enter to Edit mode. How to filter names in ExcelIf 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:
How to delete named range in ExcelTo 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. 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 errorsIf you have a number of invalid names with reference errors, 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. Note. 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? errors. Top 5 benefits of using names in ExcelSo 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. 1. Excel names make formulas easier to make and readYou 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 formulasBy using dynamic named ranges, you can create a "dynamic" formula that automatically includes new data in calculations without you having to update every reference manually. 3. Excel names make formulas easier to re-useExcel 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. Tip. To prevent Excel form creating new names on the fly, copy the formula as text in the formula bar instead of copying the formula cell. 4. Named ranges simplify navigationTo quickly get to a specific named range, just click on its name in the Name box. If a named range resides on another sheet, Excel will take you to that sheet automatically. Note. Dynamic named ranges do not show up in the Name box in Excel. To see dynamic ranges, open the Excel Name Manager (Ctrl + F3) that shows full details about all names in the workbook, including their scope and references. 5. Named ranges allow creating dynamic drop-down listsTo build an expandable and updatable drop down list, make a dynamic named range first, and then create a data validation list based on that range. The detailed step-by-step instructions can be found here: How to create a dynamic dropdown in Excel. Excel named range - tips and tricksNow 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 workbookTo get a more tangible list of all names in a current workbook, do the following:
This will insert all Excel names along with their references in the current worksheet, beginning in the selected cell. Absolute Excel names vs. relative Excel namesBy default, Excel names behave like absolute references - 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 relative references - 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:
Now, let's see what happens when we use the item_left name in a formula, for example: =SUMIF(items_list, item_left, sales) Where items_list 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 item_left 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 formulasIf 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:
Additionally, two more options are available (selected by default):
Excel name shortcutsAs 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:
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 Excel Name Manager and check the validity of your defined names (the fastest way is to filter names with errors). 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 inWhich of the following is an example of cell range?When referring to a spreadsheet, the range or cell range is a group of cells within a row or column. For example, in the formula =sum(A1:A10), the cells in column A1 through A10 are the range of cells that are added together.
Which of the following functions is used to add a range of cells?Sum a range of cells -- SUM Function
The quickest and easiest way to sum a range of cells is to use the Excel AutoSum button. It automatically enters an Excel SUM function in the selected cell. The SUM function totals one or more numbers in a range of cells.
What is used to specify range of cell?Then, hold down CTRL while you click the names of other cells or ranges in the Name box. To select an unnamed cell reference or range, type the cell reference of the cell or range of cells that you want to select, and then press ENTER. For example, type B3 to select that cell, or type B1:B3 to select a range of cells.
Which of the following steps will you take to name a range of cells in an MS Excel sheet?Select Formulas > Create from Selection. In the Create Names from Selection dialog box, designate the location that contains the labels by selecting the Top row,Left column, Bottom row, or Right column check box. Select OK. Excel names the cells based on the labels in the range you designated.
|