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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.