Microsoft Excel is one of the most powerful tools for accounting professionals. Among the program’s top time-saving tools are dynamic array functions—specifically, the filter function. Combined with other dynamic array function, the function of filter in Excel is key to quickly sort data for more efficient analysis.
What Is the Filter Function in Excel?
The function of filter in Excel lets you organize data in a just a few clicks, making it easy to view select and qualifying data and more efficiently gain insights. It lets you define the criteria of the data you wish to see, and then extracts only those data points that qualify.
What Are the Benefits of the Filter Function?
The function of filter in Excel is particularly powerful because it allows you to maintain one complete dataset and create dynamic reports for reconciliations, audits, budgeting, AP, AR, and financial analysis without repeatedly sorting or manually extracting data.
Using the filter function helps accountants:
- Save time by eliminating manual filtering and copying.
- Reduce errors because reports update automatically.
- Improve auditability by using transparent formulas rather than hidden rows.
- Support dynamic reporting from a single source dataset.
- Enhance dashboard creation for management reporting.
- Simplify transaction analysis during close, audit, tax, and FP&A processes.
What Accounting Tasks Can You Do with Filter Function?
The function of filter in Excel has many useful applications for accountants.
1. Quickly Review Transactions by Account
A general ledger may contain thousands of entries. The filter function can pull only the transactions related to a specific account. This is especially useful for:
- General ledger review
- Month-end close analysis
- Expense account reconciliation
2. Identify Outstanding Invoices
Accounts receivable professionals performing tasks like collections management and customer follow-up can quickly generate a list of unpaid invoices.
3. Review Expenses Above Approval Thresholds
Many organizations require managerial approval for expenses exceeding a certain amount. Accountants filter an expense threshold for:
- Internal controls testing
- Audit preparation
- Expense policy compliance
4. Support Audit Sampling and Testing
Auditors frequently isolate transactions meeting specific criteria. This is especially useful during tasks like:
- Substantive testing
- Risk assessment
- Journal entry review
5. Create Dynamic Financial Reporting
Instead of maintaining separate worksheets for each department, accountants can build one master dataset and use the function of filter in Excel to display department-specific reports.
6. Monitor Budget Variances
FP&A (financial planning and analysis) and management accountants often need to review only accounts exceeding budget. Whether preparing for budget review meetings, creating forecast analyses, or drafting management reports; the filter function saves time and clarifies findings.
7. Find Transactions for a Specific Vendor
When reviewing vendor activity, the filter function can instantly return all purchases from a selected supplier. Use it for responsibilities such as:
- Vendor spend analysis
- AP investigations
- Contract compliance reviews
8. Build Interactive Dashboards
The filter function can serve as the engine behind dashboard reports by responding to user selections. For example, you can build a filter formula that targets a specific data criterion, updating the results every time you add or change data in the table to ensure that your results are always up to date.
How Do You Use the Filter Function?
The function of filter in Excel has diverse applications that determine how you use it. A simple data sorting uses the formula: =FILTER(array,include,[if_empty])
In this formula, array refers to the range of cells to filter, A Boolean array whose height or width is the same as the array, and the value that should be returned if the filter turns up empty (no matches in the array).
For example, Figure 1 contains information on makeup sales transactions. Each row contains four pieces of information about a transaction:
- Name of salesperson
- Product sold
- Units sold
- Revenue from transaction
Figure 1 - Examples of the Filter Function
This figure contains two examples of the Filter function in action.
- In cell K4, the formula =FILTER(D5:G66,D5:D66="Betsy","None") spits out each row of data in which the Column D entry (Name) is Betsy. If no result is found, then the word None is returned.
- In cell L13, we created a dropdown list of products by entering the formula =UNIQUE(N14:N18). Then, in cell J18, the formula =FILTER(D5:G66,E5:E66=L13,"None") returns all rows of data for which the product sold matches the product selected from cell L13’s dropdown box.
Can You Use Filter with Other Dynamic Array Functions?
You can combine filter with other dynamic array functions to perform more complex actions. Figure 2 shows two more examples of the filter function in action, the second of which displays a combination of dynamic array functions.
Figure 2 - More applications of the Filter function
- In cell Q17, the formula =FILTER(D5:G66,(D5:D66=Q14)*(E5:E66=R14),"NONE") filters our data to show only those rows where Zaret sold lip gloss. The portion (D5:D66=Q14)*(E5:E66=R14) of the formula will be true for a row if and only if the row has Name = Zaret and Product = Lip gloss.
- In cell O27, the formula =SORT(FILTER(D5:G66,(D5:D66=Q14)*(E5:E66=R14),"NONE"),4,-1) returns all rows where Zaret sold lip gloss, and then sorts these rows based on the fourth column of data (revenue) in descending order. The argument -1 makes the sort descending, while the argument +1 (or omitted) makes the sort ascending. Note that this formula combines two dynamic array functions.
How to Use Filter Function with an “OR” Filter
Figure 3 shows how the filter function can be used to extract a subset of data using an “OR” criteria.
Figure 3 - Using the FILTER function with an OR filter
• Entering the formula =FILTER(D4:G66,(D4:D66="Jen")+(D4:D66="zaret"),"none") in cell V3 returns each row of data in which the Name is Jen or Zaret. If no such row exists, the word “none” is returned.
In cell AA7, the formula =AVERAGE(FILTER(G4:G66,(D4:D66="Jen")+(D4:D66="zaret"),"none")) averages all rows in which Jen or Zaret is the salesperson. Note that this calculation could be completed without extracting the relevant rows.
How to Use the Filter Function with an Excel Table
In the worksheet “Table,” we made source data a table by selecting the range D4:G66 and then choosing “Table” from the Insert tab. The name of the table is Table1. Any formula referring to Table1 will automatically update when new data is added. As shown in Figure 4, the formula =FILTER(Table1,Table1[Product]=M6," none") returns every row in the table involving eyeliner sales. If you add a new row to the bottom of the table involving eyeliner sales, data for that transaction will automatically update.
Figure 4 - Using the FILTER Function with an Excel Table
Learn More Excel Skills from Experts
Your Excel skills directly translate to more efficiency and greater value that you bring as an accountant. Level up your Excel knowledge and confidence—and earn CPE—with Becker’s Microsoft® Excel Fundamentals + Data Analytics Certificate.
Build your Excel Skills with Becker
With Becker's Microsoft Excel Fundamentals + Data Analytics Certificate, you'll gain essential skills in Excel, data visualization, and advanced analytics. Plus, you'll earn 28 CPE credits!
