Excel Accountant: Time-Saving Data Analysis Techniques

Learning Objectives

• To explain the menu in Excel where the Table feature resides.
• To recognize a dynamic array function from a list of worksheet functions.
• To discuss how to use Power Query to automate repetitive analysis.

Course Description

In the world of financial management, an Excel accountant armed with time-saving data analysis techniques holds the key to unlocking actionable insights and maximizing efficiency.

In this webinar, author and Excel expert David H. Ringstrom will bring numerous features and functions to your attention that fly under the radar for many Excel users. The Table feature offers tremendous automation and data integrity improvements in any version of Excel, as do dynamic array functions such as SORT, FILTER, and UNIQUE in Excel 2021 and Microsoft 365. Learn how to update multiple worksheets at once and corral large workbooks by hiding and unhiding multiple worksheets at once. You'll also see how to automate repetitive data analysis with Power Query.

Excel expert David Ringstrom, CPA,  demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Microsoft 365 (formerly Office 365) version of Excel. David draws your attention to any differences in the older versions of Excel (2021, 2019, 2016, and earlier) during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the webcast

This online Excel CPE webinar covers the following key topics:

• Creating an in-cell list by way of Excel’s Data Validation feature.
• Creating custom views that will enable you to display all worksheets in a workbook at once as well as hide/display selected worksheets.
• Diagnosing the new #CALC error that can arise within improperly crafted dynamic array formulas.
• Displaying subsets of data dynamically by way of the new FILTER worksheet function.
• Filtering a cleaned-up accounts receivable aging report to display only overdue amounts.
• Learning how the Table feature empowers you to improve the integrity of Excel spreadsheets.
• Learning how to use a single line of programming code to unhide all worksheets within a workbook.
• Making the same edits on multiple worksheets at once by grouping worksheets.
• Managing cumbersome lists of data using the Table feature.
• Managing data security prompts that may appear when you link external data into Excel spreadsheets.
• Removing duplicates from a list with the new UNIQUE function.
• Removing the Table feature from a worksheet if it’s no longer needed.
• Restoring Full Screen View in Excel 2013 and later as well as enabling a keyboard shortcut for this feature.
• Sorting lists of data dynamically from elsewhere in a spreadsheet with the new SORT function.
• Streamlining Custom Views by adding a drop-down list to Excel's Quick Access Toolbar.
• Streamlining the filtering of lists in Excel 2013 and later by using the Slicer feature with tables.
• Troubleshooting the #SPILL! the error that can arise in certain circumstances about dynamic array formulas in Excel 2021 and Microsoft 365.
• Understanding how the Table feature automates formula management within lists.
• Understanding the limitations and frustrations of manually hiding and unhiding worksheets within a workbook.
• Using Power Query to clean up accounting reports to remove pitfalls like blank rows, merged cells, missing data, and more.

Microsoft 365 is a subscription-based product that provides new feature updates as often as monthly. Conversely, the perpetual licensed versions of Excel have feature sets that don't change. Perpetual licensed versions have year numbers, such as Excel 2021, Excel 2019, and so on.

If you want to specialize in Excel then click here to view our best course: Certified Excel Specialists.

Overview

• Table feature

  5 mins
• Slicers with tables

  12 mins
• Clean A/R aging with power query

  26 mins
• Clean A/R Aging with power query

  41 mins
• Filter A/R aging for overdue amounts

  53 mins
• Unique function

  72 mins
• Group worksheets

  82 mins
• Utilizing a custom views

  94 mins