14.1 Multi-Cell Range Calculations

A range is any rectangular block of cells specified by its top-left and bottom-right corners separated by a colon. Understanding how ranges work in formulas is essential for building efficient calculations.

Types of Ranges

Multi-Range Calculations

Any function that accepts range arguments can accept multiple ranges separated by commas:

14.2 3D References — Calculating Across Multiple Sheets

A 3D reference applies the same formula to the same cell or range across multiple consecutive worksheets in a single formula. It is called "3D" because it extends a 2D cell reference (column × row) into a third dimension (sheet). This is the most efficient way to consolidate data from multiple identical sheets.

3D Reference Syntax

How to Enter a 3D Reference

1. Click the cell where you want the consolidated result
2. Type =SUM(
3. Click the first sheet tab (e.g., January)
4. Hold Shift → click the last sheet tab (e.g., December)
5. Excel enters January:December! automatically in the formula
6. Click the cell to reference (e.g., B10) on the now-selected sheet
7. Type ) → press Enter
8. Result: =SUM(January:December!B10) — sums B10 from all 12 sheets

3D Reference Rules

• The sheets must be consecutive (adjacent in the sheet tab order) — you cannot skip sheets
• All sheets between the first and last (inclusive) are included — even sheets added between them later
• The referenced cell or range must exist on all included sheets
• If you move a sheet out of the range (drag its tab to before or after the start/end sheets), it is excluded from the 3D reference automatically
• Works with: SUM, AVERAGE, COUNT, COUNTA, MAX, MIN, STDEV, VAR, PRODUCT
• Does not work with: IF, VLOOKUP, INDEX, MATCH, SUMIF, COUNTIF

3D Reference Examples

14.3 What Are Named Ranges?

A named range assigns a descriptive word or phrase to a cell or range of cells. Once named, you can use that name in formulas instead of cell addresses. This makes formulas dramatically easier to read, understand, and audit.

The Difference Named Ranges Make

Benefits of Named Ranges

• Readability: formulas explain themselves — no need to trace cell references to understand what a formula does
• Error reduction: typing a name is less error-prone than typing $B$2:$B$51 and reduces the risk of reference mistakes
• Automatic absolute reference: a named range is always an absolute reference — it does not shift when a formula is copied, eliminating the need for $ signs
• Easy updates: if the data range changes (more rows, moved to a different location), update the named range definition once in the Name Manager and all formulas using that name automatically use the new location
• Navigation: click the Name Box dropdown to instantly jump to any named range on any sheet
• Cross-sheet use: named ranges can be referenced from any sheet in the workbook without needing the SheetName! prefix

14.4 Creating Named Ranges

Method 1 — Name Box (Fastest)

1. Select the cell(s) to name (e.g., B2:B51)
2. Click the Name Box (the white field displaying the current cell address, at the left of the Formula Bar)
3. The current address highlights → type the new name (e.g., MonthlySalaries) → press Enter
4. The range is now named. Press Esc (not Enter) to cancel without creating the name.

Method 2 — Define Name (Formulas Tab)

1. Select the cell(s) to name
2. Formulas tab → Defined Names group → Define Name
3. The New Name dialog opens with:
   • Name: type the name (e.g., VATRate)
   • Scope: choose Workbook (name usable on all sheets) or a specific sheet (name only usable on that sheet). Workbook scope is almost always preferred.
   • Comment: optional description of what this range represents. Appears in tooltips when hovering over the name in formulas.
   • Refers to: the cell or range address — auto-filled from your selection. Can be edited here.
4. Click OK

Method 3 — Create from Selection (Batch Creation)

If your data already has row or column headers, Excel can automatically create named ranges using those headers as names — the most efficient method for naming many ranges at once.

