On-Demand Webinar

Excel Skills For Auditors and Accountants

  • Recorded January 16, 2025
  • 100 Minutes
  • Available now
CE credits
  • 1.50 ATATX (Aurora Accounting)

What You'll Learn

Are your Excel skills a little old school and slow? Need an efficiency boost using some new Excel tools and approaches? You’ll learn how to save dozens of hours a year and become a valuable resource to your team by making your files more dynamic, reducing the risk of errors, and providing a great, professional look.
View Full Description

If you’re an auditor, you know that it’s easy to make (and miss) mistakes in Excel. Knowing what to look for and how to avoid them is crucial. Knowing how the newest Excel tools work is a must to appropriately service your clients.

If you’re responsible for supporting auditors, you know how demanding the requests can be. Efficiency is key for getting your work done quickly and proactively, and freeing up time to manage the audit team.

In this course, you'll learn how to become super-efficient and how to take modern approaches that make your files more dynamic, reduce the risk of errors, and provide a great, professional look.

Even if you’re not a deep Excel user, you’ll gain insights that help you collaborate with your team and discuss what’s new and useful in Excel. We’ll also teach you how to continue your learning journey and keep up with the latest Excel trends.

Your Benefits For Attending:
  • Learn the risks to watch out for and how to avoid them
  • Learn valuable Excel tools all accountants should know
  • Learn crucial shortcuts for working efficiently
  • Learn time hacks that benefit yourself and others
Instructional Delivery Method: Group Internet Based
Recommended Field of Study: Computer Software & Applications
Program Level: Intermediate
Prerequisites: Basic knowledge of Excel (familiarity with structure, navigation, and formulas) is recommended.
Advance Preparation: None

Meet Your Speaker

Derek Henry, CPA, CFE

Derek is an accomplished speaker, trainer, consultant, and developer with over 20 years of experience in public accounting, corporate accounting/finance, and software pre-sales. Over that time, he has taught, managed, and mentored hundreds of people on how to work with more efficiency,...

View Full Bio of Derek Henry, CPA, CFE

What Attendees Are Saying

  • “I very much enjoyed this presenter. He presented the material in a straightforward way with good examples, and good humor.”

  • “The presentation was done very quickly. I would've like to follow along with the presenter. ”

See the survey ratings

2 attendees rated this webinar 4.8 out of 5 on average.

4.8 Overall Rating

Webinar Presentation

How many of the objectives of the event were met?
5.0
How useful was the information presented at this event?
5.0
Overall, how satisfied were you with this event?
4.5

Speaker Performance

Overall, how satisfied were you with this presenter?
4.5
How closely did the presenter follow the schedule?
5.0

CE Credits

These are the continuing education credits available to you for this webinar.

  • 1.50 ATATX Aurora Accounting Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in accounting.
Table of Contents
  1. Introduction 
  2. Who I’m Not 00:01:22
  3. Who I Am 00:02:27
  4. Agenda 00:06:02
  5. Risks 00:06:32
  6. Risks Abound 00:07:27
  7. Incorrect Data 00:08:39
  8. Formula Errors 00:18:01
  9. Hard-Coded Values 00:22:10
  10. Lack of Documentation 00:27:03
  11. Hidden Stuff 00:29:40
  12. Other Risks 00:32:23
  13. Tools 00:34:57
  14. Dynamic Array Functions 00:35:09
  15. DAF Examples 00:37:59
  16. Use Power Query 00:49:50
  17. Use Macros 00:54:13
  18. Shortcuts 01:00:13
  19. The Impact 01:00:26
  20. Don’t Sweat the Small Stuff 01:03:20
  21. Navigating 01:03:44
  22. Selecting 01:05:57
  23. Formulas 01:09:26
  24. Formatting 01:17:13
  25. Time Hacks 01:18:50
  26. New Window 01:19:07
  27. Watch Window 01:21:41
  28. Customize the Ribbon  01:23:33
  29. Navigation Pane 01:27:31
  30. Excel Checklist 01:32:03
  31. Key Takeaways 01:33:33
  32. Did You Enjoy This? 01:36:15
  33. Presentation Closing 01:40:24
