Turning a plain range into a table is the single biggest upgrade most spreadsheets can get: banded rows, filter buttons, headers that stay readable and formulas that fill themselves. It takes one shortcut.
Ctrl+T
Step by step
- Click any cell inside your data.
- Press Ctrl+T. The Create Table dialog shows the range Excel has detected.
- Check that My table has headers is ticked if your first row contains headings.
- Press Enter.
Tips & troubleshooting
- Tables grow automatically as you add rows, so charts and formulas based on them stay up to date.
- Filter buttons are added for you. Ctrl+Shift+L toggles them off and on.
- Make sure there are no completely blank rows or columns inside your data, or Excel may pick the wrong range.
What a table gives you that a range doesn't
- It expands. Type in the row below or the column beside it and the table grows to include it, along with its formatting and formulas.
- Formulas fill themselves. Enter a formula in one cell of a column and the whole column gets it.
- Readable formulas. Instead of =C2*D2 you get =[@Price]*[@Quantity], which still makes sense six months later.
- A Total Row on demand. Ctrl+Shift+T adds one, and each total cell offers Sum, Average, Count and more from a drop-down.
- Headers that stay visible. Scroll down and the column letters are replaced by your headings.
- Everything built on it stays current. Charts, PivotTables and lookups that point at the table include new rows automatically.
Set it up properly
Give the table a name straight away. Click in it, go to the Table Design tab and replace Table1 with something meaningful such as Sales. Formulas elsewhere can then say =SUM(Sales[Amount]).
Before pressing Ctrl+T, check the data has one header row with a unique name in every column, no blank rows or columns inside it, and no merged cells.
Changing the look, or going back
- Different colours: pick a style on the Table Design tab. Choose None at the top of the gallery to keep the table's behaviour without the banding.
- Slicers: Table Design, Insert Slicer adds clickable filter buttons, handy on dashboards.
- Back to a normal range: Table Design, Convert to Range. The formatting stays but the table features stop.
On a Mac
In Excel for Mac the shortcut is Cmd+T.