Get ahead before the holidays. Individual webinars are $149 throughout October. *Pricing automatically applied in cart.

Excel for HR: Beyond the Basics

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

4.3
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 $149.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 today’s data-driven HR landscape, having a solid command of Excel is no longer optional—it’s essential. This intermediate-level training is designed specifically for HR professionals ready to take their Excel skills to the next level. Through hands-on instruction, participants will learn how to clean, organize, and analyze employee data more efficiently using Excel's powerful features and functions. This session will bridge the gap between basic usage and advanced application, empowering you to make smarter, data-driven decisions.

Participants will work with real-world HR scenarios to gain fluency in tools like VLOOKUP/XLOOKUP, PivotTables, and conditional functions tailored for HR needs. Whether you’re managing large datasets, creating reports, or identifying trends in employee metrics, this session will provide the practical knowledge you need to streamline HR operations and elevate your reporting accuracy.

Topics Covered:
  • Importing HR data
  • Data cleaning and validation techniques
  • VLOOKUP and XLOOKUP for employee data retrieval
  • Using IF, SUMIF, and COUNTIF for conditional analysis
  • Creating PivotTables and PivotCharts for HR data analysis
  • Creating dynamic charts and graphs
  • Conditional formatting to highlight key HR metrics
Your Benefits For Attending:
  • Efficiently manage and organize HR data within Excel
  • Utilize advanced Excel functions and formulas to analyze HR metrics
  • Generate PivotTables and PivotCharts for HR reporting

Attending this webinar will help you become more confident in your ability to manipulate and interpret HR data, leading to improved efficiency, sharper insights, and greater value in your role.

Who Should Attend:

This course is ideal for HR professionals who have a basic understanding of Excel and are looking to advance their skills to an intermediate level. It is tailored for HR managers, HR analysts, recruiters, and anyone involved in data management and reporting within the HR department. Whether you aim to enhance your analytical capabilities, improve your reporting processes, or simply sharpen your Excel skills, this session delivers the tools and strategies needed to succeed.

  1. Introduction
  2. Opening the HR Demo Data Workbook 00:03:06
  3. Understanding Employee Data and Lookup Tables 00:04:06
  4. Using XLOOKUP to Retrieve Employee Salaries 00:07:52
  5. Using XLOOKUP to Identify Department Managers 00:11:06
  6. Copying Formulas and Understanding Cell References 00:12:33
  7. Locking Cell References with F4 00:14:46
  8. Using VLOOKUP as an Alternative to XLOOKUP 00:18:30
  9. Comparing VLOOKUP and XLOOKUP 00:22:01
  10. Creating a Pivot Table to Summarize Salaries by Department 00:25:36
  11. Formatting, Sorting, and Refreshing Pivot Tables 00:31:13
  12. Creating a Pivot Table to Count Employees by Location 00:34:54
  13. Placing Multiple Pivot Tables on an Existing Worksheet 00:35:56
  14. Using Paste Values to Protect Report Data 00:39:54
  15. Creating Charts from Pivot Tables 00:42:04
  16. Customizing Pivot Charts and Removing Field Buttons 00:44:13
  17. Understanding Pivot Table Refresh Requirements 00:46:45
  18. Creating Reports Without Pivot Tables 00:51:37
  19. Removing Duplicate Records in Excel 00:52:38
  20. Using SUMIFS to Calculate Salary Totals by Location 00:54:01
  21. Using COUNTIFS to Count Employees by Manager 00:58:06
  22. Creating Charts from Non-Pivot Table Data 01:03:47
  23. Introduction to Conditional Formatting 01:05:42
  24. Highlighting Managers with Excessive Direct Reports 01:06:15
  25. Creating Conditional Formatting Rules 01:08:17
  26. Using Format Painter to Copy Conditional Formatting 01:10:40
  27. Using Conditional Formatting to Identify Budget Overruns 01:11:49
  28. Managing Multiple Conditional Formatting Rules and Priorities 01:16:35
  29. Importing and Cleaning Data from HR Systems 01:18:18
  30. Preparing Exported Data for Reporting 01:22:26
  31. Introduction to Power Query 01:26:12
  32. Converting an Excel Data Range into a Table 01:27:11
  33. Opening the Power Query Editor 01:29:14
  34. Converting Text to Uppercase in Power Query 01:30:34
  35. Removing Prefixes Using Text After Delimiter 01:31:07
  36. Filtering Employee Records by Leave Date 01:32:01
  37. Removing Unnecessary Columns in Power Query 01:33:18
  38. Loading Cleaned Data Back into Excel 01:34:14
  39. Using the TEXTAFTER Function in Excel 01:35:28
  40. Understanding Pivot Table Rows, Columns, and Values 01:37:09
  41. Applying Filters Within Pivot Tables 01:39:21
  42. Closing Remarks and Questions 01:39:58

  • Mike Thomas

    Mike Thomas

    Mike Thomas has worked in the IT training business for 26 years. His expertise and experience covers designing and delivering training courses, creating written training materials (Quick Reference Guides and step-by-step tutorials), recording and editing video-based tutorials and providing support t [...]

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

