Microsoft Excel Certification Exam Prep: MO-201 Excel Expert

Explore the exam structure: six project cases on compass, a certiport web interface, with about 25 tasks, 50 minutes, and a 0–1000 score where 700 passes, plus immediate score reports.

Master the four objective domains for the MO-201 Excel expert exam: advanced formulas and macros, data management and formatting, advanced charts and tables, and workbook options and settings.

Explore the exam interface in Compass, including the project file and exam panel, task management, and the steps to schedule or locate a Certiport testing center.

Explore official exam details, policies, and skill summaries on the Microsoft Certifications site, plus Certiport's exam tutorial and Geometrics practice tests for MO-201 Excel Expert.

Analyze the New York Airbnb dataset using advanced formulas and formatting in Excel to forecast earnings. Create charts, pivot tables, and collaborative workbooks to support investment decisions.

Explore the Airbnb dataset in Excel, including listing data, hosts, neighborhoods, and worksheets for mortgage, occupancy, amortization, and visualizations for a host dashboard.

Explore advanced formulas and macros in Excel, covering logical, date and time, lookup, and financial functions, plus data analysis tools like scenario manager, goal seek, and auditing formulas.

Master fixed, relative, and mixed references in Excel to copy formulas accurately, using the dollar sign and F4 to lock rows or columns as you fill across or down.

Compare explicit (A1) references with structured references in Excel tables, demonstrating how table references use column names and the at symbol to compute totals and profits.

Discover how Excel logical functions drive decisions using the if statement, compare numbers, text, and dates with operators, and map true to 1 and false to 0.

Learn the if function in Excel: create a three-argument formula that returns true or false values, uses text strings, and analyzes Rotten Tomatoes ratings and Airbnb data.

Master nested if functions and the IFS function to handle multiple logical tests in Excel, classifying ratings as certified fresh, fresh, or rotten using thresholds like 75 and 60.

Explore how the not and not equal to operators invert logical tests and drive conditional formulas in Excel, using if statements to categorize availability data as active or inactive.

Learn how to use the and function and the or function in Excel to evaluate multiple conditions inside an if statement, with practical examples from wine scores and Airbnb data.

Explore the switch function in Excel, which evaluates an expression against a list of values and returns the first matching result, detailing the three required arguments and optional parameters.

Explore countif, sumif, and averageif functions to compute counts, sums, and averages by range and criteria, using wine variety and host dashboard examples.

Learn to use countifs, sumifs, and averageifs for multiple criteria, with sum and average ranges at the start, illustrated by a wine data example and a dynamic dashboard.

Utilize the maxifs and minifs functions in Excel to return the highest or lowest values based on multiple criteria, with examples from a wine dataset and host dashboard.

Explore how Excel uses date values as underlying numerical representations for dates and times. Apply date formatting, format cells, and four-digit year rules, then view underlying values.

Master a pro tip to convert text to dates in excel by extracting year, month, and day with left, mid, and right, then applying the date function.

Master the today and now functions in Excel to display the current date and time, recognize their volatile nature, and apply them to dashboards.

Explore date serialization in Excel by using year, month, day, hour, minute, and second functions with a serial number argument, and learn how formatting affects results.

Learn to use weekday, workday, and networkdays in Excel to determine weekdays, compute workdays after or before dates, and count workdays between dates with holiday support.

Record and run macros in Excel to automate tasks, change date formats (US, international, and Mexico), and save workbooks as macro-enabled using VBA and the developer tab.

Learn how to modify and delete macros via the developer tab and macros dialog, assign shortcut keys and descriptions, and understand that macro actions cannot be undone.

Add form controls to your Excel dashboards using the developer tab, insert options, and format control settings to link a spin button to a cell and drive dynamic charts.

Explore lookup functions like vlookup, hlookup, and index and match to find a lookup value in a table (e.g., a product ID) and return its corresponding description.

Master vlookup and hlookup in Excel to retrieve exact matches from the leftmost column or top row, build a dynamic host dashboard, and pull related data using table arrays.

Discover how the index function retrieves a value at a specific row and column in a range, often used with match, with pizza menu and host dashboard examples.

Master how the match function returns a value’s position in a one-dimensional array, using exact or approximate match types, and see how it complements the index function in Excel.

Combine index and match to perform flexible lookups across any row or column, pulling prices from a pizza menu and surpassing vlookup and hlookup in versatility.

