Microsoft Excel 365 Advanced

Explore eight new dynamic array functions in Microsoft 365 Excel, including sequence, round array, unique, sort, sort by, filter, Xlookup, and ex match, and see how they simplify formulas.

Explore dynamic array functions and spill behavior in Excel 365, comparing regular formulas with dynamic array formulas through practical examples like Vlookup and filter.

Extract a unique list with the unique function and count entries using count a and the hash symbol, leveraging dynamic arrays for data validation and drop-down lists.

Explore the difference between distinct and unique values in Excel 365, showing how distinct returns all unique items while uniq returns items that appear exactly once.

Learn to extract three columns with the Excel 365 unique function by returning distinct rows from a table, including an exactly-once option and dynamic updating.

Explore how to extract unique and distinct values by column using Excel's unique function, returning unique columns from an array of crayon packs and identifying items that appear only once.

Master the Excel sort dynamic array function to sort data by one or more columns, choose ascending or descending orders, and output results to any cell.

Explore horizontal sorting with the sort function in Excel 365, sorting by row, using text join to consolidate actor lists, and highlighting duplicates to reveal actors who shared movies.

Sortby enables sorting by multiple columns in a dynamic array, returning selected columns by name and allowing sorts by five-star ratings, genre, and author with ascending or descending order.

Learn to perform a horizontal sort in Excel 365 with sort by and a by-array to order title, first name, last name, department, and salary.

Explore the sequence function in Excel 365, a dynamic array tool that quickly fills rows and columns with start values and step values, and apply it to practical date schedules.

Explore the filter function, a versatile dynamic array tool, and learn to output results anywhere in the sheet with data validation and a unique exam list.

Apply or logic in Excel's filter function using the plus operator to filter by two criteria: exam equals English or grade equals D, within a dynamic named table.

Use the Excel filter function with and logic to require exam, month, and grade all match. The and operator uses an asterisk, and the month is extracted as text.

Learn to use Excel 365's filter with the equals operator to require both criteria or neither, returning exact matches on exam and grade.

Master the minus operator in the filter function in Excel to select records by one or the other but not both, such as a specific exam or a grade.

Master rand array and randbetween in Excel 365 to generate random numbers, compare dynamic array behavior with randbetween, and use choose for random departments, dates, and sample salary data.

Discover how Xlookup simplifies lookups, outperforming Vlookup and Index and Match, to pull category, type, profit, and revenue from an apps dataset.

Explore how to use xmatch in Excel 365, compare it with match and xlookup, and build lookups with index to return exact categories from a data table.

practice dynamic array functions by creating unique country and medal lists, building data validation drop down lists, and filtering to show USA gold medalists sorted by name A to Z.

Learn to perform two-way lookups with multiple criteria in Excel using XLOOKUP or INDEX+MATCH, selecting month and company from data validation dropdowns to retrieve the correct revenue.

Explore how to resolve duplicates in lookups with vlookup and xlookup, create unique keys by combining name and department, and return the correct country.

Practice advanced lookup and reference functions to build a two-way lookup by agent and quarter. Create a unique list and data validation dropdowns to feed the lookup.

Learn how the let function creates variables to simplify formulas and reduce repeating references in Excel.

Explore how to create your own reusable formulas with lambda in Excel 365, define variables, and build named functions like price plus tax for workbook-wide use.

Apply let and lambda to extract client names from invoice numbers using text mid, turning formula into a reusable lambda function, and test three middle characters in slash-delimited part codes.

Create a data model in Power Pivot by importing multiple tables from separate workbooks. Link the tables on common fields to build pivot tables from multiple sources.

Link orders, customers, and coffee types in PowerPivot via common fields like customer ID and coffee type to build a unified pivot table across all data.

Add calculated columns in PowerPivot to compute profit per coffee by subtracting cost from revenue, then create a total profit by multiplying units sold by related profit across tables.

Consolidate data from multiple tables into a single pivot table and create a pivot chart to analyze units sold by customer and coffee type.

Practice exercise five uses Power Pivot to link employee data, names, and departments via common fields, then creates a pivot table showing the average salary by department.

Explore importing data into Excel with Power Query and cleaning datasets efficiently, using Get Data and Transform Data in the Power Query editor.

Clean and transform a messy data set in Power Query by removing empty columns, setting headers, trimming, splitting product name and quantity, and loading as an Excel table.

