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

Microsoft Excel is one of the most popular data visualization and analysis tools. Examples of its analysis features include the Filter and Sort tools in the Data tab of the Excel Ribbon. A simple click of the Filter or Sort options will quickly extract and sort out the specific dataset you need. However, one disadvantage of using the Filter button is that it hides other data sets in the worksheet to highlight your selections.

One way to ensure all datasets remain visible on your worksheet, whilst extracting a range of data based on criteria you define is by using the Filter function. The Filter function can also be combined with a Sort function. Here’s how to use the filter and sort data functions in Microsoft Excel.

Things to Note About Using the Filter and Sort Function

As previously mentioned, the Filter function allows you to extract data from a data set that fits your criteria. You can use the Filter function with a single criteria or multiple criteria. The Filter function looks like the below:

=FILTER(array,include,[if_empty])

• Array (required): this refers to the range or data set to be filtered. In most cases, it is usually a table within the Excel worksheet, but it can also be a row or column with values.
• Include (required): this specifies the data to be extracted
• If empty (optional): this is optional and is the result to be shown if no items exist for the criteria specified.

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:

=SORT(array,[sort_index],[sort_order],[by_col])

• Array (required): this refers to the range or data set to be filtered. In most cases, it is usually a table within the Excel worksheet, but it can also be a row or column with values.
• sort_index (optional): this specifies the number of the row or column to sort by
• sort_order (optional): this specifies the number for the desired sorting order. To sort by ascending order, use 1, and for descending order, use -1.
• by_col (optional): this specifies the desired sort direction by row or column.

How to Use the Filter Function for 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: From your PC’s Start menu or Taskbar, click the Microsoft Excel app or workbook to open it.

Step 2: In an empty cell, type out =FILTER and tap the Tab button.

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

=FILTER(B3:I20,

Step 4: Enter the criteria you want to use to filter in the ‘include’ section. In our example, we are filtering for value HJ within column G. So, we have:

=FILTER(B3:I20,G3:G20=”HJ”,

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 that you only have to edit that one cell when you need to make changes to 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 your criteria are not met by any value in the array. In our example, we want Excel to return N/A if our criteria are not met. You can also ask Excel to return an empty string by using (“”). So, we have:

=FILTER(B3:I20,G3:G20=”HJ”,”N/A”) or =FILTER(B3:I20,G3:G20=”HJ”,””)

Step 6: Tap Enter for Excel to return results based on the criteria you set.

How to Use the Filter Function for 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: In an empty cell, type out =FILTER and tap the Tab button.

Step 2: Select the array with the dataset you would like to filter. In our example, we are selecting cells B3 to I20, which contains the information we want filtered. So, we have:

=FILTER(B3:I20,

Step 3: Enter the criteria you want to use to filter in the ‘include’ section. In our example, within column G, we are filtering for value HJ, AND in column C, we are filtering for HBN. So, we have:

=FILTER(B3:I20,(G3:G20=”HJ”)*(C3:C20=”HBN”)

Step 4: Enter the value you want Excel to return if your criteria are not met by any value in the array. In our example, we want Excel to return “N/A” if our criteria are not met.

=FILTER(B3:I20,(G3:G20=”HJ”)*(C3:C20=”HBN”),”N/A”)

Step 5: Tap Enter for Excel to return results based on the criteria you set.

How to Use the Sort Function

Just like the Filter function, you can use a Sort Function to sort data in a dataset. Here’s how to do so:

Step 1: In an empty cell, type out =SORT and tap the Tab button.

Step 2: Select the array with the dataset you would like to sort. In our example, we are selecting cells C3 to C20, which contains the information we want to sort. So, we have:

=SORT(B3:B20,

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

=SORT(B3:B20,1

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

=SORT(B3:B20,1,1)

Step 5: Tap Enter for Excel to return results based on your sort criteria.

Sorting Data in Excel with a Custom List

In Microsoft Excel, you can sort data in other forms, not just by ascending or descending order. For example, you can sort data according to a color type or department list. However, to do so, you will need to use the Excel Sort tool and create a custom list. Regardless, we hope this article helped you understand the Filter and Sort functions better.