Master approximate match lookups with VLOOKUP, returning the highest value less than or equal to the lookup value. Sort the leftmost column and apply to grades and service fees.

Apply the nper and pmt functions to compute loan periods and monthly payments, handle rate conversions, negative payments, and defaults for future value and type, illustrated with a mortgage example.

Use the data tab's what-if analysis scenario manager to model multiple input combinations—down payment, interest rate, and term length—and compare their effects on monthly payments.

Explore Excel's consolidate tool to summarize data from multiple ranges. Use by position for identical labels and order, or by category for same labels in different orders.

Learn how to use goal seek to find a single hardcoded input cell value that yields a target profit in Excel, with practical break-even and profit calculations.

Explore Excel's formula auditing tools, including trace precedents and dependents, show formulas, error checking, evaluate formula, and the watch window, demonstrated on a mortgage calculator.

Master data formatting and validation in Excel, including grouping, removing duplicates, autofill and flash fill, and conditional formatting with formula-driven rules and custom syntax.

Learn to build custom number formats in Excel using up to four semicolon separated conditions: positive, negative, zero, and text, and tailor formats with currency symbols, color, and thousand separators.

Master data validation in Excel by creating dropdown lists and restricting inputs to specific options, with input messages and error alerts to prevent invalid entries and guide users.

Master grouping and ungrouping data in Excel to hide details in rows and columns, use toggles to drill down, and employ shortcuts like Ctrl+8 and Ctrl+H to manage the view.

Learn to use Excel's subtotal tool to summarize data by category, apply average and sum functions, sort by category, and create subtotals, grand totals, and collapsible groups.

Learn how to use the remove duplicates command in Excel to extract unique values, choose column criteria, and create yearly summaries using sumifs and min ifs.

Explore autofill and flash fill in Excel, using patterns to generate data, copy cells, fill dates, months, days, and years, and extract usernames from emails with flash fill.

Explore advanced fill series options in Excel, including linear, growth, and date types, with real examples of custom step values and dates two years apart.

Apply conditional formatting to bring data to life by creating rules that highlight cells, color scales, data bars, and icon sets to reveal patterns and trends at a glance.

Harness formula-based conditional formatting to apply rules with fixed and relative references. Highlight entire rows when a formula evaluates to true, such as selecting a club or marking inactive statuses.

Edit, delete, and reorder conditional formatting rules using the manage rules menu, applying top-to-bottom order while learning with data bars, color scales, and a formula-based rule.

Learn to build heatmaps in Excel by combining conditional formatting color scales with custom number formats to hide values, revealing booking patterns in a mortgage calculator utilization calendar.

Learn to analyze Airbnb data with pivot tables and pivot charts, using advanced chart types like histograms, tree maps, waterfalls, and slicers, timelines, and calculated fields for dynamic dashboards.

Explore box and whisker charts in Excel to visualize data statistics: max, min, range, quartiles, mean, and median, and identify outliers.

Explore histograms to show the distribution of continuous data, using Excel's statistical charts to adjust bin sizes and highlight frequency patterns in examples like movie runtimes or ratings.

Explore creating a field map or filled map in Excel to visualize location-based data, convert data to geography when needed, and count host countries from Airbnb listings to compare distribution.

Master custom combo charts in Excel by blending line and column visuals with a secondary axis to reveal trends like temperature versus precipitation, and apply it to Airbnb host data.

Learn to use treemaps for 1–2 hierarchical levels and sunburst charts for deeper hierarchies, visualize relative sizes, natural groups and subgroups, and group and sort data before inserting in Excel.

Learn to build a waterfall chart in Excel to visualize profit progression from gross rental income to net income, using subtotals and formatting to reflect annual profit and loss results.

Create and format a funnel chart in Excel to visualize conversion stages, convert to percent of total, and identify the biggest drop-off from listing views to inquiries.

Discover how pivot tables in Excel filter, summarize, and analyze data without modifying the raw data, using the field list and values, rows, and columns.

Master pivot table views in excel by pivoting data, moving fields between rows and columns, and using filters to analyze demographics and room types for targeted insights.

Explore how the summarize values by option in pivot tables changes aggregation from sum to count or average, using points, price, and rating as examples.

Apply show values as calculations in pivot tables, including percent of grand total, percent of column, difference from previous, running total, and ranking.

