The Excel Advanced Filter function gives you flexibility
The Excel AutoFilter feature allows you to narrow your data based on certain criteria and extract records that match those criteria. To turn on a filter, select the cells with data that you want to filter, click the Data tab in the Ribbon, and then click Filter. Dropdown arrows will appear on the column headings, giving you options to filter the data based on the criteria you are searching for.
In addition to AutoFilter, you can create a custom filter by using the Advanced Filter tool. Advanced Filter allows you to generate a unique list of items and extract those items to another place in your worksheet or workbook.
Advanced Filter has several useful features. It allows you to apply several filter criteria simultaneously to the entire data file, which AutoFilter does not. It also lets you easily change the criteria by typing new values directly into the criteria cells. Additionally, Advanced Filter enables you to copy the filtered data to a specified area within the same worksheet or a different worksheet rather than just filter the data within the original data file. This can be very helpful with a large data file such as a gradebook.
Here’s an example of how you can use Advanced Filter. Suppose that you want to identify each student with an A for the midterm grade in section 1 of your class so that you can email those students and invite them to an honors banquet, but you have a merged gradebook. You can use the Advanced Filter function to filter this information and copy it into a separate area of the worksheet or a new worksheet for further analysis.
A best practice is to start by inserting several rows above your dataset. In the screenshot below, we have added seven rows above the original data.
Next, use the blank rows to set up and apply the criteria. Input “Midterm Grade” and “Section” in two of the empty cells and then input “A” in the cell below Midterm Grade and “1” in the cell below Section, as seen in the screenshot below. When you set up your criteria, we suggest copying the column headings you want to include directly from your original data sheet.
Now you need to determine the filter results you want displayed. You can have the results appear on the same worksheet or in a different worksheet within your workbook. To keep this simple, we will show you how to display the search results within the same spreadsheet.
To begin, you need to specify the filter results you need — in this case, Student name and Student email — and where the information should appear on the spreadsheet. Add the column headings for Student name and Student email to the right of your original data file, as seen in the screenshot below. Then, click on the Advanced button.
A pop-up box will appear that asks you to specify your list range, your criteria range, and where you would like the results to display. First, click the dropdown arrow under “List Range” and select your data range.
Next, select your criteria range. Include both your column headings and the criteria.
Finally, state where you would like the results to display. Choose the “Copy to another location” option, select the filter results you want displayed (in this case, Student name and Student email), and where to display them (in this case, the cells below Student name and Student email).
Click OK. As you can see from the screenshot below, the results indicate that three students from Section 1 have a midterm grade of an A, and their names and email addresses are displayed.
— Wendy Tietz, CPA, CGMA, Ph.D., is a professor of accounting at Kent State University in Kent, Ohio; Jennifer Cainas, CPA, DBA, is an instructor of accountancy at the University of South Florida in Tampa; and Tracie Miller-Nobles, CPA, is an associate professor of accounting at Austin Community College in Austin, Texas. See their site AccountingIsAnalytics.com for resources they have developed for teaching data analytics in introductory accounting. To comment on this article or to suggest an idea for another article, contact senior editor Courtney Vien at Courtney.Vien@aicpa-cima.com.