1. Select the data range including the header row or column (e.g., A1:F51 where row 1 has headers: Name, Department, Salary, Tax, Net Pay, Start Date)
2. Formulas tab → Defined Names → Create from Selection
3. A dialog asks which row/column contains the labels to use as names:
   • Tick Top row to use row 1 headers as column names
   • Tick Left column to use column A values as row names
   • Both can be ticked simultaneously
4. Click OK
5. Excel creates named ranges automatically — e.g., "Salary" now refers to C2:C51, "Department" refers to B2:B51, etc.

14.5 Named Range Naming Rules & Best Practices

Excel Naming Rules (Must Follow)

Best Practices for Naming

• Use CamelCase for multi-word names: MonthlySalaries, VATRate, PassMark
• Or use underscores: Monthly_Salaries, VAT_Rate, Pass_Mark
• Be descriptive but concise: TaxRate beats TR and is better than TheCorporateTaxRateForSouthAfrica
• Use a consistent prefix for grouping related names: rng for ranges, const for constants, tbl for table names — e.g., rngSalaries, constVATRate
• Use singular for single-cell constants (VATRate) and plural for multi-cell ranges (Salaries)

14.6 Using Named Ranges in Formulas

Typing the Name in a Formula

1. Type =SUM(
2. Start typing the range name → the formula AutoComplete dropdown shows matching names
3. Press Tab to accept the highlighted name → type ) → press Enter

Using the "Use in Formula" Dropdown

