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
