How to Use the Filter and Sort Data Function in Microsoft Excel

Maria is a Content Writer with a keen interest in Technology and Productivity Tools. Her articles can be found on sites such as Onlinetivity and Delesign. Outside of work, you can find her mini-blogging about her life on social media.

Afam has been a content writer with Guiding Tech Media since August 2023. However, his experience in tech writing dates back to 2018, when he worked as a freelance writer for Make Tech Easier. Over the years, he has grown a reputation for publishing quality guides, reviews, tips, and explainer articles. His work is featured on top websites, including Technical Ustad, Windows Report, and Next of Windows. He has training as a Microsoft Certified Professional and has covered more Microsoft-related guides on these platforms.

Things to Note About Using the Filter and Sort Function

The Filter function allows you to extract data from a data set that fits your criteria. It is great fordata analysis. You can use the Filter function with a single criteria or multiple criteria. The Filter function looks like the below:

The Sort function can also be used to sort the content of a range or array in the order you specify. The Sort function looks like the below:

How to Use the Filter Function

When you filter, your functions depend on whether you need a single or multiple criteria in your filters. We explore both below.

Single Criteria

In a situation where you need to filter data based on just one criteria, here’s how you can do so with the Filter function:

Step 1:Open the Microsoft Excel app or workbook.

Step 2:Type the function below and theTabbutton in an empty cell.

Step 3:Select the array with the dataset you would like to filter. In our example, we are selecting cellsB3toI20, which contains the information we want to filter for. So, we have:

Step 4:Enter the criteria you want to filter in theincludesection. In our example, we are filtering for valueHJwithin columnG. So, we have:

Note:You can directly include the criteria you are filtering for in the formula like we have done above or type it in a cell and refer to it within the formula. Typing it in a separate cell ensures you only have to edit that one cell when changing the criteria. However, if you write it directly in the formula, you must edit it to update the criteria.

Step 5:Enter the value you want Excel to return if any value in the array does not meet your criteria. In our example, we want Excel to returnN/Aif our criteria are unmet. You can also ask Excel to return an empty string using“”. So, we have:

Step 6:TapEnterfor Excel to return results based on your set criteria.

Multiple Criteria

You can combine multiple criteria to filter a dataset using the Filter function. To combine multiple criteria, you can use different Excel operators, such as (*) for AND with the Function formula. Here’s how to do so.

Step 1:Type the function below and theTabbutton in an empty cell.

Step 2:Select the array with the dataset you want to filter. In our example, we select cellsB3toI20, which contains the information we want to filter. So, we have:

Step 3:Enter the criteria you want to filter in the include section. In our example, we filter for valueHJwithin columnG, and in columnC, we filter forHBN. So, we have:

Step 4:Enter the value you want Excel to return if any value in the array does not meet your criteria. In our example, we want Excel to returnN/Aif our criteria are unmet.

Step 5:TapEnterfor Excel to return results based on your set criteria.

How to Use the Sort Function

When you sort, your functions depend on whether you need to sort through a single or multiple columns. We explore both below.

Single Column

Step 1:In an empty cell, type the function below and tap theTabbutton.

Step 2:Select the array with the dataset you want to sort. In our example, we select cellsC3toC20, which contain the information we want to sort. So, we have:

Step 3:Enter the row or column number to sort by. In our example, we only have one column. So, we have:

Step 4:Enter the order by which you want the data sorted. In our example, we are sorting in ascending order. So, we have:

Step 5:TapEnterfor Excel to return results based on your sort criteria.

Multiple Columns

If you need to organize your data more comprehensively, you may need to sort it by multiple columns. Below is how to do this.

Step 1:In an empty cell, type the function below and tap theTabbutton.

Step 2:Pick your array with the sort dataset. To sort through multiple columns, we are using a wider array. Below, we select cellsB3toD20, So, we have:

Step 3:Enter the column number to sort by. This corresponds to the position of the column within the selected array. So, if sorting by the second column in your range, your column number is2. So, we have:

Step 4:Enter the order by which you want the data sorted. In our example, we are sorting in ascending order. So, we have:

Step 5:Optionally, to include additional columns, add theSORTBYfunction nested within yourSORTFunction. So, to sort by the third column in descending order after sorting by the second column, include:

Step 6: Your final formula would look as shown below. HitEnter.

Was this helpful?

Last updated on 17 July, 2024

Leave a ReplyCancel reply

Your email address will not be published.Required fields are marked*

Name*

Email*

Comment*

Δ

The article above may contain affiliate links which help support Guiding Tech. The content remains unbiased and authentic and will never affect our editorial integrity.

More in Internet and Social

4 Ways to Fix Instagram Story Not Uploading on iPhone and Android

4 Ways to Refresh Webpages in Safari on iPhone, iPad, and Mac

JOIN THE NEWSLETTER

Get Guiding Tech articles delivered to your inbox.

© 2024 Guiding Tech Media. All Rights Reserved.