The Use of Named Ranges in Excel

Are you searching for ways to simplify your Excel experience?

Named Ranges are an excellent tool for making your formulas more readable, reducing errors and simplifying navigation within your workbook.

In this article, we’ll explore what Named Ranges are, how to create and use them and tips to make the most out of this feature.

What is a Named Range in Excel?

A Named Range is a name you assign to a cell or range of cells in Excel.

Instead of referring to a cell by its row and column coordinates (e.g., ‘A1:B10’), you can refer to them by a relevant name (e.g., ‘Expenditure’). This ultimately makes your formulas and functions easier to understand and manage, particularly when dealing with large amounts of data.

Why Use Named Ranges in Excel?

If you’re trying to simplify the user experience, using Named Ranges in Excel is crucial.

When you look at a regular formula, you can’t immediately determine what it does. To make sense of it, you would need to trace back each reference which can be incredibly time-consuming.

By using Named Ranges, you can instantly tell what a formula does, saving valuable time and effort. Additional benefits of using Named Ranges in Excel include:

• Improved Readability: Named Ranges make your formulas easier to read. For example, ‘=SUM(Outgoings)’ is more intuitive than ‘=SUM(A1:A10)’.
• Simplified Navigation: Named Ranges allow you to quickly jump to specific cells or ranges within your workbook.
• Reduced Errors: Using Named Ranges can minimize errors, especially in complex formulas, by reducing the reliance on cell references that can be difficult to track.
• Easy Updates: If your data range changes, you only need to update the Named Ranges instead of adjusting every formula where the range is used.

How to Create a Named Range in Excel

In Excel, users 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 name range or defined range.
• Table Name: This is the name of an Excel table that is created automatically when you insert a table in a worksheet.

The process of creating a Named Range can include:

Option 1: Using the Name Box

1. Select the Cell or Range: Highlight the cell or range of cells you want to name.
2. Go to the Name Box: The Name Box is located to the left of the formula bar. It usually displays the cell reference of the selected cell or range.
3. Enter the Name: Click inside the Name Box, type the desired name for the range and press ‘Enter’.

Option 2: Using the Define Name Method

1. Select the Cell or Range: Highlight the cell or range you want to name.
2. Go to the Formula Tab: Click on the ‘Formulas’ tab in the Excel ribbon.
3. Click Define Name: In the Defined Names group, click ‘Define Name’.
4. Name the Range: In the New Name dialog box, enter a name for the range. You can also add a comment and specify the scope (either the entire workbook or a specific worksheet).
5. Click OK: Your Named Range is now created!

Option 3 (recommended): Using the Create Name from Selection Method

1. Insert a new column (we recommend inserting this column on the right of the range(s) that are to be named)
2. Type the name to be used: the name should be inserted on the new column inserted on the previous step
3. Select the Range and the cell (on the right) with the name:
4. Go to the Formula Tab: Click on the ‘Formulas’ tab in the Excel ribbon.
5. Click ‘Create Names from Selection’ (or Ctrl+Shift+F3): since the Name is on the ‘Right column’ of the selection, check this option
6. Click OK: Your Named Range is now created!

Important Guidelines For Naming Formulas

When naming formulas in Excel, it’s essential to follow these rules to ensure your formulas work correctly:

• The name must begin with a letter or an underscore (_).
• After the first character, you can use letters, numbers, periods (.) and underscores (_).
• Spaces are not allowed in names. If used, Excel will replace them automatically by an underscore (_).
• Don’t use names that could be confused with cell references, such as “A1” or “B2”.
• Names can have up to 255 characters, but it’s best to keep them short and meaningful.
• While names can include both uppercase and lowercase letters, Excel treats them as the same. For example, PROFIT and profit are considered identical.

How to Reference a Named Range in Excel

To reference a Named Range in Excel, you can enter the name of the range in a cell even if the named range is located in a different sheet: Enter ‘= in a cell.

How to Remove Named Ranges in Excel

Sometimes, you might realize that you’ve used the wrong name in a workbook and need to change it. To do this, you’ll need to:

• Delete the incorrect name
• Define a new, preferred name
• Update all formulas that reference the old name so they point to the new one.

The third step can be particularly challenging, especially if there are hundreds of formulas spread across different worksheets or even other workbooks.

That’s where Operis Analysis Kit (OAK) comes in handy. With OAK’s “Names | Redefine” command, you can complete all three steps in one seamless operation, automatically updating cell references across all relevant worksheets and workbooks.

Try OAK FREE For 30 Days!

Want to be more confident when building spreadsheets?

With OAK, you can ensure your spreadsheet structure and formulas align with best modelling practices, increasing your confidence and efficiency. Start boosting your productivity today by downloading OAK FREE for 30 days!