Start learning
My learning

The Complete Excel Course with AI Integration

Excel course for tax accountants

For tax accountants, tax advisers and the people who prepare the data behind a return in Excel. This page picks out the parts of The Complete Excel Course with AI Integration that tax work uses: text functions that tidy tax and invoice numbers, lookups for matching a register to the authority's records, conditional totals for computations, Power Query for monthly downloads, PivotTables by rate and month, and a tracker for filing deadlines.

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:

  • Match a purchase register to the tax authority's records on supplier tax number and invoice number with XLOOKUP or COUNTIFS, using a key built with CONCAT or the & operator.
  • Standardise invoice numbers and tax registration numbers before matching with TRIM, UPPER and SUBSTITUTE, and take out a prefix such as a state or country code with LEFT.
  • Pull figures from a trial balance into a tax computation with SUMIFS, and pick the rate band for an amount with nested IF or IFS.
  • Clean and combine monthly downloads from a tax portal with Power Query: import, trim, fix data types, append and combine a whole folder.
  • Summarise tax by rate, state or month with PivotTables and slicers.
  • Track filing deadlines and each client's return with drop-down statuses, overdue highlights and NETWORKDAYS for the working days left.

Projects

What you will build.

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

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

    Monthly downloads handled the Power Query way: each file imported, its text trimmed and types fixed, merged with a lookup table and appended to the months before it.

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

    Matching on a code, the skill a purchase register check needs: a record found in another sheet, a plain result when it is missing, and the parts of a code taken out with LEFT, RIGHT and MID.

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

    Built as a task tracker, ready for returns: a status for each client from a drop-down list, overdue filings highlighted, and the working days left before each due date.

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

    Tax by rate, region or month in a few clicks, ready to set beside the figures on a return.

Lessons

The lessons that matter, in course order.

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

Where to start

Lesson 31 Nested IF and IFS in Excel

Begin at Lesson 31 if you write IF formulas already: rate bands and conditional totals come first, then the text clean-up that each match depends on. If IF is new, begin at Lesson 28; if Excel is new, at Lesson 1. If lookups are routine, go to Lesson 153, where Power Query begins.

61 of 186 lessons · 5 h 3 min

04 Essential formulas and functions (IF, SUMIF and more) 7 of 13 lessons · 45 min Nested IF and IFS for picking a rate band, AND and OR for rules with two conditions, COUNTA for completeness, and SUMIF, SUMIFS, COUNTIF and COUNTIFS for totals by rate and month and for checking that an invoice appears in both lists.
  1. 31 Start here Nested IF and IFS in Excel Customer value tiers with nested IF 6 min
  2. 32 Nested IF and IFS in Excel Sales performance labels with the IFS function 5 min
  3. 33 AND and OR with IF in Excel Flag high-value deals with multiple conditions 8 min
  4. 34 SUM, COUNT and COUNTA in Excel SUM with FILTER and counting numbers with COUNT 7 min
  5. 35 SUM, COUNT and COUNTA in Excel COUNTA, unique counts and completeness checks 4 min
  6. 36 SUMIF and COUNTIF in Excel Conditional sum and count with one criterion 8 min
  7. 37 SUMIFS and COUNTIFS in Excel Sum and count with multiple criteria 7 min
05 Text functions and data validation (enhancing data quality) 7 lessons · 35 min Text clean-up before any match: CONCAT and the & operator for a key of tax number plus invoice number, TRIM, UPPER and PROPER, FIND and SUBSTITUTE for stray characters, Text to Columns, drop-down lists, and a challenge on messy data.
  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
07 PivotTables and PivotCharts: data analysis made easy 10 lessons · 33 min PivotTables for tax by rate, state or month, grouped by date, filtered with slicers and refreshed when a new month arrives. The lessons use sales orders.
  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 XLOOKUP, VLOOKUP and INDEX and MATCH for matching records, IFERROR for invoices missing from one side, LEFT, RIGHT and MID for the parts of a code, EOMONTH and NETWORKDAYS for period ends, and UNIQUE, FILTER and SORT for lists of mismatches.
  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
09 Project: task automation with data validation and forms 9 lessons · 49 min TaskFlow, a tracker that suits a filing calendar: status and priority drop-down lists, overdue items highlighted, a Gantt-style timeline, days remaining to each deadline with NETWORKDAYS and TODAY, and a protected sheet.
  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
15 SalesFusion: advanced data tools and a Power Query modelling system 16 of 21 lessons · 1 h 28 min Power Query for portal downloads: import a CSV, clean columns and text, fix data types, add month fields, merge a lookup table with a left join, append one period to the next and combine a folder of files. The lessons use sales files.
  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

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 tax accountants?

Yes. The Complete Excel Course with AI Integration from Coding Liquids teaches the Excel that tax work leans on: matching a purchase register to the authority's records with XLOOKUP and COUNTIFS, cleaning tax and invoice numbers with text functions, SUMIFS for computations, Power Query for monthly portal downloads, PivotTables by rate and a filing tracker. The 61 lessons here come to 5 hours 3 minutes of the course's 16 hours.

Does it teach GST, VAT or income tax rules?

No. The Complete Excel Course with AI Integration teaches Excel, not tax: income tax, GST and VAT law, return preparation and tax filing software are not covered. It assumes you know the rules and the forms, and shows how to prepare and check the data in Excel: cleaning registers, matching invoices and totalling by rate and month.

Can I reconcile a purchase register with portal data in Excel?

No lesson in The Complete Excel Course with AI Integration does the reconciliation itself, but its steps are taught one by one: TRIM and SUBSTITUTE to tidy tax and invoice numbers, CONCAT to build a key from both, XLOOKUP or COUNTIFS to find each invoice on the other side, IFERROR to mark the missing ones and FILTER to list them. They are practised on HR and sales data.

Can Power Query combine monthly tax portal downloads?

Yes. Power Query in The Complete Excel Course with AI Integration imports CSV files, trims text, fixes dates and numbers stored as the wrong type, adds month columns and appends one table to the next, and a From Folder query picks up each file placed in a folder. The lessons use sales files; downloads from a tax portal take the same steps.

Can it help me track client filing deadlines?

Yes. TaskFlow in The Complete Excel Course with AI Integration is a tracker with drop-down lists for status and priority, highlights for overdue items, the working days left to each deadline with NETWORKDAYS and TODAY, and a protected sheet. It is built for tasks; with one row per client return it works as a filing calendar.

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