Sort & Filter

Real datasets are rarely in the order you need them. You might have 500 rows of sales transactions mixed across regions, dates, and products — and you need to find the top performers, isolate one region, or sort everything by date. Sort and Filter are the tools that let you reorganise and slice your data instantly without changing or deleting anything. They are two of the most-used features in all of Excel.

Sorting Data

Sorting rearranges the rows in your data based on the values in one or more columns. The rows stay together — the whole row moves, not just the column you are sorting by. So if you sort a sales table by region, every row shifts so that all the North rows are grouped together, all the South rows together, and so on — with each row's sales figures, dates, and other data still attached correctly.

The quickest way to sort is to click anywhere inside the column you want to sort by, then use the sort buttons in the Data tab or the Home tab → Sort & Filter dropdown:

Single-Column Sort

Let's start with a sales dataset and sort it by Total Sales, highest to lowest, so the best-performing rows rise to the top. Here is the data before sorting:

Click anywhere in the Total column → Data tab → Z→A (largest to smallest).

Notice that the entire row moved — Priya's Q1 and Q2 figures stayed attached to her name. The data is intact, just reordered.

Multi-Level Sort — Custom Sort

Sometimes a single sort level is not enough. Imagine you want to sort first by Region (A to Z), and then within each region, sort by Total Sales (highest to lowest). That requires two sort levels, which you set up using the Custom Sort dialog.

Go to Data → Sort to open it. You can add as many sort levels as you need using the Add Level button. The order of the levels matters — Excel sorts by the first level first, then breaks ties using the second level, and so on.

Introducing AutoFilter

Sorting changes the order of your data. Filtering is different — it hides rows that do not match your criteria. The data is still there, just temporarily invisible, and you can bring it back anytime by clearing the filter.

The easiest way to enable filtering is to click anywhere in your data and press Ctrl + Shift + L, or go to Data → Filter. This adds a small dropdown arrow to every header cell. Click an arrow to open that column's filter options.

Using Filter Dropdowns

When you click a filter dropdown arrow, you see a list of all the unique values in that column with checkboxes. Unticking a value hides all rows containing it. You can tick and untick as many values as you like. A small funnel icon appears on the dropdown arrow to show that a filter is active on that column.

Let's say we click the Region dropdown and untick everything except North. Only the North rows are visible — rows 2 and 5 in the original data. The row numbers in Excel turn blue to signal that rows are hidden, not deleted.

To clear a filter from one column, click its dropdown and choose Clear Filter From "Region". To clear all filters at once, go to Data → Clear. To turn off AutoFilter completely and remove all the dropdown arrows, press Ctrl + Shift + L again.

Number Filters and Text Filters

The checkbox list works well for text columns with a small number of unique values. But when a column contains numbers or dates, the dropdown also offers Number Filters (or Date Filters) with more powerful options — like showing only rows where Total is greater than 100,000, or between 50,000 and 120,000.

Filtering Across Multiple Columns

You can apply filters on multiple columns at the same time — the filters work together as AND conditions. So if you filter Region to show only North AND filter Total to show only values above 100,000, you see only rows that satisfy both conditions simultaneously.

Filter 1: Region = North
Filter 2: Total > 100,000

Result:   Only Priya (North, $119,000) is visible
          Sarah (North, $93,000) is hidden — Total does not exceed 100,000

The SUBTOTAL Function with Filters

Here is something important to know. If you have a SUM formula at the bottom of a column and you apply a filter, the SUM still includes the hidden rows. It does not adjust to show only the visible rows.

The solution is the SUBTOTAL function. Unlike SUM, SUBTOTAL automatically ignores hidden rows — so when you filter your data, the total at the bottom updates to reflect only what is visible.

=SUBTOTAL(9, B2:B100)     9 = SUM of visible rows only
=SUBTOTAL(1, B2:B100)     1 = AVERAGE of visible rows only
=SUBTOTAL(2, B2:B100)     2 = COUNT of visible rows only

When you use Format as Table (from Lesson 6), Excel automatically adds a SUBTOTAL-powered Total Row for you. That is one more reason to use Excel Tables for your data.

Sort and Filter Shortcuts

Next up — Find & Replace, where you will learn how to locate any value in your workbook instantly and make bulk changes across thousands of cells in seconds.