Using Named Ranges for Speed

A formula like =SUMIFS($E$2:$E$51;$C$2:$C$51;J2) is correct but opaque. A formula like =SUMIFS(Revenue; Region; J2) says exactly what it does. Anyone reading it — including you, six months later — understands it immediately.

Named ranges replace cell addresses with descriptive labels. Beyond readability, they make formulas more maintainable: if the data range changes, you update the name definition once and every formula using that name updates automatically.

Create a Named Range

The Name Box is the field at the left end of the formula bar that normally shows the active cell address.

• Select the cell or range you want to name;
• Click the Name Box;
• Type the name → press Enter.

Naming Rules

• Names must start with a letter or underscore — not a number;
• No spaces allowed — use underscores (Unit_Cost) or camelCase (UnitCost);
• Names are not case-sensitive — Revenue and revenue refer to the same name;
• Names cannot look like cell addresses — E2 or R1C1 are not valid names;
• Names can be up to 255 characters.

Once defined, use the name exactly as you would a cell address: =SUM(Revenue) instead of =SUM($E$2:$E$51).

Named ranges appear in Formula Autocomplete — start typing the name and Excel suggests it in the dropdown, just like function names. Press Tab to insert.

The Name Manager

The Name Manager shows every named range and formula in the workbook with its current reference, scope, and value. From here you can:

• Edit a name or its reference — if data rows are added and a named range needs to expand, update the reference here;
• Delete names that are no longer needed;
• Filter by scope (worksheet vs workbook names) or by error status.

If a named range points to a range that was deleted or moved, the Name Manager flags it with #REF!. Audit names periodically — especially in workbooks that have been heavily edited — to keep the name list clean.

Use named ranges to calculate revenue.

Create named ranges for the Units and Unit Cost columns, then use them to calculate Revenue.

1. Create a named range Units for the range E2:E52.
2. Create a named range Unit_costs for the range F2:F52.
3. In cell G2, enter a formula that calculates Revenue using the named ranges.

• Select the range → type the name in the Name Box → press Enter.
• Or use Formulas → Define Name.
• In G2, use:

=Units*Unit_costs

• If needed, confirm it as an array/dynamic formula (depending on Excel version).
• Make sure both ranges are the same size (E2:E52 and F2:F52).