Microsoft Excel - Practical Excel Masterclass

Master absolute and relative cell references in Excel, including mixed references with dollar signs. Use the keyboard shortcut to toggle between relative and absolute references while dragging formulas.

Master absolute and relative references in Excel to compute each salesperson’s percentage of total sales with a single formula, using absolute denominator and draggable references for the numerator.

Apply the if function to compute a met target flag and a performance rating using nested if statements, comparing actual sales to targets and assigning good, adequate, or poor.

Explore array formulas in Excel to find each salesperson’s highest sale by filtering with if and taking max, with partial evaluation using F9, across Microsoft 365 and older versions.

Learn to aggregate daily sales data in Excel by using count, sum, and average to compute total sales, total value, and average sale, with tips on range accuracy.

Compute cumulative sales and cumulative percentage for a ranked sales list, highlighting top contributors and using absolute and relative cell references to build the solution in Excel.

Learn to perform sums across multiple sheets with 3D formulas by aligning inputs to the same cell and using a sheet range like A:B to total monthly sales.

Explore how to rank items in Excel, overall and by category, using the rank function and a method that restarts ranks per category with sorting and an if formula.

Identify the top two salespeople for each product category by creating unique category and salesperson combinations, summing sales, ranking within each category, and filtering.

Learn to display Excel dates as readable values with custom date formats. Apply dd-mm-yyyy and hh:mm am/pm formats via format dialog, understanding that the underlying serial number stays the same.

Explore how to parse text in Excel by splitting email addresses into usernames and domains with text to columns using a delimiter, and with left, right, mid formulas.

Learn to separate a file path into the containing folder and the filename by locating the last forward slash using len and substitute, then split with text to columns.

Convert a cell range into a comma-separated list in a single cell using three approaches: text join, transpose with concatenate, and incremental concatenate.

Identify cells containing a text pattern such as London in addresses using filter and a formula method. Learn about find versus search, case sensitivity, wildcards, and counting matches.

Identify cells containing any city name from a list using a formula-based approach with search and iferror, create an array of matches, and use max to produce a single flag.

Learn to perform lookups in Excel with VLOOKUP and index and match, mapping sales reps to regions across two datasets, and apply exact versus approximate matches with absolute references.

master lookup by column name using index and match to merge large tables, bringing character attributes into a numerical dataset with dynamic, column-name driven formulas.

Explore lookup across multiple columns by concatenating first and last names to find an employee's department in a larger table. Learn vlookup and index-match approaches, plus multi-condition array techniques.

Use the indirect function to create dynamic references across worksheets by converting text to cell references, summing column G for each salesperson, and building a chart of total sales.

Explore building interactive charts in Microsoft Excel using a form control combo box to select a salesperson, with dynamic data via index and sumifs, and create responsive dashboards.

Demonstrates creating sparklines and tiny graphs in Excel to visualize sales volumes, using sparkline charts and a repeat-function text-bar approach with font adjustments.

Learn how to build dynamic charts in Excel that update automatically as new data is added, using Excel tables and named ranges with the offset and count functions.

Learn to group date data in a pivot table to calculate quarterly sales totals by year, using order date grouping, and format the results in dollars.

Explore how goal seek in Excel uses the PMT formula to determine loan term from a given principal, rate, and monthly payment.

Apply conditional formatting with a custom formula to highlight binder sales above 1000 in a data table, using absolute and relative references to highlight entire rows.

Apply formula-based conditional formatting to visualize monthly revenue growth in Excel, using green for gains over five percent, amber for zero to five percent, and red for declines.

Identify and locate external links in an Excel workbook by using find within formulas, then review the source workbooks and decide whether to update or keep current data.

Master data validation in Excel to enforce inputs: age 18–110, 10-digit phone numbers, and a gender drop-down, then validate emails with ampersands and names without digits using custom formulas.