Microsoft Office Complete Training Masterclass 2026

Explore the course overview, learn Excel basics, and navigate topic-wise lectures with downloadable resources, playback controls, and tips for optimal video quality in the Microsoft Office complete training masterclass.

Learn to use Excel's fill series to automatically extend numeric and alphanumeric sequences, leveraging the fill handle, double-click, and fill series options to generate sequential numbering and patterns.

Learn how to post a course rating and review, including selecting five stars, writing feedback, and saving progress with save and continue or save and exit, plus ask me later.

Master custom lists in Excel to auto-fill sequences, including months and days, and import lists from cells for use across worksheets.

Explore how to clean data with the text to columns feature in Excel, using fixed width and delimited options to separate names and extract first and last names.

Learn how to clean messy data with Flash Fill to automatically split first and last names, using auto fill options and drag-down techniques.

Remove all blank rows in messy data with a single technique: use go to special to select blanks, then delete entire rows to preserve the first name and last name data.

Master common keyboard shortcuts in Excel to copy, paste, move data, format, print, undo, redo, and apply filters using practical Ctrl shortcuts.

Discover how to master Excel shortcuts by using the alt key to reveal ribbon shortcuts, apply fill color, clear formats, and toggle sort and filter efficiently.

Create a simple marksheet in Excel by using the transpose function to switch data from rows to columns, then paste special and auto-fit columns.

Generate random numbers in Excel using the randbetween function to create a sample data sheet, with numbers between 30 and 95, and use the fill handle to populate across cells.

discover how to remove formulas in excel while preserving the resulting values using paste special values, quick methods, and verification with the formula bar.

Apply the sum formula in a marksheet by using equals, tab to autocomplete, and selecting the horizontal range, then fill down with a double-click or alt+equals.

Use a simple percentage formula in Excel to convert marks obtained out of total marks (five subjects, total 500) into a percentage, and apply percentage style in the home tab.

Use the Excel freeze panes feature to keep headings and names visible as you scroll. Navigate to the view tab to lock the top row and first column.

Learn to define a logical formula using an if then condition in Excel, applied to a student mark sheet to print pass or fail based on 50 percent.

Apply basic conditional formatting in Excel to highlight pass in green and fail in red, using text that contains, highlight cell rules, and manage rules for dynamic formatting.

Apply multiple conditions within a single formula to grade students, using the ifs/if logic to assign fail, d, c, b, a, or a+ based on percentage ranges.

Master sorting and filtering in Excel with a to z and by number or text criteria, using ctrl+shift+l to apply filters and easily isolate grades, colors, or names.

Learn to calculate student rankings in Excel with the rank or position formula, compare percentages across the full range, and fix ranges with absolute references (F4) to prevent drift.

Format an Excel sheet as a table to apply colors, headers with white text, alternating gray and white rows, and built-in filters, using the home tab and Ctrl+T.

Discover how to convert a table to a normal range in Excel, understand absolute and relative references, and adjust formatting and references while preserving data.

Learn to use format painter in Excel to copy formatting across columns and tables. Double-click to apply to multiple ranges without disturbing existing formats.

Remove all formatting from your Excel sheet in seconds using the Alt, H, E, F keyboard shortcuts after selecting data with Ctrl+A.

Explore count, counta, and countblank in a marksheet to identify present and absent students, count numbers, and fill formulas across subjects using range tricks and shortcuts.

Learn the basics of find and replace in Excel, replacing blank cells with a value using ctrl f and replace all, then adjust the data alignment.

Explore using count a and count if in Excel to count non-empty entries, identify absences, and verify totals across a range, with step-by-step practice and upcoming AI functions.

Copy the data column, paste as values only, and remove duplicates to keep unique values. Use these clean values for your calculations.

learn how to count how many students earned grades like A+, B+ using a single countif formula with a fixed range and dynamic criteria across all cells.

Explore essential Excel keyboard shortcuts across general, formatting, navigation, and editing tasks, with Mac and Windows variations, plus practice exercises and downloadable resources.

Learn to leverage ai in excel to build and solve real-world data projects with ai-generated data. Master vlookup with data validation, index match, and dynamic lookup across multiple sheets.

Discover Vlookup basics (vertical lookup) to automatically retrieve a product’s name, id, make, and unit price by entering a product in a search box and displaying all related details.

Explore how named ranges simplify Vlookup by locking the table array, fixing lookup values, and retrieving data across sheets with product_table and exact match.

