Analyzing Payroll Data in Excel

Access this expert-led webinar instantly, available anytime on-demand.

Included in All-Access Membership
Live Webinar - no upcoming date
Customer Satisfaction Guarantee Learn with confidence. If you're not happy, we'll make it right. That's our guarantee.

Purchase Options

Select an attendee quantity to add to cart.

Recorded Webinar Only

$219.00
or

All Access Membership

The Aurora All Access Membership is designed to provide you with the training that you want when you want it. You will have 100% access to every live webinar, on demand webinar, professional alert, and podcast that Aurora Training Advantage offers with no additional cost.

Learn More About Our All Access Membership
$599.00
All Access Membership

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. Most features and functions work in Excel for Mac as well, but expect differences.

Excel for Microsoft 365 is a subscription-based product that receives periodic feature updates. Conversely, perpetual 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:

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

• Improving the integrity of Excel PivotTables with the Table feature.

• Retrieving values from Excel tables using XLOOKUP with structured references for dynamic, readable formulas.

• Using Flash Fill to quickly insert reformat data such as Social Security Numbers, or to split text into columns.

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

• Preventing errors from the start by choosing from thousands of free Excel spreadsheet templates.

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

• Creating a PivotTable by adding fields to Rows and Values for a quick total.

• Removing Conditional Formatting rules when they are no longer needed.

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

• Transforming a column of salaries into an instant heat map by way of Excel’s Conditional Formatting feature.

Learning objectives:

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

• State which format code prevents Excel from subtracting entire 24-hour periods when summing two or more time values together.

• Identify the location of the PivotTable command within Excel's ribbon menu interface.

Level: Basic
Format: Live Webcast
Instructional Method: Group Internet Based
NASBA Field of Study: Computer Software & App (2 hours)
Program Prerequisites: None
Advance Preparation: No
  1. Introduction
  2. Topics at a Glance 00:01:22
  3. Presenting with Microsoft 365 for Windows 00:03:55
  4. Section 1: Preparing and Cleaning Payroll Data 00:06:00
  5. Transforming Data with Flash Fill 00:07:27
  6. Cleaning up Numeric Data with the TEXT Function 00:17:37
  7. Computing with the SUMPRODUCT Function 00:24:19
  8. Section 2:  Analyzing Dates and Times 00:28:32
  9. Adding Time Values 00:29:44
  10. Using DATEDIF to Calculate Tenure 00:37:45
  11. Using NETWORKDAYS.INTL to Count Workdays 00:44:14
  12. Section 3: Visualizing and Highlighting Payroll Trends 00:49:31
  13. Heat Mapping Salaries 00:50:16
  14. Removing Conditional Formatting  00:53:37
  15. Highlighting Top 10 Values with Conditional Formatting 00:55:50
  16. Section 4: Assembling Payroll Budget Elements  01:02:04
  17. Converting Supporting Lists to Excel Tables 01:03:40
  18. Building a Payroll Budget Example 01:06:26
  19. Exploring XLOOKUP (Excel 2021+) 01:09:18
  20. Section 5: Using PivotTables for Payroll Insights 01:22:09
  21. Initiating a PivotTable 01:22:37
  22. Adding Fields to a PivotTable 01:26:41
  23. Drilling Down into a PivotTable 01:30:49
  24. Section 6: Randomization and Other Tools 01:34:11
  25. RANDBETWEEN Function 01:34:36
  26. Choosing Random Sets of Employees 01:38:25
  27. Unearthing Free Payroll-Related Templates 01:39:36
  28. What We Covered 01:42:29
  29. Now It’s Your Turn—But I’m Here If You Need Me! 01:43:47
  • David H. Ringstrom, CPA

    David H. Ringstrom, CPA

    David H. Ringstrom, CPA, is a nationally recognized instructor who leads dozens of Excel webinars each year. He is the author of Microsoft 365 Excel for Dummies and several other books. With over 30 years of consulting and teaching experience he empowers users to work more efficiently in E [...]

    View Full Profile

