Creating and Using Named Ranges

Long references like B2:E6 are easy to mistype. A named range gives a block of cells a readable name you use in formulas — QuarterlyBudgets reads like plain English in governor reports.

James Okonkwo named the five department rows DeptList and the Q1–Q4 block QuarterlyBudgets. The summary sheet formula =SUM(QuarterlyBudgets) survived column inserts that would have broken raw addresses.

On Budgets in NorthgateBudget.xlsx, you will name the main grid and rewrite one formula to use the name.

Select B2:E6 (all quarterly amounts, no headers). Click the name box (left of the formula bar). Type QuarterlyBudgets and press Enter.

Click the name box dropdown — QuarterlyBudgets appears. Select it — B2:E6 highlights.

Rules: No spaces (use Quarterly_Budgets or QuarterlyBudgets). Start with a letter or underscore. Not a cell address like Q1 — Q1 looks like a cell reference and is invalid as a name.

Formulas → Defined Names → Name Manager lists every name.

Open Name Manager. Select QuarterlyBudgets — see Refers to: =Budgets!$B$2:$E$6. Click New to add DeptList for A2:A6. Add ContingencyRate for I1 (0.05). Close Name Manager.

Check Scope in Name Manager — Workbook vs Sheet. Naming B2:E6 on two sheets Data without checking scope makes formulas get the wrong sheet.

Click an empty cell (e.g. M2). Type =SUM( then start typing Quarter. Excel shows QuarterlyBudgets in IntelliSense — Tab to accept. Close parenthesis: =SUM(QuarterlyBudgets). Press Enter — total of all twenty quarterly cells.

Instead of =SUM(B2:E6), write =SUM(QuarterlyBudgets) on a Summary sheet.

Instead of **=F2*(1+$I$1), write =F2*(1+ContingencyRate)** — readable and the rate cell is obvious.

Try =SUMIF(DeptList,"Science",F2:F6) using DeptList for the criteria range.

Names are navigation shortcuts. Select QuarterlyBudgets in the name box and B2:E6 highlights — no formula runs until you write one.

You create Q1 Budget with a space — Excel rejects it. Use Q1_Budget or Q1Budget instead.

Constants like ContingencyRate document important single cells such as I1 with 0.05 for **=F2*(1+ContingencyRate)**.