QuickBooks Online/Microsoft Excel Advanced Data Analysis

In this presentation, author and Excel expert David H. Ringstrom, CPA, will guide you through the powerful features of Microsoft Excel, focusing on enhancing your data management skills. You'll learn how to effectively export and import customer contact lists using Power Query, clean and customize your data, and leverage Bing Maps for enhanced visualization. David will also demonstrate advanced techniques such as performing exact matches with XLOOKUP, automating functions with LAMBDA, and merging reports seamlessly. Additionally, you'll discover how to set up auto-refresh for your queries and create dynamic PivotTables for insightful data analysis. Join us to transform your Excel capabilities and streamline your reporting processes!

David H. Ringstrom, CPA, has more than 30 years of experience as a spreadsheet and accounting software consultant and speaker. He has presented over 2,500 live webinars and is the author or co-author of ten books, including “Microsoft 365 Excel All-in-One for Dummies”, “Microsoft 365 Excel for Dummies”, “Exploring Microsoft Excel’s Hidden Treasures”, and “QuickBooks Online for Dummies”.

In his webinars, David demonstrates every technique twice: first on a PowerPoint slide with numbered steps and then live in Excel for Microsoft 365 for Windows. He highlights any differences in Excel 2024, 2021, or 2019 during the presentation and in his detailed handouts. Attendees also receive an Excel workbook containing most of the examples he uses, making it easy to follow along and apply the techniques later. David additionally supports Excel for Mac users by answering their follow-up questions via email.

Who Should Attend
Professionals seeking to enhance their data management and reporting skills using Microsoft Excel, including data analysts, financial professionals, and project managers.

Topics Covered

• Defining parameters and testing a reusable lookup formula in Excel
• Extracting parent categories and item names with a delimiter split
• Specifying (or accepting the default) match mode of 0 for precise, unsorted lookups
• Eliminating prompts when refreshing queries linked to outside files
• Creating reusable custom lookups across QuickBooks reports
• Building mailto hyperlinks with optional subject lines in Excel
• Adjusting Power Query refresh settings to control when and how data updates
• Removing top rows, setting headers, filtering records, and connecting to the Vendor workbook
• See how to easily combine sheets from one workbook into a second workbook
• Grouping items by parent category and analyzing quantities on hand
• Removing top rows, setting headers, filtering records, and connecting to the Vendor workbook

Learning Objectives

• Recall which menu the Bing Maps feature appears on in Excel
• Recognize the correct argument value to configure XLOOKUP for exact match behavior
• State the purpose of the Data Connection security prompt

Level
Intermediate

Instructional Method
Group: Internet-based

NASBA Field of Study
Accounting (2 hours)

Program Prerequisites
QuickBooks Online/Microsoft Excel Basic Data Analysis is recommended but not required

Advance Preparation
None