Microsoft Excel Masterclass : Basic to Advance Training 2026

Name: Microsoft Excel Masterclass : Basic to Advance Training 2026
Rating: 4.7 (212 reviews)

Master Microsoft Excel Basic to Advanced Automation- Formulas, Functions, Data Cleaning, Power Query, Macros and More

Created byAccountech Training & Solutions

Last updated 1/2026

English

What you'll learn

Use Excel’s data cleaning tools (Text-to-Columns, Flash Fill, Trim, Find & Replace) like a pro
Apply logical formulas (IF, AND, OR, nested conditions) in real-world scenarios
Master lookup formulas — VLOOKUP, HLOOKUP, INDEX-MATCH, and XLOOKUP with advanced features
Use DGET for large dataset lookups with fewer limitations
Create dropdown menus, custom error messages, and user-friendly data entry tools
Combine multiple Excel sheets or files automatically with Power Query
Automate tasks using Macros and Advanced Filters
Create professional tables, charts, and dashboards with formatting best practices
Work on real-world projects such as bank reconciliations, payroll tax calculations, and aged debtor reports
Optimize work with Excel keyboard shortcuts and efficiency hacks
Much More

Course content

1 section • 133 lectures • 12h 17m total length

Introduction to Excel Course1:49
Using Fill Series to Work Smartly5:50
Learn to use Excel's fill series to quickly generate numerical and alphanumeric sequences. Drag the fill handle or double-click to fill series and extend patterns, including odd and even numbers.
Important Message2:33
Edit Custom List in Excel8:05
Learn to create and use custom lists in Excel to auto fill months and days. Import lists from cells to reuse across workbooks.
Data Cleaning Technique Using Text to Columns3:19
Learn to clean data in Excel using the Text to Columns feature, choosing fixed width or delimited methods to separate names into first and last names.
Smart Data Cleaning Technique Using Flash Fill2:59
Blank Rows Cleanup Ninja Technique2:49
Efficient Use of Common Keyboard Shortkeys in Excel7:35
Golden Shortkey to Use Complete Excel2:39
Intro to project and use of transpose function3:14
Create a simple marksheet in excel and master the transpose function by pasting data as transpose. Generate five subjects and fifty students with ChatGPT, clean blanks, and auto adjust columns.
Generate Random Numbers Data to Create a Sample Data Sheet2:35
Remove Formula but keep the values in Excel2:27
Applying Sum Formula2:39
Extracting Percentage2:32
Apply a simple percentage formula to convert marks obtained out of total marks into a percentage, format as percentage, and adjust decimal places for readability.
Freeze Multiple Rows and or Columns1:53
Discover how to freeze both the top row and the first column in Excel using the freeze panes feature to keep headings and names visible while scrolling.
Applying First Logical Formula - Simple Use of If Then Condition2:58
Applying Basic Conditional Formatting in Excel2:48
Multiple conditions within an IF function7:03
Dealing With Sort and Filter Basics3:31
Use Rank Or Position Formula In MS Excel4:38
Create and Format Visually Appealing tables in Excel2:51
Remove Table in Excel without losing Data and Convert to Normal Range (Free Prev3:48
Convert a table to a normal range in Excel without losing data, and learn about absolute and relative references, freeze panes, and formatting after conversion.
Basic and Advance Use of Format Painter2:44
Quickest Way to Remove All Formatting1:28
Effective Use of Count Count A Countblank3:02
Learn to use excel count formulas—count, counta, and countblank—to count present and absent students in a marksheet, select ranges, and copy results across subjects.
Basic Use of Find and Replace in Excel1:08
Smartly Use Count A and Count IF3:22
Learn to use Count A to count not empty cells and Count IF to apply a criterion, showing present vs absent and validating totals in Excel.
Remove Duplicate Values From Data1:49
Countif With Multiple Criterias2:29
Keyboard Shortcuts Practice With Tasks6:29
Use of AI In Excel Introduction4:46
Using Vlookup Basics5:22
Applying Vlookup Using Named Ranges6:08
How to Edit and Extend Named Ranges in Excel2:54
How to Extend or Expand the Named Range Automatically in Excel6:53
Extend named ranges automatically in Excel by editing the name in Name Manager, or convert the data to a table and use the table name in Vlookup for automatic updates.
Create a Dropdown Menu in Excel Using Data Validation2:18
Define a named product list and apply data validation to create a drop-down in Excel, enabling easy, accurate selection and eliminating manual entry.
Create Custom Error Alert in Data Validation3:14
How to Replace NA Error in Excel with Custom Text1:59
Using Match Function to Extract Column Index Number Automatically in Vlookup7:24
Applying Vlookup Using Approximate Match4:35
Combining and Mixing of Two Formulas into One2:26
How to Resolve Formatting Issues in Excel2:01
Applying Hlookup Function with Advanced Techniques9:22
Vlookup to Calculate Tax on Employees Payroll3:54
Combining Two Lookups in a Formula4:22
Index Match Function9:53
Amazing Use of Xlookup Function10:28
Xlookup Advanced Functions6:27
Use of Xlookup With Approximate Match3:59
Get Multiple Answers Using Xlookup2:40
Using Sum if Conditions and Xlooup Limitations2:03
Dget vs Vlookup4:11
Using DGET Instead of Index Match is More Powerful4:00
DGET Works Even Better than XLOOKUP4:01
XLOOKUP Wildcard Limitations vs DGET Magic3:17
Limitations and Solutions of DGET6:34
Use of VLookup In Large Data Reconcilliation (Real Company Project)5:55
Effective Use of Hyperlinks4:09
Learn how to create and manage hyperlinks in Excel, linking to sheets, defined names, external files, or a PDF, and navigate to related expenses details efficiently.
Hyperlink for Data Mapping6:39
Map data across multiple Excel sheets by creating a data mapping sheet with hyperlinks to each sheet, adding a main navigation button, and linking external sites for quick access.
How to Add Hyperlinks Across Multiple Sheets in Excel3:13
Change Defaut Colour of Hyperlink2:35
Learn to set a default hyperlink style in Excel by editing the cell style, choosing font, color, border, and fill to ensure all inserted hyperlinks auto-format.
Turn All Web Links into Named Hyperlinks in 1 Click1:17
Combine Multiple Excel Sheets in to One7:22
Split Excel Workbook Into Multiple Files Automatically1:46
Combine Multiple Excel Files in a Folder Using Power Query9:24
How to Resolve Expression error the key didn't match any rows in the table12:27
Creating a Master Total from Multiple Sheets Using Simple Sum Function6:45
Introduction to Sumif4:49
Learn how to use sumif for conditional sums in Excel, calculating region-wise sales and product totals by setting range, criteria, and sum range.
Smarter Use of Sumif Function1:06
Use of Countif and Average if Functions in Excel3:04
Concept of Absolute and Relative References9:11
Applying Two Dimensional Sum Using Sumifs12:57
Sum if using Named Ranges Using Auto Creation Mehthod4:32
Sumifs Functions By Selecting Range as Criteria (Spill Formula Function)1:32:57
Learn to use the Sumifs function with named ranges created from selection. Explore create from selection, name manager, absolute and relative references, and spill formula options.
How to Cross Verify the Accuracy of Formula in Excel1:18
Sumif Test Project4:47
Practice sumif to group asset classes with totals, using ranges and criteria, fixed references, and verification, then sum sales above 200,000.
Test Assignment Task 14:46
Test Assignment Task 29:51
Test Assignment Task 311:42
Concept of Auto Totalling With Subtotal4:06
How to Change Subtotal Criteria1:24
Application of Multiple Subtotals4:03
Comparison of Subtotals Vs Sumif Which is More Effective4:10
Using Subtotal Function is Better than All Common Functions7:40
Learn how the subtotal function outperforms sum, works with any function, and with tables and filters it automatically updates totals and averages.
Why Excel Pros Use AGGREGATE Instead of SUM or AVERAGE5:15
Advance Filters Use in Excel6:17
How to Copy Filtered Data to Another Sheet in Excel Automatically3:26
How to Resolve Excel Error You can only copy filtered data to the active sheet i2:12
How to Apply Macros to Automate Any task (Using Advance Filters With Macros)8:24
How to Remove Gridlines from Entire Sheets Or Specific Cells in Excel2:15
Learn to remove or re-enable Excel grid lines across entire sheets or specific areas by using the view tab and borders, with color adjustments for a clean, professional look.
Show Current Date and Time Automatically Using Shortcut Keys and Formulas2:48
Formula to Extract Day Month and Year From Date in to Seperate Columns3:32
Combine Day Month and Year with the DATE Function and Extracting DATEVALUE3:04
Understanding TIME, HOUR, MINUTE and SECOND Functions4:16
Using TRIM Function in Excel for Data Cleaning0:55
Excel Change Case Using UPPER , lower and Proper Functions1:39
How to Use Substitute Formula in Excel1:23
Use of Search Function in Excel1:33
Left Mid and Right Function in Excel7:03
Explore Excel's left, mid, and right functions to extract text, using search for positions and length, with practical examples and alternatives like text to column and flash fill.
Using Text Join Function1:20
Split Text Easily in Excel with TEXTBEFORE and TEXTAFTER Functions7:07
Master Excel's new text before, text after, and text split functions to extract titles and names using dot and space delimiters, including handling multiple titles with instance and match options.
How to Use TextSplit Function6:45
Calculate Age or Years of Experience7:00
Remove Unnecessary Blank Spaces for Data Cleaning1:46
Highlight Entire Row Using Conditional Formatting5:44
Bank Book Reconciliation in Excel11:52
Dealing With Simple IF Conditional Formula7:19
Working on IF AND Conditional Formulas in Excel6:57
Learn to apply if and conditional formulas in Excel to award bonuses only when unit sales meet 330+ and customer reach of 150, using two methods with fixed references.
Handling IF OR Criterias In Excel5:21
Combination of IF AND and OR Formulas in a Single Cell (Practical Scenario)7:44
Practical Application of IF Conditions With Multiple AND and OR Criterias3:48
Aged Debtors Analysis4:32
Learn to create an aging analysis of receivables in Excel by cleaning data, categorizing invoices into 0-30, 31-60, 61-90, and older days, and calculating days past due.
Aged Debtors Analysis Advance Calculation21:57
Advanced Subtotals in Excel for Large Data Sets6:50
Advance Formatting Technique Using Find and Replace Function4:25
Advanced Conditional Formatting Techniques for Large Excel Data Sets7:35
Page Layout and Print Settings in Excel4:42
Adjust Page Breaks in Excel for Perfect Printing of Large Documents4:34
How to Repeat Header Row on Every Page in Excel - Easy Print Setup1:01
How to Create and Use Custom Sheet Views in Excel3:50
Create Custom Headers and Footers in Excel3:52
Print Unlimited Cheques in Excel Using Cheques Printing System Part 17:45
Learn to build an automatic cheque printing system that prints unlimited checks from Excel into a Word template using mail merge, with spell number VBA code and a macro-enabled workbook.
Print Unlimited Cheques in Excel By Connecting with Word Document Part 28:21
Data Analysis Using Pivot Table Basics13:43
Multiple Criteria Analysis With Pivot Table4:31
How to Use Show Value as Options in Pivot Table2:01
Adding New Custom Fields in a PivotTable using Calculated Fields4:24
Use Filters and Create Multiple Pivot Table Reports with Show Report Filter Page3:19
Dashboard Reporting Basics10:44
How to Place All Charts on a Single Dashboard in Excel Using Pivot Tables6:43
Build Interactive Excel Dashboards Using Slicers and Timelines7:30
Excel Amazing AI Functions to Work Smart Effortlessly5:46
Congratulations0:29

Requirements

No prior Excel experience needed — suitable for complete beginners
Access to Microsoft Excel Latest Version is Prefered But Provided with course as well
A PC or laptop with internet access
Willingness to practice along with real-life exercises provided in the course

Description

Want to take your Microsoft Excel skills from beginner to expert level? Whether you’re a student, business professional, accountant, data analyst, or complete beginner, this practical hands-on course will transform the way you work with Excel — saving you hours of time and boosting your productivity.

We’ll start with the core Excel essentials: smart data entry using Fill Series, editing custom lists, cleaning messy data using Text-to-Columns, Flash Fill, and advanced blank row removal techniques. You’ll also master the most useful Excel keyboard shortcuts that professionals use every day.

From there, you’ll dive into core formulas and functions including SUM, IF, RANK, COUNTIF, AVERAGEIF, VLOOKUP, HLOOKUP, INDEX-MATCH, and the powerful new XLOOKUP. You’ll learn to build dropdown menus, apply data validation, fix errors like #N/A, and combine multiple formulas into powerful solutions.

The course also covers advanced Excel techniques such as:

DGET function for smarter lookups than VLOOKUP or XLOOKUP
Power Query for combining multiple sheets or files automatically
Macros for automating repetitive tasks
Advanced filters for extracting complex datasets
Conditional formatting for professional dashboards and data insights

You’ll work on real-world business scenarios including payroll tax calculations, bank book reconciliation, aged debtor analysis, sales reports, and large dataset management.

By the end of this course, you’ll be able to handle any Excel challenge — from building automated reports to cleaning massive datasets — with the speed and confidence of an Excel power user.

Who this course is for:

Business professionals wanting to improve productivity in Excel
Accountants & Finance Teams looking to speed up reporting and reconciliations
Students & Job Seekers who want in-demand Excel skills for their careers
Data Analysts & Researchers working with large datasets
Anyone who wants to go from Excel basics to advanced automation skills