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: August 9, 2023
56 Reviews (171 ratings)

Reviews

5
Member's Profile
This was one of the best courses I've taken! I loved the tips and tricks that are relevant to payroll analysis! I appreciated learning the formula to create a pay date list when pay dates fall on a certain day of the month unless that day is a weekend or holiday - that will be very useful. The instructor was knowledgeable, engaging and went over extremely helpful information for payroll professionals. The supporting material is great. It's definitely something I'll keep to reference while applying what I learned.

5
Anonymous Author
This course provided a great overview of helpful features as well as tips and tricks to more efficiently navigate and streamline data analysis within Excel. I particularly enjoyed the section on pivot tables as this is an area that one can quickly lose if you don't use pivot tables frequently. The instructor presented content in an engaging way, building on previous concepts. The supporting materials were also extremely helpful, and will serve as a point of reference.

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.

5
Anonymous Author
Instructor has a teaching style very conducive to mastering concepts and techniques. He first tells you how to do it, then he shows you how to do it. The flow of the material was consistent, and was presented in a visually graphic manner that was extremely easy to follow. The payroll-specific functions in this lesson can be used in a myriad of ways to produce timely, dynamic and detailed reports.

5
Member's Profile
Has some good excel tips and tricks. A lot of this stuff is pretty common, but if you haven't used excel much the information in this will save you tons of time. I've watched one of my payroll clerks spend a few hours trying to manually turn SS numbers into a standard format she was ammazed when she learned you can just hit a button and fix them all.

5
Member's Profile
Really this is a FIVE STAR instructor! I am not sure when it was recorded, but due to the fast pace of technology updates, and the increased use of apple and MS 365, this could be updated, which is really the only reason to grade this below a five star review. Lots of great information and materials to take away!

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.

5
Anonymous Author
I liked that the instructor discussed a wide variety of topics and after discussing each topic, would physically show the steps to follow. I learned a couple of new formulas that I'll be able to immediately apply to my job.

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
Member's Profile
You think you know everything in Excel until you take this course. We often forget that trial error takes place when using Excel as most know enough to be dangerous. But to know it as it is exactly is difficult.

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.

4
Anonymous Author
This course taught me a lot of great formulas to use for Payroll analysis. I find that we often go a very long way to get to the same answer. These tips will help me cut down time and be more efficient.

4
Anonymous Author
I've never understood pivot tables before, thanks. A discussion of vlookup would be really helpful because pivot table and vlookup are the most requested skills I notice when payroll jobs are advertised.

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 use pivot table in my daily work. This course helped to expand on the functions that I can use to maximize my use of pivot tables in my work. Very well laid out and easy to understand. Thank you!

5
Member's Profile
Great class on Excel. A lot of these formulas I've never seen before so it was really useful for me to see them and get some insight into getting more out of my spreadsheets. Thank you!

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
I enjoyed the text function for Social Security Numbers and password protection for payroll files. I can easily apply some of the functions at work.

5
Anonymous Author
Instructor extremely knowledgeable and helpful! Great tips on some very helpful functions within Excel that I will be able to utilize!

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

4
Anonymous Author
Learn several interesting aspects of excel to assist in payroll analysis and in actual payroll reports available on excel

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
I did not expect to have access to so much great information. The Supporting materials were spot on to my success.

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.

4
Member's Profile
Several payroll-specific and time-saving calculation methods were presented and easy to see and replicate.

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
Anonymous Author
Great course, instructor repeats and demonstrates everything twice. I definitely learned something!

5
Member's Profile
great info...lots of functions available in excel that I wasnt using that will be beneficial

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
Member's Profile
Great course into excel basics. I recommend for anyone trying to get an excel refresher

5
Anonymous Author
Course had great details on many of the Excel functions needed for payroll processing

5
Anonymous Author
Text based course. I need to have at a minimum of 50 characters in order to submit.

5
Anonymous Author
Excellemt course for anyone that hates or is intimidated by excel - highly recommend

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!

5
Anonymous Author
Great course, lots of useful information for new and experienced users of excel.

4
Anonymous Author
Content will assist in the payroll department and help us analyze effectively.

4
Anonymous Author
Great course of excel efficiency : Payroll analysis which added value to me

4
Member's Profile
Good course. with practice and payroll examples based on real life.

5
Anonymous Author
Very useful information, especially the sections on pivot tables.

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

5
Member's Profile
awesome excel tips. Thank you for the great information.

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 Questions and Answers1 Question
Member's Profile

Hi David,

For question 7, The Protect Sheet command appears on which ribbon tab in Excel?, shouldn't the answer be Review and not Data?

Instructor for this course
Course Syllabus
INTRODUCTION AND OVERVIEW
  Excel Efficiency: Payroll Analysis6:54
  Flash Fill SSNs (Excel 2013 and later)7:13
  TEXT Function for Social Security Numbers8:14
  Introduction to SUMPRODUCT7:48
  Using DATEDIF to Calculate Tenure10:29
  EOMONTH Function8:24
  Semi-Monthly Payroll Tax Due Dates9:15
  Conditional Formatting - Top 1011:43
  Pivot Table Interfaces9:52
  Preventing Pivot Table Drill Down11:34
  4 Ways to Remove Fields11:31
  Managing Pivot Table Layouts & Conclusion9:41
CONTINUOUS PLAY
  Excel Efficiency: Payroll Analysis1:52:39
SUPPORTING MATERIALS
  Slides: Excel Efficiency: Payroll AnalysisPDF
  Excel Efficiency: Payroll Analysis Glossary/IndexPDF
  Workbook: Excel Efficiency: Payroll AnalysisXLSX
REVIEW AND TEST
  REVIEW QUESTIONS quiz
 FINAL EXAMexam