In this webinar, 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 “Microsoft Excel 365 for Dummies”, “Exploring Microsoft Excel’s Hidden Treasures”, and has written or co-authored six other books. 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 presentation 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.

Who should attend: Professionals seeking to stay up to date on new features and functions in Excel for Microsoft 365.

Topics typically covered:

• Identifying outlying amounts via Z-scores.

• Gaining an understanding of how Python is implemented in Excel and how it is similar and different from Excel.

• Exploring the risks and benefits of participating in the Beta Channel program in Excel for Microsoft 365.

• Creating pie charts with Python.

• Filtering transactions within a Python DataFrame by amount and category.

• Appending two or more Python DataFrames together.

• Identifying duplicate transactions with Python.

• Loading big data sets of more than 1,048,576 rows into Power Query.

• Understanding which Python libraries that Excel loads automatically.

• Exploring how Python is implemented in Excel for Microsoft 365.

• Filtering Python data sets with Excel functions.

• Highlighting the limitations of Excel's row capacity with big data sets.

Learning objectives:

• Define the process for setting up Python in Excel, including opting into the Beta channel for Microsoft 365.

• Demonstrate how to use Power Query to clean data for analysis.

• Describe how to enhance Excel functions with Python for complex calculations and analyses.

Level: Intermediate

Instructional Method: Group: Internet-based

NASBA Field of Study: Computer Software & Applications (2 hours)

Program Prerequisites: Prior experience with Microsoft Excel is recommended.

Advance Preparation: None