Instructor for this course
more

Excel offers payroll professionals many features and functions that can improve the accuracy and efficiency of payroll production. In this comprehensive course, Excel expert David Ringstrom, CPA, explains: mathematics for employee timesheets, date and time formatting and stamping, conditional formatting to highlight Human Resources (HR) requirements, password protection for sensitive payroll files and worksheets, salary information formatting, using pivot tables for Human Resources (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 he uses during the webcast.

Topics covered:

  • Computing the due dates for semi-monthly federal payroll tax deposits by using the CHOOSE and WEEKDAY functions.
  • Discerning the nuance involved in making pivot tables present data in tabular form.
  • Determining the previous Friday when payroll dates fall on a weekend by using the IF and WEEKDAY functions.
  • Calculating a series of semi-monthly dates by way of the IF and EOMONTH worksheet functions.
  • Improving the integrity of Excel pivot tables with the Table feature.
  • Adding fields to a blank pivot table to create instant reports.
  • Redacting portions of Social Security numbers by way of Excel’s TEXT worksheet function.
  • Discovering four different ways to remove data from a pivot table report.
  • Formatting certain words within a column of text with Conditional Formatting.
  • 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.
  • Drilling down into the details behind any amount within a pivot table with just a double-click.
  • Exploring options for recovering lost passwords for Excel spreadsheets.

Learning Objectives

  • Recognize how to reformat Social Security Numbers using Excel’s Flash Fill feature.
  • Discover how to assign random numbers to a data set in Excel.
  • Identify password-protection techniques for sensitive payroll files, worksheets, and spreadsheet columns.
Last updated/reviewed: November 22, 2019

29 Reviews (80 ratings)Reviews

5
Member's Profile
This was an excellent presentation of tools that are key to efficient payroll analysis. The student should invest personal time practicing the applications but the material could not have been presented better. The overview followed by the actual application in the worksheet was extremely helpful in following along. All excel courses should be taught this way. Slides were well formatted and are usable for future reference.
4
Member's Profile
This course was very informative in excel commands. Not gonna lie...I got cross eyed a few times. The course started out pretty simple and then got more complex. The instructor did a great job of going over the functions and was clear speaking and easy to understand.
5
Member's Profile
Great course. Excellent information, great tips. I am proficient at excel & still learning how to work best with Pivot Tables. I was able to learn new information as how to more easily work with both. Would love to have another course with this presenter.
5
Member's Profile
I thought this course was excellent. The content is broken down in a way that is understandable and David walks through the examples so you can see the application first-hand. I would like to learn more about excel with David. He is a great teacher!
4
Member's Profile
This is a lot of information fitted into a short amount of time. I appreciate the tips and tricks, but am glad there are handouts to keep as reference guides! Also, I'm disappointed that this doesn't count as 2 RCH even though it's a 2 hour course.
4
Anonymous Author
The course teaches very helpful functions and tips related to payroll operation. I was not familiar all those nice features covered in this course before. It will be helpful to improve my data analysis capabilities.
5
Anonymous Author
This was one of the best courses I've taken so far. I use Excel in my current position but was not aware of all of the features covered in this course. I will definitely be using most of the features discussed!
5
Member's Profile
Very helpful tips and tricks in excel. I've used excel for years but never used the sumproduct or datedif before and they will be extremely helpful for me in the future to cut down on my work around formulas.
4
Member's Profile
This course was a little confusing for me. I have a weak background in Excel and have had no exposure to Pivot Tables prior to this course. I do think the presenter does a good job, especially with examples.
3
Anonymous Author
Noticed a lot of the content was very similar to some of the other "Excel Efficiency" courses, but overall not a bad review if you need a refresher on pivot tables and date/time related formulas in Excel.
4
Anonymous Author
Great information and presentation. A lot compressed into the two hour window. This material would be easier to digest in a hands on setting where the student can actually test each topic covered.
5
Member's Profile
I enjoyed this course and the instructor's clear instructions and examples. Would definitely recommend this class to anyone who is interested in building their Excel skills.
4
Member's Profile
this was a tough course to do! There are a lot of very detailed pieces of information to remember and retain that you are not using at the time to pass the quiz.
5
Member's Profile
This was a great course to review excel's features! The speaker was easy to follow and the gave an overall of the following material before it was presented.
5
Member's Profile
I learned so many different ways on how to do things and the different versions of Excel itself. Speaker new his information and well expressed it.
5
Member's Profile
good pictures in the slides, well wording, explained functions in details, just the version of excel is too old. good webinar
5
Member's Profile
Great class!! I learned a lot of useful skills. I will definitely use the pivot table information frequently on the job.
5
Anonymous Author
Great presentation both in content and in style. Demonstrating it twice does drive the point home. Thank you !
5
Member's Profile
Really helpful! I learned a lot of great tips that I can't wait to utilize and teach to the rest of the team!
5
Anonymous Author
An excellent review of tools that can be used within Excel for payroll analysis. A very informative course.
5
Member's Profile
I thought the instructor was excellent and very knowledgable, I liked his teaching style, I learned a lot.
5
Member's Profile
Great overview of several functions which relate to payroll as well as other spreadsheet functions.
5
Member's Profile
Very knowledgeable teacher. There are a lot of functions in Excel I didn't know existed.
4
Anonymous Author
the material is extensive but very explanatory and the instructor goes over each step.
5
Anonymous Author
Excellent course. Provided good details on the how-too, shortcuts, and formulas.
5
Anonymous Author
Very good Excel course and love the free templates for future use. Thank you!
4
Anonymous Author
Content will assist in the payroll department and help us analyze effectively.
5
Anonymous Author
A great two hour how to for excel. Easy to follow and understand.
5
Member's Profile
great course. learned alot of new things about pivot tables

Prerequisites

Course Complexity: Intermediate

Program Prerequisites: Experience with Excel

 

Advance Preparation: None

Education Provider Information

Company:
Illumeo, Inc., 75 East Santa Clara St., Suite 1215, San Jose, CA 95113
Contact:
For more information regarding this course, including complaint and cancellation policies, please contact our offices at (408) 400- 3993 or send an e-mail to .
Course Syllabus
INTRODUCTION AND OVERVIEW
  6:54Excel Efficiency: Payroll Analysis
  7:13Flash Fill SSNs (Excel 2013 and later)
  8:14TEXT Function for Social Security Numbers
  7:48Introduction to SUMPRODUCT
  10:29Using DATEDIF to Calculate Tenure
  8:24EOMONTH Function
  9:15Semi-Monthly Payroll Tax Due Dates
  11:43Conditional Formatting - Top 10
  9:52Pivot Table Interfaces
  11:34Preventing Pivot Table Drill Down
  11:314 Ways to Remove Fields
  9:41Managing Pivot Table Layouts & Conclusion
CONTINUOUS PLAY
  1:52:39Excel Efficiency: Payroll Analysis
SUPPORTING MATERIALS
  PDFSlides: Excel Efficiency: Payroll Analysis
  PDFExcel Efficiency: Payroll Analysis Glossary/Index
  XLSXWorkbook: Excel Efficiency: Payroll Analysis
REVIEW AND TEST
  quizREVIEW QUESTIONS
 examFINAL EXAM