Sorting, filtering and Excel Tables

Intermediate

Introduction

Lists are the heart of most workbooks: customers, orders, transactions, stock. Sorting puts them in a useful order, filtering shows just the rows you care about, and Excel Tables turn a plain range into a structured list that keeps itself tidy as it grows. Used together, they make a large list easy to explore without any formulas at all.

How it works

Sorting. Click any single cell in the list and choose Sort A to Z or Sort Z to A on the Data tab (or Home, Sort & Filter). Excel detects the whole list and the header row and moves complete rows. For several levels, such as by region and then by sales from largest to smallest, use Data, Sort and add levels. If you select only one column before sorting, Excel warns you; choose "Expand the selection", or that column would be sorted alone and break every row.

Filtering. Turn on AutoFilter with Data, Filter (Ctrl+Shift+L on Windows, Command+Shift+F on Mac). Each header gets a drop-down button where you can tick values, or use number, text and date filters such as "Greater Than" or "This Month". Filtering hides the other rows; it does not delete them. A normal SUM still adds hidden rows, while =SUBTOTAL(109,C2:C100) adds only the visible ones. Clear filters with Data, Clear.

Removing duplicates. Data, Remove Duplicates deletes rows that repeat in the columns you choose, keeping the first. It really deletes them, so work on a copy or check first with conditional formatting.

Excel Tables. Select a cell in the list and press Ctrl+T (Windows) or use Insert, Table, and confirm that the list has headers. The range becomes a Table with banded rows and filter buttons. Its main benefits:

  • It grows automatically when you type in the row below it, and charts, PivotTables and formulas based on it include the new rows.
  • A formula typed in a new column is copied to every row automatically.
  • It has a name (rename it on the Table Design tab, for example Sales) and structured references: =SUM(Sales[Amount]) adds the Amount column, and inside the table =[@Quantity]*[@Price] multiplies the quantity and price of the same row.
  • An optional total row shows a sum, average or count per column, and automatically uses SUBTOTAL so filtered rows are left out.

Example

A sales list in A1:C5 has the headers Region, Rep and Amount.

ABC
1RegionRepAmount
2NorthRina500
3SouthArif300
4NorthZoya700
5SouthOmar200

Line by line

  1. Click A1 and press Ctrl+T, confirm "My table has headers", then rename the table Sales on the Table Design tab.
  2. Tick Total Row on the Table Design tab; the total under Amount shows a sum.
  3. Open the Region filter and show only North.
  4. Sort the visible rows by Amount, largest to smallest, from the Amount filter button.
  5. Anywhere outside the table, type =SUM(Sales[Amount]).

Expected result

After step 3 only Rina and Zoya are visible and the total row shows 1200. After step 4 Zoya (700) is above Rina (500). The formula from step 5 shows 1700, because SUM includes the hidden South rows while the total row does not.

In the real world

Analysts turn every raw list into a Table first, because reports built on Tables keep working when next month's data is pasted underneath. Sorting and filtering are how most people answer quick questions, such as the largest orders this week, before deciding whether a formula or a PivotTable is needed.

Common mistakes

  • Sorting one selected column on its own and scrambling the rows.
  • Blank rows or columns inside a list, which make Excel see two lists and sort or filter only part.
  • Forgetting a filter is on and reporting totals of hidden rows, or missing rows that are hidden.
  • Removing duplicates on the original data without a backup.

Best practices

  • Keep lists free of blank rows and columns, with one header row.
  • Convert lists to Tables and give them meaningful names.
  • Use SUBTOTAL or the total row when you need totals that follow filters.

Keyboard shortcuts

ActionWindowsMac
Turn filters on or offCtrl+Shift+LCommand+Shift+F
Open a filter drop-downAlt+Down ArrowOption+Down Arrow
Create a TableCtrl+T—
Select the current region around the active cellCtrl+ACommand+A
Select the whole columnCtrl+SpaceControl+Space

Summary

Sorting reorders whole rows by one or more columns, filtering hides rows that do not meet conditions, and Remove Duplicates deletes repeated rows. Excel Tables grow automatically, fill formulas down, support structured references such as Sales[Amount] and offer a total row that respects filters.

Quick check

1. What happens to rows hidden by a filter?
2. Which formula adds only the visible rows of C2:C100?
3. What does Sales[Amount] refer to?
4. Why add new data in the row directly below a Table?

Key takeaways

  • Select one cell in the list before sorting, so Excel sorts whole rows together.
  • Filtering hides rows; SUBTOTAL and AGGREGATE can total only the visible ones.
  • Excel Tables grow with new rows, copy formulas down automatically and make formulas readable.
My notes

Write down what you want to remember from this lesson. Notes are saved on this device as you type, and appear on your dashboard.