There are many features and functions available in Excel that you can use to improve the accuracy and efficiency of payroll production. In this practical webinar, 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
- Using pivot tables for HR reporting and analysis tasks
You will see each technique demonstrated at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Office 365 version of Excel. David draws your attention to any differences in the older versions of Excel (2019, 2016, 2013, and earlier) during the presentation, as well as in his detailed handouts. You will also receive an Excel workbook that includes most of the examples used during the webcast.
Office 365 is a subscription-based product that provides new-feature updates as often as monthly. Conversely, the perpetual licensed versions of Excel have feature sets that don’t change. Perpetual licensed versions have year numbers, such as Excel 2019, Excel 2016, and so on.
You will learn how to:
- Redact portions of Social Security numbers by way of Excel’s TEXT worksheet function
- Identify 4 different ways to remove data from a pivot table report
- Format certain words within a column of text with Conditional Formatting
- Color-code the top ten (or however many you wish) amounts within a column of numbers with Conditional Formatting
- Improve the integrity of Excel pivot tables with the Table feature
- Add fields to a blank pivot table to create instant reports
- Determine the previous Friday when payroll dates fall on a weekend by using the IF and WEEKDAY functions
- Utilize the RANDBETWEEN worksheet function to create a series of random numbers
- Remove Conditional Formatting when it’s no longer needed within a spreadsheet
- Calculate a series of semi-monthly dates by way of the IF and EOMONTH worksheet functions
- Describe the nuances of adding time values together in Microsoft Excel
- Employ pivot tables to pull random sets of employee names, such as for drug testing
Upon course completion, you will be able to:
- Identify the command within the Tools menu, within the Save As dialog box, that enables you to password protect an Excel workbook from being opened or modified
- Recognize the Conditional Formatting feature that enables you to create a heat map
- State the worksheet function that returns a random number between two values that you specify each time the worksheet is recalculated
Suggested Audience: Professionals who wish to learn a variety of Excel features and functions that can improve the accuracy and efficiency of payroll production.
Prerequisites: None. Experience with Excel Recommended.
Advance Preparation: None
Program Level: Intermediate
Field of Study: Computer & Software Applications (2 hours)
Credit Type: Self-Study credit (Please note that to receive Self-Study credit you will need to complete a final exam after viewing the webinar.)