Operations Management Techniques in Excel (Video) (Course Id 2749)

• Who is this course for?
  This course is designed for anyone needing Continuing Professional Education (CPE), especially CPAs and accountants with prior experience using Microsoft Excel.

• What is this course about or what problem does this course solve?
  The course teaches a variety of Excel techniques for operations management, including Power Query, data security, Excel functions, macros, conditional formatting, and Scenario Manager, helping professionals enhance their data-handling and automation skills in Excel.

• How can the knowledge from this course be used?
  Participants can apply the techniques to improve efficiency and accuracy in spreadsheet-based tasks, automate processes, and analyze data more effectively in their day-to-day accounting or business operations.

• Why is this course important to a CPA or Accountant?
  Mastery of advanced Excel techniques is critical for CPAs and accountants to streamline workflows, manage data securely, and make informed decisions using well-organized data models.

• When is this course relevant or timely?
  This course is especially timely for professionals using Excel for Microsoft 365, Excel 2021, 2019, or 2016, and those looking to stay current with the latest Excel features and productivity tools.

• How is a course like this consumed or used?
  The course is delivered as a QAS self-study video program with accompanying handouts and an Excel workbook; learners must pass a final exam with at least 70% to earn CPE credit.

In this webinar Excel expert David H. Ringstrom will guide you on extracting PDFs with Power Query, exploring Power Query properties, enhancing external data security, creating Excel tables, adding total rows, filtering tables with slicers, utilizing functions such as IF, nested IF, VLOOKUP, IFNA, and MATCH, as well as INDEX/MATCH functions, XLOOKUP for horizontal lookup, automating tasks with macros, creating macro icons, applying conditional formatting for greater than scenarios, utilizing data bars, managing scenarios effectively, and summarizing Scenario Manager features.

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.

Video - NASBA QAS - NASBA Registry - 2749

Course Learning Objectives

• State which XLOOKUP argument causes an alternate value to be displayed in lieu of #N/A.
• Identify the items that can be added to a PivotTable from the PivotTable field list.
• Recall the section of the Excel Options dialog box where the AutoRecover setting resides that controls how often Excel creates a back-up copy of your workbooks.

Chapter 1 - Operations Management Techniques in Excel

Chapter 1 Review Questions

Key Terms