Excel tables

Whenever you have a rectangular block of data with headers, make it an Excel Table. Of all the habits here this one matters most, and most beginners have never heard of it. Select any cell in the block and press Ctrl+T (menu: Insert ▸ Table). Confirm "My table has headers," press Enter, and the raw range becomes a first-class object with a name. The default is Table1. Rename it in Table Design ▸ Table Name to something meaningful like Sales.

What does an Excel table do for you?

Why make such a fuss about it? Because a Table enforces four things that raw cells leave to your fragile human diligence:

1. Structured references. Inside and outside a Table you can refer to columns by name instead of by coordinate. =SUM(Sales[Revenue]) sums the Revenue column no matter how many rows it has today. =[@Units]*[@Price] in a Table cell means "this row's Units times this row's Price." These references document themselves, and they don't break when you insert or sort rows. Compare =SUM(D2:D5000), a coordinate you have to maintain by hand, with =SUM(Sales[Revenue]), which stays correct on its own.
2. Auto-expansion. Type a new row directly beneath a Table and the Table swallows it, extending formatting, formulas and structured references automatically. Add a column to the right and it joins too. Every downstream formula, chart and PivotTable pointed at the Table updates without you touching its range. This one behaviour eliminates the most common "my total forgot the new rows" error in all of finance.
3. Automatic filters and sorting. Every header gets a filter dropdown the moment the Table exists, and Ctrl+Shift+L toggles them. Sorting is safe because the Table knows its own boundaries. You can't sort one column while the others stay put and scramble every row, which is the most destructive beginner accident of all and comes up again under Common Mistakes.
4. A total row on demand. Table Design ▸ Total Row, or Ctrl+Shift+T in current builds, adds a footer. Each column in it offers a dropdown of aggregate functions (Sum, Average, Count, Max). They're computed with the SUBTOTAL function, so filtered-out rows are excluded correctly.

How do tables look, and how do you convert one back?

Tables are easier on the eye as well. They come with banded rows and a styled header (restyle at Table Design ▸ Table Styles), and the header row stays visible as you scroll down within the Table, a freeze-panes effect you get for free. If you ever must, Table Design ▸ Convert to Range turns a Table back into a plain range.

A professional analyst's default is data in, Table on. When we clean the messy exports in the worked examples, Ctrl+T is the very first move, before we fix a single number.