MO-211 prep: Microsoft Excel Expert (Microsoft 365 Apps)

Learn to reference data across worksheets in the same workbook by using sheet names and cell references, including handling spaces with apostrophes and updates when renaming sheets.

Explore how to reference data in other workbooks, update and change external sources, edit and break links, and understand absolute references and workbook sheet naming.

Access and manage workbook versions through auto recover, cloud autosave with OneDrive or SharePoint, and the version history to open, copy, or save earlier versions.

Protect worksheets and cell ranges in Excel, restrict editing, set passwords, and allow edits to specific ranges while managing unprotecting and locked versus unlocked cells.

Learn to protect workbook structure and understand how it differs from protecting sheets in Excel, using the review tab and optional password.

Activity 1 guides you through counting in a table with count vs counta, creating references to cells in a workbook, refreshing links, and protecting sheets and workbooks with locked columns.

Explore creating custom number formats in Excel using the format cells dialog, mastering zeros, hashes, decimals, thousand separators, and fractions across locales.

Explore custom number formats in Excel by using the format cells dialog to display fractions and digits with 0, #, ?, and /, noting the internal values remain unchanged.

Master custom number formats for dates and durations in Excel using d, m, y, h, m, s tokens and [h] for durations, with text in speech marks.

Learn to create custom number formats in Excel by dividing into positive, negative, zero, and text sections, apply colors and symbols with hard brackets and semicolons, and override defaults.

Learn to configure data validation in Excel by setting whole number rules between 1227 and 9999, plus crafting input messages and error alerts to guide users.

Learn to configure data validation for dates (including date and time), text length, and custom rules, use circle invalid data and dropdown lists, and employ input messages to guide users.

Master removing duplicates in Excel by selecting a range, using remove duplicates on the data tab, choosing columns to compare, handling headers, undoing changes, and validating unique file extensions.

Format column H as MMMM YY; color numbers under 100 million red with a custom format, validate column J with a 100 limit warning, and remove duplicates by national ID.

Create pivot tables in Excel from a data range to summarize file extensions and sizes, using the PivotTable fields pane to add rows, columns, and values.

Configure value field settings in a pivot table by changing aggregations (sum, count, average, max, min) and show values as options like percentage of grand total and running total.

Modify pivot table field options and refresh data to manage display, totals, and source data behavior in excel, exploring pivot table options, layout, and data tab settings.

Explore how to modify PivotTable field selections and options in the PivotTable Analyze and Design tabs, including renaming, changing data sources, layout forms, subtitles, print titles, and styles.

Group PivotTable data by file extensions and numeric ranges, create grouped fields like file extension two, and organize date fields into years, quarters, and months.

Learn to filter PivotTable data using slicers and filters, including creating a file extension slicer, connecting it to multiple PivotTables, and adjusting slicer options for cross-table dashboards.

Use timelines to slice date data in pivot tables by inserting a timeline, selecting year, quarter, or day, and linking to multiple pivot tables.

Create calculated fields in pivot tables to compute new metrics from existing data, such as size in kilobytes, using tools to add, name, and modify fields.

PivotCharts connect to PivotTables, reflecting changes between chart and table; drill down with plus/minus, filter with field buttons, and move or format like standard charts.

Master pivot tables and pivot charts in Excel by building a pivot from the employee data, computing average vacation hours, adding calculated fields, slicers, and a pivot chart.

Learn to create and modify dual-axis charts in Excel using pivot tables and pivot charts. Compare detached index and detached average price on primary and secondary axes, and add titles.

Explore the distribution of the detached average price across regions by building a box and whisker chart in Excel, with quartiles, whiskers, inner points, outliers, and mean markers.

Learn to build a combo chart by mixing line and column series, switch chart types in PivotChart design, and add a dynamic title linked to a cell reflecting region filters.

Demonstrate creating a funnel chart in Excel to visualize a multi-stage process from introduction through final checkout, using insert, funnel, and basic chart options such as data labels.

Explore histograms and Pareto charts to visualize data distribution by bins, adjust bin width to 40,000, set underflow and overflow bins, and read the cumulative Pareto line.

Learn to create a sunburst chart from pivot table data by converting to values, then explore multi-layer hierarchies from year 2000 to quarters using a donut-style chart.

Create waterfall charts that start from the previous level to show changes, build a difference column from PivotTable data, and insert the waterfall chart—note they can’t be used with PivotTables.

Practice Activity 4 guides you to create and format multiple Excel charts—column with secondary axis, red funnel, sunburst without grand totals, and a difference-based waterfall.

Master the if and not functions and the Evaluate Formula button in Excel, explore localization nuances (comma vs semicolon), build and test workbooks, and reverse logic using not.

Explore how to use if with and and or to test conditions in Excel, such as E4 less than five million and C4 equals .mp3, producing yes or no results.

Explore nesting in Excel: build an if formula using and and or to identify medium mp3 files between one and five megabytes, with evaluation tips.

