Goal
You’ll be able to use named ranges and Excel Tables, and understand why formulas built on top of them are genuinely more readable and reliable than formulas built on raw cell references alone.
Learn
A named range gives a meaningful name to a specific cell or range, usable directly in formulas instead of a raw reference: select a range, type a name into the Name Box (top-left), press Enter. A range named TaxRate can then be used as =A1*TaxRate instead of the far less readable =A1*$D$1.
Beyond readability, named ranges also automatically behave like absolute references — they never shift when a formula using them is AutoFilled, directly solving the exact problem covered in Part 2.1, without needing to remember dollar signs at all.
An Excel Table (Insert tab → Table, or Ctrl+T) goes further, converting a range into a genuinely structured object with named columns, automatic formatting, and — most usefully — automatic expansion: adding a new row directly below a Table automatically includes it in the Table’s range, and any formulas referencing that Table automatically extend to include the new row too, with zero manual adjustment needed.
=SUM(Sales[Amount])
This structured reference syntax (using the Table name and column name, like Sales[Amount]) is both more readable than a raw range reference, and automatically stays correct as the Table grows — solving the same “does my formula’s range still cover all my actual data” problem that plain ranges require manual vigilance to avoid.
Decision Task
You have a formula =SUM(D2:D50) covering a plain data range, and later add 10 new rows of data below row 50. Before reading on: does this formula automatically include those new 10 rows? What would be different if this same data were set up as an Excel Table instead?
Show Answer
No — a plain range reference like D2:D50 stays fixed exactly as typed; the new 10 rows below it would NOT automatically be included, requiring you to manually edit the formula’s range. If this were set up as an Excel Table instead, a structured reference like =SUM(Sales[Amount]) would automatically expand to include new rows added directly below the Table, with zero manual formula editing needed at all.
Common Mistake
Using plain cell ranges for data that’s genuinely expected to grow over time (like an ongoing transaction log), then forgetting to manually update every formula’s range as new rows get added. Converting that same data into an Excel Table from the start solves this automatically and permanently, since Table-based formulas expand along with the Table itself.
Practice Questions
1. How would you create a named range called TaxRate for the value in cell D1?
Show Answer
Select cell D1, click into the Name Box (top-left), type TaxRate, and press Enter.
2. Why does a named range never shift when a formula using it is AutoFilled, without needing a dollar sign?
Show Answer
Named ranges automatically behave like absolute references by default — this locking behavior comes built-in, without needing to manually add $ signs.
3. What keyboard shortcut converts a selected range into an Excel Table?
Show Answer
Ctrl+T
4. What genuinely useful behavior does an Excel Table provide that a plain range does not, specifically as new data is added?
Show Answer
Automatic expansion — adding a new row directly below the Table automatically includes it in the Table’s range, and formulas referencing the Table automatically extend to cover it too.
5. What does the structured reference Sales[Amount] refer to?
Show Answer
The “Amount” column specifically within a Table named “Sales”.
Try It Yourself
Without looking back, explain in your own words why converting an ongoing transaction log into an Excel Table (rather than leaving it as a plain range) would genuinely help avoid a common real-world spreadsheet maintenance problem.
Show Answer
An ongoing transaction log genuinely keeps growing over time as new entries are added; a plain range-based formula would need to be manually re-edited every time to include new rows, risking outdated calculations if this is ever forgotten, while an Excel Table’s structured references automatically expand to include new rows with zero manual maintenance required.
Quick Check
1. What does a named range let you use in a formula, instead of a raw cell reference?
Show Answer
A meaningful, readable name.
2. Do named ranges shift when AutoFilled, the same way a plain reference would without a dollar sign?
Show Answer
No — they automatically behave like absolute references by default.
3. What keyboard shortcut creates an Excel Table from a selected range?
Show Answer
Ctrl+T
4. What happens to Table-based formulas when a new row is added directly below the Table?
Show Answer
They automatically expand to include the new row, with no manual editing needed.
5. What is a “structured reference”?
Show Answer
A formula reference using a Table’s name and column name, like Sales[Amount], instead of a raw cell range.