Start learning
My learning

The Complete Excel Course with AI Integration

Excel course for payroll administrators

For payroll administrators and payroll officers who prepare the monthly input and check the run in Excel. This page picks out the parts of The Complete Excel Course with AI Integration that payroll work draws on: formulas for pay rules, totals by employee and period, lookups that join records across sheets, working-day calculations, summaries by department, and a macro that checks a file before it leaves your hands.

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:

  • Join timesheets, leave records and new joiners to the employee master with XLOOKUP or INDEX and MATCH, and catch anyone missing with IFERROR.
  • Turn pay rules into formulas with IF, IFS, AND and OR, such as an overtime rate that applies only above a set number of hours.
  • Total pay, overtime and deductions by employee and pay period with SUMIFS, and count records with COUNTIFS.
  • Work out pro-rata pay for joiners and leavers from the working days in their month with NETWORKDAYS and EOMONTH.
  • Summarise payroll cost by department or cost centre with PivotTables, grouped by month and filtered with slicers.
  • Check the payroll file before upload with a VBA macro that flags blanks and non-numeric amounts and colours each one.

Projects

What you will build.

The course has 14 projects. These four 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.

    HR records like the ones payroll input starts from: details found across sheets, gaps handled with IFERROR, and month ends and working days counted with EOMONTH and NETWORKDAYS.

  • Excel VBA macro editor (FormatWizard): a macro that runs every validation check on a sheet and reports when it has finished.

    Project 11

    FormatWizard

    One click that checks a whole sheet for you.

    The project checks a sales sheet; the same macro makes a pre-upload check for the payroll file: it walks down the rows, flags blank fields and amounts that are not numbers, and colours them before anything is sent.

  • Excel PivotTables and a PivotChart of sales data (SalesPlusPerformance): net sales by product category as a column chart, with State, Region and City filters.

    Project 05

    SalesPlusPerformance

    A sales question answered in seconds, straight from the order data.

    Cost by department or cost centre in a few clicks, grouped by pay month, with slicers to look at one team at a time.

  • Project 03

    Sales Goal Tracker

    IF, IFS, AND and OR turn rules into formulas, and SUMIFS totals by more than one condition: the same tools work out overtime bands, allowances and totals by employee and period.

Lessons

The lessons that matter, in course order.

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

Where to start

Lesson 28 Logical functions in Excel

Begin at Lesson 28 if you can already enter data and total a column: pay rules start with IF. If the employee list itself needs tidying first, take the table, sorting and cleaning lessons from Lesson 19. New to Excel? Begin at Lesson 1. If IF, SUMIFS and lookups are already familiar, go to Lesson 136, where the checking macro begins.

52 of 186 lessons · 4 h

04 Essential formulas and functions (IF, SUMIF and more) 13 lessons · 1 h 17 min Pay rules as formulas: IF, nested IF and IFS for bands such as overtime rates, AND and OR for rules with two conditions, SUMIF and SUMIFS for totals by employee and period, COUNTIF and COUNTIFS for headcounts, and date arithmetic with TODAY and MONTH.
  1. 28 Start here Logical functions in Excel TRUE and FALSE comparisons for beginners 5 min
  2. 29 The IF function in Excel Pass or fail results with a single condition 3 min
  3. 30 IF function practice in Excel Categorise sales data with IF formulas 6 min
  4. 31 Nested IF and IFS in Excel Customer value tiers with nested IF 6 min
  5. 32 Nested IF and IFS in Excel Sales performance labels with the IFS function 5 min
  6. 33 AND and OR with IF in Excel Flag high-value deals with multiple conditions 8 min
  7. 34 SUM, COUNT and COUNTA in Excel SUM with FILTER and counting numbers with COUNT 7 min
  8. 35 SUM, COUNT and COUNTA in Excel COUNTA, unique counts and completeness checks 4 min
  9. 36 SUMIF and COUNTIF in Excel Conditional sum and count with one criterion 8 min
  10. 37 SUMIFS and COUNTIFS in Excel Sum and count with multiple criteria 7 min
  11. 38 The Excel function library Insert Function dialog and Formula AutoComplete 5 min
  12. 39 Date and time functions in Excel TODAY, NOW, MONTH and date arithmetic 6 min
  13. 40 Excel Ninja Challenge COUNTIF, SUMIF, IF, COUNTIFS, SUMIFS and IFS practice 8 min
