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.

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

A | B | C | D | |
---|---|---|---|---|

1 | Salesperson | Category | Date | Sales |

2 | Aaron | Electronics | 2020-10-18 | 1050.37 |

3 | Betty | Personal Care | 2020-02-09 | 590.47 |

4 | Carl | Sports | 2020-09-30 | 830.07 |

5 | Daisy | Sports | 2020-01-27 | 880.36 |

6 | Eric | Electronics | 2020-03-24 | 520.9 |

7 | Francis | Electronics | 2020-07-22 | 550.11 |

**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

Example: Showing sales only for the “Electronics” category

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

A | B | C | D | |
---|---|---|---|---|

1 | Salesperson | Category | Date | Sales |

2 | Aaron | Electronics | 2020-10-18 | 1050.37 |

3 | Betty | Personal Care | 2020-02-09 | 590.47 |

4 | Carl | Sports | 2020-09-30 | 830.07 |

5 | Daisy | Sports | 2020-01-27 | 880.36 |

6 | Eric | Electronics | 2020-03-24 | 520.9 |

7 | Francis | Electronics | 2020-07-22 | 550.11 |

**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.

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

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

A | B | C | D | |
---|---|---|---|---|

1 | Salesperson | Category | Date | Sales |

2 | Aaron | Electronics | 2020-10-18 | 1050.37 |

3 | Betty | Personal Care | 2020-02-09 | 590.47 |

4 | Carl | Sports | 2020-09-30 | 830.07 |

5 | Daisy | Sports | 2020-01-27 | 880.36 |

6 | Eric | Electronics | 2020-03-24 | 520.9 |

7 | Francis | Electronics | 2020-07-22 | 550.11 |

**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.

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

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

A | B | C | D | |
---|---|---|---|---|

1 | Salesperson | Category | Date | Sales |

2 | Aaron | Electronics | 2020-10-18 | 1050.37 |

3 | Betty | Personal Care | 2020-02-09 | 590.47 |

4 | Carl | Sports | 2020-09-30 | 830.07 |

5 | Daisy | Sports | 2020-01-27 | 880.36 |

6 | Eric | Electronics | 2020-03-24 | 520.9 |

7 | Francis | Electronics | 2020-07-22 | 550.11 |

**Step 1: **Clear any existing filter(s).

**Step 2: **Call the filter in the “Date” column.

**Step 3: **Call out the function list of the Date Filter and choose “Between…”

**Step 4: **Enter the criteria.

**Example 2:** Showing sales over $850

**Step 1: **Clear any existing filter(s).

**Step 2: **Call the filter in the “Sales” column.

**Step 3: **Call out the function list of the Number Filter and choose “Greater than…”

**Step 4: **Enter the criteria.

**Example 3:** Showing sales over $850 in the “Electronics” category.

**Step 1: **Clear any existing filter(s).

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

**Step 3: **Filter out the “Electronics” category in the Data Filter.

**Step 4: **Call the filter in the “Sales” column.

**Step 5: **Call out the function list of the Number Filter and choose “Greater than…”

**Step 6: **Enter the criteria

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]:

A | B | C | D | |
---|---|---|---|---|

1 | Salesperson | Category | Date | Sales |

2 | Aaron | Electronics | 2020-10-18 | 1050.37 |

3 | Betty | Personal Care | 2020-02-09 | 590.47 |

4 | Carl | Sports | 2020-09-30 | 830.07 |

5 | Daisy | Sports | 2020-01-27 | 880.36 |

6 | Eric | Electronics | 2020-03-24 | 520.9 |

7 | Francis | Electronics | 2020-07-22 | 550.11 |

**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.

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]:

A | B | C | D | |
---|---|---|---|---|

1 | Salesperson | Store | Category | Sales |

2 | Aaron | A | Grocery | 190.22 |

3 | Betty | B | Grocery | 287.22 |

4 | Carl | B | Personal Care | 201.45 |

5 | Daisy | D | Sports | 296.73 |

6 | Eric | E | Sports | 388.2 |

7 | Ben | B | Sports | 316.38 |

8 | Aaron | A | Personal Care | 256.85 |

