Microsoft Excel Masterclass : Basic to Advance Training 2026

Discover the Excel interface, identifying cells, rows, and columns in a blank worksheet, and learn cell addresses and the name box for smart navigation.

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.

Review the course workflow by rating your experience after completing the first three videos, including how to rate, edit, and save your feedback to support new content.

Learn to create and use custom lists in Excel to auto fill months and days. Import lists from cells to reuse across workbooks.

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.

Master smart data cleaning with flash fill to automatically extract first names and last names from messy data, using spaces and dashes.

Learn to clean master data by removing entire blank rows in Excel using the go to special command to select blanks and delete rows, avoiding manual row-by-row cleanup.

Master Excel shortcuts to copy, paste, and move data with ctrl c, ctrl v, ctrl r, and drag; apply filters with ctrl shift l and copy down with ctrl d.

Master Excel shortcuts by using the Alt key to reveal menu options, apply fill colors, clear formats with Alt H F, and apply filters with Alt H S F.

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 in Excel with randbetween to populate a sample data sheet. Keep values between 30 and 95 and use the fill handle to copy across.

Learn to remove formulas while preserving values in Excel using paste special values, with quick methods like copy here as values only.

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.

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.

Apply the first logical formula using the if then condition in Excel to determine pass or fail based on a student's percentage below 50%.

Learn how to apply basic conditional formatting in Excel by highlighting pass and fail students, using text that contains rules, and customizing colors with manage rules.

Learn to apply multiple IFS in a single formula to create a grading system. Configure thresholds from 40% to 80%+ and assign pass, fail, and letter grades in Excel.

Sort and filter data in Excel using A to Z, header filters, and the ctrl+shift+l shortcut. Apply number and text filters, including greater than 90 and contains Clark.

Use the rank formula in Excel to determine a student's position among percentages, fix the range with absolute references (F4), and fill down, noting relative versus absolute references.

Learn to visually enhance spreadsheets by formatting as a table, applying header styling, alternating row colors, and using table design to apply filters and quick formats.

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.

Master format painter to copy formatting from a sample column and paste it across other columns, and use double click to apply the format to multiple ranges efficiently.

Discover the quickest way to remove all formats from an Excel sheet using keyboard shortcuts. Select data, press alt+h, e, f or ctrl+a to clear formats fast.

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.

Demonstrates basic use of find and replace in Excel to replace blank cells with a, by selecting the range and choosing replace all, then align text left and numbers right.

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 duplicates and keep unique values in your data by copying a column, pasting as values only, and using remove duplicates in Excel.

Apply a single countif formula with a fixed range to count multiple grades, using criteria from the same column to automatically update as you drag.

Master Excel keyboard shortcuts with a comprehensive set of over 200 keys, covering general, formatting, navigation, editing, and pivot table shortcuts for Windows and Mac.

Learn to use ai in excel to power vlookup with data validation, index match, dynamic data mapping, and hyperlinks, and build advanced projects including payroll taxation and discount tables.

Learn how to use vlookup basics to auto retrieve product details, including name, id, make, and price, via a searchable lookup box with exact and approximate match options.

Master vlookup using named ranges to fix the table array, such as product_table, enabling exact matches and consistent results when dragging across cells.

Edit and extend named ranges in Excel using Name Manager to accommodate new data and keep vlookups and formulas dynamic.

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.

Define a named product list and apply data validation to create a drop-down in Excel, enabling easy, accurate selection and eliminating manual entry.

Design custom error alerts in Excel data validation to prevent invalid entries, and choose stop, warning, or information messages with tailored text.

Learn how to replace NA errors in Excel with custom text using IFERROR, wrapping your formula, specifying the display on error, and dragging the fill handle.

Master how to use the match function to auto determine a column index for Vlookup, enabling dynamic data retrieval across many fields with headings and relative references.

Apply vlookup with approximate match to determine discounts from a named discount table, using the second column for percentages and range-based thresholds.

Combine two formulas into one to compute the discount amount from total sales using vlookup. Multiply by the percentage and save space by avoiding extra columns.

Master quick techniques to clean and standardize Excel formatting, remove inconsistent borders and colors, convert data to a table, and apply short date and comma style formats.

Master hlookup techniques with advanced vlookup-based workflows, including transposing the data, creating a dropdown with data validation, and extracting name, department, date of hire, and monthly pay by employee ID.

use vlookup to apply tax from text labs and text bands in payroll, calculate tax percentage, multiply by total pay, and derive net salary.

Learn to combine two vlookup-derived amounts in a single formula to calculate payroll totals, incorporating tax slabs with lower and upper limits and fixed charges.

