Start learning
My learning

The Complete Excel Course with AI Integration

Excel course for job seekers facing Excel tests

This guide is for candidates required to demonstrate their spreadsheet skills during a practical interview assessment. It highlights the areas of The Complete Excel Course with AI Integration most relevant to these tests: finding and fixing errors, conditional formulas like IF and SUMIFS, preparing lists, drop-down lists, lookup functions, PivotTables, and charts, alongside the practice challenges placed at the end of several sections.

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:

  • Use INDEX and MATCH, VLOOKUP or XLOOKUP for data retrieval tasks, and handle #N/A errors cleanly with IFERROR.
  • Build COUNTIFS, SUMIFS, IFS and IF formulas to handle multiple conditions, and apply absolute references for accurate copying.
  • Create a PivotTable from raw data, organise it by date, insert a PivotChart and a slicer, and refresh the results.
  • Tidy up disorganised lists by removing duplicate rows, correcting capitalisation using PROPER, and separating names with Flash Fill or Text to Columns.
  • Select and format the appropriate chart for a given question, insert drop-down lists, and use conditional formatting.
  • Identify and resolve #VALUE!, #NAME? and #DIV/0! errors, relying on the Insert Function dialog to verify formula arguments.

Projects

What you will build.

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

  • Nine Excel chart types from the Marketing Engagement Dashboard: combo, bar, funnel, Pareto, waterfall, histogram, box and whisker, sunburst and treemap.

    Project 04

    Marketing Engagement Dashboard

    Nine charts, each picked for the question it answers.

    Adding clear titles, readable data labels and appropriate colours so that an interviewer can immediately understand the visual data.

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

    Organising a raw table into a PivotTable, grouping the results by date, and adding a slicer to filter information as often required in practical assessments.

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

    Practising data retrieval with VLOOKUP, XLOOKUP, and the INDEX and MATCH combination, while substituting #N/A errors with clean results using IFERROR.

  • Project 03

    Sales Goal Tracker

    Conditional formulas like SUMIFS, COUNTIFS, AND, OR, IFS and IF applied to a sales sheet to practise the specific calculations tests frequently demand.

  • Project 02

    Dunder Michael Paper Company employee database

    Data cleaning techniques put into practice by deleting duplicates, correcting text with PROPER, splitting names, and setting up drop-down lists for future entries.

Lessons

The lessons that matter, in course order.

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

Where to start

Lesson 8 Basic maths in Excel

Start with Lesson 8 if you know how to navigate a spreadsheet and enter data, since tests often begin with errors and references. You can try Managing data in Excel free by email alongside its practice files to see if it suits you. Complete beginners should start at Lesson 1. If your assessment is approaching and formulas are already familiar, skip to Lesson 57 for PivotTables and Lesson 67 to practise lookups.

Get the free section by email

63 of 186 lessons · 5 h 10 min

01 Microsoft Excel fundamentals: building a foundation from scratch 5 of 13 lessons · 19 min Core arithmetic and errors on a personal budget, covering SUM and AutoSum, percentage calculations, relative and absolute references, and resolving #DIV/0!, #NAME? and #VALUE! errors with IFERROR.
  1. 8 Start here Basic maths in Excel Add, subtract, multiply and divide with formulas 3 min
  2. 9 SUM and AutoSum in Excel Total income, expenses and savings with MAX 6 min
  3. 10 Relative vs absolute references in Excel Dollar signs and mixed references 3 min
  4. 11 Calculating percentages in Excel Each expense as a percentage of income 2 min
  5. 12 Common Excel errors and how to fix them DIV0, NAME, VALUE errors and IFERROR 4 min
02 Project: personal budget tracker 1 of 5 lessons · 3 min Using conditional formatting tools such as icon sets, data bars and highlight rules to identify and mark specific values requested by an assessor.
  1. 15 Conditional formatting in Excel Highlight rules, data bars and icon sets 3 min
