How to Make a Table in Excel — Borders vs. Format as Table (Ctrl+T), and Why Sorting and Totals Differ

Two kinds of table — what's different

The practice file has 8 made-up flower-shop orders (Order date, Customer, Item, Qty, Price). Here is what happened when I turned the same list into each kind of table. This PC runs Excel with a Korean display language, so menu names below come from Microsoft's English documentation; the numbers are what Excel actually returned.

For a sheet you'll print and never change, borders are enough. For a list that keeps growing and that you sort, filter and total, use a real table. Let's do both.

Way 1 — draw a table with borders

1. 1Select A1:F9 (one header row plus 8 orders). Put =D2*E2 in F2 and fill it down.
2. 2Home tab > Font group > the arrow next to Borders > All Borders.
3. 3Make the header row bold (Ctrl+B) and you're done.

Borders are just lines on cells — Excel doesn't know the cells form a list. I added a new order in row 10 (Oak Pharmacy, 2 orchid pots, $130): row 10 got no border, and =SUM(F2:F9) kept its range and stayed at $610. To include the new order you have to edit the formula to =SUM(F2:F10) yourself. (I entered the value by automation; when you type by hand, Excel's 'Extend data range formats and formulas' option may carry some formatting down — I didn't test that.)

Way 2 — Format as Table (Ctrl+T)

1. 1Click any single cell inside the order list — no need to drag-select it.
2. 2Press Ctrl+T, or go to Insert > Table (you can also pick a color first from Home > Format as Table).
3. 3In Create Table, check that the range (=$A$1:$F$9) was picked for you and My table has headers is checked, then OK.

After OK, Excel named the table (Table1 in English Excel) and applied the Medium 2 style. Every header cell got a filter button, rows were banded, and clicking inside the table shows a Table Design tab. Its Table Style Options group has the check boxes for Header Row, Total Row, Banded Rows and Filter Button.

[@Qty] is a structured reference that means 'the Qty cell in this row'. Because it uses the header name instead of an address like D2, nothing needs fixing when rows are added. Outside the table, Table1[Amount] means the whole Amount column of that table.

Total Row and filters — adding only the visible rows

The 109 in the Total Row's SUBTOTAL means 'sum, ignoring hidden rows'. So with only 'Rose bouquet' showing, it added the three visible orders: $70 + $35 + $105 = $210, and the status bar showed 3 of 9 records. SUM, on the other hand, adds hidden rows too and stayed at $740. Use the Total Row when you want the total of what you're looking at, SUM when you want the grand total. Microsoft's docs note that the arrow next to the total cell lets you switch to Average, Count and other functions.

The sorting trap — rows that come apart

In the bordered range, select only the Item column (C2:C9) and click Data > Sort A to Z. Excel shows a Sort Warning asking you to Expand the selection or Continue with the current selection. Choose the second, and only the Item column is sorted. When I did that, 6 of the 8 orders ended up with a different item — Sophie's wreath order became 'Preserved flowers', yet the amount stayed $89, so nothing looks broken and no error appears.

Where people get stuck

• A blank row in the middle: Ctrl+T looks for the block of filled cells around the one you clicked. With row 6 empty, the range stopped at A1:F5. Delete the blank row or fix the range in Create Table
• Blank or duplicate headers: a blank header was renamed automatically (Column1 in English Excel) and a second 'Amount' became 'Amount2'. Table headers must be unique
• My table has headers unchecked: Excel treats your first row as data and inserts a new header row above it (Column1, Column2), so the table grew by one row (F2:G3 → F2:G4)
• Merged cells: making a table from a list with A2:A3 merged (by automation, no prompts) unmerged them. I didn't check what message Excel shows on screen. Merging cells is lesson 5
• Back to a normal range: Table Design > Convert to Range. The colors stayed, the filter buttons and table name went away, and =[@Qty]*[@Price] turned into a plain-address formula like =Sheet1!$D2*Sheet1!$E2

Practice

Open the practice file (order list only) and follow these steps.

1. 1Select A2, press Ctrl+T, keep My table has headers checked.
2. 2In F2 type =[@Qty]*[@Price] (per Microsoft's docs, clicking D2 inside the table after typing = enters [@Qty] for you). Watch the whole column fill.
3. 3Table Design > Total Row. If F10 shows $610, you match the answer file.
4. 4Adding an order later: with the Total Row on, a new order typed in the row below it (row 11) did not join the table — the total stayed $610. Turn Total Row off, type the order in row 10, and turn it back on, or press Tab in the last cell of the last order row as Microsoft's docs describe.

In the answer file the table is renamed 'Orders' (Table Design > Properties > Table Name), so formulas outside it read =SUM(Orders[Amount]).

FAQ