Sort and filter

Nobody reads an orders table row by row. People come to it with questions. Which three orders are the biggest? How many orders came from Boston, and how much are they worth? Searching by eye takes a while, and it's easy to miss something. Sorting answers the first question in a couple of clicks, and a filter answers the second.

Sorting

Sorting arranges rows by the values in one column. Click any cell in that column, then on the Data tab click Sort Ascending or Sort Descending.

When you sort, each row moves as a whole: the order number, city and manager stay next to their amount, and the headers stay on top. After sorting in descending order, the top three rows answer the first question: orders 1007, 1001 and 1003.

In ascending order, numbers go from smallest to largest and text goes alphabetically, from A to Z. Descending order is the reverse.

You don't need to select the whole column, one cell is enough: Excel finds the edges of the table by itself. It treats rows that go one after another as a table. If there's an empty row in the middle, Excel sorts only the part where the selected cell is. So it's best not to leave empty rows inside a table.

The original order

Sorting really rearranges the rows, and there's no "put it back" button. Right after sorting, Ctrl + Z undoes it (⌘ + Z on a Mac).

If it's too late for that, sort by the column that holds the original order. In the orders table, that's the Order column: sort it in ascending order, and the orders line up from 1001 to 1010 again.

Filter

A filter doesn't rearrange or delete anything. It hides the rows you don't need, and they come back as soon as the filter is cleared.

To turn on a filter, click a cell in the table and on the Data tab click Filter. A button with an arrow appears in every header. Click the arrow of the column you need, leave only the values you want checked and click OK.

In the sheet below, only the orders from Boston are already left:

It's easy to tell that a filter is on:

• the row numbers turn blue and skip some numbers;
• the button in the City header shows a funnel instead of an arrow;
• below the sheet, Excel says how many rows are left: "4 of 10 records found".

The Sum in the status bar counts only the visible rows. It shows $27,800 for the four Boston orders, and that answers the second question.

To clear the filter from one column, click its arrow and choose Clear Filter. The Filter button on the Data tab removes the filter from the whole table: the arrows disappear, and all the rows come back.

A condition for numbers

Checkboxes are handy for text, like picking the cities or managers you need. For numbers, a condition works better, for example "is greater than 5000". To set it, click the arrow in the Amount header, choose Number Filters → Greater Than… and type 5000.

The difference shows up when new data is added to the table. Below, the filter is already set up with checkboxes. Add a new large order to the table, and then replace the checkboxes with a condition.

Filters in different columns add up. If you keep Boston in the City column and set "is greater than 5000" in the Amount column, only the large orders from Boston stay on the sheet.

A filter that's switched on is easy to forget. If some rows seem to be missing from a table, look at the row numbers on the left: blue numbers with gaps mean the filter is still on. Clear it with the Filter button on the Data tab, and all the rows come back.