Edit and extend named ranges in Excel using the formulas tab and Name Manager to redefine ranges, update lookups, and ensure formulas adapt as data expands.

Learn to automatically extend named ranges in Excel by using dynamic tables or adjusting the name manager, so new products update in vlookup formulas without manual edits.

Create a drop-down menu in Excel with data validation by defining a named product list and using it as the source for easy, error-free product selection.

Design custom error alerts for data validation in Excel 2024, turning default rejections into stop, warning, or information messages for invalid product names and misspellings.

Use iferror to replace the na error in Excel with 'not found' and drag the formula to apply it across cells.

Learn to use the match function with vlookup to automatically locate column indices, extract fields like product id, make, and unit price, and drag formulas across many columns.

Master vlookup with approximate match to calculate discounts from a named discount table in excel, applying percentage ranges to sales amounts.

Combine two Excel formulas into one to calculate discount amount from total sales using Vlookup to retrieve percentage, then apply to total sales and format with decimals and dash-style comma.

Clean up Excel formatting by clearing formats, turning data into a table with Ctrl+T, applying table design, converting back to a range, and adjusting date, comma style, and decimals.

Master HLOOKUP with advanced techniques to retrieve employee details via transposed data, using VLOOKUP comparisons, MATCH for dynamic row indexes, and data validation to build drop-down search lists.

Use vlookup on the payroll sheet with a text bands table to fetch the tax percentage based on total pay, compute tax amount, and derive net salary.

Combine two Vlookup results in one formula to calculate payroll totals, including tax amounts and a fixed charge, using text slabs, approximate matches, and the plus formula.

Learn how index match overcomes vlookup's left-to-right constraint by locating a position with match and retrieving values with index, applied to orders, car models, and vendor bids in Excel.

Explore the powerful Xlookup function and compare it with Vlookup, Index Match, and Hlookup, demonstrating dynamic lookup with data validation drop-downs, exact match, wildcards, and not found handling.

Master advanced xlookup functions, replacing vlookup with exact and wildcard matches, handling not found as nil, and building dynamic return arrays across six examples using car parts and monthly revenue.

Learn how to use XLOOKUP with approximate match to assign bonus percentages based on revenue thresholds, with step-by-step examples and practical formula tips.

Learn to fetch multiple outputs from a single xlookup formula by returning profit, new customers, and rating for each customer in a unified dynamic array.

Explore the limitations of Xlookup and learn how to use Sumif to sum profits by country, selecting the range and criteria and applying sum_range across all rows.

Explore how the dget function compares with vlookup, index, match, and xlookup, showing database-based extraction, header-driven field selection, and dynamic country-wise sales results.

See how DGET powers multi-dimensional lookups by combining country and month in a search box to extract precise results more simply than index match.

Compare xlookup with dget to show dget's advantage in handling multiple criteria and dynamic field updates with absolute and relative references.

Compare xlookup with wildcard matching and its limitations, then master dget’s wildcard-based retrieval to pull sales data from long name databases using first or last names.

Explain the limitations of the dget function, including heading dependence and drag constraints, and offer fixes using paste special as values, absolute references, and index match and vlookup comparisons.

Discover how to use Vlookup for large data reconciliation between two accounts, automatically matching invoice numbers, highlighting discrepancies, and drastically reducing manual checks.

Learn to create and use hyperlinks in a workbook to jump between sheets, link to external files, and reference defined names like expenses for January to navigate data.

Explore how to use hyperlinks for data mapping in Excel, linking sheets to an index, creating navigation buttons, and formatting links, including external web pages and screen tips.

Create a hyperlink on the first sheet linking to the data mapping (main) sheet using place in document. Copy and paste the link across all sheets to apply it everywhere.

Set the default hyperlink style by modifying the cell style and applying a chosen font, color, and border settings so all future links format automatically.

Learn to convert all web links into clickable, named hyperlinks using the hyperlink formula, fill down, and apply simple formatting to display friendly names with a consistent color.

Combine multiple Excel sheets into a single sheet using manual merge, vstack, or Power Query, with dynamic updates and pivot table readiness through transform, load, and refresh.

Split each Excel sheet into separate files automatically by enabling the developer tab, inserting a module, pasting a VBA code, and running it to create new workbooks.

Learn to combine multiple Excel files from a folder using Power Query, filtering by XLS files, expanding tables, and loading a unified dataset that refreshes with new data.

