Skip to content

This site uses cookies to store information on your computer. Some are essential to make our site work; others help us improve the user experience. By using the site, you consent to the placement of these cookies. Read our privacy policy to learn more.

Close
AICPA-CIMA
  • AICPA & CIMA:
  • Home
  • CPE & Learning
  • My Account
Journal of Accountancy
  • TECH & AI
    • All articles
    • Artificial Intelligence (AI)
    • Microsoft Excel
    • Information Security & Privacy

    Latest Stories

    • AI-driven spreadsheet tools — what CPAs need to know
    • Is spending on technology spinning out of control?
    • Using 3 Excel View tools to manage large spreadsheets
  • TAX
    • All articles
    • Corporations
    • Employee benefits
    • Individuals
    • IRS procedure

    Latest Stories

    • Job cuts mean strong 2025 tax season may be hard to repeat, IRS watchdog warns
    • IRS removes associated property rule in final interest capitalization regulations
    • Spouse is not entitled to sales proceeds in a judicial sale of taxpayer’s home
  • PRACTICE MANAGEMENT
    • All articles
    • Diversity, equity & inclusion
    • Human capital
    • Firm operations
    • Practice growth & client service

    Latest Stories

    • AI-driven spreadsheet tools — what CPAs need to know
    • Job cuts mean strong 2025 tax season may be hard to repeat, IRS watchdog warns
    • Is spending on technology spinning out of control?
  • FINANCIAL REPORTING
    • All articles
    • FASB reporting
    • IFRS
    • Private company reporting
    • SEC compliance and reporting

    Latest Stories

    • SEC accepting Professional Accounting Fellow applications
    • SEC names new chief accountant
    • SEC ends legal defense of its climate rules
  • AUDIT
    • All articles
    • Attestation
    • Audit
    • Compilation and review
    • Peer review
    • Quality Management

    Latest Stories

    • AICPA unveils new QM resources to help firms meet Dec. 15 deadline
    • 8 steps to build your firm’s quality management system on time
    • Auditing Standards Board proposes a new fraud standard
  • MANAGEMENT ACCOUNTING
    • All articles
    • Business planning
    • Human resources
    • Risk management
    • Strategy

    Latest Stories

    • Business outlook brightens somewhat despite trade, inflation concerns
    • AICPA & CIMA Business Resilience Toolkit — levers for action
    • Economic pessimism grows, but CFOs have strategic responses
  • Home
  • News
  • Magazine
  • Podcast
  • Topics
Advertisement
  1. newsletter
  2. Extra Credit
Extra Credit Cover

The Excel Advanced Filter function gives you flexibility

By Wendy Tietz, CPA, Ph.D.; Jennifer Cainas, CPA, DBA; and Tracie Miller-Nobles, CPA
October 12, 2021

Please note: This item is from our archives and was published in 2021. It is provided for historical reference. The content may be out of date and links may no longer function.

Related

October 2, 2025

AI-driven spreadsheet tools — what CPAs need to know

October 2, 2025

Car talk: M&A, AI and EVs changing the dealership landscape

October 2, 2025

Is spending on technology spinning out of control?

TOPICS

  • Technology
    • Microsoft Excel
  • Accounting Education

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.  

excel-advanced-filter-1


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.

excel-advanced-filter-2


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.

Advertisement

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.

excel-advanced-filter-3


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.

excel-advanced-filter-4


Next, select your criteria range. Include both your column headings and the criteria.

excel-advanced-filter-5


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

excel-advanced-filter-6


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.

excel-advanced-filter-7


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

Advertisement

latest news

October 2, 2025

Job cuts mean strong 2025 tax season may be hard to repeat, IRS watchdog warns

October 2, 2025

Is spending on technology spinning out of control?

October 1, 2025

IRS removes associated property rule in final interest capitalization regulations

September 30, 2025

IRS withdraws prop. regs. affecting corporate spinoff transactions

September 30, 2025

FASB update refines reporting scope for derivatives

Advertisement

Most Read

MAP Survey finds CPA firm starting pay on the rise
IRS finalizes regulations for Roth catch-up contributions under SECURE 2.0
NASBA, AICPA release proposed revisions to CPE standards
IRS releases draft form for tip, overtime, car loan, and senior deductions
IRS shutdown plan: Employees stay on the job for first 5 workdays
Advertisement

Podcast

October 2, 2025

Car talk: M&A, AI and EVs changing the dealership landscape

September 25, 2025

Professional liability risks related to Form 1065, CPA firm acquisitions

September 18, 2025

‘We’re still the thinkers’ — a reminder for tax pros in the AI era

Features

AI-powered hacking in accounting: ‘No one is safe’
AI-powered hacking in accounting: ‘No one is safe’

AI-powered hacking in accounting: ‘No one is safe’

Building a better firm: How to pick the proper technology
Building a better firm: How to pick the proper technology

Building a better firm: How to pick the proper technology

Why accountants need to master the art of reading the room
Why accountants need to master the art of reading the room

Why accountants need to master the art of reading the room

How BI and analytics enhance management accountants’ partnering role
How BI and analytics enhance management accountants’ partnering role

How BI and analytics enhance management accountants’ partnering role

SPONSORED REPORT

Preparing clients for new provisions next tax season

Preparing clients for new provisions next tax season

As the 2025 filing season approaches, H.R. 1 introduces significant tax reforms that CPAs must be prepared to navigate. These legislative changes represent some of the most comprehensive tax updates in recent years, affecting both individual and corporate taxpayers. This report provides in-depth analysis and guidance on H.R. 1.

From The Tax Adviser

September 30, 2025

Current developments in taxation of individuals: Part 1

August 30, 2025

2025 tax software survey

August 30, 2025

Are you doing all you can to keep the cash method for your clients?

July 31, 2025

Current developments in S corporations

MAGAZINE

October 2025

October 2025

October 2025
September 2025

September 2025

September 2025
August 2025

August 2025

August 2025
July 2025

July 2025

July 2025
June 2025

June 2025

June 2025
May 2025

May 2025

May 2025
April 2025

April 2025

April 2025
March 2025

March 2025

March 2025
February 2025

February 2025

February 2025
January 2025

January 2025

January 2025
December 2024

December 2024

December 2024
November 2024

November 2024

November 2024
view all

View All

http://JofA_Default_Mag_cover_small_official_blue

PUSH NOTIFICATIONS

Coming soon: Learn about important news

CPA LETTER DAILY EMAIL

CPA Letter Logo

Subscribe to the daily CPA Letter

Stay on top of the biggest news affecting the profession every business day. Follow this link to your marketing preferences on aicpa-cima.com to subscribe. If you don't already have an aicpa-cima.com account, create one for free and then navigate to your marketing preferences.

Connect

  • X Logo JofA on X
  • facebook JofA on Facebook

HOME

  • News
  • Monthly issues
  • Podcast
  • A&A Focus
  • PFP Digest
  • Academic Update
  • Topics
  • RSS feed rss feed
  • Site map

ABOUT

  • Contact us
  • Advertise
  • Submit an article
  • Editorial calendar
  • Privacy policy
  • Terms & conditions

SUBSCRIBE

  • Academic Update
  • CPE Express

AICPA & CIMA SITES

  • AICPA-CIMA.com
  • Global Engagement Center
  • Financial Management (FM)
  • The Tax Adviser
  • AICPA Insights
  • Global Career Hub
AICPA & CIMA

© 2025 Association of International Certified Professional Accountants. All rights reserved.

Reliable. Resourceful. Respected.