In this presentation, author and Excel expert David H. Ringstrom, CPA, will guide participants through various payroll-related Excel techniques. Topics covered include contrasting using Flash Fill versus the TEXT function to reformat Social Security Numbers. You'll see how to calculate total payroll and total payroll taxes with the SUMPRODUCT function for data analysis, and understand the nuance of adding up time values in Excel. David will also show how to calculate employee tenure with the DATEDIF function, optimize work schedules with the NETWORKDAYS.INTL function, and applying heat mapping techniques to salary data. He'll also contrast using VLOOKUP in any version of Excel versus XLOOKUP in Excel 2021 and Excel for Microsoft 365 for looking up data from lists. Attendees will gain valuable insights and skills to enhance their Excel proficiency and efficiency.

David is the author of “Microsoft Excel 365 for Dummies”, “Exploring Microsoft Excel’s Hidden Treasures”, and has written or co-authored six other books. He demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Excel for Microsoft 365. David draws your attention to any differences in Excel 2021, 2019 or 2016 during the presentation and in his detailed handouts. The handouts include an Excel workbook with most of the examples he uses during his demonstrations.

Excel for Microsoft 365 is a subscription-based product that receives periodic feature updates. Conversely, perpetually licensed versions have year numbers in their names and do not receive any feature updates.

Who should attend: Professionals seeking to use Microsoft Excel more effectively.

Topics typically covered:

• Revealing the undocumented DATEDIF function in Excel for determining the number of months or years between two dates.

• Adding rows to a blank PivotTable to create instant reports.

• Using Flash Fill to quickly insert dashes into a column of Social Security or telephone numbers.

• Discovering four different ways to remove data from a PivotTable report.

• Improving the integrity of spreadsheets with Excel’s VLOOKUP function.

• Drilling down into the details behind any amount within a PivotTable with just a double-click.

• Determining how to calculate the last day of the current month, as well as future or prior months, with the EOMONTH function.

• Expanding reports horizontally by adding columns to PivotTables.

• 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.

• Gleaning the nuances of adding time values together in Microsoft Excel.

• Redacting portions of Social Security numbers by way of Excel’s TEXT worksheet function.

• Utilizing the RANDBETWEEN worksheet function to create a series of random numbers.

Learning objectives:

• Recall the number of arrays that the SUMPRODUCT function allows.

• Recognize a unique characteristic of Excel's DATEDIF worksheet function.

• Recall the worksheet function that returns the last day of a month based on a specified number of months in the past or future.
 

Level: Basic

Instructional Method: Group Internet based

NASBA Field of Study: Specialized Knowledge (2 hours)

Program Prerequisites: None

Advance Preparation: None