• 6 hours
  • Easy

Free online content available in this course.

course.header.alt.is_video

course.header.alt.is_certifying

Got it!

Last updated on 9/1/22

Use Filtering and Sorting Features

Use filtering and sorting features
Use filtering and sorting features

In this chapter, we’re going to cover two Excel features that help you to work more easily with large files with many rows and columns: filter and sort.

Filtering

How Filtering Works

When you filter your data, you’ll only see cells that meet the filter criteria, but the rest of the data won’t be deleted from the workbook, it will just be hidden.

Before we look at how to create a filter, here’s what it looks like.

Here’s a workbook that only displays salary data for people with Junior in the seniority column (E):

Table with a filter on seniority
Table with a filter on seniority

You’ve probably noticed a couple of things:

  • The top row of this list with the column headings has a little downward arrow in the lower right corner. This means that filtering is currently active for this list.

  • The row numbers appear in blue and the funnel symbol in the Seniority column tells us that the list is currently filtered.

Now that you've seen what it looks like, let’s find out how to do it!

Create a Filter

To create a filter, follow these steps:

  • Select any cell in the list.

  • Click on the Home tab.

  • In the Editing group, click on the Sort and Filter icon.

  • Click on Filter.

  • You’ll see that an arrow will appear on each column heading. 

  • Click on the arrow on the “Seniority” column, and you’ll see this:

The filter is activated by clicking on the arrow on the “Seniority” column
The filter is activated by clicking on the arrow on the “Seniority” column

In the list that appears next to the Seniority column, you can:

  • “Select All” (by default), which means that all the data will be displayed and no filter is active.

  • check one or more levels of seniority that you want to be displayed.

  • check “Blanks” to only display cells with no data.

Use the Different Filter Rules

The three basic filter rules are number, text and color.

There are many filtering options:

  • Text filters, such as the one we used in our example.

  • Number filters, to display one or more value(s).

  • Date filters, to filter based on year.

  • From the 2016 version of Excel onwards, you can also filter by color.

Let’s take one last example for this chapter.

Let’s imagine that in your list of employees, you've colored the temporary employees or contractors in orange, and employees with permanent contracts in blue. If you activate the filter in the Last Name column, you’ll see this:

Filter by color
Filter by color

You can then select the color of the cells you want to display, which will hide the others.

Combine Several Filters

You can combine filters by filtering on several columns.

For example, if you only want to display employees with a seniority of “Skilled Worker” and who are working on a temporary basis, you need to:

  • Filter the “Seniority” column and select only “Skilled Worker.”

  • Filter the “Contract” column and select only “Contractor.”

You can combine as many filters as needed.

To turn off filtering:

  • Click on the Home tab.

  • In the Editing group, click on the Sort and Filter icon.

  • Click again on the Filter icon.

If you just want to reset all the filters:

  • Click on the Home tab.

  • In the Editing group, click on the Sort and Filter icon.

  • Click on the Clear icon.

Sorting

Before sorting the data, the cells are arranged in the order they were originally entered. The “sort” function allows you to change this, and works like this:

  • Select a cell in the column you want to sort by.

  • Click on the Home tab.

  • In the Editing group, click on the Sort and Filter icon.

Based on the type of data held in the column, you can sort:

  • from A to Z (Sort A to Z) for text fields.

  • from oldest (Sort Oldest to Newest) for date fields.

  • from smallest to largest (Sort Smallest to Largest) for numeric fields.

Data sorted by “Start Date” (column F) from oldest to newest.
Data sorted by “Start Date” (column F) from oldest to newest

Watch the Video Tutorial!

Watch a step-by-step recap of everything described in this chapter in the tutorial below:

Let’s Recap!

  • Filtering enables you to display data from a list based on one or more criteria.

  • Data that doesn't meet the criteria is hidden.

  • Sorting data arranges it into a specific order.

  • You can sort using text, dates or numbers.

In this second part, you’ve seen how to set up the page layout for an Excel file, especially when you have lots of data. Now, let’s go and put what you’ve learned into practice in the next chapter.

Ever considered an OpenClassrooms diploma?
  • Up to 100% of your training program funded
  • Flexible start date
  • Career-focused projects
  • Individual mentoring
Find the training program and funding option that suits you best
Example of certificate of achievement
Example of certificate of achievement