Excel Dexterity: Payroll Analysis (video)

  • Credits: 2
  • Format: Self-Study Video
  • Field of Study: Computer Software and Applications
  • Author/Speaker: David H. Ringstrom, CPA

Course ID: Advanced Preparation: Experience Level:
DR-312.20 None Overview
Published Date: Program Prerequisites: Other Course Formats:
© June 2020 Understanding of Technology Self-Study Video

COURSE DESCRIPTION

Excel offers multiple features and functions payroll professionals can implement to improve the accuracy and efficiency of payroll production. In this comprehensive course, Excel expert David Ringstrom, CPA, demonstrates and 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, and using pivot tables for HR reporting and analysis tasks.

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 he uses during the webcast.

Topics include:

  • Redacting portions of Social Security numbers by way of Excel’s TEXT worksheet function.
  • 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.
  • Using Excel’s Text to Columns features to convert Social Security numbers to values and then apply Excel’s Social Security number format.
  • Color-coding the top ten (or however many you wish) amounts within a column of numbers with Conditional Formatting.
  • Revealing the undocumented DATEDIF function in Excel for determining the number of months or years between two dates.
  • Transforming a column of salaries into an instant heat map by way of Excel’s Conditional Formatting feature.
  • Discerning the nuance involved in making pivot tables present data in tabular form.
  • Limiting access to sensitive workbooks by way of password protection.
  • Removing the Table feature from Excel spreadsheets once it’s no longer needed or simply erasing the alternate row shading.
  • 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.

Learning Objectives:

After reading the course material, you will be able 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.

Who Should Attend:

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

  • All Certified Public Accountants (CPAs)

Qualifies and Approved with All State Boards of Accountancy and the following sponsorship’s:

NASBA