Index
  • Artificial Intelligence (AI) 00:14:29, 00:56:23
  • Cell 00:15:29, 00:23:19, 00:35:21
  • Column 00:20:30, 00:29:49, 00:41:24, 00:52:00, 01:04:25, 01:17:18
  • Conditional Formatting 00:24:24
  • Data Validation 00:12:45, 00:14:17, 00:17:50
  • Dynamic Array Function 00:35:09
  • FILTER Function 00:38:00
  • Format 00:09:30, 00:29:23, 00:54:56
  • Formula 00:18:45, 00:20:24, 00:21:44, 00:35:50, 00:38:28, 00:47:47, 01:09:26
  • LAMBDA 00:45:39
  • LOOKUP 00:09:33
  • Macro 0:54:13, 01:07:31
  • Navigation Task Pane 01:27:35
  • Order of Operations 00:09:24
  • Power Query  00:49:55, 00:54:32
  • Query 00:13:08
  • Quick Access Toolbar 01:23:51
  • Ribbon 01:23:33
  • Row 00:20:31, 00:29:49, 00:41:24, , 01:17:18
  • SEQUENCE 00:40:58
  • SORT 00:40:52
  • Spreadsheet 00:07:10
  • TEXTSPLIT 00:44:01
  • UNIQUE 00:40:29
  • VBA - Visual Basic for Applications 00:55:11
  • Watch Window 01:21:41
  • Workbook 00:47:40, 00:50:52
  • XLOOKUP 00:42:09