CPE Credit

Continuing Professional Education

Aurora Training Advantage is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.

For more information regarding administrative policies such as complaint and refund, and cancellation please contact our offices at 407-542-4317 or [email protected].

You must answer all questions during the webinar, view the recording completely and pass the test at the end with 70% correct answers to receive CPE credit.

ATAPR Credit

Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in payroll.
  • Artificial Intelligence (AI) 00:01:33
  • Cell 00:08:20, 00:19:17, 00:32:21, 00:51:35, 00:54:10, 01:03:47, 01:22:43
  • Column 00:06:22, 00:08:17, 00:24:48, 00:29:53, 00:48:44, 01:09:46, 01:34:02
  • Column Heading 01:02:56
  • Conditional Formatting 00:02:00, 00:49:36, 00:51:46, 01:38:37
  • DATEDIF 00:28:46, 00:37:48
  • Dialog Box 00:18:08, 01:03:58, 01:38:36
  • Drill Down 00:03:28, 01::22:21, 01:31:13
  • Field 01:22:20
  • Flash Fill 00:01:31, 00:06:12, 00:07:27, 00:18:40
  • Format 00:17:51, 00:32:18
  • Formula 01:23:24
  • Heat Mapping 00:50:20
  • HLOOKUP 01:09:24
  • INDEX Function 01:09:29
  • Keyboard Shortcut 00:09:25
  • LOOKUP Value 01:10:19
  • MATCH Function 01:09:28
  • Microsoft 365 00:03:58
  • Name Box 01:08:55
  • NETWORKDAYS 00:29:38, 00:44:14
  • Number Formatting 00:30:12
  • Pivot Table 00:03:21, 01:22:14, 01:31:27, 01:39:24
  • RANDBETWEEEN 01:34:19
  • Ribbon 01:03:52
  • Row 01:38:27
  • Spreadsheet 00:07:50, 01:02:25, 01:11:19
  • SUMPRODUCT 00:07:06, 00:24:20
  • Table 00:02:57, 01:02:26, 01:11:03, 01:23:07, 01:30:22
  • TEXT Function 00:08:43, 00:17:43
  • Worksheet 00:29:27, 00:38:54
  • XLOOKUP 00:03:02, 01:09:02

Artificial Intelligence (AI): Artificial intelligence is intelligence demonstrated by machines, as opposed to the natural intelligence displayed by humans or animals.

Cell: In spreadsheet applications, a cell is a box in which you can enter a single piece of data. The data is usually text, a numeric value, or a formula. The entire spreadsheet is composed of rows and columns of cells.

Column: A column is a vertical series of cells in a chart, table, or spreadsheet in Excel.

Column Headings : The column heading or column header is the gray-colored row containing the letters (A, B, C, etc.) used to identify each column in the worksheet. The column header is located above row 1 in the worksheet.

Conditional Formatting: A feature on Excel's Home menu that allows you to dynamically apply formatting such as colors, bolding, icons, data bars, and so on based on criteria that you specify for a given set of worksheet cells.

DATEDIF: A worksheet function in Excel that works in any version of Excel but that mysteriously doesn't appear in Excel's online help documentation. DATEDIF has three arguments: Date1, Date2, and Interval. Keep in mind that DATEDIF does not count the starting period, so you may need to add 1 to its result.

Dialog Box: A dialog box in Excel is a screen where you input information and make choices about different aspects of the current worksheet or its content, such as data, charts, and graphic images.

Drill Down: When a user double-clicks on any number within a pivot table, Excel creates a new worksheet that displays the underlying records.

Field: In a PivotTable or PivotChart, a category of data that is derived from a field in the source data. PivotTables have row, column, page, and data fields. PivotCharts have series, category, page, and data fields.

