Excel Functions: Filtering and Sorting

Summary statistics of group data generated by functions such as countif, sumifs, and maxifs reduce a group of data to a single value. However, these summary statistics do not directly identify the specific datum meeting the criteria. For example, the countif function can indicate that there are three exceptions meeting the criteria but cannot specifically identify the three exceptions. Filtering and sorting allow us to extract and view the specific data that meet the criteria while hiding those that do not.

How to filter

Assume the following dataset [you may paste it to Excel]:

Step 1: Select the data to be filtered. (This includes any cell in the dataset.)
Step 2: Click the following in sequence: Home > Sort and Filter > Filter

Step 3: Check if there are arrow buttons next to the headers of the columns.

Step 4: To filter a column, click that column’s arrow button to call out the filter. The following is the filter for the “Category” column:

Below are the functions of the different parts of Excel Auto filter:

• Basic Sorter: Performs basic sorting (e.g., alphabetical, chronological, or numerical order)
• Filter Clearer: Clears the filter and shows all the data
• Color Filter: Filters data based on visual criteria, such as font color, cell color, or icon sets. You can filter whether or not you have formatted cells, applied cell styles, or used conditional formatting.
• Search Bar: Searches by text, date and time, or numerical value
• Text Filter: Filter specifically designed for texts, such as texts starting with a letter
• Number Filter: Filter specifically designed for numbers, such as numbers above the average
• Date Filter: Filter specifically designed for date/time values, such as a date between two dates
• Data Filter: Lists all of the unique data in a given column

Filtering based on values in cells

Example: Showing sales only for the “Electronics” category

Assume the following dataset [you may paste it to Excel]:

Step 1: Clear any existing filter(s) using the Filter Clearer or the following in sequence:

•  Home > Sort and Filter > Clear

Step 2: Call the filter in the “Category” column.

Step 3: Uncheck (Select All) in the Data Filter to unselect all the data.

Step 4: Check only the box for “Electronics” to filter out only the “Electronics” category.

Search to filter

Example: Showing sales only for the “Personal Care” category

Assume the following dataset [you may paste it to Excel]:

Step 1: Clear any existing filter(s).

Step 2: Call the filter in the “Category” column.

Step 3: Type “Personal Care” in the Search Bar.

Filtering based on criteria

Example 1: Showing sales between 1/1/2020 and 7/15/2020 

Assume the following dataset [you may paste it to Excel]:

Filtering using functions

filter function (available in Office 365 or later versions)

The filter function filters a set of data and returns the filtered data based on the specified criteria. It has the following syntax:

Note: The filter function returns only the data, not the format.

Assume the following dataset [you may paste it to Excel]:

Example 1

The formula =filter(A2:D7,B2:B7=“Electronics”,“No Records”) looks at the whole dataset from range A2 through D7 and extracts data with the text “Electronics” in the range B2 through B7. If the text “Electronics” is found, the formula returns all the data of the row meeting the criterion. Otherwise, the formula returns the text “No Records.” The sales by Aaron, Eric, and Francis satisfy the criteria.

Note: The range to be filtered should not include the header of each column.

Multi-criteria filter function

Criteria can be added to the filter function using the following operators:

• Use * to simulate the and function.
• Use + to simulate the or function.

Example 1

The formula =filter(A2:D7,(B2:B7=“Electronics”)*(D2:D7>550),“No Records”) returns the sales that are both in the “Electronics” category AND over $550. If none of the data meet both criteria, the formula returns the text “No Records.” The sales by Aaron and Francis satisfy both criteria.

Example 2

The formula =filter(A2:D7,(B2:B7=“Sports”)+(D2:D7>550),“No Records”) returns the sales that are either in the “Sports” category OR over $550. If none of the data meet either criterion, the formula returns the text “No Records.” All the sales except those by Eric satisfy either criterion.

Filtering using values

unique function (available in Office 365 or later versions)

The unique function filters the unique data in the columns and returns the filtered data. It has the following syntax:

Note: The filter function returns only the data, not the format.

Assume the following dataset [you may paste it to Excel]:

Example 1

The formula =unique(A2:A16) looks at the data in the “Salesperson” column and returns all the unique names of the salespersons.

Example 2

The formula =unique(A2:B16) looks at the data in both the “Salesperson” and “Store” columns and returns all the data that are unique in both columns. For example, in both store A and B, there is a salesperson named “Betty.” The two Bettys are treated as unique because they are from different stores. Similarly, the two Carls, one from store B and the other from store C, are treated as unique.