Explore how to simplify complex formulas with IFS and SWITCH in Excel, replacing nested IFs and handling multiple conditions with a default result.

Master COUNTIF, SUMIF, and AVERAGEIF to count, sum, and average by criteria in Excel, with absolute/mixed references, text wildcards, and conditional operators.

Learn to apply multiple criteria with countifs, sumifs, averageifs, maxifs, and minifs in Excel, including conditional sums, averages, and error handling with iferror.

Explore how the let function creates variables for total and count inside a formula to improve readability, avoid repeating calculations, and limit scope to the let function.

Explore Excel formula mastery in practice activity 5, using not, nested if, ifs, switch, countifs, sumifs, and let to analyze organizational levels, salaries, and the average.

Use vlookup to look up a value in the leftmost column of a range and return a value from a specified column, with exact match (false) and iferror handling.

Compare vlookup with sumifs and minifs to know when to retrieve the first match or total values; vlookup returns text, while sumifs/minifs compute sums or earliest dates.

Explore hlookup, the horizontal lookup, and learn how true versus false controls approximate and exact matches; understand the impact of data order on results, alongside vlookup comparisons.

Master the match function in Excel to locate a value's position in an array, with exact or approximate matches. Learn how match can drive robust VLOOKUPs when columns shift.

Learn how to use the index function to retrieve values from an array by specifying row and column, overcoming leftmost-column limits of vlookup with practical examples.

Explore how the XLOOKUP function replaces complex lookups in Excel 2022, covering its six arguments, optional parameters for not matched, and scenarios for exact, wildcard, and binary searches.

Explore Excel date and time functions, from year to second, and learn now, today, and weekday. Understand deterministic formulas that recalculate with time and use Switch to display user-friendly weekdays.

Master the WORKDAY function to return a date two work days after a start date, accounting for holidays and weekends, and use WORKDAY.INTL for custom weekends.

Learn to use vlookup and xlookup with absolute references, perform exact and approximate matches, calculate hire dates, tenure, weekday, and workday with holidays to plan projects.

Use Goal Seek for what-if analysis in Excel to find input values that produce a target result, with examples like tax calculations and squaring numbers.

Master Excel's scenario manager to perform what-if analysis by creating, editing, and comparing scenarios with changing cells like B3, B4, B10, and B16, and generate a scenario summary.

Learn to forecast data with the nper function, using and if, for mortgage and investment scenarios, by inputting rate per period, payment, present value, future value, and period timing.

learn to calculate loan payments with the pmt function in Excel, using rate per period, number of periods, present value, future value, and payment type, with mortgage and investment examples.

Filter data with the filter function by defining the array, criteria, and a no results message. Combine multiple criteria using or and, using and, and observe spill behavior.

Master sorting data with the sortby function by selecting the range to sort, the sortby range, and the order (1 for ascending or -1 for descending).

Learn to use excel what-if analysis and scenario manager. Create scenarios with changing cells like F4, perform goal seek to reach 45 million, and filter and sort employees by salary.

Explore workbook calculation options in Excel, including automatic, manual, and automatic except for data tables, and learn how to enable iterative calculations with maximum iterations and maximum change.

Learn to generate multiple random numbers with the RANDARRAY function in Excel, using five optional arguments to set rows, columns, min and max values, and integer or decimal outputs.

Learn to group and ungroup data in Excel to collapse details into summary rows, expand groups on demand, and navigate outlines and auto outline settings.

Learn how to create and manage subtotals in Excel using the Subtotal dialogue to group by changes, apply sum or other functions, and control their display with filters.

Summarize data from ranges with Excel's Consolidate feature. Use top row and left column labels, functions like sum, count, min, max, and average, and live links to source data.

Learn to trace precedence and dependence in Excel formulas, view arrows in the formulas tab, manage external references, and print or remove tracing arrows.

Master the watch window to monitor cells and formulas across multiple workbooks, add watches like C5 or A1, and view values in real time.

Identify error checking rules and green triangles in Excel, resolve issues from inconsistent formulas to two-digit years and numbers formatted as text, and customize error indicators.

Practice activity demonstrates using formula auditing with the watch window, sorting by organisation level and job title, inserting subtotals and grouping rows, and verifying automatic workbook calculation.

Use flash fill to create a new column from existing data, merging titles and authors or first and last names, with automatic pattern detection and one-time filling.

Master advanced fill series in excel using linear, growth, and date sequences with step and stop values. Set series in rows or columns and apply workday dates when needed.

Create and manage custom conditional formatting rules in Excel, using two color scales, three color scales, data bars, color scales, and icon sets, plus top/bottom and range rules.

Explore how to create conditional formatting rules using formulas to highlight cells when values exceed 10 million, applying the rule across columns with fixed references.

Create conditional formatting rules using formulas for date and text conditions. Apply absolute or mixed references to highlight dates above a cutoff and identify text not starting with zero.

Perform conditional formatting in Excel: highlight organisation level below 2 with yellow and login IDs not ending 0 with red; use flash fill and growth series from 1,000 to 1,240,000.