Excel Intermediate Lesson 1: Named Ranges and Structured References

📎 This article includes 1 downloadable practice file ↓

📘 Excel Intermediate Course · Lesson 1 of 12

Open any report built by someone in a hurry and you will see formulas like =SUMIF(Sheet1!$D$2:$D$81,"North",Sheet1!$K$2:$K$81). They work, but nobody can read them, and the day someone adds invoice 82 the total is quietly wrong. This lesson fixes both problems.

Named ranges

Select Sales!K2:K81, click the Name Box (left of the formula bar), type Amount and press Enter. Now =SUM(Amount) works anywhere in the workbook.

• Formulas > Name Manager (Ctrl+F3) lists, edits and deletes names.
• Formulas > Create from Selection names every column at once from its header.
• Names can’t contain spaces or look like a cell address: Q1 and GST18 are taken by cells; use Q1_Sales.

Excel Tables: names that grow

Click inside the data and press Ctrl+T. Give the table a name (Table Design > Table Name), say SalesT. Now you can write:

=SUM(SalesT[Amount])
=SUMIFS(SalesT[Amount], SalesT[Region], "North")
=[@Qty]*[@Rate]          ' inside the table: this row's Qty × Rate

Type a new invoice in the row under the table and it joins the table: every formula that uses SalesT[Amount] now includes it. Charts and PivotTables built on the table grow too.

Which to use?

Common mistakes

• Naming a fixed range for growing data. Amount = K2:K81 won’t include row 82. Use a Table.
• Structured references that break when copied sideways. SalesT[Amount] shifts to the next column when you drag right; write SalesT[[Amount]:[Amount]] to lock it.
• Blank rows inside the data. Ctrl+T stops at the first fully blank row. Delete blanks first.

Practice

Download this lesson’s workbook below. Name the columns of the Sales sheet first, then answer the tasks using the names. Type your formula in the yellow column; the Check column turns green when the result matches, and the Answers sheet has a working formula for every task.