Analyzing Payroll Data in Microsoft Excel

Author: David H. Ringstrom

In this course, 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 “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 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.

Publication Date: November 2024

Designed For
Professionals seeking to use Microsoft Excel more effectively.

Topics Covered

• Utilizing the RANDBETWEEN worksheet function to create a series of random numbers
• Preventing errors from the start by choosing from thousands of free Excel spreadsheet templates
• The capabilities of the SUMPRODUCT function for calculating payroll and other amounts
• The nuances of formatting numbers within a PivotTable
• Four different ways to remove data from a PivotTable report
• Adding rows to a blank PivotTable to create instant reports
• Employing the NETWORKDAYS.INTL worksheet function to determine the number of workdays in a period by excluding holidays as well as specific days of the week
• Limiting access to sensitive workbooks by way of password protection
• Transforming a column of salaries into an instant heat map by way of Excel’s Conditional Formatting feature
• Gleaning the nuances of adding time values together in Microsoft Excel
• Employing PivotTables to pull random sets of employee names, such as for drug testing
• Revealing the undocumented DATEDIF function in Excel for determining the number of months or years between two dates

Learning Objectives

• Identify the number of arrays that the SUMPRODUCT function allows
• Recognize a unique characteristic of Excel's DATEDIF worksheet function
• Identify the keyboard shortcut that activates Excel's Flash Fill feature
• Identify the function that can multiply cells together and provide a sum of the result
• Identify the worksheet function that returns the last day of a month based on a specified number of months in the past or future

Level
Basic

Instructional Method
Self-Study

NASBA Field of Study
Specialized Knowledge (2 hours)

Program Prerequisites
None

Advance Preparation
None