Analyzing Payroll Data in Microsoft Excel

Author: David H. Ringstrom

CPE Credit:  2 hours for CPAs

Payroll processing doesn't have to be cumbersome. Here, course participants will learn how to make payroll tasks more efficient and accurate with a review of formatting, security for sensitive data, pivot tables for human resources reporting and analysis, and more.

Publication Date: July 2018

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

Topics Covered

  • Revealing the undocumented DATEDIF function in Excel for determining the number of months or years between two dates.
  • Determining the previous Friday when payroll dates fall on a weekend by using the IF and WEEKDAY functions.
  • Employing the NETWORKDAYS.INTL worksheet function to determine the number of work days in a period by excluding holidays as well as specific days of the week.
  • Utilizing the RANDBETWEEN worksheet function to create a series of random numbers.
  • Gleaning the nuances of adding time values together in Microsoft Excel.
  • Drilling down into the details behind any amount within a pivot table with just a double-click.
  • Transforming a column of salaries into an instant heat map by way of Excel's Conditional Formatting feature.
  • Limiting access to sensitive workbooks by way of password protection.
  • Color-coding the top ten (or however many you wish) amounts within a column of numbers with Conditional Formatting.
  • Improving the integrity of Excel pivot tables with the Table feature.
  • Discovering four different ways to remove data from a pivot table report.
  • Preventing users from drilling down into pivot table amounts.

Learning Objectives

  • Identify the function that can multiply cells together and provide a sum of the result.
  • Recall the menu command that only appears when you right-click a field that appears within the Values section of the Pivot Table field list.
  • Define the purpose of Excel's TEXT function.

Level
Intermediate

Instructional Method
Self-Study

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

Program Prerequisites
Experience with Excel

Advance Preparation
None

Registration Options
Quantity
Fees
Regular Fee $55.00

 Chat — Books Support