Chapter 48: Name Manager in Excel and Altering Named Ranges

Excel’s Name Manager is a powerful feature that helps you manage named ranges, formulas, and constants in your worksheets. This chapter will explain how to use the Name Manager to create, modify, and delete named ranges.

1. Understanding Name Manager

The Name Manager in Excel allows you to define and manage names for ranges of cells, formulas, and constants. Named ranges make formulas easier to understand and simplify complex formulas.

• Accessing Name Manager:
  1. Open Excel: Start by opening the workbook where you want to manage named ranges.
  2. Go to the Formulas Tab: Click on the Formulas tab in the Ribbon.
  3. Click on “Name Manager”: In the Defined Names group, click on Name Manager. This opens the Name Manager dialog box.

2. Creating Named Ranges

You can create a named range to refer to a specific range of cells, a formula, or a constant value.

• Steps to Create a Named Range:
  1. Select the Range: Highlight the range of cells you want to name.
  2. Go to the Formulas Tab: Click on the Formulas tab in the Ribbon.
  3. Click on “Define Name”: In the Defined Names group, click on Define Name. This opens the Edit Name dialog box.
  4. Enter the Name: Type a name for the range in the Name field. Make sure the name is descriptive and does not contain spaces.
  5. Verify the Range: Check that the Refers to box correctly displays the cell range you selected.
  6. Click OK: Click OK to save the named range.

Pro Tip: Use underscores instead of spaces when naming ranges (e.g., Sales_Data instead of Sales Data).

3. Modifying Named Ranges

You can edit an existing named range if the range of cells or the formula needs to be updated.

• Steps to Modify a Named Range:
  1. Open Name Manager: Click on the Formulas tab and then Name Manager.
  2. Select the Name: In the Name Manager dialog box, select the name you want to modify.
  3. Click on “Edit”: Click Edit to open the Edit Name dialog box.
  4. Change the Name or Range: Update the Name field or the Refers to field as needed.
  5. Click OK: Click OK to save the changes.

Pro Tip: To quickly change the reference of a named range, use the Refers to field in the Name Manager to update the range.

4. Deleting Named Ranges

If a named range is no longer needed, you can delete it from the Name Manager.

• Steps to Delete a Named Range:
  1. Open Name Manager: Click on the Formulas tab and then Name Manager.
  2. Select the Name: In the Name Manager dialog box, select the name you want to delete.
  3. Click on “Delete”: Click Delete to remove the named range.
  4. Confirm Deletion: Confirm the deletion when prompted.

Pro Tip: Be cautious when deleting named ranges used in formulas or other parts of your workbook, as this can cause errors in your calculations.

5. Altering Named Ranges Directly in the Worksheet

You can also modify named ranges directly in the worksheet.

• Steps to Alter a Named Range:
  1. Select the Named Range: Click on any cell within the named range.
  2. Update the Range: Drag the handles to adjust the range as needed.
  3. Update Name Manager: Return to the Name Manager and verify that the Refers to field reflects the new range.

Pro Tip: Use the Go To feature (press Ctrl + G) and enter the named range to quickly navigate and review named ranges in your worksheet.

6. Using Named Ranges in Formulas

Named ranges can simplify formulas and make them easier to read.

• Example of Using Named Ranges:
  • Suppose you have a named range called Sales_Data referring to a range of sales figures. Instead of using =SUM(A1:A10), you can use =SUM(Sales_Data) in your formulas.

Pro Tip: Named ranges are automatically updated if the range of cells changes, as long as the range size remains the same.

By mastering the Name Manager and effectively using named ranges, you can enhance the clarity and manageability of your Excel workbooks.