03 Managing data in Excel: sort, filter and tables (Dunder Michael Paper employee database) 9 lessons · 1 h 1 min Practical clean-up operations on an employee database, featuring tables, sorting, filters, TRIM, Find and Replace, Remove Duplicates, Text to Columns, Flash Fill, print settings and frozen headers.
  1. 19 Excel tables Create and format a table, structured references and total row 8 min
  2. 20 Sorting data in Excel Single and multi-level sort on an employee database 6 min
  3. 21 Filtering data in Excel AutoFilter, text and number filters on employee data 10 min
  4. 22 Cleaning data in Excel TRIM, Paste Special values and Find and Replace 13 min
  5. 23 Remove duplicates in Excel Find and delete duplicate rows in an employee list 6 min
  6. 24 Flash Fill in Excel Split first and last names automatically 3 min
  7. 25 Text to Columns in Excel Split data by delimiter into separate columns 6 min
  8. 26 Freeze panes in Excel Lock header rows and columns while scrolling 3 min
  9. 27 Printing in Excel Print area, page orientation, scaling and page layout 6 min
04 Essential formulas and functions (IF, SUMIF and more) 13 lessons · 1 h 17 min Working with the Insert Function dialog, date formulas like MONTH and TODAY, and functions including IF, nested IF, IFS, AND, OR, COUNT, COUNTA, SUMIF, COUNTIF, SUMIFS and COUNTIFS, finishing with a mixed challenge.
  1. 28 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
05 Text functions and data validation (enhancing data quality) 7 lessons · 35 min Enhancing text data with CONCAT, TEXTJOIN, TRIM, UPPER, LOWER, PROPER, FIND, SEARCH and SUBSTITUTE, alongside drop-down lists and Text to Columns, followed by a problem and solution challenge.
  1. 41 CONCAT, the & operator and TEXTJOIN in Excel Combine text from multiple cells 6 min
  2. 42 TRIM, UPPER, LOWER and PROPER in Excel Clean messy text and fix capitalisation 6 min
  3. 43 FIND, SEARCH and SUBSTITUTE in Excel Locate and replace text with formulas 5 min
  4. 44 Text to Columns wizard in Excel Split a column by a comma delimiter 4 min
  5. 45 Data validation in Excel Create drop-down lists for consistent data entry 4 min
  6. 46 Ninja Challenge: problem Clean messy data with text functions and validation 4 min
  7. 47 Ninja Challenge: solution PROPER, Text to Columns and drop-down lists 7 min
06 Data visualisation: charts and graphs 6 of 9 lessons · 28 min Selecting appropriate pie, line, combo or column charts for marketing data, and adjusting colours, data labels and titles to make the information clear and readable.
  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
07 PivotTables and PivotCharts: data analysis made easy 10 lessons · 33 min Building a PivotTable from raw data by placing fields, adjusting Value Field Settings, grouping by date, inserting slicers and PivotCharts, changing layouts and refreshing data, ending with a sales challenge.
  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 Retrieving HR data using VLOOKUP, INDEX and MATCH, and XLOOKUP with its if-not-found argument, plus IFERROR, text functions, date calculations, and the dynamic arrays FILTER, UNIQUE, SORT and SORTBY, concluding with a lookup challenge.
  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

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 prepares job seekers for an Excel test?

The Complete Excel Course with AI Integration from Coding Liquids addresses the topics that commonly appear in practical interviews. It covers list cleaning, choosing and formatting charts, PivotTables with slicers, conditional formulas like IF and SUMIFS, lookups such as VLOOKUP and XLOOKUP, and resolving #DIV/0! and #N/A errors. The 63 lessons listed here represent 5 hours 10 minutes out of the course's 16 hours, and feature practice challenges in several sections.

Will this prepare a job seeker for one particular employer's Excel test?

No, The Complete Excel Course with AI Integration focuses entirely on spreadsheet skills rather than a specific employer's timed platform, question bank, interview technique or numerical reasoning format. It builds the practical abilities those assessments evaluate, using section quizzes and challenges to highlight areas that require more practice.

A job seeker's Excel test is soon. Where should they start?

Begin with the topics you struggle with, as The Complete Excel Course with AI Integration allows you to study sections independently. Jump to Lesson 10 for absolute references, Lesson 22 for list cleaning, Lesson 57 for PivotTables, or Lesson 67 for lookups. Following along with the practice files instead of just watching will help you perform tasks faster under pressure.

Should a job seeker learn VLOOKUP or XLOOKUP for an Excel test?

You should learn both. The Complete Excel Course with AI Integration covers VLOOKUP for older workbooks, XLOOKUP for searching in either direction and its if-not-found argument, and the adaptable INDEX and MATCH combination, all using the same HR data. It also shows how IFERROR replaces #N/A errors with clear results, finishing with a dedicated lookup challenge.

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