Learn to resolve the 'key didn't match any rows in the table' error when combining Excel files with Power Query, by filtering, transforming, and cleaning headers, currencies, and dates.

Master summing sales across multiple Excel sheets with a simple sum formula, autosum, and dynamic references that adjust across sheets to compute weekly totals.

Master conditional sums in Excel with sumif to compute region-wise sales and choose the sum range. Learn how to define range, criteria, and fixing references for accurate drag-down results.

Learn a smarter way to use sumif by applying it with a range and a full column as criteria, letting it select parallel data automatically.

Learn to use countif and average if formulas in Excel to count transactions by region or project. Explore method one and method two with ranges and criteria.

Learn how to use absolute and relative references in formulas by fixing rows or columns with the dollar sign and F4, demonstrated through a multiplication table and tax calculations.

Learn to use the sumifs function to sum sales by two criteria, such as a region and a product, by fixing ranges and using absolute and relative references.

Master sumifs with named ranges created from selection, using create from selection to automate naming, and apply smart absolute and relative references for dynamic sales analysis across regions and products.

Learn to use sumifs by selecting range as criteria and spilling results, naming ranges via create from selection, and applying absolute and relative references for scalable analyses.

Learn to cross verify Excel formulas by using filters to check results against the original data, filtering for north and games to confirm the 452 sales.

Learn to create group totals from a fixed asset class register using the sumif formula, defining range and criteria, and verify results with filters for values above 200,000.

Apply vlookup to fetch item prices from a price list, name the range price_list, and fill data across large datasets; then compute sales totals by multiplying quantities by prices.

Master countif and sumif in Excel, create named ranges, and use wildcards to sum trucks, while covering Boston orders, refrigerators, and washing machines.

Use countifs and sumifs to apply multiple criteria for microwave orders by city and date, then total sales and items for destinations like New York, Boston, and Pittsburgh.

learn how to use auto totaling with subtotals in a dataset by sorting with a custom month list, applying subtotals for multiple fields, and viewing grand totals and level-based summaries.

Learn to change subtotal criteria by sorting data and applying subtotals, producing per-salesperson totals for units sold, customer reach, sales amount, and profit, with three levels and a grand total.

Learn to apply multiple subtotals with the subtotal function by sorting by month in a January–March order, then by salesperson, to show monthly and salesperson totals and the grand total.

Compare the sumif function with subtotals to explain when each works best, noting that sumif supports multiple sheets and data validation for months, while subtotals require a clean single sheet.

Learn how the subtotal function outperforms sum in dynamic data, including filtering and table contexts; apply subtotal with sum, average, and other functions for accurate totals.

master the Excel aggregate function to ignore errors and hidden rows, delivering accurate totals, counts, and averages, and compare it with sum and count.

Learn to apply and combine filters in Excel, including advanced filters, color-based filtering, sorting, and lead source, city, and status examples.

Learn to copy filtered data to another sheet automatically with Excel's advanced filter: select criteria range, copy to another location, keep original data intact, and reapply the filter when needed.

Fix Excel advanced filter error you can only copy filtered data to the active sheet by applying the filter on the correct sheet and using the list and criteria ranges.

Learn how to apply macros to automate tasks using advanced filters, by recording a macro, enabling the developer tab, and refreshing filters across sheets.

Learn to remove and restore grid lines in Excel, apply borders to specific areas, and customize line colors, using the view and home tabs for future projects.

Display the current date and time automatically in Excel using Ctrl+semicolon and Ctrl+Shift+semicolon, with a dynamic formula to track due dates, payments, payroll, and timesheets.

Learn to extract day, month, and year from dates in Excel and place them into separate columns using month, day, and year, with text formatting options.

Learn to combine day, month, and year into a single date with the date function, and use date value and text functions to extract and format date names.

Learn to use Excel time functions like now, hour, minute, second, and time value to format times, extract hours, minutes, and seconds, and build precise time sheets and records.

Learn to clean data in Excel with the trim function, removing extra spaces at the start or end to ensure formulas work, and apply this to practice projects.

Convert text in Excel using the upper, lower, and proper functions to apply uppercase, lowercase, or proper case across datasets.

Learn how to use the substitute function in Excel to replace spaces with underscores and adapt text for named ranges, demonstrated with a simple example.

Explore how the search function in Excel returns the starting character position of a word within text, demonstrated by finding 'band' at the sixth character.

Explore left, mid, and right functions in Excel to extract text, with practical examples like employee IDs and years, and learn alternatives such as text to column and flash fill.

