Analyzing Payroll Data in Excel (Video) (Course Id 2616)

• Who is this course for?
  Anyone needing Continuing Professional Education (CPE), such as CPAs, accountants, or IRS Enrolled Agents.

• What is this course about or what problem does this course solve?
  It teaches payroll-related Excel techniques—like reformatting SSNs, doing time calculations, tenure analysis, tax calculations, heat mapping salaries, and data lookup—so that users can analyze payroll data efficiently in Excel.

• How can the knowledge from this course be used?
  Participants can apply Excel functions (e.g., SUMPRODUCT, DATEDIF, NETWORKDAYS.INTL, VLOOKUP/XLOOKUP) and formatting tools (Flash Fill, TEXT, heat maps) to streamline payroll analysis, optimize schedules, calculate taxes, and present payroll data more effectively.

• Why is this course important to a CPA, Accountant, or IRS Enrolled Agent?
  It enhances Excel proficiency—critical for managing and analyzing payroll data accurately and efficiently—which supports timely and error-free reporting, tax compliance, and data-driven decision-making in their professional roles.

• When is this course relevant or timely?
  Released in 2025 and revised on August 9, 2025, it covers up-to-date features relevant to modern Excel versions and provides the latest techniques.

• How is a course like this consumed or used?
  Delivered as a QAS Self-Study Video format with PowerPoint demonstrations and Excel walkthroughs, it includes detailed handouts and a workbook. Participants complete review questions and a final exam (70% passing score) within one year to earn credit.

In this presentation, author and Excel expert David H. Ringstrom, CPA, will guide participants through various payroll-related Excel techniques. Topics covered include contrasting using Flash Fill versus the TEXT function to reformat Social Security Numbers. You'll see how to calculate total payroll and total payroll taxes with the SUMPRODUCT function for data analysis, and understand the nuance of adding up time values in Excel. David will also show how to calculate employee tenure with the DATEDIF function, optimize work schedules with the NETWORKDAYS.INTL function, and applying heat mapping techniques to salary data. He'll also contrast using VLOOKUP in any version of Excel versus XLOOKUP in Excel 2021 and Excel for Microsoft 365 for looking up data from lists. Attendees will gain valuable insights and skills to enhance their Excel proficiency and efficiency.

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. Most features and functions work in Excel for Mac as well but expect differences.

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 - 2616

Course Learning Objectives

• Recall the number of arrays that the SUMPRODUCT function allows.
• State which format code prevents Excel from subtracting entire 24 hour periods when summing two or more time values together.
• Identify the location of the PivotTable command within Excel's ribbon menu interface.

Chapter 1 - Analyzing Payroll Data in Excel

Chapter 1 Review Questions

Key Terms