How to sort

Basic sort

Basic sorting can be done using the Excel Auto Filter.

1. Select the data to be sorted.
2. Click the following in sequence: Home > Sort and Filter > Filter
3. Click the arrow button on the column to call out the auto filter.
4. Select whether to sort in ascending or descending order.
   • Text data (e.g., category) are sorted from A to Z (ascending).
   • Date time data (e.g., date) are sorted from Oldest to Newest (ascending).
   • Numerical data (e.g., sales) are sorted from Smallest to Largest.

Assume the following dataset [you may paste it to Excel]:

Example 1

The dataset sorted by the “Category” column in ascending order

Example 2

The dataset sorted by the “Date” column in chronological order

Multi-level sort

Assume the following dataset [you may paste it to Excel]:

Multi-level sorting can be done using the following steps:

1. Select the data to be sorted.
2. Click the following in sequence: Home > Sort and Filter > Custom Sort…
3. Select how to sort the first level (e.g., sort the “Category” column in ascending order).
4. Use the button to add a lower level and select how to sort the lower level (e.g., sort the “Date” column in ascending order).
   

Sorting using functions

sortby function (available in Officer 365 or later)

The sortby function sorts a set of data and returns the sorted data based on the specified criteria. It has the following syntax:

Note: The sort_order argument takes the number 1 to indicate sorting by ascending order and the number -1 to indicate sorting by descending order.

Assume the following dataset [you may paste it to Excel]:

Example 1:

The formula =sortby(A2:D7,B2:B7,1) takes the whole dataset from range A2 through D7 and sorts it by the range B2 through B7 (the “Category” column) in ascending order.

Example 2:

The formula =sortby(A2:D7,B2:B7,1,D2:D7,-1) takes the whole dataset from range A2 through D7, sorts it by the range B2 through B7 (the “Category” column) in ascending order, and then by the range D2 through D7 (the “Sales” column) in descending order (multi-level sort).

Example 3:

The formula =sortby(A2:A7,B2:B7,1) takes the range A2 through A7 and sorts it by the “Category” column in ascending order. Because only the range A2 through A7 is chosen, the sorted data display only the “Salesperson” column of the dataset and hide other columns.

sort function (available in Officer 365 or later)

The sort function sorts a set of data and returns the sorted data based on the specified criteria. It has the following syntax:

Note:

1. The sort_index parameter is the nth column in the array that is sorted.
2. The sort_order parameter takes the number 1 to indicate sorting in ascending order and the number -1 to indicate sorting in descending order.
3. The by_col parameter takes the value TRUE to indicate sorting by column and the value FALSE to indicate sorting by row.

The sort function differs from the sortby function in the following ways:

1. It does not allow displaying only one sorted column if the data is sorted by the other column (i.e., the array specified must contain the column to be displayed and the column to be sorted by).
2. It does not allow sorting by multiple levels.
3. It allows sorting by row (e.g., Example 3 under the “sortby” function above).

Example 1:
Assume the following dataset [you may paste it to Excel]:

The formula =sort(A2:D7,2,1,FALSE) takes the range A2 through D7, sorts it by the 2^nd column (“2”, or the “Category” column) in ascending order (“1”) by row (FALSE), and returns the sorted data.

Example 2:

Assume the following dataset [you may paste it to Excel]:
Note: This dataset is the same as in the previous example, transposed (i.e., the columns and rows are swapped).

The formula =sort(B1:G4, 4,-1,TRUE) takes the range B1 through G4, sorts it by the 4^th row (“4”, or the “Sales” row) in descending order (“-1”) by column (TRUE), and returns the sorted data.

Learning Excel Data Analytics with Gleim

At Gleim, we know learning data analytics is vital for future certified accountants. That is why we are offering this series of data analytics blogs and continually updating all of our review materials for CPA, CMA, and CIA with the necessary information you need to pass your exam the first time!

We’ll continue our weekly blog series. Check back regularly for all exam news and study tips!

Excel Lessons
Excel Basics
Excel Shortcuts
Excel Calculation Rules
Cell References
Excel Functions
Function Basics
Working with Numbers
Working with Datetime Data
Working with Text Data
Logical Testing
Summarizing a group of data
Filtering and Sorting
Lookup Functions
Tables
Pivot Tables