HRCI Credit

Human Resource Certification Institute
Browse HRCI-approved webinars and earn recertification credits online. Live and on-demand HR training for PHR, SPHR, and GPHR recertification. Expert-led sessions from Aurora Training Advantage.

SHRM Credit

Society for Human Resource Management
Aurora Training Advantage is recognized by SHRM to offer Professional Development Credits (PDCs) for the SHRM-CPSM or SHRM-SCPSM. For more information about certification or recertification, please visit www.shrmcertification.org.

ATAHR Credit

Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in human resources.
  • Absolute Reference 00:14:46, 00:15:45, 00:15:58, 00:16:14, 00:24:52, 00:57:16, 00:58:49, 01:02:59, 01:03:10
  • Chart 00:39:33, 00:39:46, 00:42:04, 00:42:50, 00:43:00, 00:44:13, 00:45:12, 00:45:55, 00:46:20, 00:46:26, 01:03:47, 01:03:59, 01:04:52
  • CONCATENATE Function 01:37:09
  • Conditional Formatting 00:01:21, 01:05:42, 01:05:54, 01:08:17, 01:11:20, 01:11:34, 01:13:25, 01:14:39, 01:17:07
  • COUNTIFS Function 00:58:06, 01:01:50, 01:02:08, 01:02:18
  • Dashboard 00:35:56, 01:11:51
  • Format Painter 01:10:40, 01:10:57, 01:12:53
  • Pivot Chart 00:39:33, 00:42:04, 00:43:00, 00:45:12, 00:46:26
  • Pivot Table 00:24:25, 00:25:36, 00:28:02, 00:28:24, 00:29:18, 00:29:52, 00:31:13, 00:32:33, 00:32:48, 00:34:54, 00:35:01, 00:35:26, 00:36:30, 00:37:29, 00:38:57, 00:40:05, 00:46:45, 00:49:15, 00:50:03, 00:56:13, 01:37:09, 01:38:19
  • Power Query 01:26:12, 01:27:21, 01:27:52, 01:29:14, 01:30:00, 01:32:26, 01:32:36, 01:34:39, 01:34:44, 01:34:56, 01:35:15
  • Power Query 01:26:12, 01:27:21, 01:27:52, 01:29:14, 01:30:00, 01:32:26, 01:32:36, 01:34:39, 01:34:44, 01:34:56, 01:35:15
  • Refresh 00:32:33, 00:40:05, 00:46:26, 00:49:15
  • Remove Duplicates 00:52:38, 01:01:04
  • SUM Function 01:12:18
  • SUMIFS Function 00:50:46, 00:54:01, 00:54:13, 00:54:26, 00:57:38, 00:58:49, 01:01:50, 01:02:08
  • Table 01:27:11, 01:27:21, 01:27:24, 01:27:34, 01:27:52, 01:28:09, 01:28:18
  • TEXTAFTER Function 01:31:27, 01:31:37, 01:35:51, 01:36:01
  • VLOOKUP 00:02:43, 00:05:41, 00:08:01, 00:18:30, 00:18:31, 00:21:02, 00:21:15, 00:22:01, 00:22:30, 00:23:22, 00:24:37, 00:24:52
  • XLOOKUP 00:01:21, 00:02:43, 00:07:52, 00:08:01, 00:08:09, 00:08:16, 00:08:31, 00:11:20, 00:17:34, 00:22:01, 00:22:30, 00:23:22, 00:24:14, 00:24:52

