Course Description
In this eye-opening presentation, Excel expert David Ringstrom, CPA, shares tips, tricks, and keyboard shortcuts to help you analyze payroll data in Excel. You’ll discover ways to accomplish tasks that might currently be taking you several minutes or even hours to complete. David explains a variety of Excel’s payroll features and shows you how to filter data faster, streamline repetitive tasks, create keyboard shortcuts, and access folders and workbooks easily.
David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Microsoft 365 (formerly 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. David also provides an Excel workbook that includes most of the examples he uses during the session.
Microsoft 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.
Topics covered:
- 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
- Drilling down into the details behind any amount within a pivot table with just a double-click
- Improving the integrity of Excel pivot tables with the Table feature
- Color-coding the top ten (or however many you wish) amounts within a column of numbers with Conditional Formatting
- Discovering the capabilities of the SUMPRODUCT function for calculating payroll and other amounts
- Discovering four different ways to remove data from a pivot table report
- Distinguishing the differences among pivot table-related menus in Excel 2013 and later versus older versions of Excel
- Removing Conditional Formatting when it’s no longer needed within a spreadsheet
- Preventing errors from the start by choosing from thousands of free Excel spreadsheet templates
- Transforming a column of salaries into an instant heat map by way of Excel’s Conditional Formatting feature
- Adding rows to a blank pivot table to create instant reports
- Formatting certain words within a column of text with Conditional Formatting
Learning objectives:
- Recall how to reformat Social Security Numbers using Excel’s Flash Fill feature
- Define how to assign random numbers to a data set in Excel
- Apply password-protection techniques for sensitive payroll files, worksheets, and spreadsheet columns
Review our course policies and procedures page for further information