Analyzing Payroll Data in Microsoft Excel (Currently Unavailable)

Author: David H. Ringstrom

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