An Excel table tells Excel where your data starts and ends. Sorting, totals and filters then come built in.
Point at a step to see the sheet right after it.
A2:A6 = ORD-02, ORD-01, ORD-04, ORD-05, ORD-03 The most expensive order is on top. The buttons were there from the moment the table was made.
Most spreadsheets that break do it at the bottom: a new row typed under the data that the totals and formulas never pick up. A table fixes that. It knows where the data starts and ends, and it keeps track as the data grows.
Below, an office-supply order list becomes a table. You sort it from a header, add a Total Row, and answer a budget question without writing a formula. After that, each part is taken apart on a small sheet where you can try the choices yourself.
This is Witsheet, Witsy’s in-browser spreadsheet. It works like Excel and checks each step as you go.
Insert → Table turns a block of cells into one dataset with headers. Every header gets a button straight away, and the rows are banded so they’re easy to read across. The button sorts the whole table by its column, and it filters: untick what you don’t want to see.
A2:A6 = ORD-02, ORD-05 The two Ink orders. The funnel on Category’s button says a filter is on.
Point at a step to see the sheet right after it.
Filtering hides rows; it never deletes them. A filter left on is easy to miss later, so look for the funnel on a header button, and clear it from the same button.
Type in the row right under a table and it grows to include it, formatting and formulas too. A formula can name the table’s column, =SUM(Table1[Total]), instead of a range like =SUM(D2:D6), and the column
grows with the table.
D8 = 233 The new row joined the table, so the table’s Total column now runs to row 7, and a SUM of it counts ORD-06 too.
Table Design → Total Row adds a row under the data. It totals the last column, and every cell in it has a dropdown: Sum, Average, Count, Max and more. Count counts every filled cell, text too; Count Numbers counts only numbers. The Total Row adds up only the rows you can see, so a filter turns it into that category’s total.
D7 = 89 The Total Row adds only the rows on show: the two Ink orders. Change the filter and it follows.
The other side of it: clear the filter before you report the full total.
Insert → Table takes the unbroken block around the cursor. A blank row in the middle stops it, and everything below the gap stays outside the table. Delete the gap first, or fix the range in the dialog before you click OK. And if the Table Design tab disappears, click any cell in the table: the tab only shows while the cursor is inside one.
To keep practicing in your own Excel, download the workbook: a year of office-supply orders, 350 rows to turn into a table, total, filter and pivot. The tasks sheet walks you through it.
A year of orders for a fictional office-supplies company — regions, reps, categories, quantities and totals. The classic pivot table dataset; also good for SUMIF, sorting and filtering.
350 rows · Order ID · Date · Region · Sales Rep · Product · Category · Units · Unit Price · Total
Free · fictional data · opens in Excel and Google Sheets
Witsy’s Excel course teaches a few hundred small skills, called Wits, the way this page teaches tables: in a real spreadsheet, a step at a time. The placement test finds the ones you already have.
Take a few minute test to see how good you actually are with spreadsheets. Witsy creates a map of the Wits you should learn next.
Take the placement test Or just start learning →