9 | Doris | D | Electronics | 340.72 |

10 | Carl | C | Sports | 218.37 |

11 | Daisy | D | Personal Care | 232.68 |

12 | Dan | D | Electronics | 193.87 |

13 | Betty | A | Personal Care | 395.77 |

14 | Aaron | A | Grocery | 312.3 |

15 | Daisy | D | Electronics | 379.58 |

16 | Ada | A | Electronics | 346.88 |

**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.

Basic sorting can be done using the Excel Auto Filter.

- Select the data to be sorted.
- Click the following in sequence: Home > Sort and Filter > Filter
- Click the arrow button on the column to call out the auto filter.
- 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]:

A | B | C | D | |
---|---|---|---|---|

1 | Salesperson | Category | Date | Sales |

2 | Aaron | Electronics | 2020-10-18 | 1050.37 |

3 | Betty | Personal Care | 2020-02-09 | 590.47 |

4 | Carl | Sports | 2020-09-30 | 830.07 |

5 | Daisy | Sports | 2020-01-27 | 880.36 |

6 | Eric | Electronics | 2020-03-24 | 520.9 |

7 | Francis | Electronics | 2020-07-22 | 550.11 |

Example 1

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

**Example 2**

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

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

A | B | C | D | |
---|---|---|---|---|

1 | Salesperson | Category | Date | Sales |

2 | Aaron | Electronics | 2020-10-18 | 1050.37 |

3 | Betty | Personal Care | 2020-02-09 | 590.47 |

4 | Carl | Sports | 2020-09-30 | 830.07 |

5 | Daisy | Sports | 2020-01-27 | 880.36 |

6 | Eric | Electronics | 2020-03-24 | 520.9 |

7 | Francis | Electronics | 2020-07-22 | 550.11 |

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

- Select the data to be sorted.
- Click the following in sequence: Home > Sort and Filter > Custom Sort…
- Select how to sort the first level (e.g., sort the “Category” column in ascending order).
- 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).

**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]:

A | B | C | D | |
---|---|---|---|---|

1 | Salesperson | Category | Date | Sales |

2 | Aaron | Electronics | 2020-10-18 | 1050.37 |

3 | Betty | Personal Care | 2020-02-09 | 590.47 |

4 | Carl | Sports | 2020-09-30 | 830.07 |

5 | Daisy | Sports | 2020-01-27 | 880.36 |

6 | Eric | Electronics | 2020-03-24 | 520.9 |

7 | Francis | Electronics | 2020-07-22 | 550.11 |

**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:**

- The sort_index parameter is the
*n*th column in the array that is sorted. - The sort_order parameter takes the number 1 to indicate sorting in ascending order and the number -1 to indicate sorting in descending order.
- 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:**

- 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).
- It does not allow sorting by multiple levels.
- 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]:

A | B | C | D | |
---|---|---|---|---|

1 | Salesperson | Category | Date | Sales |

2 | Aaron | Electronics | 2020-10-18 | 1050.37 |

3 | Betty | Personal Care | 2020-02-09 | 590.47 |

4 | Carl | Sports | 2020-09-30 | 830.07 |

5 | Daisy | Sports | 2020-01-27 | 880.36 |

6 | Eric | Electronics | 2020-03-24 | 520.9 |

7 | Francis | Electronics | 2020-07-22 | 550.11 |

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).

A | B | C | D | E | F | G | |
---|---|---|---|---|---|---|---|

1 | Salesperson | Aaron | Betty | Carl | Daisy | Eric | Francis |

2 | Category | Electronics | Personal Care | Sports | Sports | Electronics | Electronics |

3 | Date | 2020-10-18 | 2020-02-09 | 2020-09-30 | 2020-01-27 | 2020-03-24 | 2020-07-22 |

4 | Sales | 1050.37 | 590.47 | 830.07 | 880.36 | 520.9 | 550.11 |

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.

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

**Python Lessons**

Python Basics

Conditional Statements

Loops

Functions and Modules

Numerical Python (NumPy)

Pandas Basics

Pandas Data Capture and Cleansing

Merging and Grouping

Manipulating Text and Datetime Data

Visualization

Web Scraping

Errors and Exceptions