Start learning
My learning

The Complete Excel Course with AI Integration

Excel course for HR analysts

This guide selects material from The Complete Excel Course with AI Integration for HR analysts and people analytics teams reporting on employee data in Excel, rather than maintaining records. It covers headcount, joiners and leavers, IFERROR-protected rates, charts, HR dataset lookups, a dashboard with KPI cards and slicers, and Power Query with a Power Pivot data model.

See the plans Watch free lessons

Taught by Sagnik Bhattacharya and Virginia Thorn

The Complete Excel Course with AI Integration: Sagnik Bhattacharya and Virginia Thorn

What you will be able to do

At work, you will be able to:

  • Produce monthly headcount, joiner and leaver reports using COUNTIFS or PivotTables with date grouping.
  • Work out team attrition and retention rates, using IFERROR to return a blank when a team has no employees.
  • Use SUMIFS to add up each team's absence days and overtime hours, and NETWORKDAYS to count working days.
  • Present departmental engagement survey scores through a PivotTable summary and a PivotChart.
  • Create an HR dashboard that combines headline KPI cards and charts with department slicers and a monthly timeline.
  • Bring monthly HR system extracts together from a folder in Power Query, then link employees and departments in a Power Pivot data model with DAX measures.

Projects

What you will build.

The course has 14 projects. These three matter most here.

  • XLOOKUP formula in Excel (PeopleInsight): each employee row looks up its location from another sheet.

    Project 06

    PeopleInsight HR workbook

    Employee data joined across sheets with one formula.

    Use the course's HR dataset for reporting rather than maintaining records. Bring department and location together in a table, set reporting month ends with EOMONTH and calculate working days with NETWORKDAYS.

  • Marketing dashboard in Excel with slicers at work (MarketPulse): KPI cards, a spend against revenue chart and a monthly ring, filtered to one channel by the Channel and Campaign slicers.

    Project 08

    MarketPulse

    One screen that shows which campaigns earn back their spend.

    Campaign data provides a dashboard design to apply to HR: headcount and attrition KPI cards, IFERROR-protected rates, charts, team slicers and a monthly timeline.

  • Power Query sales report in Excel (SalesFusion): sales by category, sales by region and a monthly trend, with slicers for channel, year, category and region.

    Project 12

    SalesFusion

    A sales report that refreshes itself from a folder of files.

    Apply techniques taught with sales data to monthly HR system extracts: combine files from a folder, connect employees with departments in a data model and calculate DAX measures such as percent of total.

Lessons

The lessons that matter, in course order.

The Complete Excel Course with AI Integration has 186 lessons. This page picks out 68 of them, 5 h 44 min of video. Each lesson keeps its number in the course.

Where to start

Lesson 34 SUM, COUNT and COUNTA in Excel

If entering data and writing simple formulas are familiar, start with Lesson 34: counting is the starting point for HR reporting. For a first introduction to Excel, use Lesson 1. If you already use COUNTIFS and PivotTables routinely, begin the dashboard at Lesson 105, then move to Power Query and the data model at Lesson 153.

68 of 186 lessons · 5 h 44 min

04 Essential formulas and functions (IF, SUMIF and more) 6 of 13 lessons · 36 min Sales examples teach the formulas behind workforce counts: use COUNTA for headcount and COUNTIFS for joiners and leavers within a month or team. SUMIFS adds absence days or overtime hours, while TODAY, MONTH and date arithmetic support tenure calculations and reporting months.
  1. 34 Start here SUM, COUNT and COUNTA in Excel SUM with FILTER and counting numbers with COUNT 7 min
  2. 35 SUM, COUNT and COUNTA in Excel COUNTA, unique counts and completeness checks 4 min
  3. 36 SUMIF and COUNTIF in Excel Conditional sum and count with one criterion 8 min
  4. 37 SUMIFS and COUNTIFS in Excel Sum and count with multiple criteria 7 min
  5. 38 The Excel function library Insert Function dialog and Formula AutoComplete 5 min
  6. 39 Date and time functions in Excel TODAY, NOW, MONTH and date arithmetic 6 min
