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.
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(items_list, 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 make and use a table in Excel.
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 name scope (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.
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 Manager
Usually, 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:
- Go to the Formulas tab > Defined Names group, click 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.
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 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 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 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
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:
- 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.
Note. 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 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 OFFSET formula to create a dynamic range
- INDEX formula to create a dynamic range
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 NameBox).
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 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 names
A 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 precedence
A 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 names
As 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:
- On the Formulas tab, in the Define Names group, click the Name Manager
- Press the Ctrl + F3 shortcut.
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 Excel
To 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 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. 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 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 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.
1. 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 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-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.
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 navigation
To 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 lists
To 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 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.
Absolute Excel names vs. relative Excel names
By 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:
- Select cell B1.
- Press Ctrl + F3 to open Excel Name Manager, and click the New…
- In the Name box, type the desired name, say, item_left.
- In the Refers to box, type
=A1. - Click OK.
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 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 Excel Name Manager.
- Ctrl + Shift + F3 to create named ranges from selection.
- F3 to get a list of all Excel names 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 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 in
- How to create and use dynamic named range in Excel
- Microsoft Excel formulas with examples
- Excel data entry form - how to make and use
FAQs
How do you apply defined names to formulas in a range in Excel? ›
- Select a cell and enter a formula.
- Place the cursor where you want to use the name in that formula.
- Type the first letter of the name, and select the name from the list that appears. Or, select Formulas > Use in Formula and select the name you want to use.
- Press Enter.
In Excel, you can give spreadsheet cells names in place of their reference numbers to make formulas easier to understand. Named cells are called named ranges, and you can easily change and manage them in your workbook. If you know how to use named ranges in Excel, you can read your formulas faster and easier.
How to apply the range names to the existing formulas and functions in the worksheet? ›- Define the named range you want used in your worksheet.
- Select the cells that contain formulas.
- Display the Formulas tab of the ribbon.
- Click the down-arrow at the right of the Define Name tool (in the Defined Names group) and then choose Apply Names. ...
- Click OK.
- On the Ribbon, click the Formulas tab.
- Click Name Manager.
- In the list, click on the name that you want to change.
- In the Refers To box, change the range reference, or drag on the worksheet, to select the new range.
- Click the check mark, to save the change.
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.
How do you apply a formula to a cell range? ›Select the cell with the formula and the adjacent cells you want to fill. Click Home > Fill, and choose either Down, Right, Up, or Left. Keyboard shortcut: You can also press Ctrl+D to fill the formula down in a column, or Ctrl+R to fill the formula to the right in a row.
Which formula can you use to determine how many cells in a range have values? ›Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers. For example, you can enter the following formula to count the numbers in the range A1:A20: =COUNT(A1:A20).
Why might you define and use a named range in a formula? ›A named range is one or more cells that have been given a name. Using named ranges can make formulas easier to read and understand. They also provide simple navigation via the Name Box.
How do you replace existing cell references with defined names? ›To replace the references with defined names in all formulas on the active sheet, select any single blank cell. Go to the Formulas tab > Defined Names group, click the arrow next to Define Name, and then click Apply Names… In the Apply Names dialog box, select one or more names, and click OK.
What happens when autofill is used to copy a formula that uses range names? ›With Auto Fill, dragging the fill handle on a cell copies the cell's contents or continues a series of data into adjacent cells.
How to apply formula in Excel without changing cell references? ›
Press F2 (or double-click the cell) to enter the editing mode. Select the formula in the cell using the mouse, and press Ctrl + C to copy it. Select the destination cell, and press Ctl+V. This will paste the formula exactly, without changing the cell references, because the formula was copied as text.
How do you reference a cell in a named range? ›The easiest is using the reference window while working on an Excel worksheet. In the upper left portion of the Excel environment is a small box which contains the cell name of the selected cell. A1, C10, etc. Click inside this box and type in a name then hit enter.
Are range names used in formulas as absolute references? ›By default, named ranges behave like absolute references and don't change when you copy formulas. In addition, they make your formulas easier to read.
How do I manage named ranges in Excel? ›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.
What happens to a formula when a range name is used in a formula and the name is deleted? ›Note: When you delete a named range, any formulas that reference it will no longer work. Protected ranges that reference a named range will use the cell values and continue to work.
What do all formulas begin with to allow Excel to distinguish formulas from data? ›Create a formula that refers to values in other cells
Note: Formulas in Excel always begin with the equal sign. Select a cell or type its address in the selected cell. Enter an operator. For example, – for subtraction.
Dynamic Formulas begin with &== and are followed by an Excel formula. Repeating Dynamic Formulas begin with &=&= and are followed by an Excel formula. You may use most of Excel's functions in a Dynamic Formula.
What is a dynamic named range in Excel? ›What do You Mean by Excel Dynamic Named Range? Dynamic named range in Excel is the ranges that change as the data in the range changes, and the dashboard or charts or reports associated with them. So that is why it is called dynamic. So we can name the range from the name box, so the name is a dynamic name range.
How do you master a dynamic range? ›- Consider the genre and listener. Before pushing faders and slapping compressors on every track, make note of the style of music you're mixing and where it will be listened to. ...
- Don't forget proper gain staging. ...
- Listen for too many dynamics. ...
- Listen for over-compression. ...
- Use a meter to monitor dynamic range.
Use the keyboard shortcut to apply the formula to the entire column, including any new rows you insert: Ctrl + d (Windows) or Cmd + d (Mac). The formula will be copied to the entire column and applied to new rows as soon as they are added.
What is the formula for calculating range? ›
To calculate the range, you need to find the largest observed value of a variable (the maximum) and subtract the smallest observed value (the minimum).
How do you check if a cell value is in a range in Excel formula? ›In Excel, to check if a value exists in a range or not, you can use the COUNTIF function, with the IF function. With COUNTIF you can check for the value and with IF, you can return a result value to show to the user. i.e., Yes or No, Found or Not Found.
What does VLOOKUP do? ›VLOOKUP stands for "Vertical Lookup" and is used to search for a specific value in the first column of a dataset and retrieve a corresponding value from a different column within the same row. It takes four arguments: lookup_value, table_array, col_index_num, and [range_lookup].
How do I know if a named range is used? ›You can find a named range by using the Go To feature—which navigates to any named range throughout the entire workbook. You can find a named range by going to the Home tab, clicking Find & Select, and then Go To. Or, press Ctrl+G on your keyboard.
What is an advantage to defining range names? ›advantages of the name range. - name ranges are absolute references (don't need to use $ to make them absolute they are automatic) - a range name in one sheet in a workbook can be used in another worksheet in the same workbook without having to specify the name of the worksheet where the range name resides.
Which of the following commands would you use to insert a named range in a formula? ›Make a named range by using Excel Name Manager
Go to the Formulas tab > Defined Names group, click Name Manager. Or, just press Ctrl + F3 (my preferred way).
- Click the cell in which you want to enter the formula.
- In the formula bar. , type = (equal sign).
- Do one of the following, select the cell that contains the value you want or type its cell reference. ...
- Press Enter.
If you do not want cell references to change when you copy a formula, then make those cell references absolute cell references. Place a "$" before the column letter if you want that to always stay the same. Place a "$" before a row number if you want that to always stay the same.
How to use AutoFill feature to automatically apply the formula to other cells? ›Click and hold the left mouse button, and drag the plus sign over the cells you want to fill. And the series is filled in for you automatically using the AutoFill feature. Or, say you have information in Excel that isn't formatted the way you need it to be, such as this list of names.
Why is Excel not recognizing AutoFill pattern? ›The first thing to check when AutoFill isn't working is your settings. Make sure that the AutoFill option is turned on in Excel. To do this, go to the File menu, click on Options, and then select Advanced. Scroll down to the Editing Options section and make sure that the Enable AutoFill option is checked.
Can you copy a formula using AutoFill? ›
You can quickly copy formulas into adjacent cells by using the fill handle. When you drag the handle to other cells, Excel will show different results for each cell.
How do I add columns in Excel without messing up Formulas? ›- Select Cell A1.
- On Formulas go to Define Name.
- In the dialog put in the Name box the name that you want to use. For example: my_formula.
- In the Refers to box, write: =B1+D1+F1-C1-E1-G1 and click OK.
- On A1 write =my_formula , check if the result is correct and then copy to A2, A3, A4..... A100.
- Create a table. ...
- Insert a new column into the table. ...
- Type the formula that you want to use, and press Enter. ...
- When you press Enter, the formula is automatically filled into all cells of the column — above as well as below the cell where you entered the formula.
To turn off automatic recalculation and recalculate open workbooks only when you explicitly do so (by pressing F9), in the Calculation options section, under Workbook Calculation, click Manual. Note: When you click Manual, Excel automatically selects the Recalculate workbook before saving check box.
How do you create a named range referenced in the formulas? ›- Select the range for which you want to create a Named Range in Excel.
- Go to Formulas –> Define Name.
- In the New Name dialogue box, type the Name you wish to assign to the selected data range. ...
- Click OK.
You can use a named range instead of a cell address in a formula. Answer: The statement is true. MS Excel allows us to assign names to cells in a worksheet and use it for quickly locating specific cells by entering the names. This could be useful when working with large spreadsheets.
What is the difference between a formula and a function in Excel? ›Formulas are defined as a statement written by any user, either simple or complex whereas a function is a type of formula which is pre-defined. On the other hand, functions are predefined formulas that are already there in the sheet.
Can VLOOKUP use a named range? ›Things to Remember Here. The named ranges are useful when regularly applying the VLOOKUP formula. Also, it is very helpful if we need to go to different worksheets to select the table array.
What is the difference between relative and absolute reference in Excel? ›There are two types of cell references: relative and absolute. Relative and absolute references behave differently when copied and filled to other cells. Relative references change when a formula is copied to another cell. Absolute references, on the other hand, remain constant no matter where they are copied.
What is the use of named range in Excel? ›Excel Named Ranges makes it easy to refer to data sets in Excel. You can create a named range in Excel for each data category, and then use that name instead of the cell references. For example, dates can be named 'Date', Sales Rep data can be named 'SalesRep' and sales data can be named 'Sales'.
What is the use of naming the cells and the cell range? ›
Named ranges can also be used in formulas instead of typing or pointing to specific cells. When you name a cell or range, Excel uses an absolute reference for the name by default, which is almost always what you want. You can see the absolute reference in the Refers to box in the New Name dialog box.
What is the cell or range of cells that we use in calculations called? ›Instead of typing each individual cell in a formula, you can reference multiple adjacent cells, called a range of cells. This range of cells is referred to as F2:G5.
Where is named ranges in Excel? ›You can find a named range by using the Go To feature—which navigates to any named range throughout the entire workbook. You can find a named range by going to the Home tab, clicking Find & Select, and then Go To. Or, press Ctrl+G on your keyboard.
What is difference between function and formula in Excel? ›Formulas are defined as a statement written by any user, either simple or complex whereas a function is a type of formula which is pre-defined. On the other hand, functions are predefined formulas that are already there in the sheet.
How do you define names in Excel? ›- Select the cell, range of cells, or nonadjacent selections that you want to name.
- Click the Name box at the left end of the formula bar. Name box.
- Type the name you want to use to refer to your selection. Names can be up to 255 characters in length.
- Press ENTER.
Perhaps what is most notable about named ranges is that they allow you to construct formulas before adding the data. When you are designing your worksheet, you can create formulas using names instead of traditional cell references, and then define the names for the corresponding ranges as data becomes available.
What is the significance of naming a range? ›Range names are names used to refer to cell references, formula results, or values. They are often used to avoid hard-coded values appearing in formulas and to make formulas clearer in general.
How do you reference a named range? ›Click on the "Formulas" tab on the Excel Ribbon at the top of the window. Click "Define Name" button in the Formula tab. In the "New Name" dialogue box, under the field "Scope" choose the specific worksheet that the range you want to define is located (i.e. "Sheet1")- This makes the name specific to this worksheet.
Which formula is a simple formula? ›Simple formulas always start with an equal sign (=), followed by constants that are numeric values and calculation operators such as plus (+), minus (-), asterisk(*), or forward slash (/) signs. Let's take an example of a simple formula. On the worksheet, click the cell in which you want to enter the formula.
What selects the range of cells that will be used in an Excel calculation? ›To select a range, select a cell, then with the left mouse button pressed, drag over the other cells. Or use the Shift + arrow keys to select the range. To select non-adjacent cells and cell ranges, hold Ctrl and select the cells.
What part of a formula identifies a cell or a range of cells on a worksheet and tells Excel where to look for the value or data you want to use in the formula? ›
A reference identifies a cell or a range of cells on a worksheet, and tells Excel where to look for the values or data you want to use in a formula.
What are the rules for naming tables in Excel? ›Rules for Naming Tables in Excel
The name must begin with a letter, underscore, or backslash. The remaining characters can be letters, numbers, underscores, or periods. You can't use the names “C”, “c”, “R”, or “r” as these are designated shortcuts in Excel.