Day 19: Excel Tables & Structured References

An Excel Table (Insert → Table or Ctrl+T) converts a range into a structured object with a name, automatic formatting, built-in filters, and auto-expansion. Advantages: (1) New rows added below automatically become part of the table. (2) Formulas in table columns automatically fill down to new rows. (3) Pivot Tables connected to a table automatically pick up new rows on refresh. (4) Tables use structured references in formulas (=Table1[Revenue]) which are more readable and do not break when rows are inserted. Every data analyst should convert data to a Table immediately upon opening.

Structured references let formulas refer to table columns by name instead of cell addresses. =SUM(Table1[Revenue]) sums the Revenue column of Table1, regardless of where the table is or how many rows it has. Special references: Table1[#All] = entire table including headers. Table1[#Headers] = header row only. Table1[#Data] = data rows only. Table1[@Revenue] = the Revenue cell in the current row. Structured references automatically adjust when columns are added or removed — unlike cell references that can shift incorrectly.

Click anywhere in the table → Table Design tab → check "Total Row". A new row appears at the bottom with a dropdown in each column to select Sum, Count, Average, Min, Max, etc. The Total Row uses SUBTOTAL function internally, which means it respects applied filters — the total updates when you filter the table, showing the total of visible rows only. This is more useful than a SUM formula below the table, which always totals all rows regardless of filters.

Click anywhere in the table → Table Design → change the name in the "Table Name" field (top left). Rename from "Table1" to something descriptive like "SalesData" or "CustomerList". Meaningful names: (1) Make structured references readable: =SUM(SalesData[Revenue]) vs =SUM(Table3[Revenue]). (2) Make Pivot Table sources identifiable. (3) Allow formulas to reference the table across worksheets clearly. Always rename tables immediately after creating them.

No. An Excel Table must exist entirely on one worksheet. If you need to combine data from multiple sheets, use Power Query (Append Queries) to combine them into a single table on one sheet. Alternatively, use 3D references for simple calculations across sheets: =SUM(Sheet1:Sheet3!A1) sums cell A1 across three sheets. But for serious multi-sheet consolidation, Power Query is the right tool.