Python in Excel for Auditors

Author: David H. Ringstrom

In this course, Excel expert David H. Ringstrom, CPA, will guide you through setting up Python in Excel, including how to opt into the Beta channel for Microsoft 365. He will introduce essential Python data manipulation/analysis libraries like pandas and numpy. He’ll also demonstrate importing and exporting data to efficiently transfer information between Excel and Python. You'll learn methods for cleaning datasets to prepare them for analysis and automate data validation using Python scripts to ensure data accuracy. David will show you how to enhance Excel functions with Python for complex calculations and analyses, create interactive dashboards for better data visualization, and automate PivotTables for creating and manipulating summaries and analyses. He will also cover reconciling data by implementing Python scripts, performing statistical analysis within Excel, and generating reports automatically by combining data from multiple sources. Finally, you'll discover how to create graphs and charts with Python in Excel, enhancing your data presentation capabilities.

David is the author of “Exploring Microsoft Excel's Hidden Treasures: Turbocharge your Excel proficiency with expert tips, automation techniques, and overlooked features”. He demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Excel for Microsoft 365. David draws your attention to any differences in Excel 2021, 2019 or 2016 during the course and in his detailed handouts. The handouts include an Excel workbook with most of the examples he uses during his demonstrations.

Excel for Microsoft 365 is a subscription-based product that receives periodic feature updates. Conversely, perpetually licensed versions have year numbers in their names and do not receive any feature updates.

Publication Date: October 2024

Designed For
Professionals seeking to stay up to date on new features and functions in Excel for Microsoft 365.

Topics Covered

• Setting up Python in Excel by learning how to opt into the Beta channel for Microsoft 365
• Introducing essential Python data manipulation and analysis libraries like pandas and numpy
• Importing and exporting data to efficiently transfer data between Excel and Python
• Cleaning data by mastering methods to prepare your datasets for analysis
• Automating data validation using Python scripts to ensure data accuracy
• Enhancing Excel functions with Python to perform complex calculations and analyses
• Creating interactive dashboards in Excel using Python for better data visualization
• Automating PivotTables to create and manipulate summaries and analyses
• Reconciling data by implementing Python scripts to automate the process
• Performing statistical analysis with Python within Excel
• Generating reports automatically by combining data from multiple sources
• Creating graphs and charts with Python in Excel

Learning Objectives

• Explain the process for setting up Python in Excel, including opting into the Beta channel for Microsoft 365
• Explain how to use Power Query to clean data for analysis
• Describe how to enhance Excel functions with Python for complex calculations and analyses
• Identify the keyboard shortcut that is used to enter Python mode in Excel
• Identify the button clicked on the Navigator dialog box to access the Power Query Editor in Excel for Windows

Level
Intermediate

Instructional Method
Self-Study

NASBA Field of Study
Specialized Knowledge (2 hours)

Program Prerequisites
Prior experience with Python or attendance of David’s “Introduction to Python in Excel” are recommended.

Advance Preparation
None