Naming and using cell ranges in Excel is useful when you have lots of cell ranges to deal with.
A named range is a human readable name for a range of cells in Excel, if you have large data, consider naming and using cell ranges in Excel to make you formulas readable.
The amazing thing about naming and using cell ranges is that you can use it like a cell reference in formulas.
For example, if you name the range of cells A1:A100 as “data”, you can use the SUM function to get the total with the simple formula below.
=SUM(data) // Total value
How to create named range.
The easiest way to create a named range is to select range of cells and type a name into the name box.
To easily test the new range. Choose the new name in the drop down list from the name box and Excel will select the range on the worksheet.
Want to know more about IF Function in Excel
Excel can create names automatically with the shortcut key:
- Control (Ctrl) + Shift + F3
When you have well-structured data with labels, you can have Excel create named ranges for you with few clicks of your mouse.
Select the data with the labels, and use the “Create from Selection” command on the Formulas tab on the ribbon bar.
With the help of the “Create from Selection”, you can create named ranges sales of perfume oils for the first half of the year in a single step.
When you click OK, the names are automatically created. Go to the name box to find the newly created names.
When you have named ranges like the above. You can use them with formulas for easy calculations.
For example: =SUM(May,March)
Updating named ranges.
It’s possible to update your created named range using the Name Manager (Control (Ctrl) + F3).
To easily see all named ranges on your worksheet, use the name box to the left of the formula bar.
If you want to view more details, the Name Manager (Control (Ctrl)+F3), which lists all names with references and provide filter option.
NOTE: 1. When you set the zoom level of your worksheet to less than 40%. Excel will show range names directly on the worksheet.
- By default, named ranges behave like absolute references.
- Once you’ve created a named range, it will appear automatically in formulas when you type the first letter of the name.
If you plan to use named ranges in your Excel worksheet, here are the rules to keep in mind.
- Names must begin with a letter, an underscore (_) or a backslash (/)
- Names can’t contain spaces or punctuation characters.
- Names can’t conflict with cell references- you can’t name a range “A1” or “Z100”.
- Single letters are okay for names but the letters “r” and “c” are reserved.
- Names are not case sensitive in Excel.
The most amazing benefit of named range is that they make formulas easier to read and maintain.
Download file for practice
Now that you have a pretty good idea of naming and using cell ranges in Excel, kindly watch the video below for visual explanation. Thanks, sharing is caring.