Power Query combines files from the transaction data folder into a single dataset with consistent columns and tables, and updates automatically when new files are added to support pivot tables.

Link accounts and products to the transaction data model in Excel by importing separate tables, establishing one-to-many relationships, and using a pivot table with refresh to update analyses.

practice Power Query to combine and transform files from a folder into a single Excel table, remove the first column, verify data types, and refresh to include 2022 data.

Create a custom pivot table style in Excel 365 advanced, duplicating a built-in design, applying green borders, bold headers, and tailored number formats for consistency.

Explore creating and applying custom number formats in Excel, using four-part syntax to control positive, negative, text, and zero values, including currency symbols, colors, and symbols in pivot tables.

Create and apply custom lists to sort pivot table data in a specific order using Excel 365 advanced settings and sort options.

Create report filter pages in pivot tables by placing a country field in filters, then generate a separate worksheet for each country for targeted, country-specific reports.

Learn to enhance Excel dashboards by using slicers and pivot charts, customizing slicer styles, headers, borders, and fonts for a cohesive, interactive country and region filter.

Learn to protect a worksheet or workbook in Excel 365 while keeping slicers active, by unlocking slicers and enabling use of pivot tables and pivot charts.

Learn to add calculated fields to pivot tables, using existing fields to compute a difference percentage between cogs and gross sales, and format it as a percentage.

Compare calculated items and fields in a pivot table. Create a calculated item such as the percentage profit of Royal Oak for each country and format it as a percentage.

Discover how the getpivotdata formula retrieves pivot table values, compare it with direct cell references, and learn why getpivotdata remains accurate when pivot layouts change.

Create a dynamic pivot chart title that updates to show gross sales for slicer-selected products, by linking the title to a cell via helper sheet using text join and concat.

Develop advanced pivot table and charting skills using the food sales data, creating a pivot with category and product, a sales total field, and city filters with a slicer.

Explore form controls in Excel 365 to add interactivity and visuals to dashboards, including combo boxes, check boxes, option buttons, spin buttons, list boxes, and scroll bars.

Insert a combo box from the developer ribbon’s form controls, set its input range to months, and use index to output the selected month and its revenue.

Learn to insert checkboxes in Excel 365, link their true/false output, and use IF formulas with conditional formatting to show or hide salaries.

Showcases the new checkboxes in Excel 365, with a sequence formula to auto-number tasks, and conditional formatting to gray out and strike through completed items.

Learn to use option buttons in Microsoft Excel 365 advanced to show or hide columns, link buttons to a cell, and drive dynamic table views with an index formula.

Learn to add a spin button form control in Excel 365 advanced to cycle through an employee list, link to a cell, and retrieve salaries with index and lookup.

Configure a list box form control to display employees and use a cell link and index formula to show salaries and update the heading for selected employee, with a chart.

Add a scroll bar form control to display five employees and their salaries at a time, using an index formula with absolute references and iferror to hide errors.

Practice exercise eight in this advanced excel course: link option buttons to H6 to show the year in column c, and apply checkboxes with conditional formatting for tasks.

Automate repetitive Excel tasks by recording steps as macros and converting them to VBA code you can rerun on new data; use the Developer tab to run and manage macros.

Learn to automate repetitive tasks in Excel by using the macro recorder to capture steps and convert them to VBA code, then save and run macros across workbooks.

Learn to record macros with relative referencing in Excel, compare absolute and relative approaches, and save as a macro-enabled workbook (.xlsm).

Record a multi-step macro in excel 365. Split the customer name into first and last names, remove leading zeros, strip hyperlinks, mask credit card numbers, and auto fit.

Explore the vba editor to view and tweak macro code in Excel 365. Understand how macros become vba, navigate the project pane and modules, and edit via the macro recorder.

Learn to record macros for sorting by department and hire date, filtering by job title, and clearing the filter, then assign these macros to clickable form control buttons for use.

Learn to add macros to the quick access toolbar and build a macro ribbon with a sorting and filtering group to access and run macros easily.

Generate VBA code with Copilot to auto fit rows and columns in Excel, bold header row, and export as a pdf saved to the desktop, following step by step instructions.

practice creating a VBA macro to reformat a data set in Excel 365, including autofit, currency formatting in columns I and J, table formatting, and a button labeled reformat data.

Concludes the Excel 365 advanced course with a recap of dynamic arrays and Xlookup, let and lambda, power pivot and query, macros and vba, and ai tools Copilot and ChatGPT.