Start learning
My learning

The Complete Excel Course with AI Integration

Excel course for software testers

This page is for software testers and QA analysts managing defect logs, test cases and execution tracking in workbooks. It highlights the parts of The Complete Excel Course with AI Integration that this work uses: tidying test data, applying IF and COUNTIFS for pass or fail calculations and defect metrics, building drop-down selectors, following test runs with TaskFlow, and writing inspection macros with FormatWizard.

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:

  • Clean and format test data by trimming whitespace, removing duplicate records, replacing text in bulk and splitting entries across columns.
  • Structure test case tables with unique identifiers created using CONCAT, selecting severity and status values from drop-down menus.
  • Determine pass or fail status for each test by comparing expected outcomes against actual results with IF formulas.
  • Calculate defect age using TODAY, and measure pass percentages alongside unresolved defects grouped by severity and module with COUNTIFS.
  • Monitor a testing cycle using a visual timeline, highlighting delayed items and computing remaining working days prior to release.
  • Create a VBA macro that steps through table rows, validates values and applies a fill colour to cells failing the check.

Projects

What you will build.

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

  • Task tracker in Excel (TaskFlow): start and due dates, status, priority and a progress bar for each task, with working days left, days past due and unfinished high-priority tasks highlighted.

    Project 07

    TaskFlow

    One sheet that shows what is done, what is late and what is urgent.

    Apply this tracker to test management by selecting case statuses from drop-down menus, shading rows according to dates or outcomes, and calculating remaining business days ahead of release.

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

    Construct a row-by-row validation routine using a loop, an If statement to verify cell data, cell colouring for detected failures, and Debug.Print for troubleshooting issues.

  • Project 02

    Dunder Michael Paper Company employee database

    Test data is prepared the way you would handle any long list: purge duplicate records, strip extra spaces, make mass text replacements, and split, sort and filter table columns.

  • Project 03

    Sales Goal Tracker

    Use sales targets to practise evaluating pass or fail conditions with IF, combining multiple criteria via AND and OR, and calculating module pass percentages using COUNTIFS.

Lessons

The lessons that matter, in course order.

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

Where to start

Lesson 19 Excel tables

If you are already maintaining test cases in workbooks, start at Lesson 19. Those completely new to spreadsheets can start at Lesson 1. If you are already comfortable with drop-down menus and standard formulas, jump straight to Lesson 136 to learn macros. The initial segment of this path, Managing data in Excel, is free by email along with its practice files: request access on the course page.

Get the free section by email

55 of 186 lessons · 4 h 59 min

03 Managing data in Excel: sort, filter and tables (Dunder Michael Paper employee database) 9 lessons · 1 h 1 min Get test records ready using Excel Tables, order and filter entries by module or status, tidy strings with TRIM plus Find and Replace, eliminate duplicate defects, separate text fields with Text to Columns and Flash Fill, lock headers with Freeze Panes, and configure print layouts.
  1. 19 Start here 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 Evaluate results logic with formulas: perform TRUE and FALSE checks between expected and actual figures, output pass or fail via IF, categorise severities using IFS, combine two conditions with AND and OR, calculate defect age with TODAY, and tally pass percentages and unresolved bugs using COUNTA, COUNTIF and COUNTIFS.
  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 Generate identifiers for test cases via CONCAT and the & operator, normalise strings using TRIM and UPPER, locate error text inside test outputs with SEARCH and FIND, and enforce consistent severity and status inputs through drop-down menus.
  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
09 Project: task automation with data validation and forms 9 lessons · 49 min TaskFlow, a tracker that serves a test cycle: add priority and status drop-down pickers, shade completed or late rows with conditional formatting, construct a Gantt-style schedule, compute remaining working days via NETWORKDAYS, lock the sheet, and conduct a stress test using sample data before it is used.
  1. 79 TaskFlow project tracker in Excel Overview of the task tracker dashboard 4 min
  2. 80 Designing a task tracker in Excel TaskFlow layout, columns and headers 5 min
  3. 81 Data validation in Excel Status and priority drop-down lists in TaskFlow 8 min
  4. 82 Conditional formatting in Excel Highlight overdue and completed tasks 8 min
  5. 83 Gantt chart in Excel Build a project timeline with conditional formatting 6 min
  6. 84 Data bars and icon sets in Excel Visual priority and progress in TaskFlow 6 min
  7. 85 NETWORKDAYS and TODAY in Excel Days remaining and task deadlines in TaskFlow 5 min
  8. 86 Protect a sheet and lock cells in Excel Secure the TaskFlow tracker workbook 4 min
  9. 87 Testing an Excel task tracker Stress test TaskFlow with sample data 3 min
14 VBA and macros in Excel: FormatWizard 17 lessons · 1 h 16 min Develop FormatWizard macros: begin with recording, then author VBA utilising variables, For Next loops and If Else blocks to traverse worksheets, tint failing cells, validate numeric values with IsNumeric, and log problems via Debug.Print and an error handler. The course exercises validate sales records; comparing expected values to actual results follows that pattern.
  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 an Excel course available for software testers?

Yes. The Complete Excel Course with AI Integration from Coding Liquids addresses the spreadsheet skills needed in manual testing: building test cases in tables with status drop-down lists, using IF to evaluate pass or fail conditions, calculating open defects and pass rates with COUNTIFS, cleaning and de-duplicating test datasets, running a test cycle tracker, and writing VBA to examine worksheets row by row. The curated path on this page highlights 55 lessons, representing 4 hours 59 minutes of the course's 16 hours.

Does the course teach software testers test automation tools such as Selenium, or Jira?

No. The Complete Excel Course with AI Integration teaches Excel rather than testing: it does not cover testing methods, test management systems such as Jira or TestRail, or test automation. Its VBA lessons run checks within a workbook, which works well for comparing expected and actual outcomes on a sheet, rather than driving an application under test.

Can software testers use a VBA macro to compare expected and actual test results?

Yes, by applying the structure that The Complete Excel Course with AI Integration demonstrates in FormatWizard: a For Next loop progresses through the rows, an If Else condition contrasts two values, Interior.Color highlights any failing cells, and Debug.Print alongside an error handler assists with diagnosing faults. The macro demonstrated in the course inspects sales records for missing and non-numeric entries, and a macro comparing expected and actual results is constructed in the same manner.

How can a software tester calculate a test pass rate in Excel?

Tally the successful cases using COUNTIF or COUNTIFS, then divide that figure by the total tests executed, counted using COUNTA. The Complete Excel Course with AI Integration teaches both functions, with COUNTIFS allowing you to segment rates by test cycle or module. First, IF converts each comparison into Pass or Fail, following the lesson that assigns pass or fail outcomes under a single condition.

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