Excel Tutorials
Turn a plain data range into a structured Table with built-in filtering, formatting and formulas that grow with your data. This lesson uses the Event Planner workbook from Excel Foundations, free to download below.
Follow along in the same file used in the video. No email required.
An Excel Table is different from just formatted-looking data, it's an actual structured object. Once a range is converted to a Table, it comes with automatic filter dropdowns, banded row colors, and formulas that expand on their own as you add rows.
It's the difference between data that merely looks organized and data Excel actually treats as a defined, growing set, which is what makes structured references possible in the first place.
Step-by-step, matching the video above.
Click anywhere inside the data you want to convert, including the header row.
Or go to Insert > Table on the ribbon.
In the dialog that appears, make sure My table has headers is checked if your top row holds column names, then click OK.
Filter dropdown arrows appear on every header, rows get alternating band colors, and a new Table Design tab shows up on the ribbon.
On the Table Design tab, replace the default name (like Table1) with something meaningful, such as Orders, for use in structured references.
Just start typing in the row directly below the table. The Table automatically expands to include it, formatting and all.
On the Table Design tab, click Convert to Range to remove the Table structure while keeping your data and formatting.
Tables have a built-in way to add totals at the bottom that automatically account for filtering, no formula-writing required.
On the Table Design tab, check the Total Row box. A new row appears at the very bottom of the table.
Click any cell in that new row and a dropdown appears with options like Sum, Average, Count and Max.
Unlike a plain SUM formula, the Total Row automatically recalculates based on whichever rows are currently visible if you've filtered the table.
Leaving it as Table1, Table2, and so on makes structured reference formulas much harder to read later. Rename it the moment you create it.
A Table already has filter dropdowns the moment it's created, there's no need to separately turn on Filter from the Data tab.
Any formula using Orders[Quantity] style references reverts to plain cell addresses once you convert a Table back to a normal range, and won't auto-expand anymore.
A table shouldn't need rebuilding every time your data grows.
Free 14-day trial. No credit card required.
Select your data, press Ctrl+T, confirm whether it has headers, and click OK.
A Table is a structured object with automatic filtering, expanding formulas and a defined name, plain formatted data is just cells that look organized.
No. Filter dropdown arrows are already built into every Table header automatically.
On the Table Design tab, check the Total Row box, then pick a calculation like Sum or Average from the dropdown in any bottom-row cell.
The data and formatting stay, but the Table's structured references and automatic expansion stop working.
A meaningful name like Orders makes structured reference formulas, like Orders[Quantity], far easier to read than the default Table1, Table2 naming.
Yes, unlike a plain SUM formula, the Total Row recalculates based on whichever rows are currently visible.
Yes, a formula in one Table can point into another Table using its structured reference, similar to how VLOOKUP works, while each Table keeps its own independent name and range.
Yes, and it's a common pairing. A PivotTable built from a Table source automatically picks up new rows as the Table grows, without you needing to update the PivotTable's range.
Every business starts with a spreadsheet. Updoot is where you scale past it, everything's already structured and connected.
Start Your Free Trial →