Absolute Reference : Absolute references in Excel are a direct link to a specific cell or range of cells that remain fixed if you copy or drag the formula. Absolute references are represented by $ symbols. A $ before a column letter freezes the column, while a $ before the row number freezes the row number. You can freeze the column letter and/or row number when needed.

CONCATENATE Function : The CONCATENATE function in Excel is designed to join different pieces of text together or combine values from several cells into one cell.

Chart: In Microsoft Excel, a chart is often called a graph. It is a visual representation of data from a worksheet that can bring more understanding to the data than just looking at the numbers. A chart is a powerful tool that allows you to visually display data in a variety of different chart formats such as Bar, Column, Pie, Line, Area, Doughnut, Scatter, Surface, or Radar charts.

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.

Dashboard: An Excel dashboard is a one-pager (mostly, but not always necessary) that helps managers and business leaders in tracking key KPIs or metrics and take a decision based on them. It contains charts/tables/views that are backed by data. A dashboard is often called a report, however, not all reports are dashboards.

Format Painter: The Format Painter copies formatting from one place and applies it to another. For example, if you have written text in Word, and have it formatted using a specific font type, color, and font size you could copy that formatting to another section of text by using the Format Painter tool.

Pivot Chart: Pivot charts are an adjunct to Excel’s pivot table feature, which allows you to summarize data by dragging and dropping data with your mouse. Pivot charts are much more interactive than the traditional Excel charts.

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.

Power Query: Power Query is a data connection technology that enables you to discover, connect, combine, and refine data sources to meet your analysis needs. Features in Power Query are available in Excel and Power BI Desktop. Power Query is one of three data analysis tools available in Excel: Power Pivot.

Refresh: The Refresh command appears on the Options tab of Excel 2007 and 2010 as well as the Analyze tab of Excel 2013. Pivot tables store a snapshot of the underlying source data, so they don’t immediately reflect changes to said data. You must periodically refresh any pivot table to ensure it reflects any changes to the source data.

Remove Duplicates: This feature first appeared in Excel 2007. This Data tab command allows you to reduce a list of items to a list of unique constituents. This action required the Advanced Filter command in Excel 2003 and earlier.

SUM: Microsoft Excel defines SUM as a formula that “Adds all the numbers in a range of cells”. This definition clearly points that Sum function has a job to add numbers and the arguments can be supplied using combinations of both numbers and range of cells. =SUM The SUM function is a built-in function in Excel that is categorized as a Math/Trig Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the SUM function can be entered as part of a formula in a cell of a worksheet.

