Define and Reference Named Ranges

Why named ranges matter on MO-210

Domain 2 of Exam MO-210 (Manage Data Cells and Ranges) requires you to define a named range and reference a named range. On a performance project, that usually means two linked steps: give a range a clear name the grader can verify, then build a formula that uses the name instead of a raw address. Named ranges also appear in Domain 1 navigation tasks (jump to a named cell), but this section focuses on creating and using names for data work.

Task wording you will see includes phrases such as "Name the range B2:B25 as Q1Sales", "Create a defined name TaxRate that refers to cell G1", or "In cell D30, enter a formula that sums the named range RegionalTotal." Those map to the Name Box, Formulas → Defined Names → Define Name, and Name Manager, plus typing the name into a formula.

What a named range is

A named range (also called a defined name) is a friendly label that points to one cell, a block of cells, a constant, or a formula. Instead of remembering that monthly totals live in Sheet1!$C$2:$C$13, you work with a name like MonthlyTotals. Excel stores the name, its scope (workbook or a specific worksheet), and the reference it points to.

Names make formulas readable and reduce broken references when you insert or rearrange rows—provided you update the name when the structure truly changes. On the exam, graders check that the name exists, that it refers to the correct cells, and that your formula uses the name, not only the original address.

Naming rules you must follow

Excel rejects invalid names. Memorize these rules so you do not lose points fixing Name Box errors under time pressure:

If the task specifies an exact name such as CommissionRate, type it exactly as written—including capitalization for readability—even though Excel treats names as case-insensitive.

Method 1: Name Box (fastest for a simple selection)

1. Select the cell or range the project describes (for example B2:B50).
2. Click the Name Box — the field left of the formula bar that normally shows the active cell address.
3. Type the required name (for example EastRegion).
4. Press Enter. Do not click away without pressing Enter; the name will not stick.

Confirm success by selecting a different cell, then typing the name in the Name Box and pressing Enter. Excel should reselect the named range. You can also open the Name Box drop-down list to see defined names on the workbook.

When to use the Name Box on the exam: single contiguous range, workbook scope is fine, and the task does not ask for a comment or worksheet-only scope. It is the fastest path for "Name the selected range…" instructions.

Method 2: Define Name dialog

1. Select the range (optional but helpful so the reference prefills).
2. Go to the Formulas tab.
3. In the Defined Names group, click Define Name.
4. In the New Name dialog, set:
   • Name — the exact label from the task
   • Scope — Workbook (default) or a specific sheet if the task says the name applies only to one worksheet
   • Comment — optional; rarely required at Associate level
   • Refers to — absolute reference such as =Sheet1!$B$2:$B$50 (adjust sheet name if needed)
5. Click OK.

Use Define Name when you need an explicit scope, when you are naming a single cell that holds a rate or constant, or when you must verify the Refers to formula before committing. Absolute references ($) are normal for named ranges so the definition does not shift when you enter formulas elsewhere.

Workbook scope vs worksheet scope

• Workbook scope (most MO-210 tasks): the name is available on every sheet. Formulas on Sheet2 can use =SUM(Q1Sales) even if the data lives on Sheet1.
• Worksheet scope: the same name can exist on two sheets with different references. Reference a sheet-scoped name from another sheet with Sheet1!LocalName syntax. Only choose worksheet scope when the task says so.

Method 3: Name Manager basics

Formulas → Name Manager opens the control center for all defined names.

From Name Manager you can:

• New… — create a name without preselecting cells
• Edit… — change the name, comment, or Refers to range after rows were inserted or the task asks you to correct a definition
• Delete — remove a name when the project says to delete a defined name
• Review Value and Refers To columns to spot #REF! broken names after sheet deletions

If a formula shows #NAME?, check spelling in the formula first, then open Name Manager to confirm the name exists and points where you expect. If a name shows #REF! in Name Manager, edit Refers to to a valid range rather than recreating blindly—unless the task tells you to delete and redefine.

Filter and sort controls in Name Manager help on large workbooks; Associate projects usually have only a handful of names, so scanning the list is enough.

Referencing a named range in formulas

Once the name exists, use it anywhere a range address would work:

=SUM(Q1Sales)
=AVERAGE(EastRegion)
=TaxableIncome*TaxRate
=MAX(MonthlyTotals)

You can also type = and start typing the name; Excel's Formula AutoComplete lists matching defined names. Press Tab to accept the name, then finish the formula.

Performance tip: After defining the name, click the formula cell and type the function using the name, not the original B2:B50 address. Graders often check the formula text for the defined name. Using the address alone may leave the name unused even if totals look correct on screen.

Names in charts, validation, and Go To

Named ranges also feed data validation lists and chart series, and Ctrl+G (Go To) lists defined names. Domain 1 may ask you to navigate to a name; Domain 2 cares that you created it and used it in a calculation. If a later task says "Go to the named range CommissionRate", the Name Box drop-down or Go To both work.

Common exam pitfalls

• Typing a name in the Name Box and clicking another cell without pressing Enter — the name never saves.
• Including spaces or starting with a number — Excel rejects the name or creates something unexpected.
• Defining the name on the wrong sheet reference after copying data — verify Refers to in Name Manager.
• Building =SUM(B2:B50) when the task required =SUM(Q1Sales) — visually correct, score incorrect.
• Creating two similar names (Sales vs SalesTotal) and referencing the wrong one.
• Forgetting absolute sheet references when the active sheet differs from the data sheet in the Define Name dialog.

Suggested practice sequence

1. Select C2:C20, name it UnitCosts via the Name Box, press Enter.
2. In an empty cell, enter =AVERAGE(UnitCosts) and confirm the result.
3. Open Name Manager, edit UnitCosts to include an extra row the task added, and verify the average updates.
4. Delete a practice name you no longer need so you are comfortable with Delete in Name Manager.

Master those four moves and you cover the Define / Reference objectives for named ranges on MO-210.