07 PivotTables and PivotCharts: data analysis made easy 10 lessons · 33 min PivotTables for payroll cost by department, cost centre or month, with slicers and a refresh when the next month is added. The lessons use sales orders; the steps are the same for a payroll register.
  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 The PeopleInsight HR dataset: VLOOKUP, XLOOKUP and INDEX and MATCH for joining records across sheets, IFERROR for missing people, LEFT, RIGHT and MID for employee codes, TODAY, EOMONTH and NETWORKDAYS for month ends and working days, then 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
14 VBA and macros in Excel: FormatWizard 17 lessons · 1 h 16 min FormatWizard: a recorded macro, then VBA with loops and If statements that checks each row, flags missing values, tests amounts with IsNumeric and colours what fails, with Debug.Print and an error handler for when something goes wrong.
  1. 136 Excel macros and VBA for beginners What macros are, the FormatWizard project 3 min
  2. 137 Record your first Excel macro Name, run and use absolute references 6 min
  3. 138 Relative references in Excel macros Make recorded macros dynamic 4 min
  4. 139 Developer Tab in Excel Run macros from a button and the quick access toolbar 4 min
  5. 140 Save macros in Excel Macro-enabled workbooks and the XLSM file format 3 min
  6. 141 The VBA Editor in Excel Open it with Alt+F11 and learn the workspace 4 min
  7. 142 Working in the VBA editor Macro security, trust settings, switching to Excel 6 min
  8. 143 Your first VBA code Modules, worksheets, rows, columns and formatting 4 min
  9. 144 VBA variables and data types Sub vs function for Excel beginners 6 min
  10. 145 VBA functions in Excel Build GetLastUsedRow with find and exit function 5 min
  11. 146 Clear old flags with VBA Reset messages and fill colours with if statements 5 min
  12. 147 For Next loop in VBA Flag missing region values in Excel 4 min
  13. 148 VBA If Else and loops Check missing sales data and build a FlagText message 5 min
  14. 149 Highlight cells with VBA Interior.Color and RGB for missing data 5 min
  15. 150 IsNumeric in VBA Validate unit price and amount data types in Excel 7 min
  16. 151 Debugging VBA for beginners Debug.Print and the Immediate window 5 min
  17. 152 Complete Excel validation macro One VBA subroutine with an error handler 3 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.

Is there an Excel course for payroll administrators?

Yes. The Complete Excel Course with AI Integration from Coding Liquids teaches the Excel a payroll run depends on: IF and IFS for pay rules, SUMIFS for totals by employee and period, lookups that join records across sheets on an HR dataset, NETWORKDAYS and EOMONTH for pro-rata days, PivotTables for cost by department, and a VBA check before upload. The 52 lessons here come to 4 hours of the course's 16 hours.

Does the course teach payroll calculations or tax rules?

No. The Complete Excel Course with AI Integration teaches Excel, not payroll: payroll law, income tax and social security rules and payroll software are not covered, and there is no payroll dataset. What it gives you are the formulas a payroll workbook is built from, taught on HR and sales data.

How do I compare this month's payroll with last month's in Excel?

With functions The Complete Excel Course with AI Integration teaches: XLOOKUP brings last month's figure next to this month's for each employee, IFERROR marks joiners who have no earlier row, a subtraction gives the change, and FILTER lists the rows where the change is not zero. The lessons use HR and sales data, not a payroll file.

Can a macro check the payroll file before it is uploaded?

Yes. FormatWizard in The Complete Excel Course with AI Integration is a VBA macro that loops through a sheet, flags blank fields, tests amounts with IsNumeric, colours each problem and reports through Debug.Print, run from a button. It is built on a sales sheet; adapted to the columns of a payroll file, the same checks apply.

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