SUMIFS: A look-up function in Excel that allows you to add up numbers based upon up to 127 criteria that you specify. Unlike VLOOKUP, the SUMIFS function can add up two or more values and returns zero (instead of #N/A) if no match is found.

TEXTAFTER: The TEXTAFTER function in Excel extracts text following a specific character or substring (delimiter).

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.

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.

Webinar Survey Overall Rating

This webinar received a total of 2 survey responses. Attendees have given an average rating of 4.3 stars out of a possible 5, reflecting the quality and value of the content presented.

Average rating

4.3 / 5
Webinar Presentation
How many of the objectives of the event were met?
4.5 Stars
How useful was the information presented at this event?
4.0 Stars
Overall, how satisfied were you with this event?
4.0 Stars
Speaker Performance
Overall, how satisfied were you with this presenter?
4.5 Stars
How closely did the presenter follow the schedule?
4.5 Stars

Reviews From Webinar Survey

Our webinars are crafted to deliver exceptional value and insight to business professionals. Below, you'll find genuine feedback from attendees.

Patty M.
October 22, 2026
5.0 / 5
Webinar Rating:
5.0 Stars
Speaker Rating:
5.0 Stars
Do you have any other comments, questions or concerns?
no comment
Lindsey N.
October 22, 2026
3.6 / 5
Webinar Rating:
3.3 Stars
Speaker Rating:
4.0 Stars
Do you have any other comments, questions or concerns?
The volume of the presenter was very low compared to the female hosts volume. It was hard sometimes to follow the presenter. I would also like to have written instructions to follow for examples to try when off the call.

Frequently Asked Questions

Lookup functions are essential for HR professionals who need to pull employee information—such as salary, department, job title, or start date—from one dataset into another without manual searching. VLOOKUP finds a value in the leftmost column of a table and returns data from a specified column to the right. For example, =VLOOKUP(EmployeeID, HR_Table, 4, FALSE) retrieves the fourth column value matching the ID. XLOOKUP, available in Excel 2021 and Microsoft 365, is more flexible: it searches in any direction, handles multi-column returns, and includes a built-in not-found message. For HR analysts merging payroll exports with performance data or matching applicant records to job requisition codes, these functions eliminate hours of manual lookup work. XLOOKUP is particularly powerful for matching employee IDs across systems with inconsistent column orders. Mike Thomas covers VLOOKUP and XLOOKUP for employee data retrieval in Aurora Training Advantage's Excel for HR: Beyond the Basics webinar.
COUNTIF and SUMIF are foundational HR analytics functions that allow professionals to tally and total data based on specific conditions without pivot tables. COUNTIF counts the number of cells in a range that meet a criterion—for example, =COUNTIF(Department_Column,"Engineering") counts all Engineering employees. SUMIF adds values based on a matching condition—=SUMIF(Department_Column,"Engineering",Salary_Column) totals salaries for the Engineering department. For multi-condition scenarios, COUNTIFS and SUMIFS accept multiple criteria ranges and conditions simultaneously, such as counting full-time employees in a specific region. HR applications include headcount by department, total overtime hours by team, leave balance summaries, turnover counts by quarter, and salary range analyses. These functions update automatically as data changes, making them ideal for live HR dashboards and monthly reporting templates. Aurora Training Advantage's Excel for HR: Beyond the Basics webinar, taught by Mike Thomas, demonstrates these conditional functions with real HR data scenarios.
PivotTables allow HR professionals to quickly summarize large employee datasets into clear, actionable reports without writing formulas. After creating a PivotTable from an HR data table, you drag fields into rows, columns, values, and filters—instantly generating cross-tabulations such as headcount by department and location, average salary by job grade, turnover rate by manager, or training completion by team. PivotCharts paired with PivotTables visualize these metrics for presentations or dashboards. The drill-down feature lets you double-click any aggregate value to see the underlying employee records. Slicers add visual filter buttons for interactive reports that stakeholders can explore without Excel knowledge. Building PivotTables from Excel Tables ensures automatic inclusion of new employee records as they are added. For HR managers, analysts, and recruiters managing workforce reporting, PivotTables transform raw data into leadership-ready insights in minutes. Aurora Training Advantage's Excel for HR: Beyond the Basics webinar covers PivotTable creation and customization for common HR reporting needs.
Data validation in Excel prevents incorrect entries from corrupting HR datasets by restricting what values can be entered in a cell. Common HR applications include restricting department fields to a dropdown list of valid departments, limiting date fields to a specific range (e.g., no future hire dates), requiring numeric entries within salary bands, or validating that employee IDs follow a specific pattern using custom formulas. Setting up validation via Data > Data Validation lets you configure allowed input types, acceptable ranges, and custom error messages that guide users when they attempt invalid entries. For shared HR workbooks where multiple people enter data, validation reduces inconsistencies in how departments, job titles, or employment types are recorded—improving report accuracy and reducing cleanup time. Conditional Formatting can complement validation by visually flagging outliers or unusual values. Mike Thomas covers data cleaning and validation techniques in Aurora Training Advantage's Excel for HR: Beyond the Basics webinar.
Conditional Formatting (Home > Conditional Formatting) in Excel automatically applies colors, icons, data bars, or bolding to cells based on rules you define—making key HR metrics immediately visible without manual review. HR applications are extensive: highlight employees with performance scores below a threshold in red, flag anyone approaching or exceeding vacation hour limits, color-code salary cells outside the approved band, mark overdue performance reviews with a warning icon, or use data bars to visually compare headcount across departments. Rule types include Greater Than, Less Than, Between, Equal To, Contains Text, and custom formula-based rules for complex logic. Icon Sets display traffic-light or arrow indicators directly in cells. Color Scales apply gradient shading across a range, useful for visualizing attrition rates or engagement scores across teams. Multiple rules can be layered with priority ordering. The Excel for HR: Beyond the Basics webinar at Aurora Training Advantage teaches HR professionals to apply Conditional Formatting strategically for executive-ready workforce dashboards.