Excel Tables, Sorting and Filtering Explained

An Excel Table (select the data and press Ctrl + T) turns a plain range into an object with a name, banded rows, automatic filters, and formulas that grow as you add rows. Sorting puts rows in an order, and filtering temporarily hides the rows that do not match. All three are ordinary features almost everyone uses, and all three have small rules that decide whether a total stays correct as the data changes.

This guide builds a table on one small sales list, shows how its structured references and totals behave as rows are added and filtered, how Excel sorts different kinds of values, and the difference between AutoFilter, Advanced Filter and Remove Duplicates. Every result was produced by running it in Microsoft Excel.

Creating a table and structured references

Select A1:D5 and press Ctrl + T (or Insert, Table). Excel turns the range into a Table, gives it a default name such as Table1, and you can rename it, here to Sales, in the Name Box or on the Table Design tab. A formula written outside the table can refer to a column by name instead of by cell address: =SUM(Sales[Amount]) is easier to read than =SUM($D$2:$D$5), and unlike that fixed range, it follows the table wherever it grows:

A table grows with the data

Type a value in the row directly beneath a table and Excel extends the table to include it automatically (this needs the default option Include new rows and columns in the table, under AutoCorrect Options). The SUM formula above needed no editing:

Inside the table, a formula written using the @ symbol refers to the current row. Type =[@Amount]*2 once, in any data row, and Excel fills the whole column for you, and keeps filling it as rows are added:

=[@Amount]*2 typed once in E2

The same formula appears in every row

ROWS(TableName) and its variants

