MO-201 Excel Expert Exam Prep

What is the MO-201 Excel Expert Certification?

The Microsoft Office Specialist Excel Expert exam (MO-201) is the advanced Excel certification from Microsoft. It tests expert-level Excel skills including complex formula construction, advanced data analysis, automation, and data management.

MO-201 is intended for experienced Excel users who need a formal credential to validate their skills. It builds on the MO-200 Associate level — you should be comfortable with all MO-200 content before attempting the Expert exam.

Exam details:

Exam code: MO-201
Duration: approximately 50 minutes
Format: live in-application tasks
Passing score: 700 out of 1000
Provider: Certiport / Microsoft

MO-201 Exam Objectives

1. Manage Workbook Options and Settings (5–10%)

Manage workbooks
Prepare workbooks for collaboration
Use and configure language options

2. Manage and Format Data (20–25%)

Fill data using Flash Fill and advanced options
Apply custom data formats: #,##0.00 or dd-mmm-yyyy
Apply advanced conditional formatting with custom formulas
Create and modify custom views

Custom format syntax to know:

0.00 — two decimal places
#,##0 — thousands separator
"$"#,##0.00 — currency
dd/mm/yyyy — date format

3. Create Advanced Formulas and Macros (35–40%)

This is the heaviest section. Key areas:

Logical and lookup:

=IF(AND(A1>10, B1<5), "Yes", "No")
=IFERROR(VLOOKUP(A2,Sheet2!A:C,2,0),"Not found")
=INDEX(B:B, MATCH(D2, A:A, 0))
=XLOOKUP(D2, A:A, B:B, "Not found")

Statistical and mathematical:

=SUMPRODUCT(A1:A10, B1:B10)
=SUMIFS(C:C, A:A, "East", B:B, ">100")
=COUNTIFS(A:A, "Passed", B:B, ">80")

Text functions:

=LEFT(A2, FIND(" ", A2)-1) — extract first word
=TRIM(UPPER(A2))
=TEXTJOIN(", ", TRUE, A2:A10)

Date functions:

=DATEDIF(A2, TODAY(), "Y") — years between dates
=EOMONTH(TODAY(), 0) — last day of current month
=WORKDAY(TODAY(), 10) — 10 business days from today

Named ranges:

Define a named range: Formulas > Name Manager
Use in formulas: =SUM(SalesData) instead of =SUM(B2:B100)

Macros (basic):

Record a macro
Assign a macro to a button
Edit macro in VBA editor (basic understanding)

4. Manage Advanced Charts and Tables (20–25%)

Create and modify dual-axis charts
Create waterfall, funnel, and histogram charts
Add trendlines and error bars
Create PivotTables from multiple ranges
Create calculated fields in PivotTables
Group PivotTable data by date

5. Manage Data by Using Power Query and Power Pivot (10–15%)

Power Query:

Import data from CSV, Excel, and web sources
Transform data: filter, sort, remove columns, split text
Merge queries (like a JOIN)
Load to worksheet or data model

Power Pivot:

Create relationships between tables
Write basic DAX formulas
Build PivotTables from the data model

Study Plan for MO-201

Week 1: Advanced formulas

Spend time with INDEX MATCH, XLOOKUP, SUMPRODUCT, and IFERROR patterns. Practice constructing multi-condition SUMIFS and COUNTIFS on real datasets.

Week 2: Data analysis

Build PivotTables with calculated fields. Create custom conditional formatting rules using formulas. Work with custom number formats.

Week 3: Power Query and Power Pivot

Import external data, merge tables, clean and transform data. Create a simple data model with relationships.

Week 4: Charts and finishing

Practice advanced chart types. Review macros basics. Take a full practice exam and note weak areas.

Frequently Asked Questions

Do I need to pass MO-200 before MO-201? No — Microsoft does not require MO-200 as a prerequisite. However, MO-201 assumes all MO-200 knowledge. Most candidates should pass MO-200 first. Is MO-201 significantly harder than MO-200? Yes. MO-201 introduces Power Query, Power Pivot, DAX, complex formula nesting, and macro basics — none of which appear on MO-200. Expect 4–8 weeks of preparation for someone already competent at MO-200 level. What jobs ask for MO-201? Financial analyst roles, business intelligence positions, operations management, and senior administrative roles often list advanced Excel as a requirement. MO-201 is the objective proof of that skill level. How do I register for the exam? Register at a Certiport authorised testing centre. Find one at certiport.com. Some universities and community colleges also host Certiport exams for enrolled students at reduced cost.