Using the Dynamic Array Filter Function to automatically select audit transactions


Using the Dynamic Array Filter Function to automatically select audit transactions

Hello my auditing friends, this is a quick post to show how to use Excel Dynamic Arrays to help take the pain out of selecting transactions to audit.

To demonstrate, I will use an export of a QuickBooks Online report for the Repairs and Maintenance account.

Could there be some uncapitalized expenses in there? Never - right?

Here is the report . . .

The Exported Report

Here is the formula to show all transaction greater than $1,000

(In pseudo-code)

=FILTER( entire_range_to_return, range_to_filter[=,>,<,<> . .][value])

(in this example)

=FILTER(Export!$A:$J,Export!$I:$I>1000)

** You only type the formula into the first cell, Excel "spills" as needed to adjacent cells. Welcome to Dynamic Arrays!!

Formula in Action

Dig in

Dig in . . .

Ok, that is just the start. To dig in check out the articles at ExcelJet and start dreaming.

https://exceljet.net/dynamic-array-formulas-in-excel


Leave a Reply

Your email address will not be published.