Chapter 1: Taking the Microsoft Office Specialist Exams
Learning Objectives
- Describe the MO-210 (Excel Associate) and MO-211 (Excel Expert) exams: who gives them, how they're delivered, and how they're scored.
- Explain how exam tasks are worded and turn a task's description into the Excel action it calls for.
- Use the objective map to find where each exam objective is covered, in Introduction to Excel or in this book.
- Plan your study and register for an exam.
You finished Introduction to Excel, so you already know most of what the Microsoft Office Specialist (MOS) Excel exams test. You can build formulas, write IF and lookup functions, chart data, work with tables and apply conditional formatting. This book fills in the rest. It covers the exam objectives that Introduction to Excel doesn't, and it gives you practice in the exam's format so nothing about test day surprises you.
This chapter teaches no Excel skills. It explains how the exams work, how to read an exam task, and how this book and Introduction to Excel fit together. Read it first, then come back to the objective map whenever you want to check where a skill is covered.
1.1 The MOS Excel certifications
Microsoft offers two Excel certifications for Microsoft 365, each earned by passing one exam:
| Exam | Certification | What it shows |
|---|---|---|
| MO-210 | Microsoft Office Specialist: Excel Associate (Microsoft 365 Apps) | You can create and manage workbooks, format cells and ranges, work with tables, write common formulas and functions, and build charts. |
| MO-211 | Microsoft Office Specialist: Excel Expert (Microsoft 365 Apps) | You can manage workbook options and protection, validate and format data with advanced tools, write nested and advanced formulas, record macros, and build advanced charts, PivotTables and PivotCharts. |
The two exams build on each other. MO-211 assumes everything on MO-210, and many MO-211 tasks only work if you're fluent in Associate-level skills. This book follows the same order: chapters 2–7 prepare you for MO-210, and chapters 8–15 prepare you for MO-211. Take MO-210 first.
Each exam stands alone. Passing MO-210 earns the Excel Associate certification, and passing MO-211 earns the Excel Expert certification. Microsoft also offers broader credentials that combine several Office exams, such as Word and PowerPoint. Requirements for those change from time to time, so check Microsoft Learn's certification pages if you're interested in them.
Microsoft owns the exams, and Certiport (part of Pearson VUE) delivers them. You'll create a Certiport account to register, and your results and certificates are kept there. Employers can verify your certification through a digital badge you can add to LinkedIn or a résumé.
1.2 How the exam works
Both exams are performance-based. There are no multiple-choice questions. You work in a real copy of Excel, and the exam checks the workbook you leave behind.
1.2.1 Projects and tasks
The exam is divided into several projects. Each project opens a workbook, and a panel at the bottom of the screen lists that project's tasks, usually five or six of them. A task describes one change to make to the workbook, such as "On the Summary worksheet, show the first row at all times as you scroll."
You can do a project's tasks in any order. Each task stands on its own, so if one task asks you to sort a table, a later task in the same project won't depend on the sort being right. You can't move to a different project until you've finished the one you're in, though, so do a project's tasks before submitting it.
1.2.2 The exam window
The exam panel gives you a few controls for each task and project. Their exact labels can change, so watch Certiport's exam tutorial before your exam, but they work like this:
- Mark as complete records that you think a task is done. It's just a note for you. The exam grades the workbook, not this mark.
- Mark for review flags a task you want to come back to. At the end of the exam you can return to marked tasks if you have time.
- Restart project puts the project's workbook back the way it started and erases every change you made in that project. Use it only if you've made a mess that undo can't fix, because you'll have to redo the tasks you'd already finished.
- Submit project (or the next-project button) moves on to the next project.
A timer at the top shows how much time you have left. When it runs out, the exam ends and grades what you've done.
1.2.3 Timing and scoring
At the time of writing, each exam allows 50 minutes. Scores run from 1 to 1,000, and 700 passes. Certiport shows your score as soon as you finish, along with a breakdown by objective domain that shows where you lost points. Certiport's exam page has the current details, so check it before you test.
With five or six projects of about six tasks each, you have roughly a minute and a half per task. That's plenty for a task you know how to do and not enough to discover how to do one you don't. That's why this book has you practice each skill until you can do it without looking it up.
1.2.4 How tasks are graded
The grader checks the result in the workbook. It doesn't watch which buttons you pressed. If a task asks you to show the first row while scrolling, any method that freezes row 1 gets credit. This has two consequences:
- Do exactly what the task says, and nothing extra. If a task asks you to format a range, format that range and not the whole column. Extra changes can make a correct answer look wrong to the grader.
- Don't add helper cells, rename things or move data unless a task asks you to. The grader looks for specific results in specific places.
Each task is graded on its own, but changes you make can still affect other tasks. If you delete a column while doing one task, a later task that refers to that column can't be done. Read every task in a project before you start, so you know what the project expects the workbook to look like.
1.3 Reading exam tasks
The hardest part of the exam for most students isn't Excel. It's reading the tasks. Exam tasks describe the result you need, not the command or function that produces it. Certiport's objective sheets say so directly: function names are avoided and replaced with descriptions, so you have to understand what each feature is for.
Here are some examples of how a task might be worded and what it's asking for:
| The task says | What it's asking for |
|---|---|
| "Ensure that the column headings remain visible as you scroll down the worksheet." | Freeze the top row (View › Freeze Panes › Freeze Top Row), or freeze panes below the heading row. |
| "In cell F2, enter a formula that returns the number of empty cells in the Response column." | A formula using COUNTBLANK. |
| "Display the value in each cell of column D as a percentage with no decimal places." | Apply the Percentage number format with 0 decimal places. |
| "Configure the table so that the last row shows the total for the Amount column." | Turn on the table's total row and set the Amount column's total to Sum. |
| "Add a description of the chart for people who use screen readers." | Add alternative text to the chart. |
| "Make it impossible for users to add, delete or rename worksheets." | Protect the workbook's structure. |
Notice the habits in this wording. "Ensure," "configure" and "display" describe an end state. Words like "remain visible as you scroll" and "for people who use screen readers" describe what the feature does, so you have to know each feature well enough to recognize it from its purpose.
1.3.1 A method for every task
- Read the whole task. Note every detail: which worksheet, which range, which format, and any value the task gives you. Tasks often name a worksheet or table you need to go to first.
- Name the feature. Translate the description into the Excel feature or function it means. If you can't name it, mark the task for review and move on.
- Find the location. Go to the exact sheet, cell or object the task names.
- Do it, then check it. Look at the result and compare it with every detail in the task. Did you include the decimal places? Did you use the cell the task named?
Tasks often put exact text in quotation marks, bold or italics, such as a sheet name or a title to type. When a task gives you text to enter, type it exactly as shown. A missing capital letter or an extra space can cost you the task. Some exam versions let you copy text from the task pane. If yours does, copy instead of typing.
1.4 How this book works with Introduction to Excel
This book assumes you know everything in Lessons 1–10 of Introduction to Excel. It doesn't reteach those skills. When an exam objective is covered there, the objective map below sends you back to the lesson. When it isn't, a chapter in this book covers it.
Each chapter (except this one and the two practice exams) ends with a Practice section of exam-style tasks on that chapter's skills. Each task is worded the way the exam words them, so you practice reading tasks as well as doing them.
When you miss a practice task, the book builds a prompt for you to paste into your own AI assistant, such as ChatGPT, Claude, Gemini or Copilot. The prompt tells the assistant what the task asked, what you did and what the correct result was, and asks it to explain why your answer was wrong and how to do the task correctly. Use it whenever an answer surprises you. Understanding why you missed a task is what keeps you from missing it on the exam.
Chapters 7 and 15 are full practice exams for MO-210 and MO-211, covering every objective on each exam, including the ones Introduction to Excel teaches. Take each one under exam conditions: one sitting, a 50-minute timer and no notes.
1.5 Objective map
The tables below list every objective on each exam, grouped by the exam's objective domains, and show where each one is covered. "Lesson" means a lesson in Introduction to Excel. "Chapter" means a chapter in this book. Where Introduction to Excel covers part of an objective and this book covers the rest, both are listed.
1.5.1 MO-210: Excel Associate
Manage worksheets and workbooks
| Objective | Covered in |
|---|---|
| Import data from text files and online sources | Chapter 2 |
| Search for data; go to named cells, ranges and workbook elements | Chapter 2 |
| Insert and remove hyperlinks | Chapter 2 |
| Modify page setup; customize headers and footers | Chapter 3 |
| Adjust row height and column width | Lesson 1 |
| Manage the Quick Access Toolbar | Chapter 2 |
| Display and modify worksheets in different views; change window views | Chapter 2 |
| Freeze worksheet rows and columns | Chapter 2 |
| Modify built-in workbook properties; display formulas | Chapter 2 |
| Set a print area; configure print settings | Chapter 3 |
| Save and export workbooks in other file formats | Chapter 3 |
| Inspect workbooks and correct issues | Chapter 3 |
| Manage comments and notes | Chapter 3 |
Manage data cells and ranges
| Objective | Covered in |
|---|---|
| Paste data by using special paste options | Chapter 4 |
| Fill cells by using Auto Fill | Lesson 1; Chapter 4 |
| Insert and delete multiple columns, rows and cells | Chapter 4 |
| Generate numeric data with RANDBETWEEN and SEQUENCE | Chapter 4 |
| Merge cells; modify alignment, orientation and indentation; wrap text | Lesson 1; Chapter 4 |
| Format cells with Format Painter; apply number formats, Format Cells options and cell styles; clear formatting | Lesson 1; Chapter 4 |
| Format multiple worksheets by grouping | Chapter 4 |
| Define and reference named ranges | Chapter 4 |
| Insert sparklines | Chapter 4 |
| Apply and remove built-in conditional formatting | Lesson 8 |
Manage tables and table data
| Objective | Covered in |
|---|---|
| Create tables from ranges; apply table styles | Lesson 7 |
| Convert tables to ranges; add or remove table rows and columns | Lesson 7; Chapter 5 |
| Configure table style options; insert and configure total rows | Lesson 7; Chapter 5 |
| Filter records; sort by multiple columns | Lesson 7; Chapter 5 |
Perform operations by using formulas and functions
| Objective | Covered in |
|---|---|
| Insert relative, absolute and mixed references | Lesson 2 |
| Use structured references in formulas | Lesson 7; Chapter 5 |
| AVERAGE, MAX, MIN, SUM, COUNT, COUNTA, COUNTBLANK | Lessons 2 and 4 |
| Conditional operations with IF | Lesson 3 |
| Sort data with SORT; get unique values with UNIQUE | Chapter 5 |
| Format text with LEFT, RIGHT, MID, UPPER, LOWER, LEN, CONCAT, TEXTJOIN | Lesson 5 |
Manage charts
| Objective | Covered in |
|---|---|
| Create charts | Lesson 6 |
| Create chart sheets | Chapter 6 |
| Add data series; switch rows and columns in source data | Chapter 6 |
| Add and modify chart elements; apply chart layouts and styles | Lesson 6; Chapter 6 |
| Add alternative text to charts | Chapter 6 |
1.5.2 MO-211: Excel Expert
Manage workbook options and settings
| Objective | Covered in |
|---|---|
| Copy macros between workbooks; enable macros in a workbook | Chapter 12 |
| Reference data in other workbooks | Chapter 8 |
| Manage workbook versions | Chapter 8 |
| Restrict editing; protect worksheets, cell ranges and workbook structure | Chapter 8 |
| Configure formula calculation options | Chapter 8 |
Manage and format data
| Objective | Covered in |
|---|---|
| Fill cells with Flash Fill and advanced Fill Series options | Chapter 9 |
| Generate numeric data with RANDARRAY | Chapter 9 |
| Create custom number formats | Chapter 9 |
| Configure data validation | Chapter 9 |
| Group and ungroup data; insert subtotals and totals | Chapter 9 |
| Remove duplicate records | Chapter 9 |
| Create custom conditional formatting rules, including rules that use formulas; manage rules | Lesson 8; Chapter 9 |
Create advanced formulas and macros
| Objective | Covered in |
|---|---|
| Nested logical functions: IF, AND, OR, NOT | Lesson 3; Chapter 10 |
| IFS, SWITCH, LET | Chapter 10 |
| SUMIF, AVERAGEIF, COUNTIF, SUMIFS, AVERAGEIFS, COUNTIFS | Lesson 4 |
| MAXIFS, MINIFS | Chapter 10 |
| XLOOKUP, VLOOKUP, MATCH, INDEX | Lesson 4 |
| HLOOKUP | Chapter 10 |
| NOW, TODAY | Lesson 5 |
| WEEKDAY, WORKDAY | Chapter 10 |
| Summarize data from multiple ranges with Consolidate | Chapter 11 |
| What-if analysis with Goal Seek and Scenario Manager | Chapter 11 |
| Forecast data with AND, IF and NPER; calculate with PMT | Lessons 2 and 3 |
| Filter data with FILTER; sort data with SORTBY | Chapter 10 |
| Trace precedents and dependents; use the Watch Window; error checking; Evaluate Formula | Chapter 11 |
| Record, name and edit simple macros | Chapter 12 |
Manage advanced charts and tables
| Objective | Covered in |
|---|---|
| Dual-axis and combo charts | Chapter 13 |
| Box & Whisker, Funnel, Histogram, Sunburst and Waterfall charts | Chapter 13 |
| Create PivotTables; modify fields and options; slicers; grouping; calculated fields; value field settings | Chapter 14 |
| Create PivotCharts; manipulate options; apply styles; drill down | Chapter 14 |
Microsoft revises the exam objectives from time to time. This map follows the skills outlines published for MO-210 and MO-211 (Microsoft 365 Apps). Before you schedule an exam, open the exam's page on Microsoft Learn, download its current skills outline and compare it with this map.
1.6 Getting ready for exam day
1.6.1 Make a study plan
- Review the lessons the map points to. Skim the Introduction to Excel lessons listed for each exam, and redo a few exercises in any lesson you've forgotten.
- Work through this book's chapters in order. Do each chapter's Practice section until you can finish every task without looking anything up.
- Take the practice exam under exam conditions. For MO-210 that's chapter 7. Use a 50-minute timer, work in one sitting and don't use notes.
- Study what you missed. Use the AI prompt for each missed task, then go back to the chapter or lesson that covers it.
- Schedule the real exam once you can pass the practice exam comfortably, with time to spare.
Then repeat steps 2–5 with chapters 8–15 for MO-211.
1.6.2 Register for the exam
You can take the exam at a Certiport Authorized Testing Center, such as many colleges, libraries and training centers, or online with a remote proctor through Certiport Exams from Home. Your college may have a testing center that offers MOS exams at a discount. Ask before you pay full price.
To register, create a Certiport account using your legal name exactly as it appears on your ID, then schedule through a testing center or through Exams from Home. For an online exam, run Certiport's system check on the computer you'll use well before exam day. You'll need a webcam, a microphone and a quiet room with nothing on your desk.
1.6.3 On exam day
- Read each project's tasks first. It takes a few seconds and keeps you from undoing your own work.
- Skip what you don't know. Mark a task for review and move on. Every task is worth points, and a hard task is worth no more than an easy one.
- Watch the clock. If you're more than two minutes into a task, mark it and move on.
- Don't restart a project in a panic. Undo (Ctrl+Z) fixes most mistakes. Restart only when the workbook is beyond repair, and remember it erases everything you did in that project.
- Come back to marked tasks once you've worked through the exam, if you have time left.