Learn how to use index match in Excel to locate an order id and retrieve related data like customer name, car model, and total price, overcoming vlookup's left-to-right limitation.

Discover the power of Xlookup and how it replaces Vlookup, Index Match, and Hlookup with exact, wildcard, and error handling across real-world data examples.

Apply Xlookup with approximate match to determine bonus percentages across revenue thresholds, using slabs, fixed ranges, and next smaller item logic for non exact matches.

Apply a single xlookup formula to return multiple fields: profit, new customers, and rating from a customer database using an exact match and a return array that spills across columns.

Identify the limitations of xlookup for summing repeated country entries and apply sumif to aggregate profits by country, using range, criteria, and sum_range.

Learn how the DGET function quickly retrieves multi-dimensional data by country and month criteria, showing a simpler alternative to index match with examples like Canada in April.

Compare xlookup and dget in excel to handle multiple criteria and dynamic references, and learn when dget outperforms xlookup and how table versus range design affects formulas.

Use wildcard matching in XLOOKUP and DGET to extract sales data from a database by focusing on the main keyword and ignoring surrounding text with static and the and function.

Identify limitations of the dget function, including sensitivity to spaces and headings. Learn fixes using pasted field names, vertical arrangement, and absolute references, with comparisons to index match and xlookup.

Use vlookup to reconcile large data by matching invoice numbers across accounts, highlight non-matching transactions, and compute differences for quick validation.

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.

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.

Create a hyperlink on the first sheet and copy it to all sheets to link to the main data mapping sheet, enabling quick navigation across multiple Excel sheets.

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.

Learn to turn web links into named hyperlinks in Excel using the hyperlink formula, converting URLs to clickable friendly names and automatically filling down across cells, with simple formatting options.

combine multiple excel sheets into a single sheet or file using vstack or Power Query, so you can apply a pivot table and automatically update when data changes.

Split each Excel sheet into separate files or workbooks by enabling the developer tab, pasting a VBA code, and running it for January, February, and March.

Learn to combine multiple Excel files from a folder using Power Query by filtering extensions, expanding tables, and loading a unified dataset with cleaned columns.

combine sales totals from multiple sheets into a single master total using a 3d sum across sheets. use auto sum to total the same cell across months.

Learn how to use sumif for conditional sums in Excel, calculating region-wise sales and product totals by setting range, criteria, and sum range.

Learn a smarter use of the sum if formula in Excel by applying range, criteria, and sum range across a full column to auto-align with parallel data.

Explore countif and average if in Excel to count transactions by region or project, compare methods, set ranges and criteria, and verify results with filters.

Learn how absolute and relative references work in Excel, using F4 and dollar signs to fix columns or rows, and apply them to formulas, tables, and tax calculations.

Master the sumifs function by auto creating named ranges from the top row, using create from selection, and applying absolute and relative references for region and product sales.

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.

Learn to cross verify a formula in Excel by using filters to isolate regions like north and products like games, confirming that 452 sales match the results.

Practice sumif to group asset classes with totals, using ranges and criteria, fixed references, and verification, then sum sales above 200,000.

Practical Excel training demonstrates using vlookup to fetch item prices from a price list, fill missing data, and calculate total sales.

Learn to use countif and sumif (and sumifs) in Excel, create named ranges from data, and apply criteria like Boston and truck qualifiers to summarize orders and sales.

Master countifs and sumifs with multiple criteria to analyze microwave orders in Boston, Peter White's journeys on track one, and date ranges, and to sum sales and items across regions.

Apply auto totaling with subtotals by filtering and sorting, use a custom month order, and sum multiple fields to reveal monthly totals and a grand total with collapsible levels.

Learn to change subtotal criteria by sorting on another column, apply subtotals to sum by salesperson, including units sold, sales amount, and profit, and remove subtotals.

Master applying multiple subtotals in Excel by sorting months and salespersons, then adding subtotals at both levels with a custom January through March order.

Compare subtotals and sumif to show when each is effective. Explain subtotals' single-sheet limitation and sumif's ability to work across sheets and with unsorted data, with a monthly totals example.

Learn how the subtotal function outperforms sum, works with any function, and with tables and filters it automatically updates totals and averages.

Master how the aggregate function in Excel ignores hidden rows and errors, even with nested subtotals, to deliver accurate totals, counts, and averages, outperforming sum and count.

Learn to sort and filter data in Excel, apply basic and advanced filters, use color criteria, and manage results with serial numbers and quick repeat actions.

Use Excel's advanced filter to copy filtered data to another sheet while preserving the original data; specify criteria, copy to a location, and optionally use macros for updates.

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.