Learn how the text join function in Excel replaces concatenate, sets a space delimiter, ignores empty cells, and joins a range in one step.

Learn to use text before and text after in Excel to extract titles and names from complex strings. Handle multiple titles with dot and space delimiters.

Learn to use the text split function in modern office versions to split data into columns with comma and row delimiters. Use trim and sort to keep results tidy.

Calculate age or years of experience for employees using date difference functions, linking start dates to today and applying the and function to add years, months, and days as text.

Learn to clean data by removing blank spaces in a sample column, using Ctrl+G to go to special and delete entire rows containing blanks, avoiding data misplacement.

Highlight entire rows in Excel using a custom conditional formatting formula. Turn rows green when status in column F is cleared, red when it is uncleared, with manage rules.

Master bank book reconciliation in excel by matching book and bank entries, computing running balances, and using status and conditional formatting to highlight uncleared items.

Explore how to build simple if-then conditional formulas in Excel to determine bonus eligibility based on units sold and targets, calculate sales price, and apply the 8% bonus.

Learn to apply conditional formulas in Excel to enforce two simultaneous criteria—unit sold and customer reach—and calculate a bonus from the sales amount.

Learn to apply or conditions in Excel to qualify bonuses when sales meet units sold or customer reach targets, and to set fixed targets and calculate the bonus.

Apply a combination of if and or formulas in a single cell to determine insurance premium eligibility and 50% company contribution for grade six employees with dependent spouses or children.

Apply complex if conditions with multiple and and or criteria to determine life insurance premium eligibility, using age calculation with datediff and named ranges for grade, dependency, and age.

Prepare a complete data analysis to manage receivables and optimize debt collection using aging buckets (0–30, 31–60, 61–90, older). Clean invoice data, calculate days past, and enable daily automatic categorization.

Master excel analytics for aged debtors by calculating days past due with today and due dates, categorizing 0-30, 31-60, and 61-90 days, with fixed references and error handling.

Learn an advanced technique to compute per-field subtotals in large Excel datasets by adding a status column and using the subtotals feature to display totals selectively.

Apply advanced formatting with the find and replace function to style subtotals across ranges, using bold font, borders, and fills, and contrast with conditional formatting that cannot handle varying ranges.

Apply advanced conditional formatting to format headings across large Excel data sets, using named ranges and formula-based rules to color headings blue with white text and add borders.

Learn how to adjust page layout and print settings in Excel, switch between portrait and landscape, and hide columns to fit a payroll sheet into fewer print pages.

Learn to apply page break preview to control how large Excel sheets print, drag page breaks, adjust margins and column widths, wrap text, and set scaling for clean prints.

Learn to repeat header rows on every printed page in Excel by using page layout and print titles, selecting the top row to repeat across all pages.

Create and manage custom views in Excel to save column sizes, hidden columns, and filters for reusable payroll and report layouts.

Create custom headers and footers in Excel, adding page numbers, dates, file paths, or text and images. Place content in left, center, or right sections and preview with print settings.

Create an automated check printing system by linking Excel and Word using mail merge to print unlimited checks, with VBA spell number, bank-specific templates, and macro-enabled workbook.

link excel payroll data to a word mail merge template to print unlimited checks, inserting date, name, amount in words, and amount in usd.

Learn pivot table basics to analyze sales data by region and salesperson, using subtotals, sumifs, and sumif, and compare inside data totals with outside data totals for clear insights.

Learn to create month and month-year sales reports using a pivot table, grouping dates by months and years, and using collapse and expand for region and product breakdowns.

Learn to use show value as options in a pivot table to display beverage-wise sales totals and their percentage of grand total, with the sales amount shown alongside the percentage.

Learn to create calculated fields in pivot tables, defining profit as sales minus cost of goods sold, and customize region, date, and formatting to analyze sales data.

Learn to use the filter option in pivot tables and the show report filter pages feature to generate salesperson-specific reports across separate sheets.

Learn to build interactive Excel dashboards that consolidate sales data from multiple sheets into a single graphical, filterable page using pivot charts, bar, pie, and line graphs.

Place all charts on a single excel dashboard using pivot tables, creating line, bar, and donut charts with month and year grouping for clear insights.

Learn to create interactive Excel dashboards by using slicers and timelines to dynamically filter charts, group and align visuals, and connect pivot tables for synchronized reports.

Discover how to install free AI functions in Excel via add-ins and use ChatGPT for Excel to format, extract data, and create AI tables in real time.