Maximize Excel: Anlyzing Payroll Data

CPE Credit: 2 hours

 
There are many features and functions payroll professionals might not be aware of that can be used to improve the accuracy and efficiency of payroll production. In this valuable presentation, Excel expert David Ringstrom, CPA, clearly explains: mathematics for employee timesheets, date and time formatting and stamping, 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 Excel 2016. He draws your attention to any differences in Excel 2013, 2010, or 2007 during the presentation as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples used in the course. 

Topics Covered

• Color-coding the top ten (or however many you wish) amounts within a column of numbers with Conditional Formatting.
• Calculating a series of semimonthly dates by way of the IF and EOMONTH worksheet functions.
• Preventing users from drilling down into pivot table amounts.
• Using Flash Fill in Excel 2013 and later to quickly insert dashes into a column of Social Security or telephone numbers.
• Determining the previous Friday when payroll dates fall on a weekend by using the IF and WEEKDAY functions.
• Employing pivot tables to pull random sets of employee names, such as for drug testing.
• Removing Conditional Formatting when it’s no longer needed within a spreadsheet.
• Adding fields to a blank pivot table to create instant reports.
• Discerning the nuance involved in making pivot tables present data in tabular form.
• Drilling down into the details behind any amount within a pivot table with just a double-click.
• Gleaning the nuances of adding time values together in Microsoft Excel.
• Understanding the nuances of formatting numbers within a pivot table.

 
Learning Objectives:

• Describe how to apply the Text to Columns feature to convert Social Security numbers to values and then apply Excel’s Social Security number format.
• Define how to assign random numbers to a data set in Excel.
• Recall how to remove Conditional Formatting when it’s no longer needed.

Course Number: 
DR1918
NASBA Field of Study: 
Computer Software and Applications
Level: 
Intermediate
Presenter:
David Ringstrom
Prerequisites: 
Experience with Excel
Advanced Preparation: 
None