Key Terms
Artificial Intelligence (AI)
Artificial intelligence is intelligence demonstrated by machines, as opposed to the natural intelligence displayed by humans or animals.
Cell
In spreadsheet applications, a cell is a box in which you can enter a single piece of data. The data is usually text, a numeric value, or a formula. The entire spreadsheet is composed of rows and columns of cells.
Column
A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.
Conditional Formatting
A feature on Excel's Home menu that allows you to dynamically apply formatting such as colors, bolding, icons, data bars, and so on based on criteria that you specify for a given set of worksheet cells.
Dynamic Array Function
Dynamic Arrays will make certain formulas much easier to write. You can now filter matching data, sort, and extract unique values easily with formulas. Dynamic Array formulas can be chained (nested) to do things like filter and sort. Formulas that return more than one value will automatically spill.
FILTER Function
The Excel FILTER function returns a range filtered on criteria you define. It can also handle multiple AND/OR criteria. array is the range or array containing the values you want filtered. include is the logical test that returns a Boolean array (TRUE/FALSE) the same height or width as the array.
Format
When we format cells in Excel, we change the appearance of a number without changing the number itself. We can apply a number format (0.8, $0.80, 80%, etc) or other formatting (alignment, font, border, etc). By default, Excel uses the General format (no specific number format) for numbers.
Formula
A formula is an expression which calculates the value of a cell.
LAMBDA Function
The LAMBDA function provides a way to create a custom function in Excel. Once defined and named, a LAMBDA function can be used anywhere in a workbook. LAMBDA functions can be very simple, or quite complex, stringing together many Excel functions into one formula. A custom LAMBDA function does not require VBA or macros.
LOOKUP
The Microsoft Excel LOOKUP function returns a value from a range (one row or one column) or from an array. The LOOKUP function is a built-in function in Excel that is categorized as a Lookup/Reference Function. It can be used as a worksheet function (WS) in Excel.
Macro
One or more lines of programming code that automate tasks. The Macro Recorder allows users to automate tasks without seeing the underlying programming code.
Navigation Task Pane
The Navigation pane in Excel is an easy way to understand a workbook's layout, see what elements exist within the workbook, and navigate directly to those elements. Find and access elements such as tables, charts, PivotTables, and images within your workbook. Once you've opened the Navigation pane, it displays on the right side of the Excel window.The Navigation pane also makes it easier for those with visual impairments to access all parts of the workbook. It can improve how tools such as screen readers interpret your workbook.
Order of Operations
The sequence with which Excel carries out arithmetic operations. Unless superseded by enclosing portions of a calculation in parentheses, Excel first divides, then multiplies, then adds, and finally subtracts.
Power Query
Power Query is a data connection technology that enables you to discover, connect, combine, and refine data sources to meet your analysis needs. Features in Power Query are available in Excel and Power BI Desktop. Power Query is one of three data analysis tools available in Excel: Power Pivot.
Query
A database query extracts data from a database and formats it in a readable form. A query must be written in the language the database requires; usually, that language is Structured Query Language (SQL). For example, when you want data from a database, you use a query to request that specific information.
Quick Access Toolbar
A customizable shortcut toolbar that appears above the ribbon in Office 2007 and later.
Ribbon
The "ribbon" is the strip of buttons and icons located above the work area that was first introduced in Excel 2007. The ribbon replaces the menus and toolbars found in earlier versions of Excel. Above the ribbon are a number of tabs, such as Home, Insert, and Page Layout.
Row
A row is the range of cells that go across (horizontal) the spreadsheet/worksheet. Rows are identified by numbers e.g. row 1, row 5. Examples of use. A row might contain the headings of a table e.g. product ID, product name, price, number sold.
SEQUENCE
The SEQUENCE function allows you to generate a list of sequential numbers in an array, such as 1, 2, 3, 4.
SORT
Sorting is the process of arranging objects in a certain sequence or order according to specific rules. In spreadsheet programs such as Excel and Google Spreadsheets, there are several different sort orders available depending on the type of data you're sorting.
Spreadsheet
Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation or computation capabilities, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. Excel forms part of the Microsoft Office suite of software.
TEXTSPLIT
Splits text strings by using column and row delimiters. The TEXTSPLIT function works the same as the Text-to-Columns wizard, but in formula form.
UNIQUE
=UNIQUE - The Excel UNIQUE function returns a list of unique values in a list or range.
VBA - Visual Basic for Applications
Visual Basic for Applications is a computer programming language developed and owned by Microsoft. With VBA you can create macros to automate repetitive word- and data-processing functions, and generate custom forms, graphs, and reports. VBA functions within MS Office applications; it is not a stand-alone product.
Watch Window
A formula auditing and workbook navigation feature that enables users to monitor the value and formulas within key worksheet cells as well as easily navigate to said cells.
Workbook
In Microsoft Excel a workbook is a collection of one or more spreadsheets, also called worksheets, in a single file.

Frequently Asked Questions