Flash Fill: Flash Fill automatically fills your data when it senses a pattern. For example, you can use Flash Fill to separate first and last names from a single column, or combine first and last names from two different columns. Note: Flash Fill is only available in Excel 2013 and later.

Format: When we format cells in Excel, we change the appearance of a number without changing the number itself. We can apply a number format (0.8, $0.80, 80%, etc) or other formatting (alignment, font, border, etc). By default, Excel uses the General format (no specific number format) for numbers.

Formula: A formula is an expression which calculates the value of a cell.

HLOOKUP: HLOOKUP is an Excel function to lookup and retrieve data from a specific row in table. The "H" in HLOOKUP stands for "horizontal", where lookup values appear in the first row of the table, moving horizontally to the right. HLOOKUP supports approximate and exact matching, and wildcards (* ?) for finding partial matches.

Heat Mapping: To create a heat map in Excel, simply use conditional formatting. A heat map is a graphical representation of data where individual values are represented as colors.

INDEX Function: The INDEX function can be used to return data from within a given range based on a row and/or column number that you specify.

Keyboard Shortcut: A keyboard shortcut is a series of one or several keys that invoke a software program to perform a preprogrammed action. This action may be part of the standard functionality of the operating system or application program, or it may have been written by the user in a scripting language.

LOOKUP Value: Returns the value for the row that meets all criteria specified by one or more search conditions. LOOKUP Value in Power Pivot is the equivalent of VLOOKUP in Excel.

MATCH Function: The MATCH function searches a prescribed range for specified criteria and returns a column or row number if a match is found. MATCH can be used with other functions that require a column or row number.

Microsoft 365: Microsoft 365, formerly Office 365, is a line of subscription services offered by Microsoft which adds to and includes the Microsoft Office product line.

NETWORKDAYS: Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.

Name Box: The Name Box is the box to the left of the formula bar that displays the cell that is currently selected in the spreadsheet. If a name is defined for a cell that is selected, the Name Box displays the name of the cell. You can use the Name Box to define a name for a selected cell as well.

Number Formatting: Number formats are used to control the display of cell values that contain numeric data. This numeric data can include things like dates, times, costs, percentages, and anything else expressed as a number. To apply a number format, just select one or more cells and choose a format.

Pivot Table: A report creation tool in Excel that enables you to quickly summarize lists of data into summary reports by clicking checkboxes and dragging fields onscreen.

RANDBETWEEEN: The Microsoft Excel RANDBETWEEN function returns a random number that is between a bottom and top range. The RANDBETWEEN function returns a new random number each time your spreadsheet recalculates.

Row: A row is the range of cells that go across (horizontal) the spreadsheet/worksheet. Rows are identified by numbers e.g. row 1, row 5. Examples of use. A row might contain the headings of a table e.g. product ID, product name, price, number sold.

SUMPRODUCT: The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products.

Spreadsheet: Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation or computation capabilities, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. Excel forms part of the Microsoft Office suite of software.

TEXT Function: The TEXT function enables you to convert a number in Excel to any number of text formats. For instance, the format code mmmm d, yyyy would transform the date 1/1/2018 into January 1, 2018.

Table: A table is an arrangement of data in rows and columns, or possibly in a more complex structure. Tables are widely used in communication, research, and data analysis. Tables appear in print media, handwritten notes, computer software, architectural ornamentation, traffic signs, and many other places.

VLOOKUP: An Excel worksheet function that allows you to look up data from a list by specifying criteria, cell coordinates for the list, column number from which to return data, and an indication as to whether you want an exact or approximate match.

Worksheets: A worksheet is a collection of cells where you keep and manipulate the data. Each Excel workbook can contain multiple worksheets.

XLOOKUP: The XLOOKUP function searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. Where a valid match is not found, return the [if_not_found] text you supply.

Customer Satisfaction Guarantee
Invest in your future with confidence! Our Customer Satisfaction Guarantee eliminates all risk, letting you focus purely on mastering new skills and advancing your career. If you're not completely satisfied, we'll ensure you are. Your satisfaction is not just a promise; it's our guarantee.

