Excel Skills For Auditors and Accountants

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

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