Explore the pivot table analyze tab tools for expanding fields, inserting slicers and timelines, refreshing pivot, and adding calculated fields or pivot charts, plus the design tab’s layout and styles.

Explore pivot table report layouts: compact, outline, and tabular; configure subtotals, grand totals, and headers for clearer analysis across sizes.

Apply number formats to pivot tables in Excel using the number format option to set decimals, thousand separators, and currency, and customize blank or error values via pivot table options.

Master sorting and filtering pivot tables in Excel by sorting data in rows or values, and filtering by label or value criteria including top ten and multiple filters.

Explore slicers and timelines to filter pivot tables with interactive controls. Link slicers to report connections and pivot tables, filter by room type and date ranges, and compare by price.

Group numerical fields in a pivot table to create custom buckets and analyze distributions like a histogram, using points and price examples; ungroup fields before adding to values.

Learn how to group date fields in pivot tables to roll dates into years, quarters, and months, automatically or via the group tools, with tips on ungrouping and using days.

Create calculated fields in pivot tables to build measures from existing numerical fields, such as percentage of citizens and price per guest, using sums and sorting by the calculated field.

Explore calculated items in pivot tables, creating new measures from existing numerical variables and new dimensions from existing categorical fields, with cautions about duplicates.

Create pivot charts linked to pivot tables to visualize data. Use field buttons, slicers, and timelines to filter and keep the chart and table in sync as you slice.

Format pivot charts in Excel with the design tab, apply a style and layout, edit axis titles, remove the legend, add data labels, and adjust colors to match pivot table.

Expand and collapse pivot charts to drill down into details, such as rating class within a neighborhood, and compare price per guest across Manhattan neighborhoods.

Master domain workbook options and settings, including cell protection, collaboration, macros and macro security, linking and referencing data between workbooks, protection and recovery, comments, iterative calculation, and language options.

Learn how to enable macros in Excel responsibly, review the four macro security options, enable content for trusted workbooks, and choose between macro enabled and macro free workbooks.

Copy macros between workbooks by moving modules in the Visual Basic editor, and store frequently used macros in the personal macro workbook for universal access.

Link data across workbooks in Excel using path, workbook name, sheet name, and cell reference; open workbooks update automatically, and you can edit or break links.

Protect worksheets by enabling sheet protection, unlock specific cells to allow editing, hide formulas, and use password-protected ranges via allow edit ranges to secure critical data.

Protect the workbook structure to prevent moving or deleting sheets, and encrypt the workbook to require a password to open.

Learn to use Excel's auto recover feature, set save intervals, and recover files by navigating to file info, manage workbooks, selecting a version, and clicking restore.

Explore Excel's calculation modes: automatic, manual, and automatic except for data tables. Learn how to enable iterative calculations and adjust max iterations for circular references.

Learn how to set display and authoring languages in Excel, use translation and proofing tools, manage dictionaries, and customize language options to prepare for the MO-201 exam.

Take a MO-201 Excel expert practice test with six projects and 25 tasks, plus walkthroughs. Set a 50-minute timer to simulate the exam environment and improve time management.

Explore six-project excel expert practice test structure with downloadable files, pdf instructions, and color-coded worksheets covering retail, beer pricing, non-profit indicators, winery inventory, coffee sales, and New York City collisions.

Demonstrates solutions for project 1 in the mo-201 excel course: use vlookup to fill product names, countifs for orders, add a field map, compute pmt, and format a pivot chart.

Set data validation in C6 from Beer Prices A2:A31. Select Philadelphia Phillies; apply red-white-green scale B2:F31; build IF in G2 with OR and AVERAGE to display none, increase, or decrease.

Walks through project 3 in Excel: remove duplicates by country in indicators; add Spanish Mexico language and verb forms only; disable macros; sort pivot by population; add population density field.

Explore a solution walkthrough for winery inventory in Excel: flash fill years, conditionally format stock versus reorder, compute arrival dates with workday, and password-protect the workbook.

Explore fifth project solution in the excel practice workbook, apply subtotals by product group for units sold and total sales, consolidate NYC store figures, and use Goalseek for $3,000 profit.

Watch project 6 solution, create macro header with Ctrl+Shift+T to format A1:F1, and build a monthly combo chart with clustered column and secondary-axis line for injury percentage.