Frequently Asked Questions

Excel offers a powerful suite of functions and features purpose-built for payroll data analysis. The SUMPRODUCT function calculates total payroll and tax obligations across complex multi-condition datasets without requiring helper columns. PivotTables quickly summarize payroll by department, location, or pay period with just a few clicks—and drilling into any total reveals the underlying records instantly. Flash Fill reformats Social Security numbers and cleans inconsistent data in seconds, while the TEXT function handles formatting with precision. For date-based analysis, DATEDIF calculates employee tenure accurately, and NETWORKDAYS.INTL counts actual working days while accounting for custom weekend and holiday schedules. Excel expert David H. Ringstrom, CPA, covers all of these techniques and more in Aurora Training Advantage's Analyzing Payroll Data in Excel webinar, with step-by-step demonstrations in Excel 365 and compatibility notes for older versions.
VLOOKUP has been the standard payroll lookup tool for decades, but XLOOKUP—available in Excel 2021 and Microsoft 365—offers significant improvements. While VLOOKUP requires the lookup column to be the leftmost in the range and returns only one column at a time, XLOOKUP can search in any direction and return multiple columns in a single formula. XLOOKUP also handles errors more gracefully with a built-in 'if not found' argument, eliminating the need to wrap formulas in IFERROR. For payroll professionals working with employee rate tables, benefits data, or tax brackets, XLOOKUP with structured table references creates more readable, self-documenting formulas that are easier to audit. VLOOKUP remains useful for teams on older Excel versions. Aurora Training Advantage's payroll data webinar demonstrates both functions side-by-side so attendees understand when to use each.
Excel's conditional formatting feature transforms a column or range of salary figures into a color-coded heat map that instantly highlights the highest and lowest values—no chart required. To create one, select the salary data range, navigate to Conditional Formatting on the Home tab, choose Color Scales, and select a three-color gradient. The darkest shade marks the highest salaries and the lightest marks the lowest, enabling instant visual identification of outliers, pay compression issues, or inequities across departments. You can also use Conditional Formatting to highlight the top 10 salaries or flag figures outside a defined range. Heat maps are particularly effective in executive presentations where stakeholders need to grasp salary distribution at a glance. Aurora Training Advantage's Excel payroll webinar covers heat mapping in detail, including how to remove or modify conditional formatting rules efficiently.
DATEDIF is a hidden gem in Excel—it calculates the difference between two dates in years, months, or days and is particularly well-suited for computing employee tenure. The function takes three arguments: a start date (hire date), an end date (today's date or a review date), and an interval code ('Y' for complete years, 'M' for months, 'D' for days). One critical nuance: DATEDIF does not count the starting period, so for full-year tenure calculations you may need to add 1 depending on your organization's policy. Unlike simple subtraction, DATEDIF correctly handles varying month lengths and leap years. For HR and payroll professionals managing anniversary bonuses, vesting schedules, or benefits eligibility, accurate tenure calculations are non-negotiable. David Ringstrom's Excel payroll webinar at Aurora Training Advantage demonstrates DATEDIF with practical payroll use cases and common pitfalls to avoid.
PivotTables are Excel's most powerful summarization tool for payroll reporting, enabling professionals to transform thousands of raw payroll records into organized summary reports in minutes without writing a single formula. By dragging and dropping fields into Rows, Columns, and Values areas, you can instantly summarize total pay by department, count headcount by classification, or break down overtime by pay period. Double-clicking any total in a PivotTable drills down to display every underlying transaction that makes up that figure—invaluable for auditing and reconciliation. Converting source data to an Excel Table before building a PivotTable ensures the report automatically captures new records when refreshed. PivotTables also serve as the foundation for PivotCharts, which add visual context to payroll summaries. Aurora Training Advantage's payroll data in Excel webinar includes a complete PivotTable walkthrough tailored to payroll and HR data scenarios.