Filter and sort

Excel tables can quickly become huge and confusing. Especially when quickly searching for specific data from a large table, frustration quickly arises. To keep the overview, Excel offers the possibility to sort and filter the data automatically. This way, data can be sorted alphabetically from A to Z or rows of numbers can be realigned according to size. This tutorial shows how we can filter and sort the data correctly, avoiding the most common mistakes.

In the second section, we show how to filter unneeded data from the list, thus temporarily hiding it. This way we quickly evaluate even the most unmanageable table deserts.

Sort data in Excel

For this example, we will edit a large employee table that contains first names, last names and employee numbers. In our example, we will select the Last Name column, which will determine the sort order.

Tip: Empty columns and cells can optionally be removed even before sorting. To do this, we right-click on the corresponding column or row header and select Delete cells. If we sort a table with empty cells, the empty columns or rows end up at the top or bottom, depending on the sorting, and can then be deleted as a block (which is faster).

We click the “Sort and Filter” button in the Edit group of the Start tab.

Tip: Sorting and filtering options are located in the Start tab and also in Data.

Then we select the option Sort from A to Z (Ascending).

If a sorting warning should come, we click on “Extend marking”.

If a sorting warning should come, we click on “Extend marking”.

We can see how the table has been realigned. All other data belonging to the row moves with it and is sorted by last name.

Since many employees share the same last name, we now want to additionally sort by first name. We select all columns in the next step.

We click the “Sort and Filter” button on the Start tab in the Edit group.

Next we select “Custom sort”.

The Sort dialog window opens. We select the Last name column in the left cell (Sort by), with the order from A to Z.

Now we click on the Add Layer button.

In the new line “Then by” we select the column First name and confirm our input with OK.

Excel now sorts the data as follows: Sort by last name (from A to Z) then by first name (from A to Z). That means concretely: Only if two or more persons have the same last name, Excel sorts within this group with the same last names on the basis of the first names. If persons do not have the same last name, the first name will not be considered for sorting. If there are persons who have the same first and last name, a third level can be used to determine the sorting even more finely.

And this is the result!

Filter data with AutoFilter in Excel tables

This example shows us how to hide unneeded records and see only relevant information. The larger and more confusing tables become, the more important Excel’s filter functions become.

In our example we see people with first name, last name and the city where they live. 1.

We select all relevant columns.

We click on our table and select the Filter button in the Data tab in the Sort and Filter group.

All column headers now have a gray box with a list arrow. We click on the arrow of the Location column.

A menu opens where all values from the column are listed.

We click once on the check mark at “Select all” so that it disappears and all other check marks are removed as well (as in the following screen shot)

Now we check the value (or values) that should not be hidden – in our example the city Dresden – and click OK.

Now our table shows only the people who come from the place Dresden.