learn to display the current date and time automatically in excel using shortcuts and the today function, enabling dynamic updates when opening the sheet for tracking due dates and payroll.

Learn to extract day, month, and year from a date in Excel and place them into separate columns using month, year, and text for full or short month names.

Learn to combine day, month, and year into a single date with the date function, and extract a date value with datevalue in Excel.

Explore Excel time functions such as now, hour, minute, second, and time value; format cells for 24-hour or 12-hour displays; extract hours, minutes, seconds, and combine them into time.

Learn how to clean data in Excel using the trim function to remove extra spaces at the start and end for ready-to-analyze text.

Learn how to use the substitute formula in Excel to replace specific text with another text, such as converting spaces to underscores, and apply it in broader formulas.

Explore how to use the search function in Excel to find a word's starting position in text, with a practical example such as locating 'band' at the sixth character.

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.

Discover how the text join function in Excel replaces concatenate and add, joins a range with a delimiter, ignores empty cells, and fixes extra spaces.

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.

Learn to use the text split function in office versions to split data into columns or rows with comma delimiters, featuring names, departments, products and prices, plus trim and sort.

Calculate age or years of experience by comparing a start date (birth or joining date) with today, updating automatically. Concatenate years, months, and days with text using the and function.

Learn to remove unnecessary spaces in Excel for data cleaning by selecting blanks with go to special and deleting entire rows to prevent errors in filters and formulas.

Learn to highlight an entire row with conditional formatting using a custom formula that checks the status in column f, turning rows green for cleared and red for uncleared.

Learn bank book reconciliation in excel by calculating running balances, matching payments and receipts against bank records, and applying status checks with conditional formatting to mark cleared and uncleared entries.

Manipulate simple if conditional formulas in Excel to determine bonus eligibility based on units sold and sales amount, using named targets and fixed references to automate payouts.

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.

Learn to implement if or conditions in Excel to determine bonus eligibility by evaluating unit sales targets or customer reach. Apply logical tests to flexibly reward top performers.

Apply a single-cell if with and/or logic to decide employee insurance premiums, granting 50% company contribution when grade is six or higher and dependency is spouse or child.

Learn how to use Excel if with multiple and or criteria, including age calculations via datedif, named ranges, and applying a 50% premium rule for eligible employees.

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.

Apply an advanced subtotals approach for large data sets by adding a status column and using an if-based rule to display per-field totals where needed.

Explore advanced data formatting in Excel using find and replace to apply bold, borders, and fill colors to subtotals, overcoming conditional formatting limitations with nonuniform ranges.

Learn how to apply conditional formatting to format all headings across large data sets using two named ranges and a formula, enabling automated blue headers and gray subheadings.

Master page layout and print preview in Excel to manage a large payroll sheet by switching to landscape, hiding unnecessary columns, and adjusting layout for fewer pages.

Learn to adjust page breaks in excel for large documents using page break preview, margins, and column width adjustments to print on fewer pages with scaling from 80% to 100%.

Learn to repeat header rows on every printed page in Excel by using the page layout, print titles, and rows to repeat at the top.

Master custom views in Excel to save and recall payroll layouts, including column widths and hidden columns, for quick printing across different reports.

Learn to create custom headers and footers in Excel for payroll sheets, adding page numbers, dates, and prepared by, signed by, and checked by in left, center, or right sections.

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.

Learn to print unlimited checks by linking an Excel payroll sheet to a Word mail merge template, pulling date, name, and amount in words and USD into each check.

Explore pivot table basics to analyze regional sales, using subtotals and sumifs, and learn to build clean data, remove duplicates, and create versatile pivot reports.

Extract month wise and year wise sales using pivot tables by dragging dates into rows and sales amount into values, then group by months (and years when needed).

Learn to use show value as options in a pivot table to display beverage-wise sales totals and convert them into percentage of grand total for quick contribution comparisons.

Apply pivot table filters to focus the data. Use show report filter pages to generate separate, salesperson-specific reports for regionwise beverage sales.

Create a single-page dashboard that visually presents data with charts and pivot charts, enabling interactive filters by time or region and guiding chart choices like bar, line, or pie.

Learn to place all charts on a single Excel dashboard using pivot tables, including month-wise sales and beverage-wise contribution, with line, bar, and donut or pie charts and data labels.

Learn to build interactive Excel dashboards by using timelines and slicers to dynamically filter charts, connect to pivot tables, and customize views by region, salesperson, and beverages.

Install free ai functions for Excel via the add-ins feature to access ai tools for formatting, data extraction, and ai tables with real-time answers via ai dot ask.