06 Data visualisation: charts and graphs 9 lessons · 45 min Show workforce patterns with line charts for headcount over time and combo charts comparing headcount with joiners and leavers. Add clear formatting for quick reading, team sparklines and histograms showing how tenure or age is distributed.
  1. 48 Choosing the right Excel chart Column, line, pie, combo and modern chart types 4 min
  2. 49 Column charts in Excel Insert, format and label a column chart 5 min
  3. 50 Line charts in Excel Show trends over time with a line chart 4 min
  4. 51 Pie charts in Excel Show proportions and parts of a whole 4 min
  5. 52 Combo charts in Excel Column and line chart with a secondary axis 4 min
  6. 53 Formatting charts in Excel Titles, data labels, colours and chart styles 7 min
  7. 54 Sparklines in Excel Line, column and win-loss mini charts in cells 5 min
  8. 55 Funnel, Pareto and waterfall charts in Excel Modern charts for business data 5 min
  9. 56 Histogram, box and whisker, treemap and sunburst Advanced Excel chart types 6 min
07 PivotTables and PivotCharts: data analysis made easy 10 lessons · 33 min Learn through sales data how to organise PivotTables for monthly joiners and leavers and headcount by department and location. Use slicers and PivotCharts to explore the summaries, then refresh them when the next month's data arrives.
  1. 57 PivotTables 101 in Excel What a PivotTable does and the sales dataset 5 min
  2. 58 Preparing data for PivotTables Clean data with TRIM, PROPER, find and replace 5 min
  3. 59 Create your first PivotTable in Excel Insert a PivotTable from a sales table 1 min
  4. 60 PivotTable fields in Excel Rows, values, columns and Value Field Settings 2 min
  5. 61 PivotTable summaries in Excel Sales by product category and grouping by date 4 min
  6. 62 Slicers in Excel Filter PivotTables with clickable buttons 3 min
  7. 63 PivotCharts in Excel Create interactive charts from a PivotTable 5 min
  8. 64 PivotTable design Tab Report layouts, PivotTable styles and number formats 5 min
  9. 65 Refresh a PivotTable in Excel Update pivots when source data changes 2 min
  10. 66 Excel Ninja Challenge PivotTable sales by region, category and monthly trend 1 min
08 Advanced Excel functions: lookups, text and arrays 12 lessons · 54 min Work with PeopleInsight HR data to assemble department and location in a table using XLOOKUP, VLOOKUP or INDEX and MATCH. The material also covers IFERROR, month-end reporting dates with EOMONTH, working days with NETWORKDAYS, and team lists using UNIQUE, FILTER and SORT.
  1. 67 Lookup functions in Excel The PeopleInsight HR dataset and why lookups matter 2 min
  2. 68 VLOOKUP in Excel Find employee data across sheets with exact match 4 min
  3. 69 XLOOKUP in Excel Exact match, if not found and two-way lookup 6 min
  4. 70 IFERROR in Excel Handle lookup errors in VLOOKUP and XLOOKUP formulas 3 min
  5. 71 INDEX and MATCH in Excel A flexible alternative to VLOOKUP 6 min
  6. 72 CONCAT and TEXTJOIN in Excel Combine employee names and text with a delimiter 3 min
  7. 73 LEFT, RIGHT and MID in Excel Extract parts of a text code like DAL-SAL-0001 7 min
  8. 74 TODAY, EOMONTH and NETWORKDAYS in Excel Date calculations for HR data 5 min
  9. 75 UNIQUE function in Excel Extract a distinct list with dynamic arrays 6 min
  10. 76 FILTER function in Excel Dynamic array filtering with AND and OR criteria 5 min
  11. 77 SORT and SORTBY in Excel Sort data dynamically with formulas 4 min
  12. 78 Excel Ninja Challenge XLOOKUP, totals and text concatenation lookup challenge 3 min
11 MarketPulse campaign intelligence dashboard 10 lessons · 50 min Build the MarketPulse dashboard from campaign data, beginning with cleaning and checks, then calculating rates with IFERROR and creating PivotTables. Add KPI cards, charts, slicers and a timeline; those cards can also present attrition and absence rates.
  1. 105 MarketPulse marketing dashboard in Excel Project overview and campaign data 4 min
  2. 106 Clean and check marketing data in Excel Remove duplicates, Copilot KPI plan 4 min
  3. 107 Plan a marketing dashboard with ChatGPT KPIs and the PivotTable strategy 4 min
  4. 108 Marketing PivotTables in Excel Build the MarketPulse pivot back end 6 min
  5. 109 CTR, conversion rate and ROI in Excel Marketing KPI formulas with IFERROR 4 min
  6. 110 PivotTables with Copilot Generate and verify marketing pivots in Excel 6 min
  7. 111 Marketing charts and a timeline in Excel MarketPulse dashboard PivotCharts 4 min
  8. 112 KPI cards in Excel Marketing KPI cards for the MarketPulse dashboard 7 min
  9. 113 Slicers for an Excel dashboard Interactive filtering in MarketPulse 4 min
  10. 114 Polish an Excel marketing dashboard Final formatting and insights in MarketPulse 9 min
