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.

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

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.

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.

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.

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.

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.

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

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 to fetch multiple outputs from a single xlookup formula by returning profit, new customers, and rating for each customer in a unified dynamic array.

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

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.

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.

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

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

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

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.