Excel Skills For Auditors and Accountants

Access this expert-led webinar instantly, available anytime on-demand.

4.8
Included in All-Access Membership
Live Webinar - no upcoming date
Customer Satisfaction Guarantee Learn with confidence. If you're not happy, we'll make it right. That's our guarantee.

Purchase Options

Select an attendee quantity to add to cart.

Recorded Webinar Only

$219.00
or

All Access Membership

The Aurora All Access Membership is designed to provide you with the training that you want when you want it. You will have 100% access to every live webinar, on demand webinar, professional alert, and podcast that Aurora Training Advantage offers with no additional cost.

Learn More About Our All Access Membership
$599.00
All Access Membership

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.

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
  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
  • Derek Henry, CPA, CFE

    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, accountabilit [...]

    View Full Profile

ATATX Credit

Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in accounting.
  • 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

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.

Customer Satisfaction Guarantee
Invest in your future with confidence! Our Customer Satisfaction Guarantee eliminates all risk, letting you focus purely on mastering new skills and advancing your career. If you're not completely satisfied, we'll ensure you are. Your satisfaction is not just a promise; it's our guarantee.

Webinar Survey Overall Rating

This webinar received a total of 2 survey responses. Attendees have given an average rating of 4.8 stars out of a possible 5, reflecting the quality and value of the content presented.

Average rating

4.8 / 5
Webinar Presentation
How many of the objectives of the event were met?
5.0 Stars
How useful was the information presented at this event?
5.0 Stars
Overall, how satisfied were you with this event?
4.5 Stars
Speaker Performance
Overall, how satisfied were you with this presenter?
4.5 Stars
How closely did the presenter follow the schedule?
5.0 Stars

Reviews From Webinar Survey

Our webinars are crafted to deliver exceptional value and insight to business professionals. Below, you'll find genuine feedback from attendees.

Lee E.
January 17, 2025
5.0 / 5
Webinar Rating:
5.0 Stars
Speaker Rating:
5.0 Stars
Do you have any other comments, questions or concerns?
I very much enjoyed this presenter. He presented the material in a straightforward way with good examples, and good humor.
STACEY F.
January 17, 2025
4.6 / 5
Webinar Rating:
4.7 Stars
Speaker Rating:
4.5 Stars
Do you have any other comments, questions or concerns?
The presentation was done very quickly. I would've like to follow along with the presenter.

Frequently Asked Questions

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