15 SalesFusion: advanced data tools and a Power Query modelling system 21 lessons · 2 h 7 min In SalesFusion, use Power Query to gather monthly extracts from a folder, clean the data and unpivot wide month columns. Connect the tables through Power Pivot and calculate totals and percent of total with DAX measures. Excel for Windows is required for the Power Pivot lessons.
  1. 153 SalesFusion Power Query project Excel ETL pipeline and data model overview 5 min
  2. 154 Introduction to Power Query in Excel The ETL workflow explained 5 min
  3. 155 Import a CSV with Power Query Get data from text and transform data 2 min
  4. 156 Clean columns in Power Query Remove and rename columns for modelling 5 min
  5. 157 Clean text in Power Query Trim, clean, replace values and fix text case 6 min
  6. 158 Data types in Power Query Fix date, whole number, decimal and text types 7 min
  7. 159 Date columns in Power Query Add year, month and year-month fields for reports 7 min
  8. 160 Business logic in Power Query Custom and conditional columns for profitability 6 min
  9. 161 Group By in Power Query Summarise sales by month and region to validate data 5 min
  10. 162 Close and Load in Power Query Connection only, table or Data Model 5 min
  11. 163 Merge queries in Power Query Look up product and region names with a left join 5 min
  12. 164 Reuse a Power Query pipeline Duplicate queries for a new year of sales data 5 min
  13. 165 Append queries in Power Query Combine 2024 and 2025 sales tables 3 min
  14. 166 From Folder in Power Query Combine every sales file in a folder automatically 9 min
  15. 167 From Folder automation in Power Query Profit metrics and fiscal year columns 7 min
  16. 168 Build a sales fact table in Power Query Merge lookups and append history 6 min
  17. 169 Power Pivot Data Model in Excel Relationships and Diagram View 6 min
  18. 170 DAX measures in Power Pivot Total sales, margin percent and percent of total 10 min
  19. 171 PivotTable report from the Data Model Format the SalesFusion report in Excel 5 min
  20. 172 Interactive Excel dashboard Data model PivotTables, slicers and styling 10 min
  21. 173 Unpivot columns in Power Query Turn wide month columns into a long table 8 min

Instructors

Who teaches it.

Virginia Thorn smiling at the camera, dark hair tied back, wearing a navy top.

Instructor, Excel with AI

Virginia Thorn

Emmy Award winner. Teaches Excel with AI at Coding Liquids.

Questions

Asked before starting.

Which Excel course covers workforce reporting for an HR analyst?

The Complete Excel Course with AI Integration from Coding Liquids teaches Excel tools for HR reporting: COUNTIFS and PivotTables for monthly headcount, joiners, leavers and attrition rates, plus charts. It also covers a dashboard with KPI cards and slicers, and Power Query with a Power Pivot data model for monthly extracts. The 68 lessons selected on this page account for 5 hours 44 minutes of the course's 16 hours.

Will this course help me calculate attrition and turnover rates as an HR analyst?

Yes. You can apply the tools in The Complete Excel Course with AI Integration by counting leavers during a period and people employed with COUNTIFS, then dividing the counts to calculate a rate. IFERROR prevents errors for empty teams, and monthly grouping in PivotTables reveals the trend. There are no dedicated attrition lessons: MarketPulse teaches rate formulas using campaign data.

Can an HR analyst use this course to build an HR dashboard in Excel?

Yes. The MarketPulse project in The Complete Excel Course with AI Integration uses campaign data to build a dashboard supported by PivotTables, with KPI cards, charts, slicers and a timeline. That approach also supports departmental reporting on headcount, attrition and absence. The lookup lessons use HR data itself to bring department and location together in a table.

Does this course teach an HR analyst people analytics statistics or Power BI?

No. The Complete Excel Course with AI Integration teaches Excel, not HR information systems, people analytics statistics such as regression, or Power BI. SalesFusion extends the Excel work to Power Query, a Power Pivot data model and DAX measures. The lessons covering Power Pivot require Excel for Windows.

Excel with AI

See the whole course.

The Complete Excel Course with AI Integration: 16 hours of video in 186 lessons, built around 14 projects, with lifetime access, a Certificate of Completion and a 14-day money-back guarantee.

See the plans Watch free lessons

The same course

For another job, level or topic.

By job

Finance and accounting
HR and office administration
Operations and supply chain
Data and reporting
Sales, marketing and customer service
Business and management
Engineering, construction and manufacturing
Retail, property and hospitality
Healthcare and life sciences
Education and careers
Public sector, non-profit and legal
Technology

By level and topic