Microsoft Excel 2021/365 Masterclass: Advanced Excel Course

Learn how dynamic arrays in Excel 365/2021 let a single formula return multiple results, using the six functions sequence, ran array, unique, sort, sort by, and filter to simplify spreadsheets.

Explore spills and dynamic arrays in Excel, including vlookup examples, csc arrays, filter and sort by, proper case, and the concept of spill ranges that auto-expand.

Explore the difference between Excel's unique and distinct: distinct lists all values, while unique returns items that appear exactly once, illustrated with country examples and table usage.

Learn to use the unique function to extract multiple criteria, pulling country, population, and region into a single table of unique rows with dynamic updates.

Use the unique function to extract values by column, not by row, leveraging the by column option and true or false arguments to identify distinct crayon packs and unique colors.

Master the Excel sort function to sort data by single or multiple columns, using an array, sort index, and ascending or descending order, with options for multi-criteria sorting.

Sort data horizontally using the sort function and text join to consolidate actor lists. Identify actors who share the same movie by sorting, joining, and highlighting duplicates.

Use the sortby function to perform a horizontal sort, ranking columns by an array to reorder title, first name, last name, department, and salary, and apply currency formatting.

Explore the sequence function to generate a grid of numbers with configurable rows, columns, start, and step; then apply it to date schedules and stacking records with index.

Use the Excel 2021 filter function to filter exam results by exam, date, and grade within a named table, building multi-criteria filters with data validation dropdowns and unique lists.

Learn to apply the filter function with or logic using the plus operator to filter by multiple criteria, such as maths exam or grade C, in Excel.

Master the and-logic in Excel's filter by using the asterisk operator to combine exam and grade criteria, and convert data into a dynamic table for automatic updates.

Discover rand array, dynamic arrays, and rand between in Excel 2021 to generate random numbers, with configurable rows, columns, min, max, and decimal or integer options, plus practical data randomization.

Discover how xlookup simplifies complex lookups with dynamic arrays, returning multiple fields and handling not found, first to last, and binary search.

Explore XMATCH, a dynamic array formula that extends MATCH and unlocks powerful lookups. It uses five arguments, wildcard lookups, and search modes to return positions and enable index-based lookups.

Practice dynamic array functions in Excel to extract unique months, count them, filter and sort by sales agent and month, and use XLOOKUP and RANDARRAY for dummy data.

Master two-way lookups using index and match and the xlookup function to retrieve sales by month and company from a data table.

Practice two-way xlookup with data validation dropdowns for months and teams to return sales totals, then use choose and randbetween for team colors and switch to map ratings to grades.

Explore how to use median and mode in Excel, including mode.single and mode.multi, their compatibility with older versions, and interpreting odd versus even data with dynamic arrays.

Explore large and small to identify the second through fifth largest and smallest values in a data range, using ifs for genre filtering and F4 locking for formula copying.

Rank data in a list using RANK.EQ and RANK.AVG, illustrated with movies and five-star reviews. Discover ranking basics, ties handling, and descending order options.

Learn how to use the count blank function to count empty cells in a range, update results with tables, and analyze how many days employees are off to optimize scheduling.

Learn to use round, round up, and round down for precise money values, including penny, dollars, and negative-digit rounding.

Explore specialized rounding in Excel using m round, ceiling dot math, and floor dot math to round to custom multiples like 0.5 or 0.1, with optional mode for negatives.

Learn how to use the new lambda function in Excel to create custom functions with user friendly names, reuse complex calculations, and manage them with name manager.

Learn to build let-based range variables and filter calculations, then wrap a countifs in a lambda to create reusable functions like count company sales.

learn how to create and apply a custom pivot table style in excel, including branding colors, theme tweaks, and formatting options for table elements, filters, and totals.

Apply custom number formatting in pivot tables using the value field settings and formatting options. Discover layout choices like tabular form and how to display zero values as no sales.

Sort pivot table data by the sum of profit, or drag items to a specific order. Create a custom list in Excel to apply that order across multiple pivot tables.

Learn to add and customize slicers in pivot tables and charts, including layout, styles, report connections, and date timelines for visual, interactive data filtering.

unlock the slices, protect the sheet with a password, and let users filter with slicers while the pivot table remains locked.

Create calculated fields in a pivot table to add tax and profit plus tax, name formulas using field headers, apply consistent formatting, and visualize results with conditional symbols.

Compare calculated fields and calculated items in pivot tables, create a calculated item for Royal Oak, and format and refresh the pivot to show percentage of sales.

Use the get pivot data formula to extract pivot table values, build a data preparation table, and create regular charts that show all months with slicer-driven dynamics.

Create a dynamic pivot chart title that updates with slicer selections by using a helper sheet, text join, and a linked cell to display total gross sales.

Build a fully dynamic stacked column chart by adding a dynamic data-label series using a scatter plot to place 2018 and 2019 labels mid-bar, powered by get pivot data.

Learn to build a stacked column chart, add a dynamic total as a new series, and display totals as top markers above each stack with formatted data labels.

Create a pivot table from service desk data on the chart worksheet, export counts by state to a filled map chart, and use a status slicer with a dynamic title.

Insert a combo box form control linked to a months list to return the selected month's position, then use index formulas to show the month name and revenue.

Create interactive reports in Excel by adding a checkbox to toggle salary visibility, linking to a true/false cell, and using conditional formatting to show or hide the salary column.

Use option buttons as form control in Excel to switch between sick days and holiday days, linking a cell and using index formula to pull data from data preparation table.

Add a spin button form control in Excel to cycle through employees via a linked cell, then use index and vlookup to display the selected name and salary.

Select an employee from the list box to show the salary and title, using an input range, cell link, and index formula with text, with a table for auto updates.

Use a scrollbar form control to navigate an employee list in Excel, linking a cell to drive an index-based formula that returns names and salaries, with proper absolute references.

Create a combo box form control in Excel, populate it with months, and link it to a cell to drive a dynamic chart updated by index and match.

Power Query imports data from multiple sources, cleans and transforms it via a graphical interface, uses Get and Transform to import, refresh updates with one click, and exports to Excel.

Navigate the Power Query Editor interface, learning to view data, rename queries, and track applied steps, while understanding how to expand and combine multiple files into one dataset.

Combine multiple sales data files into a single dataset using Power Query, preview samples, review applied steps, and prepare a clean, correctly typed dataset for Excel loading.

Tidy dataset in power query by removing unused columns, setting data types, and splitting product into product and location; add total from unit price and sales quantity, formatted as currency.

Close and load the cleaned data from Power Query into Excel as a table, enabling automatic updates and pivot table analyses with currency formatting adjustments when needed.

Explore how to forecast in Excel using the Fred add-in to import US data from the Federal Reserve Bank of St. Louis, then apply forecast sheets for practice forecasting.

Explore how to forecast sales in Excel using forecast functions, emphasizing time-based data with dates and values, and compare linear forecasts to seasonality-aware methods with charts.

Learn to forecast seasonal sales in Excel using the forecast.ets function with exponential smoothing, compare it to linear forecasts, and visualize results with line charts.

Explore advanced forecasting in Excel by generating an ETS forecast with confidence intervals, calculating upper and lower bounds, and visualizing them with forecast sheets.

Practice forecasting temperature data in Excel by using forecast sheets to project 2022 and 2023, with 95% confidence interval and seasonality detection.