What are the most common Excel risks that auditors and accountants should watch out for?
Spreadsheet errors in accounting and audit work can have serious consequences, and Derek Henry, CPA, CFE, identifies several key risk categories. Incorrect data includes importing or linking data that contains errors, duplicates, or outdated values that then flow through financial models. Formula errors arise from incorrect logic, broken references, or wrong order of operations—especially dangerous in complex nested formulas. Hard-coded values embedded directly in formulas (e.g., =A1*0.21 instead of =A1*TaxRate) are invisible risks that become outdated when rates change and are easily missed in reviews. Lack of documentation—no explanatory notes, unnamed assumptions, or missing version history—makes models difficult to audit or hand off. Hidden rows, columns, or worksheets may contain data that affects calculations but is invisible to reviewers. Other risks include manual overrides that bypass formula logic and circular references that silently produce incorrect results. Aurora Training Advantage's Excel Skills For Auditors and Accountants webinar, taught by Derek Henry, CPA, CFE, covers all these risk categories with strategies to identify and mitigate them.
How do dynamic array functions like FILTER, SORT, and UNIQUE benefit accountants in Excel?
Dynamic array functions, available in Microsoft 365 and Excel 2021, allow accountants to perform data analysis tasks that previously required complex array formulas, helper columns, or manual filtering. FILTER returns a subset of data meeting specified conditions—=FILTER(transactions, amounts>10000) instantly lists only high-value transactions, updating automatically as data changes. SORT and SORTBY return sorted versions of a range without disturbing the source data, enabling multiple sort views simultaneously. UNIQUE extracts distinct values from a list—invaluable for building unique customer or account lists from transaction data. SEQUENCE generates sequential numbering arrays, simplifying period labeling and series creation. These functions spill their results into adjacent cells automatically. For accountants, dynamic arrays reduce the need for manually maintained helper columns, eliminate many SUMPRODUCT workarounds, and enable more transparent, auditable formulas. XLOOKUP and XMATCH complete the suite of modern dynamic functions. Derek Henry, CPA, CFE, covers dynamic array functions in depth in Aurora Training Advantage's Excel Skills For Auditors and Accountants webinar.
How can accountants use Power Query to reduce manual data processing and errors in Excel?
Power Query addresses one of the most significant sources of accounting errors: manual data manipulation. By automating the import and transformation of data from systems like accounting software exports, bank feeds, or ERP reports, Power Query creates a documented, repeatable process that reduces human error. Each transformation step is recorded and visible in the Power Query Editor, creating an audit trail of exactly how source data was cleaned and shaped. Refreshing the query with new data re-runs all steps consistently—unlike manual processes that may vary each time. Common accounting applications include standardizing chart-of-accounts descriptions from imported GL exports, merging multiple period files into one dataset, unpivoting multi-column budget vs. actual reports into analysis-ready formats, and removing extraneous header rows from system exports. Power Query handles these transformations in minutes rather than hours of manual work. Derek Henry, CPA, CFE, advocates Power Query as a core efficiency and risk-reduction tool in Aurora Training Advantage's Excel Skills For Auditors and Accountants webinar.
What is the LAMBDA function in Excel and how can accountants use it to create custom functions?
The LAMBDA function, available in Microsoft 365, allows accountants to create custom reusable functions in Excel without any VBA or macro programming. The syntax is =LAMBDA(parameter1, parameter2, ..., formula), and once defined and named via the Name Manager (Ctrl+F3), the custom function can be called anywhere in the workbook just like a built-in function. For example, a tax calculation that requires multiple steps can be defined as a LAMBDA and called with =CalcTax(income, rate) rather than duplicating the formula in every cell. LAMBDA functions improve spreadsheet maintainability—change the logic in one place and it updates everywhere the function is used. They also improve auditability by hiding implementation complexity behind a readable function name. For recurring accounting calculations such as amortization schedules, present value computations, or multi-step reconciliation checks, LAMBDA creates firm-standard functions that can be shared across workbooks. Derek Henry, CPA, CFE, introduces LAMBDA as a modern tool for accountants in Aurora Training Advantage's Excel Skills For Auditors and Accountants webinar.
What Excel shortcuts and tools help auditors work more efficiently when reviewing spreadsheets?
Efficiency in Excel directly impacts audit quality and turnaround, and Derek Henry identifies several high-value tools. Navigation shortcuts such as Ctrl+End jump to the last used cell—important for understanding a workbook's scope. Ctrl+[ (trace precedents by keyboard) and Ctrl+] (trace dependents) navigate formula relationships quickly during review. The Watch Window (Formulas > Watch Window) monitors key cells as you edit elsewhere, allowing auditors to verify that critical totals remain unchanged when making adjustments. The Navigation Pane (View > Navigation Pane in Microsoft 365) provides a structural overview of all tables, charts, pivot tables, and named ranges in a workbook, making it easy to locate and audit every element. Customizing the Quick Access Toolbar and Ribbon with frequently used commands reduces menu navigation. The Excel Checklist approach—systematically reviewing formula consistency, link integrity, hard-coded values, and documentation—provides a structured audit methodology. Aurora Training Advantage's Excel Skills For Auditors and Accountants webinar, taught by Derek Henry, CPA, CFE, covers these efficiency techniques with the specific workflows accountants encounter most frequently.