Excel names and named ranges: how to define and use in formulas

This tutorial clarifies the function of Excel names and demonstrates how to define names for cells, ranges, constants, or formulas. It also covers editing, filtering, and deleting defined names.

Excel names, while incredibly useful, are often overlooked. This tutorial not only explains how to create named ranges but also shows how they simplify formula creation, readability, and reusability.

Understanding Excel Names

In everyday life, names simplify referencing. Similarly, Excel names provide human-readable identifiers for cells and ranges, replacing complex cell references. For example, instead of =SUMIF($A$2:$A$10, $E$1, $B$2:$B$10), use =SUMIF(items_list, item, sales). The latter is significantly clearer.

Excel Name Types:

• Defined Names: Names assigned to single cells, ranges, constants, or formulas (including named ranges). This tutorial focuses on defined names.
• Table Names: Automatically generated names for Excel tables (created using Ctrl T).

Creating Named Ranges

Three methods exist for defining names:

1. Name Box:

1. Select the cell(s) or range.
2. Type the name in the Name Box.
3. Press Enter.

2. Define Name Option:

1. Select the cell(s).
2. Go to the Formulas tab, click Define Name.
3. In the dialog box, enter the name, scope (workbook is default), and reference.
4. Click OK.

3. Name Manager:

1. Go to Formulas tab > Name Manager (or Ctrl F3).
2. Click New….
3. Configure the name as in method 2.

Creating Named Constants and Formulas

You can define names for constant values (e.g., =0.93 for USD_EUR) and formulas (e.g., =COUNTA(Sheet5!$A:$A)-1 for Items_count). These enhance formula clarity and maintainability.

Naming Columns and Rows

Select the table including headers, go to Formulas > Create from Selection, and choose the header rows or columns to create names automatically.

Dynamic Named Ranges

For expanding datasets, use dynamic named ranges (using OFFSET or INDEX functions) to automatically include new data.

Excel Naming Rules and Scope

• Names are case-insensitive and under 255 characters.
• They must start with a letter, underscore, or backslash.
• Spaces and most punctuation are not allowed.
• Scope can be worksheet-level or workbook-level. Workbook-level names are accessible throughout the workbook.

Excel Name Manager

The Name Manager (Ctrl F3) allows editing, filtering (by scope, errors, type), and deleting names. Filtering for names with errors helps identify and correct invalid references.

Benefits of Using Excel Names

1. Improved formula readability and creation.
2. Creation of expandable formulas using dynamic ranges.
3. Easier formula reuse across sheets and workbooks.
4. Simplified navigation to named ranges.
5. Enables creation of dynamic dropdown lists.

Tips and Tricks

• Use Paste Names… (F3) to list all names.
• Understand the difference between absolute and relative names. Relative names adjust based on the active cell's position.
• Use Apply Names… to apply defined names to existing formulas.
• Utilize keyboard shortcuts (Ctrl F3, Ctrl Shift F3, F3).
• Be aware of #REF! (deleted range) and #NAME? (invalid name) errors.

This enhanced summary retains the core information while improving clarity and flow. Remember to include all images as requested in the original format.

The above is the detailed content of Excel names and named ranges: how to define and use in formulas. For more information, please follow other related articles on the PHP Chinese website!