You might not be aware of the many features and functions Excel offers that can improve the accuracy and efficiency of payroll production. In this valuable 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.
- Drilling down into the details behind any amount within a pivot table with just a double-click.
- Removing the Table feature from Excel spreadsheets once it’s no longer needed or simply erasing the alternate row shading.
- Determining the previous Friday when payroll dates fall on a weekend by using the IF and WEEKDAY functions.
- Determining how to calculate the last day of the current month, as well as future or prior months, with the EOMONTH function.
- Exploring options for recovering lost passwords for Excel spreadsheets.
- Calculating a series of semi-monthly dates by way of the IF and EOMONTH worksheet functions.
- Discerning the nuance involved in making pivot tables present data in tabular form.
- Transforming a column of salaries into an instant heat map by way of Excel’s Conditional Formatting feature.
- Formatting certain words within a column of text with Conditional Formatting.
- Utilizing the RANDBETWEEN worksheet function to create a series of random numbers.
- Using Excel’s Text to Columns feature to convert Social Security numbers to values and then apply Excel’s Social Security number format.
- Preventing users from drilling down into pivot table amounts.
Delivery Method: Online QAS Self Study.
Version: David teaches from Excel 2010 in this presentation but every aspect of the material applies equally to Excel 2007, 2013, and 2016.
Prerequisites: Experience with Excel spreadsheets
Advanced Preparation: None