What is the difference between an Excel Table and a regular range?

Tables provide built-in management features such as auto expansion, fixed headers, structured references, Total Rows, and slicers, making reports easier to maintain.

Why use structured references?

They reference columns and rows by name, so they remain reliable when columns are inserted or deleted. Example: =SUM(Tbl[Amount])

How do I connect a Table to Power Query?

Using Tables as Power Query inputs and outputs makes your workflow more resilient to schema changes and lets refreshes update data automatically.

Excel Table—an editor concept showing Kkong-i pointing to notes for Ctrl+T, Structured References, Total Row, and Slicer, along with =SUM(SalesTbl[Amount])

Excel Tables: Structured References, Auto Expansion, Total Rows, and Slicers

CONTENTS

Excel Tables: Structured References, Auto Expansion, Total Rows, and Slicers

This guide includes only procedures that were directly reproduced and verified in Excel 365. AI tools were used as an aid in preparing the draft.

1) Why Start with a Table?

Auto expansion: New rows and columns are automatically included in calculations and formatting.
Structured references: Reference by name, such as =SUM(SalesTbl[Amount]), for easier maintenance.
Total Rows and slicers: Ready to use in reports right away.
Works naturally with PivotTables, Power Query, data validation, and conditional formatting.

2) Create and Name a Table

Select the range → Ctrl+T → select “My table has headers.”
Go to Table Design ▸ enter SalesTbl in Table Name.
Keep column names concise and without spaces (for example, SalesDate, Category, Amount).

3) Master Structured References in 10 Minutes

Goal	Formula (Example)
Total	`=SUM(SalesTbl[Amount])`
Conditional total	`=SUMIFS(SalesTbl[Amount], SalesTbl[Category], "A")`
Date filter	`=SUMIFS(SalesTbl[Amount], SalesTbl[SalesDate], ">="&DATE(2025,1,1))`
XLOOKUP	`=XLOOKUP(H2, SalesTbl[Code], SalesTbl[Name], "Not Found", 0)`
Dynamic list	`=SORT(UNIQUE(SalesTbl[Category]))`
Spill reference	`=A2#` (entire dynamic array)

TIP Formulas update automatically even if the Table name or column names change.

4) Total Rows and Summaries

Go to Table Design ▸ select Total Row → choose Sum, Average, or Count for each column.
For reporting, expand to a PivotTable or PivotTables and slicers.

5) Table Slicers, Sorting, and Filtering

Select any cell in the Table → Table Design ▸ Insert Slicer (for example, Category).
Click a value in the slicer to filter the Table immediately.
Use the ▼ icon in the header for sorting and filtering, or use it alongside FILTER and SORT.

6) Connect with Power Query and PivotTables

Tables are the standard input and output format for ETL and analysis.

Use Tables for Power Query inputs and outputs → refreshes update automatically.
Using a Table as a PivotTable source makes it more resilient to schema changes.

7) Troubleshooting Checklist

Formulas do not fill down automatically → Check for blank or merged cells beneath the column header.
Structured references do not appear → While entering a formula, click a Table cell to insert one automatically.
Slow performance → Keep the Table range to a minimum (do not include thousands of blank rows); if needed, preprocess with Power Query.
Printing looks awkward → See the Print/PDF guide for “Fit to One Page.”

8) Practice Tasks and Templates

Task A — Build a Table + Slicer Dashboard in 10 Minutes

Create SalesTbl → add a Category slicer.
Use the Total Row for sales totals and averages.
Highlight status with conditional formatting.

Task B — Combine a Data Validation Drop-Down with Structured References

Define =SORT(UNIQUE(SalesTbl[Category])) as the named range ddCategory → connect it to a data validation list.

Bookmark this guide and try reproducing the three steps next month: **Table → Power Query → PivotTable**.

엑셀 조건부 서식 — 아이콘·색 눈금·수식 규칙·행 전체 강조·중복/유효기간 하이라이트 완전 가이드