Excel Functions Tutorials
- Excel Tools
- Excel Ribbons and Tabs
- Quick Access Toolbar in Excel (QAT)
- "Save As" Shortcut in Excel
- Accounting Number Format in Excel
- Add-Ins in excel
- Add Filter in Excel
- Advanced Filter in Excel
- Auto Filter In Excel
- Auto Format Excel
- AutoFill in Excel
- Analysis ToolPak in Excel
- ANOVA in Excel
- Border in Excel
- Checkbox in Excel
- Check Mark in Excel (? Tick Symbol)
- Combo Box in Excel and VBA
- Conditional Formatting in Excel
- Conditional Formatting with Formulas
- Conditional Formatting for Blank Cells
- Conditional Formatting Based on Another Cell Value
- Conditional Formatting in Pivot Table
- Consolidate Data in Excel
- Comma Style in Excel
- CSV vs Excel
- Data Bars in Excel
- Data Table in Excel
- Data Validation Excel
- Data Model in Excel
- Developer Tab in Excel
- Descriptive Statistics in Excel
- Dynamic Named Range in Excel
- Drawing in Excel
- Excel Fill Handle
- Excel Fill Down
- Error Bars in Excel
- Excel Forms for Data Entry
- Excel Tables
- Excel Power View
- Exponential Smoothing in Excel
- Filters in Excel
- Flash Fill in Excel
- Freeze Panes in Excel
- Freeze Columns in Excel
- Freeze Cells in Excel
- Format Painter in Excel
- Shortcut for Format Painter in Excel
- F-Test in Excel
- Goal Seek in Excel
- Gridlines in Excel
- Heat Map in Excel
- 3D Maps in Excel
- Header and Footer in Excel
- Insert Button in Excel
- Insert / Draw Line in Excel
- Insert Function in Excel
- List Box in Excel VBA
- Lock Cells in Excel
- Macros in Excel
- Enable Macros in Excel
- Merge and Center in Excel
- Merge Cells in Excel
- Merge Tables in Excel
- Name Box in Excel
- Name Range in Excel
- Null in Excel
- One Variable Data Table in Excel
- OneDrive Excel
- Protect Workbook in Excel
- Pivot Table in Excel
- Pivot Table Examples
- Pivot Table Filter
- Pivot Table Slicer
- Paste Special in Excel (With Top 10 Shortcuts)
- Quick Analysis Tools in Excel
- Radio Button in Excel
- Recording Macros in Excel
- Regression Analysis in Excel
- Scenario Manager in Excel
- Scroll Bars in Excel
- Scroll Lock in Excel
- Slicers in Excel
- Solver in Excel
- Sort by Color in Excel
- Sort by Number in Excel
- Sort Data in Excel
- Sparklines in Excel
- Spell Check in Excel
- Split Panes in Excel (Horizontally, Vertically, Cross Split)
- Status Bar in Excel
- Text to Columns in Excel
- Timeline in Excel
- Toolbar on Excel
- Track Changes in Excel
- Trend Line in Excel
- Two-Variable Data Table in Excel
- Watch Window in Excel
- Wrap Text in Excel
- XML 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 Tips (178+)
- VBA (162+)
Name range in excel are the ranges which have been given a name for the future reference, to make a range as named range first select the range of data and then insert a table to the range, then we put a name to the range from the name box in the left-hand side of the window, after this we can refer to the range by its name in any formula.
Name Range in Excel (Table of Contents)
What is Name Range in Excel?
Name Range in excel makes it much cooler to keep track of things, especially when you’re using formulas. You can assign a name to a range. And, if there is any change in that range, no problem you need to update the range from the Name Manager. You don’t need to update every formula manually. Similarly, you can create a name for a formula. And, if you want to use that formula in another formula or another location, just refer to it by name.
- The significance of named ranges is that you can put any names in your formulas without thinking about cell references/address. You can assign the range with any name.
- Create a named range for any data or a named constant and use these names in your formulas in place of data references. By this way, you can make your formulas easier to comprehend better. A named range is just a human-understandable name for a range of cells in Excel.
- By using the name range in excel, you can make your formulas much simpler to comprehend better. You can assign a name for a range in excel sheet for a function, for a constant, or for a table data. Once you start using the names in your excel sheet, you can easily understand these names.
Define Names From a Selected Range
- Select the data range you want to assign a name, then Select Formulas and Create from Selection.
- Click on “Create Names from Selection” then select the Top row, Left column, Bottom row, or Right column check box and Select OK.
Excel names the cells based on the labels in the range you designated.
- Use names in formulas then Select a cell and enter a formula.
- Place the cursor where you want to use the name range formula.
- Type the first letter of the name, and select the name from the list that appears.
- Or, select Formulas then Use in Formula and select the name you want to use.
- Press Enter.
Update named ranges in the Name Manager (Control + F3)
You can update the Name form Name manager. Press Control and F3 to update the name. Select the name you want to change, then change the reference range directly.
How to Use Name Range in Excel?
Let understand the working on conditional formatting by simple excel examples.
Example #1 Create a name by using the Define Name option
- Select the cell(s).
- On the Formulastab, in the Define Names group, click the Define Name
- In the New Namedialog box, specify three things:
- In the Namebox, type the range name.
- In the Scopedropdown, set the name scope (Workbook by default).
- In the Refers tobox, check the reference and correct it if needed, Click OK to save the changes and close the dialog box.
Example #2 Make a named range by using Excel Name Manager
- Go to the Formulas tab then Defined Names group, and click the Name Manager Or, just press Ctrl + F3 (my preferred way).
- the top left-hand corner of the Name Manager Dialog window, click the New… button:
Name Range using VBA
We can apply the naming in VBA, here is the example as follows:
‘Adding a Name
Names.Add Name:=”myData”, RefersTo:=”=Sheet1!$A$1:$A$10″
‘You can use Name property of a Range
Sheet1.Range(“$A$1:$A$10”).Name = “myData”
Things to Remember
You must follow the below instruction while using the name range in excel.
- Names can start with a letter, backslash (\) or an underscore (_).
- An Excel name should be under 255 characters long.
- Names must be continuous and can’t contain spaces and most punctuation characters.
- There must be no conflict with cell references in names using in excel.
- You can use single letters as names, but the letters “r” and “c” are reserved in excel.
- Names are not case-sensitive – “Tanuj”, “TANUJ”, and “TaNuJ” are all the same to Excel.
This has been a guide to Name Range in Excel. Here we discuss how to use Names in Excel along with practical examples and downloadable excel templates. You may also look at these useful functions in excel –