A bare table name in a formula, such as Sales, means the data body only, without the header row or the total row. Sales[#All] includes everything, and Sales[Amount] is one column:

The total row, and SUBTOTAL versus SUM after filtering

Table Design, Total Row adds a row at the bottom with a dropdown per column. Choosing Sum for Amount writes =SUBTOTAL(109,[Amount]), not a plain SUM:

The reason for SUBTOTAL shows up once you filter. Function number 109 means “sum, ignoring rows hidden by a filter (or by hand)”. Filtering the table to Region = North changes the total row, but a plain SUM(Sales[Amount]) or ROWS(Sales) written elsewhere still counts every row, filtered or not:

Dynamic arrays do not spill inside a table

A formula that would normally spill into several cells, such as SEQUENCE(3), gives #SPILL! if any of the cells it needs are part of a Table. Here the table was extended to include column E, and SEQUENCE(3) was entered in E2:

The same formula spills without trouble in a cell that is not part of the table, even right next to it:

Convert to Range

Table Design, Convert to Range turns the table back into a plain range, keeping the formatting but removing the filter buttons and the table name. Formulas that used structured references are rewritten to ordinary cell references automatically:

After Convert to Range, F1 still shows 1,700

The formula is rewritten to an ordinary range

Sorting: what stays together, and the sort order

Sorting a table (or any range with Data, Sort) by one column moves the whole row, so a row’s other values stay matched to it. Sorting the sales table by Amount, descending, keeps each date, region and product with its own amount:

When a column holds a mix of types, Excel sorts them in a fixed order: numbers, then text, then logical values, then errors, then blank cells last (blanks sort last whichever direction you choose). One column with 5, Pear, TRUE, an error, an empty cell, -3, Apple and FALSE, sorted both ways:

Text that looks like a number but is stored as text sorts alphabetically, not numerically, wherever it appears among other text:

AutoFilter: deleting visible rows, and going stale

AutoFilter (Data, Filter, or the dropdown arrows a Table adds automatically) hides rows instead of removing them. Selecting the filtered rows and deleting them affects only what is visible, which is a safe way to delete a filtered subset. Here a filter to Region = North was applied, the visible rows were selected with Go To Special, Visible Cells Only (Alt + ; is the shortcut) and deleted, and the filter was then cleared to reveal what is left:

A filter does not refresh itself. If you edit a cell so it now matches or no longer matches the criteria, the row’s hidden state does not change until you filter again (the Data, Reapply command, or clicking the dropdown and choosing OK again):

Advanced Filter: AND versus OR

Advanced Filter (Data, Advanced) uses a separate criteria range instead of dropdowns, and it is the tool for combining conditions with OR, which AutoFilter on a single column cannot do across columns. The rule for its criteria range: conditions on the same row are AND, conditions on different rows are OR. To copy the results elsewhere, choose “Copy to another location” (this is Action 2 through COM automation; Action 1 filters the list in place).

Remove Duplicates

Data, Remove Duplicates deletes every repeat of a row, keeping the first occurrence. Matching is not case sensitive, but it is sensitive to spaces, since a trailing space makes a different piece of text. Column A holds Asha, asha, Ravi, “Asha ” (with a trailing space) and Ravi:

Run TRIM on the column first if trailing or doubled spaces should count as the same value.

Duplicate and blank headers

Table headers must be unique text. If you turn a range with a duplicate or blank header into a table, Excel renames them for you rather than refusing:

Common mistakes

Mistake 1: expecting a plain range formula to grow

=SUM($D$2:$D$5) stays fixed at those five rows. Only a Table with a structured reference (Sales[Amount]) grows automatically. A whole-column reference such as SUM(D:D) also grows, at the cost of including anything else typed lower in the column.

Mistake 2: assuming SUM respects a filter

It does not. Use the table’s total row, or SUBTOTAL(109, range) or AGGREGATE(9, 5, range) for a total of only the visible rows.

Mistake 3: forgetting to reapply a filter after editing

A row that now matches the criteria stays hidden, and a row that no longer matches stays visible, until you filter again.

Mistake 4: sorting only part of the data

Selecting one column and sorting it, instead of the whole table, scrambles rows: each cell moves independently of its neighbours. Always select the whole range, or better, sort a proper Table, where Excel sorts full rows automatically.

Cheat sheet

Try it yourself

Work out each answer first, then open the solution.

1. A table named Sales holds four rows and F1 holds =SUM(Sales[Amount]). You type a new row directly under the table. Does F1 need to be edited?

Show solution

No. The table grows to include the new row automatically, and the formula follows it. Typing 900 in the new row’s Amount cell changes F1 to 2600, as shown earlier.

2. Sort the values 72, Absent, 85, a blank cell and 64 in one column, ascending. What order do you get?

Show solution

	A
1	Score
2	64
3	72
4	85
5	Absent
6

Numbers first, smallest to largest, then the text Absent, and the blank cell last.

3. A table’s total row uses SUBTOTAL(109, [Amount]). You filter the table and then write =SUM(TableName[Amount]) in a cell outside the table. Does it match the total row?

Show solution

No. The total row ignores the hidden rows, but a plain SUM of the same column, written elsewhere, adds every row whether it is filtered out or not.

4. You need every row where Region is North or Amount is at least 500. Which Excel feature can do this in one step, and how do you lay out the criteria?

Show solution

Advanced Filter, with the two conditions on different rows of the criteria range (one row for Region = North, a separate row with only the Amount condition filled in). Conditions on the same row are AND instead.

Frequently asked questions

What is an Excel Table?

A range converted with Ctrl + T into a structured object with a name, filter buttons, banded formatting, and formulas that use column names instead of cell references. It grows automatically as rows are added.

What is a structured reference in Excel?

A formula reference that uses table and column names, such as Sales[Amount] or [@Amount] for the current row, instead of cell addresses like D2:D5.

Why does my table total not match SUM of the same column?

The table’s total row uses SUBTOTAL, which ignores rows hidden by a filter. A plain SUM formula elsewhere still adds every row.

How does Excel sort mixed data types?

Ascending order is numbers, then text, then logical values (FALSE before TRUE), then error values, with blank cells always last. Descending order reverses everything except the blanks, which still sort last.

Why does a filtered row not update automatically when I change its value?

AutoFilter checks the criteria at the moment you apply the filter. An edit afterwards does not re-run the check. Use Data, Reapply, or filter again.

What is the difference between AutoFilter and Advanced Filter?

AutoFilter uses dropdown buttons per column and combines conditions within one column with OR and across columns with AND. Advanced Filter uses a separate criteria range and can combine any conditions with AND (same row) or OR (different rows), and can copy the results elsewhere.

Does Remove Duplicates keep the first or the last copy?

The first one it finds, reading down the column. It ignores upper and lower case but treats a trailing or leading space as a real difference.

Can two columns in a table have the same header?

No, Excel renames the second one automatically, for example turning a second Amount into Amount2, and a blank header into a name such as Column3.