Analyzing Payroll Data in Microsoft Excel

Author: David H. Ringstrom

If you work with payroll data, you’ll benefit from learning how many Excel features and functions can improve the accuracy and efficiency of payroll-related tasks. In this comprehensive presentation, Excel expert David Ringstrom, CPA, explains: mathematics for employee timesheets, date and time formatting, conditional formatting to highlight HR requirements, password protection for sensitive payroll files and worksheets, salary information formatting, using pivot tables for HR reporting and analysis tasks, and more.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Microsoft 365 (formerly Office 365) version of Excel. David draws your attention to any differences in the older versions of Excel (2021, 2019, 2016 and earlier) during the course as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the course.

Microsoft 365 is a subscription-based product that provides new feature updates as often as monthly. Conversely, the perpetual licensed versions of Excel have feature sets that don't change. Perpetual licensed versions have year numbers, such as Excel 2021, Excel 2019, and so on.

Publication Date: April 2022

Designed For
Payroll professionals who wish to learn practical Excel fundamentals to improve the accuracy and efficiency of payroll production.

Topics Covered

• Adding rows to a blank pivot table to create instant reports
• Calculating a series of semimonthly dates by way of the IF and EOMONTH worksheet functions
• Color-coding the top ten (or however many you wish) amounts within a column of numbers with Conditional Formatting
• Computing the due dates for semimonthly federal payroll tax deposits by using the CHOOSE and WEEKDAY functions
• Determining how to calculate the last day of the current month, as well as future or prior months, with the EOMONTH function
• Determining the previous Friday when payroll dates fall on a weekend by using the IF and WEEKDAY functions
• Discovering four different ways to remove data from a pivot table report
• Discovering the capabilities of the SUMPRODUCT function for calculating payroll and other amounts
• Distinguishing the differences among pivot table-related menus in Excel 2013 and later versus older versions of Excel
• Drilling down into the details behind any amount within a pivot table with just a double-click
• Employing pivot tables to pull random sets of employee names, such as for drug testing
• 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
• Scrubbing social security numbers
• Flash fill SSNs (Excel 2013 )
• TEXT function for social security numbers
• Adding time values
• Using DATEDIF to calculate tenure
• Semi-monthly payroll tax due dates
• Heat mapping salaries
• Remove conditional formatting
• Conditional formatting - Top 10
• Conditional formatting - Equal to
• Initiating a pivot table
• Pivot table interfaces
• Adding fields to a pivot table
• Pivot table drill down
• 4 ways to remove fields
• RANDBETWEEN introduction
• Choosing random sets of employees
• Remove table feature from worksheet
• Free payroll-related templates
• Password protect the workbook
• Recovering lost passwords

Learning Objectives

• Recognize and apply password-protection techniques for sensitive payroll files, worksheets, and spreadsheet columns
• Describe how the Table feature can improve the integrity of Excel pivot tables
• Recognize and apply the Text to Columns feature to convert Social Security numbers to values and then apply Excel's Social Security number format
• Identify the ribbon tab or menu in Excel that the Text to Columns command appears on
• 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 the future
• Identify the ribbon tab that the Conditional Formatting feature appears on
• Identify the worksheet function that returns a random number between two values that you specify each time the worksheet is recalculated

Level
Intermediate

Instructional Method
Self-Study

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

Program Prerequisites
Experience with Excel.

Advance Preparation
None