How to Sort and Filter Data in Excel

Excel is one of the most powerful tools for organising, analysing and presenting information. When working with large datasets, it can be hard to make sense of the numbers — but Excel’s Sort and Filter tools make it quick and easy to find what you need.

In this guide, you’ll learn how to sort and filter data step by step, along with tips to avoid common mistakes. These skills are essential for anyone working with spreadsheets, from beginners to advanced users.

👉 If you’re new to Excel and want a guided introduction to these tools, our Microsoft Excel Introduction Course covers sorting and filtering as part of its core content.

Why Sorting and Filtering Matter

• Sorting lets you arrange your data in a logical order (e.g., alphabetically by name, or numerically by sales figures).
• Filtering allows you to show only the rows that meet certain conditions (e.g., all sales from January, or customers in London).

Together, these tools make it much easier to explore data, prepare reports, and make decisions.

Understanding Excel Tables and Ranges

Before using Sort and Filter effectively, it’s important to structure your data properly.

• Use column headings – each column should have a descriptive name.
• Keep data in a continuous block – avoid blank rows or columns inside your dataset.
• Turn ranges into Tables – by pressing Ctrl + T, Excel creates a dynamic table that makes sorting and filtering more robust.

👉 Converting data into Tables is introduced in the Excel Intermediate Course, where you’ll also learn advanced techniques like slicers for filtering.

How to Sort Data in Excel

Basic Sorting

1. Click anywhere in the column you want to sort.
2. On the Data tab, choose Sort A to Z (ascending) or Sort Z to A (descending).

• For text: A to Z = alphabetical order.
• For numbers: smallest to largest.
• For dates: oldest to newest.

Custom Sorting

1. Select any cell in your data.
2. Go to Data > Sort.
3. Add one or more levels — for example:
   • Sort by Region (A to Z).
   • Then by Sales (largest to smallest).

This creates a structured sort across multiple columns.

Example

A sales list with Region, Salesperson, and Sales Amount can be sorted to show the highest sales per region.

👉 Multi-level sorting is taught in the Excel Intermediate Course.

How to Filter Data in Excel

Applying a Filter

1. Select your data range.
2. Go to Data > Filter (or press Ctrl + Shift + L).
3. Drop-down arrows will appear in each column heading.

Using Filters

• Text filters – e.g., show only rows where “City = London”.
• Number filters – e.g., show all values greater than 1,000.
• Date filters – e.g., show only January 2025.
• Custom filters – combine rules like “greater than 500 AND less than 1,000”.

Example

If you have a list of orders, you can filter by product, by date range, or by sales rep.

👉 Filters are covered in depth in the Excel Introduction Course, with more complex filters and criteria covered in Excel Intermediate.

Advanced Filtering Techniques

Using the Search Box

Filter drop-downs include a search field — perfect for quickly narrowing results without scrolling.

Colour Filters

You can filter by cell colour or font colour if formatting has been applied.

Top 10 Filter

Find the top 10 (or any number) of items in a column, such as the highest 10 sales.

👉 Advanced filtering options like colour filters and Top 10 are included in the Excel Intermediate Course.

Sorting and Filtering in Tables

When data is formatted as a Table, filters and sort options are automatically applied. Tables also expand automatically as you add data, so you don’t need to reapply filters.

👉 Working with Tables is taught in the Excel Intermediate Course, with more advanced Table features explored in the Excel Advanced Course.

Common Mistakes to Avoid

• Forgetting to select the full dataset – only part of the data gets sorted.
• Leaving blank rows/columns – breaks the sort and filter range.
• Mixing data types – text in a number column causes unexpected results.
• Not converting to a Table – makes sorting/filtering less reliable.

Practical Examples

Example 1: Sorting Employee Records

Sort staff alphabetically by last name, then by department.

Example 2: Filtering Sales Data

Filter sales to show only orders from January worth more than £500.

Example 3: Top Customers

Use the “Top 10” filter to see which customers placed the largest orders.

Taking Filtering Further: Advanced Tools

As you progress beyond the basics, Excel offers more powerful ways to filter and summarise data:

• Advanced Filter – allows criteria ranges for complex filtering. (Covered in the Excel Advanced Course).
• Slicers – graphical buttons that make filtering interactive. (Introduced in the Excel Intermediate Course and expanded in Excel Advanced).
• Power Query – a specialised tool for importing, transforming, and filtering very large datasets. (Covered fully in the Excel Power Query Course).
• VBA Automation – you can write macros to apply recurring sorts and filters automatically. (Introduced in the Excel VBA Introduction Course).

When to Use Sorting vs Filtering

• Sort when you want to arrange all your data into a meaningful order.
• Filter when you want to hide irrelevant rows and focus only on what matters.
• Often, you’ll use them together — sort to arrange, then filter to refine.

Final Thoughts

Sorting and filtering are two of the most useful tools in Excel. They allow you to organise your data, find what you need, and prepare reports with ease. Whether you’re a beginner just learning the basics or an advanced user working with Power Query and VBA, mastering these tools will make you more productive.

👉 Start your journey with our Excel Introduction Course for the basics of sorting and filtering.
👉 Progress to the Excel Intermediate Course for multi-level sorts, advanced filters, and Tables.
👉 Explore the Excel Advanced Course to use Advanced Filter and Slicers effectively.
👉 For serious data transformation, try our Excel Power Query Course.
👉 If you want to automate repetitive sorting and filtering tasks, check out our Excel VBA Introduction Course.

With these courses, you’ll move beyond the basics and unlock the full potential of Excel.

‍

‍