Introduction
Named ranges enable users to give descriptive names to cells or cell ranges in Microsoft Excel, as this makes data management even easier to handle and helps read formulas. Using names instead of the standard cell references often brings about clearer and more intuitive spreadsheets. This resource provides a how-to for defining named ranges, types, and other benefits of having it in data analysis in Excel.
Name in Excel
Names are common references in daily life to people, things, and geographic locations.
Similarly, you can name a single cell or an entire range of cells in Microsoft Excel using something more meaningful than the reference and then refer to them by name instead of referring to their references.
For example, you can compute the sum of the sales column B2:B10 for a particular item in cell E1 with the formula:
=SUMIF ($A$2: $A$10, $E$1, $B$2: $B$10)
Or giving meaningful names to the ranges and cells and letting them to be used by formula:
=SUMIF (items list, item, sales)
Name Types in Excel
Two types of names in Excel:
Defined name: This is a name for one cell, range of cells, constant value, or formula. For instance, you can assign names to cell ranges. Defined range is also known as named range. Today we are going to discuss types of names.
Table name: A default name of an Excel table automatically created when inserting a table into the worksheet with the keyboard shortcut Ctrl + T.
How to create an Excel named range
There are some ways to create a definition name in Excel: using the Name Box, clicking the Define Name button, and the Excel Name Manager.
Using the Name Box
Click a cell or range that you want to name.
Go into the Name Box left-hand side of the formula bar.
Define the range you are trying to apply the name to (no spaces, it must begin with a letter).
Save the name with the Enter key.
Using the Name Manager
Choose cell or range of cells.
Open Formulas tab
Click on Define Name
New Name dialog box;
Name: This gives your set a name.
Make sure that you enter the correct Refers to field for your scale
Click OK.
Using Name Manager for Dynamic Named Range
Formulas>Name Manager>New.
Name is the assigned name to the range.
In the Refers to box, type a formula to create a dynamic range. In this example,
=OFFSET (Sheet1! $A$1,0,0, COUNTA (Sheet1! $A: $A),1)The formula in Excel creates a range that will change its dimension when you enter more entries into Column A.
Click OK.
Using the Define Name Button
Choose the cells.
In the ribbon, choose Formulas, then Define Names in the Define Names group, and finally Choose Define Name.
In the New Name dialog, you can specify the following three things:
– Range name: Here you put the name of the range.
– At this stage, you are only setting the scope for the name. Workbook is the default.
-Refer to: You accept the reference here, and edit there as needed.
Click OK to accept your change, closing the dialog.
Benefits of using Names in Excel
Excel names make formulas easier to make and read
You do not have to type long references or scroll through open trying ranges on the sheet. Just enter the name you want to use within the formula, and Excel will render a list of names to scroll through for the applicable ones. It’ll put the formula in automatically on your double click.
Excel names allow creating expandable formulas
In this, you create a “dynamic” formula, making use of dynamic named ranges; and automatically, it’s going to take in new data so it gets calculated and you do not need to go update every reference.
Excel names make formulas easier to re-use
That way, when one needs to shift the formula to another sheet or even move the formula to another workbook, there will never be any issues. The same names one adds in the destination workbook, it can then copy and paste that formula and nothing else would be required for it to work immediately.
Named range simplify navigation
Clicking on the name in Name box sends you directly to the named range. Whenever you make use of a name created on another sheet within your Excel spread sheet, that sheet automatically updates.
Named range allow creating dynamic drop-down lists
To make a dynamically expanding and updating drop-down list, you will have to first create a dynamic named range and then, on the basis of that range, you will make a data-validation list.
Conclusion
Named ranges make the use of formulas in Excel clearer, helping users to navigate through it, and make data management flexible and updatable. Using named ranges enables the users to efficiently perform their operations on complex datasets, such that their spreadsheets are both manageable and scalable.