1. Click the cell where you want the formula
2. Type =SUM(
3. Formulas tab → Defined Names → Use in Formula → select the name from the list
4. The name is inserted at the cursor position

F3 — Paste Name Dialog

1. While editing a formula, press F3
2. The Paste Name dialog opens showing all defined names
3. Double-click any name to insert it at the cursor — or select and click OK
4. Paste List button: inserts a complete list of all names and their cell references into the worksheet — useful for documentation

Named Ranges in Common Functions

Named Ranges Are Always Absolute

A key advantage: named ranges behave like absolute references ($A$1 style) — when you copy a formula containing a named range to another cell, the name always refers to the same cells. You do not need to add dollar signs.

14.7 The Name Manager

The Name Manager is the central control panel for all named ranges in the workbook. It shows every name, its scope, the cell/range it refers to, and any comment. All creation, editing, and deletion of names happens here.

Opening the Name Manager

• Formulas tab → Defined Names group → Name Manager
• Keyboard shortcut: Ctrl+F3

Name Manager Interface

Name Manager Actions

Editing a Named Range to Include New Rows

A common task: your Salaries range was defined as B2:B51 but you have added 10 more employees (now B2:B61):

1. Formulas → Name Manager (Ctrl+F3)
2. Select the "Salaries" name
3. In the Refers to field at the bottom: change =$B$2:$B$51 to =$B$2:$B$61
4. Press Enter → click Close
5. Every formula using Salaries now automatically covers the extended range — no formula changes needed

14.8 Dynamic Named Ranges

A dynamic named range automatically expands or contracts as data is added or removed — you never need to update the Name Manager when your list grows. There are two approaches:

Approach 1 — Use an Excel Table (Recommended)

Converting your data range to an Excel Table (Ctrl+T) automatically creates a named range that always includes all data rows. When new rows are added to the Table, all formulas referencing the Table automatically include them:

Approach 2 — OFFSET Formula (Legacy Workbooks)

For workbooks that cannot use Tables, a dynamic named range can be created using OFFSET:

• When you add a new salary in B52, COUNTA increases by 1, OFFSET extends the range automatically
• Any formula using =SUM(Salaries) immediately includes the new value

14.9 Named Constants

A named constant is a name that refers to a fixed value (not a cell range). It is used for values that are used repeatedly across many formulas — tax rates, pass marks, interest rates, VAT rates, KPIs.

Creating a Named Constant

1. Formulas → Define Name — or — Ctrl+F3 → New
2. Name: type the constant name (e.g., VATRate)
3. Refers to: delete the cell reference that appears and type the value directly (e.g., =0.15 for 15% VAT)
4. Click OK

The name VATRate now equals 0.15. It is not stored in any cell — it exists only in the name definition. Use it in formulas exactly like a named range:

Advantages of Named Constants

• When the VAT rate changes (e.g., from 15% to 16%), update VATRate once in Name Manager and every formula in the workbook updates automatically
• No need to store the rate in a cell — it does not appear in the spreadsheet grid and cannot be accidentally edited by a user
• Formulas are self-documenting: =B2*(1+VATRate) is immediately understandable

SA Office Named Constants Examples

14.10 Scope — Workbook vs Sheet-Level Names

Every named range has a scope that determines where in the workbook it can be used.

Using the Same Name on Multiple Sheets (Local Scope)

1. Navigate to the January sheet → select B2:B30 (January revenue data)
2. Formulas → Define Name → Name: Revenue → Scope: January → OK
3. Navigate to the February sheet → select B2:B28 (February revenue data, different row count)
4. Formulas → Define Name → Name: Revenue → Scope: February → OK
5. On each sheet, =SUM(Revenue) refers to that sheet's own Revenue range

14.11 Navigating with Named Ranges

Named ranges double as navigation shortcuts in large workbooks.

Name Box Navigation

1. Click the Name Box (the field showing the current cell address, left of the Formula Bar)
2. A dropdown appears listing all named ranges in the workbook
3. Click any name → Excel immediately jumps to that range and selects it — even if it is on a different sheet

Go To with Named Ranges

1. Press Ctrl+G or F5 to open the Go To dialog
2. The list shows all named ranges → click any name → OK
3. Excel selects and scrolls to the named range

14.12 Quick Self-Check

Q1: Your workbook has 12 monthly sheets named January through December. Cell B25 on each sheet contains that month's total revenue. Write a formula on a Summary sheet that totals the revenue from all 12 months using a single formula. Name the technique used.

✓ =SUM(January:December!B25). This uses a 3D reference. The colon between January:December tells Excel to include every sheet from January through to December (inclusive). B25 is the cell on each sheet to sum. The result is the total of all 12 months' B25 values in a single compact formula. To enter it manually: click the Summary sheet cell → type =SUM( → click the January tab → hold Shift → click the December tab → click B25 on the visible sheet → type ) → Enter.

Q2: You have a salary column in B2:B200 that you frequently reference. You name it "Salaries". Three months later, you hire 50 more staff (salaries in B201:B250). How do you update the name without changing any formulas?

✓ Formulas tab → Name Manager (Ctrl+F3) → click "Salaries" in the list → in the "Refers to" field at the bottom, change =$B$2:$B$200 to =$B$2:$B$250 → press Enter → click Close. Every formula in the workbook that uses "Salaries" (e.g., =SUM(Salaries), =AVERAGE(Salaries)) now automatically includes all 250 employees — no formula changes required. This is the most powerful maintenance advantage of named ranges.

Q3: Write a named constant for South African VAT (15%) and then write formulas to: (a) calculate VAT on a price in B2, (b) calculate the VAT-inclusive price from B2.

✓ Create the constant: Formulas → Define Name → Name: VATRate → in "Refers to" delete the cell reference and type =0.15 → OK. (a) VAT amount: =B2*VATRate. This multiplies the price by 0.15, returning the Rand VAT amount. (b) VAT-inclusive price: =B2*(1+VATRate) which equals =B2*1.15. The advantage: if VAT changes to 16%, update VATRate to 0.16 once in Name Manager and every formula throughout the workbook updates automatically.

Q4: What are the key naming rules for Excel named ranges? List at least 5 valid and 5 invalid name examples.

✓ Key rules: no spaces, must start with a letter or underscore, no special characters (only letters/numbers/underscore/period allowed), cannot be a cell address (A1, B2), cannot be an Excel function or keyword (SUM, IF, TRUE), max 255 characters, not case-sensitive. Valid: MonthlySalaries, VAT_Rate, PassMark, _2024Data, Emp.Count. Invalid: Monthly Salaries (space), 2024Sales (starts with number), Salary+Bonus (special character +), B2 (cell address), SUM (reserved function name).

Q5: You use =B2*VATRate in cell C2 and copy it to C3:C51. What happens to the VATRate reference as the formula is copied, and why?

✓ VATRate remains exactly the same in every copied formula — C3 shows =B3*VATRate, C4 shows =B4*VATRate, C51 shows =B51*VATRate. Named ranges always behave as absolute references. VATRate always points to the same defined value or cell regardless of where the formula is copied. The B2 part shifts relatively (B3, B4...) because it is a standard relative reference. This is one of the greatest advantages of named ranges: you get absolute-reference behaviour without needing to type any $ signs.

Q6: What is the difference between workbook scope and sheet scope for a named range? Give a practical example of when you would use sheet scope.

✓ Workbook scope means the name is global — it can be used in any formula on any sheet without any prefix. Sheet scope means the name is local to one sheet — it can only be used directly on that sheet; from other sheets it requires the SheetName!Name prefix. Practical example for sheet scope: A workbook has 12 monthly sheets. Each month has a different number of sales records. By creating a sheet-scoped name "SalesData" on each sheet (January!SalesData for Jan rows, February!SalesData for Feb rows), formulas on each sheet can simply use =SUM(SalesData) and they automatically reference that sheet's own data. If the name had workbook scope, you could only have one "SalesData" name for the entire workbook and could not reuse the same name on each monthly sheet.

✓ Module 14 Complete — You Have Learned:

• Range types — contiguous, multi-column blocks, non-contiguous (comma-separated), entire columns/rows, intersection (space operator); multi-range in functions (SUM, AVERAGE, COUNT, MAX with multiple range arguments)
• 3D references — syntax (FirstSheet:LastSheet!CellRef); how to enter by clicking sheet tabs with Shift; rules (consecutive sheets only, all sheets between included, works with SUM/AVERAGE/COUNT/MAX/MIN but NOT IF/VLOOKUP); 4 formula examples; SA practical consolidation example
• What named ranges are — assigning descriptive names to cells/ranges; before vs after comparison table (4 formula pairs); 6 benefits (readability, error reduction, automatic absolute reference, easy updates, navigation, cross-sheet use)
• Creating named ranges — 3 methods: Name Box (fastest, must press Enter to confirm), Define Name dialog (Name/Scope/Comment/Refers to fields), Create from Selection (batch from headers, spaces become underscores)
• Naming rules — 7 rules (no spaces, start with letter/underscore, no special chars, not a cell address, not a reserved word, max 255 chars, not case-sensitive); best practices (CamelCase, underscores, descriptive, consistent prefix, singular/plural)
• Using names in formulas — typing with AutoComplete (Tab to accept), Use in Formula dropdown, F3 Paste Name dialog (Paste List for documentation); named ranges in SUM/AVERAGE/COUNTIF/VLOOKUP/IF/arithmetic; names always absolute (no $ needed)
• Name Manager (Ctrl+F3) — interface columns (Name, Value, Refers To, Scope, Comment); New/Edit/Delete/Filter actions; editing the Refers To field to extend a range; step-by-step update for new data rows
• Dynamic named ranges — Table approach (Ctrl+T auto-expands, preferred); OFFSET approach for legacy workbooks with COUNTA for auto-expanding
• Named constants — defining a name that refers to a value (=0.15) not a cell; SA examples (VATRate, UIF_Rate, PassMark, WorkingDays, MinWage); single update propagates everywhere
• Scope — Workbook (global, usable anywhere) vs Sheet (local, same name per sheet for different ranges); practical monthly sheet example with local scope
• Navigating with named ranges — Name Box dropdown; Go To dialog (Ctrl+G/F5); pro tip for large workbook navigation