Excel Functions Tutorials
- Excel Tips
- Excel vs Access
- Excel Rows vs Columns
- Apple Numbers vs Excel
- 3D Reference in Excel
- Absolute Reference in Excel
- Mixed References in Excel
- Excel Reference to Another Sheet
- Array Formulas in Excel
- Arrays in Excel VBA
- Auto Numbering in Excel
- AutoFit in Excel
- AutoCorrect in Excel
- AutoSave in Excel
- AutoRecover in Excel
- Bullet Points in Excel
- Break Links in Excel
- Barcode in Excel
- Change Case in Excel
- CAGR Formula in Excel
- Calculate Age in Excel
- Calculate Percentage in Excel Formula
- Cell Reference in Excel
- Checklist in Excel
- Circular Reference in Excel
- Column Sort in Excel
- Column Lock in Excel
- Move Columns in Excel
- Custom List in Excel
- Consolidate in Excel
- Combine Cells in Excel
- Compare Two Columns in Excel
- Compare and Match Columns in Excel
- Compound Interest Formula in Excel
- Convert Columns to Rows in Excel
- Convert Date to Text in Excel
- Convert Numbers to Text in Excel
- Convert Text to Numbers in Excel
- Convert Excel to CSV
- Count Characters in Excel
- Count Rows in Excel
- Count Unique Values in Excel
- Countif not Blank in Excel
- Create Templates in Excel
- Family Tree in Excel Template
- Custom Number Format in Excel
- Delete Row Shortcut in Excel
- Divide in Excel Formula
- Drop Down List in Excel
- Dynamic Tables in Excel
- Dashboard in Excel
- KPI Dashboard in Excel
- Date to Text in Excel
- Date Format in Excel
- Database in Excel
- Delta Symbol in Excel
- $ Symbol in Excel
- Excel Column to Number
- Edit Drop-Down List in Excel
- Equations in Excel
- Exponents in Excel
- Excel Extensions
- Excel Translate
- Excel Not Responding
- Excel Find and Replace
- Find and Select in Excel
- Excel Subtraction Formula
- Excel Formula for Grade
- Excel as Calculator
- Excel Formula Not Working (Updating)
- Excel Table Styles & Formats
- Excel vs Google Sheets
- External Links in Excel
- Excel Alternate Row Color
- Excel Worksheet Tab
- Extract Number from String Excel
- Evaluate Formula in Excel
- Find Duplicates in Excel
- Finding Links in Excel
- Filter Shortcut in Excel
- Formatting in Excel
- Format Numbers to Millions & Thousands in Excel
- Format Phone Numbers in Excel
- Formula Errors in Excel
- Fractions in Excel
- Frequency Distribution in Excel
- Group in Excel
- Group Worksheets in Excel
- Group Columns in Excel
- Hide Formula in Excel
- Hiding a Column in Excel
- Highlight Every Other Row in Excel
- Highlight Duplicates in Excel
- How to Create a Formula in Excel?
- How to Create an Excel Spreadsheet?
- How to Add Text in Excel Formula?
- How to Create Dashboard in Excel?
- How to Copy Sheet in Excel?
- How to Delete Pivot Table?
- How to Calculate Percentage Increase in Excel?
- How to Multiply in Excel Formula?
- How to Unhide Columns in Excel?
- Insert Date in Excel
- Insert Calendar in Excel
- Import Data into Excel
- Insert Comment in Excel
- Insert Hyperlinks in Excel
- Insert Multiple Rows in Excel
- Insert Row Shortcut in Excel
- Insert New Worksheet in Excel
- Insert (Embed) an Object in Excel
- Insert Image in Excel Cell
- Insert Page Break in Excel
- Line Breaks in Excel
- Linear Interpolation in Excel
- Leading Zeros in Excel
- Last Day of the Month in Excel
- Logical Operators in Excel
- Lookup Table in Excel
- Mortgage Calculator in Excel
- Moving Average in Excel
- Not Equal to in Excel
- Numbering in Excel
- Name Manager in Excel
- Page Numbers in Excel
- Page Break in Excel
- Personal Budget Template in Excel
- Project Management Template in Excel
- Percentage Difference in Excel (Increase / Decrease)
- Pivot Table Calculated Field & Formula
- Pivot Table Sort
- Pivot Table From Multiple Sheets
- Print Comments in Excel
- Print Excel Gridlines
- Print in Excel
- Print Preview in Excel
- Print Area in Excel
- Print Titles in Excel
- Print Labels From Excel
- Project Timeline in Excel
- Protect Sheet in Excel
- Ratio in Excel Formula
- Random Numbers in Excel
- Randomize List in Excel
- Refresh Pivot Table in Excel
- Relative References in Excel
- Remove Blank Rows in Excel
- Remove Duplicates in Excel
- Remove Duplicates from Excel Column
- Remove Hyperlinks in Excel
- Remove Space in Excel
- Remove Leading Spaces in Excel
- Remove Watermark in Excel
- Row Limit in Excel
- Rows and Columns in Excel
- Rows to Columns in Excel
- Row Header in Excel
- Search in Excel
- Search For Text in Excel
- Share an Excel Workbook
- Shortcut to Merge Cells in Excel
- Show Formula in Excel
- Split Cells in Excel
- Separate Text in Excel
- Strikethrough in Excel
- Strikethrough Text in Excel
- Sum by Color in Excel
- Subscript in Excel
- Superscript in Excel
- Themes in Excel
- Timesheet Calculator in Excel
- Top 20 Keyboard Shortcuts in Excel
- Unmerge Cells in Excel
- Uppercase in Excel
- Word Count in Excel
- Word Cloud in Excel
- Watermark in Excel
- Weighted Average in Excel
- Wildcard in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- VBA (162+)
Name manager in excel are used to create a named ranges or edit it or delete, while working with formulas in excel we sometimes used names instead of giving cell references, if we want to add a new reference or edit any reference or delete it we can do it by the name manager, this is available in the Formulas tab under the defined names section.
Name Manager in Excel – Table of Contents
Excel Name Manager
Excel Name Manager can be found in the “Formulas” tab.
Tip: “Ctrl + F3” is the shortcut used to access it.
Usually, it is used to work with existing names. However, it also allows you to create a new name too.
How to Use Name Manager in Excel?
Please follow the below steps for using the Excel Name Manager.
Go to Formulas tab > Defined Names group, then click the Name Manager. Alternatively, we can just press Ctrl + F3 (the shortcut for Name Manager)
For a new named range, click on the “New” button.
On clicking the “New” button, you will see the below window.
Type in the name that you want to give to your range, as well as the cells it will refer to in the “Refers to” section.
Examples of Name Manager in Excel
Name Manager can be used to create, edit, delete and filtering excel names. Below, we will see one example of each, along with their explanations.
Example #1 – Creating, Editing & Deleting Named Range in Excel
Let’s suppose we want to refer to the cells in the range B2: E2, by the name “near”. To do that follow the below steps.
- Go to Formulas tab > Defined Names group, then click the Name Manager. Alternatively, we can just press Ctrl + F3 (the excel shortcut for Name Manager)
- For a new named range, click on the “New” button.
- Then in Name write “Near” and in Refer to select B2: E2 and click ok.
- After this, you can see the name “Near” created when you click on the “Excel Name Manager”.
- You can see the other options like edit & delete. Let’s suppose, you want to edit the cell reference. Then just select the relevant named range (here “near”), click on “Edit” and change the configuration.
- Similarly, for deleting, select the relevant named range & click on “Delete”.
In case you want to delete multiple named ranges at once, all you need is to select the relevant ones by pressing the “Ctrl” button. All the relevant ones will be selected and then you just need to click on “Delete”. To delete all the names, select the first one, press Shift button and then click on the last “named range”. In this way, all will be selected, then just click “Delete”.
Example #2 – Create an Excel Name for a Constant
Not only named ranges, but excel also allows us to define a name without any cell reference. This can be used to create a named constant.
Suppose you want to use a conversion factor in your calculations. Instead of referring every time to that value, we can assign that value to a name, and use that name in our formulas.
For example: 1 km = 0.621371 mile
- Let’s first create the named range which will then be used in a formula. In Formula tab click on Name Manager.
- Once you click on Name Manager, a window will open in that click on New.
- In “Name Box” write “Km_In_Miles” and in “Refer to Box” specify the value as 0.621371,
- After this, you can see the name “Km_In_Miles” created when you click on the “Excel Name Manager”.
- Whenever you want to use a name in a formula, type and you will see it in the list of suggestions to select.
- So the answer will be,
- Then drag the plus sign to get the answer for all.
Example #3 – Defining Name for a formula
Similar to the above, we can give a name to an excel formula.
Let’s suppose, column A contains the names of people who participated in the run, and I want to know the number of people who participated. Let’s just create a named formula for it.
- Create a named Range “Run_Number” following the above-given steps. So, in the New Name window write the following Attributes and click Ok.
- Then use this named range as follows, it will give you the correct number of participants.
Note: If the cells being referred to are in the current sheet, then we don’t need to mention the Sheet number in the excel formula. However, add the sheet’s name followed by the exclamation point before the cell/range reference, if you are referring to cells on a different worksheet.
Rules for Name Manager in Excel
- Under 255 characters
- Cannot contain spaces and most punctuation characters
- Must begin with a letter, underscore (“_”) or backslash (“\”)
- Can’t have names like cell references. For example, B1 is not a valid name
- Names are case-insensitive
- A single letter name can be used to name a range. However, they can’t be “c”, “C”, “r” or “R”
Worksheet level takes precedence over workbook level.
The scope of excel name can be either at worksheet level or at workbook level.
Worksheet level name is recognized only within that worksheet. In order to use it in another worksheet, we will need to prefix the worksheet name followed by an exclamation to the named range.
Workbook level name is recognized in any of the worksheets inside a workbook. To use another workbook’s name range in another workbook, we need to prefix the workbook name followed by an exclamation mark to the named range.
Example #4 – Filters in Excel Name Manager
Excel name Manager also has the filter functionality to filter out the relevant named ranges. Please see the screenshot below.
Here, you can see the relevant criteria for filtering the relevant named ranges. Select the one you want to restrict to and then do whatever you want.
Things to Remember
- Open Excel Manager: “Ctrl + F3”
- To get a list of all excel named ranges, use F3
- Named Ranges are case-insensitive
This has been a guide to Name Manager in Excel. Here we discuss how to Create, Use and Manage Names in Excel along with practical examples and downloadable excel templates. You may also look at these useful functions in excel –