Microsoft Excel (MO-210) Certification — Questions and Answers

Question 1: How does Watch Window help across multiple worksheets?

Monitors cells from any worksheet in a floating window without switching (Correct answer)
Monitors file size
Watches tutorials
Watches for access

Correct answer: Monitors cells from any worksheet in a floating window without switching

Watch Window displays cell values and formulas from any sheet in a persistent panel.

Question 2: Cell addresses are used in formulas as:

Formularizing
Cell mathematics
Prefixing
Cell referencing (Correct answer)

Correct answer: Cell referencing

When you use a cell address (like A1, B5, or C10) within an Excel formula, you are performing 'cell referencing.' This means the formula will use the value currently stored in that specific cell to perform its calculation. Cell referencing is crucial for creating dynamic and interconnected spreadsheets, allowing formulas to update automatically when referenced cell values change.

Question 3: What is a box and whisker chart used for?

Decorative chart borders
Statistical distribution showing median, quartiles, and outliers (Correct answer)
Stock price open/high/low/close
Minimum and maximum only

Correct answer: Statistical distribution showing median, quartiles, and outliers

Box plots display five-number summaries: min, Q1, median, Q3, max, plus outliers.

Question 4: How does Protect Workbook Structure differ from Protect Sheet?

Identical features
Structure prevents sheet operations (add/delete/move); Sheet prevents cell editing (Correct answer)
Structure is Excel Online only
Structure locks formulas

Correct answer: Structure prevents sheet operations (add/delete/move); Sheet prevents cell editing

Protect Structure prevents structural changes; Protect Sheet prevents content changes.

Question 5: What does the Fill Handle do?

Adds a border
Extends series, copies values, or applies patterns when dragged (Correct answer)
Opens Fill dialog
Fills with color

Correct answer: Extends series, copies values, or applies patterns when dragged

Dragging the fill handle auto-fills adjacent cells by extending patterns or copying.

Question 6: Which Excel feature allows you to apply consistent formatting across multiple cells based on their values?

Cell Styles
Data Validation
Format Painter
Conditional Formatting (Correct answer)

Correct answer: Conditional Formatting

Conditional Formatting automatically applies formatting rules based on cell values, such as highlighting cells above a certain threshold.

Question 7: What shortcut keys can you use to quickly aggregate rows so you may enlarge or reduce an area of data?

ALT + A + G + G (Correct answer)
ALT + A + W + G
ALT + A + B
ALT + A + U + U

Correct answer: ALT + A + G + G

The shortcut keys ALT + A + G + G are used to group rows or columns in Excel. This feature allows users to collapse or expand sections of data, making large spreadsheets more manageable and easier to navigate. It's a powerful tool for organizing and presenting data efficiently.

Question 8: To analyze a large dataset containing sales transactions, you need to summarize the data by calculating total sales per region, average order value per salesperson, and the count of unique customers per product category. Which tool provides the most efficient way to create this type of interactive summary report?

PivotTable (Correct answer)
Subtotals feature
Consolidate feature
Advanced Charting

Correct answer: PivotTable

A PivotTable is the most efficient and powerful tool for this task. It allows you to quickly summarize, group, and analyze large amounts of data by dragging and dropping fields into different areas (Rows, Columns, Values). It can perform various calculations like sum, average, and count, and can be interactively filtered and sorted to explore the data from different perspectives.

Question 9: What is the default file extension for Excel workbooks in Excel 2016 and later?

.csv
.xls
.xlsx (Correct answer)
.xlsm

Correct answer: .xlsx

The .xlsx format is the default XML-based format introduced in Excel 2007 and used in all modern versions.

Question 10: How does AutoComplete differ in Tables vs regular ranges?

Doesn't work in Tables
Slower in Tables
Tables offer column name suggestions when typing [ in formulas (Correct answer)
No difference

Correct answer: Tables offer column name suggestions when typing [ in formulas

Tables show structured reference column names when typing [ after the table name.

Question 11: How do you move a worksheet to another workbook?

File > Export > Move Sheet
Right-click tab > Move or Copy, select destination workbook (Correct answer)
Drag tab to other window
Cannot move between workbooks

Correct answer: Right-click tab > Move or Copy, select destination workbook

Right-click > Move or Copy lets you choose destination workbook and position.

Question 12: What does Paste Special > 'Values' do?

Pastes only calculated results, removing formulas and formatting (Correct answer)
Pastes everything
Pastes with conditional formatting
Pastes only formatting

Correct answer: Pastes only calculated results, removing formulas and formatting

Paste Values strips formulas and formatting, leaving only the result.

Question 13: How do you protect a worksheet?

Lock individual cells only
Review > Protect Sheet with optional password and allowed actions (Correct answer)
Automatic on save
Set file to read-only

Correct answer: Review > Protect Sheet with optional password and allowed actions

Protect Sheet lets you set password and specify which actions users can still perform.

Question 14: You are working with a large dataset and want to quickly select all the blank cells within a specified range to either delete them or fill them with specific data. Which feature would be most efficient for this task?

Flash Fill
Find and Replace
Go To Special (Correct answer)
Conditional Formatting

Correct answer: Go To Special

The Go To Special feature allows you to select cells that meet specific criteria, such as being blank. By selecting 'Blanks' in the Go To Special dialog box, you can highlight all empty cells in a range at once for further action.

Question 15: You have an Excel table named 'SalesData' with columns for '[Units]' and '[Unit_Price]'. You want to add a new calculated column named '[Revenue]' that multiplies the units by the unit price for each row. Which formula, when entered into the first cell of the new column, will correctly and automatically populate for all other rows in the table?

=[@[Units]]*[@[Unit_Price]] (Correct answer)
=PRODUCT([Units],[Unit_Price])
=B2*C2
=SalesData[Units]*SalesData[Unit_Price]

Correct answer: =[@[Units]]*[@[Unit_Price]]

In an Excel table, using `[@ColumnName]` creates a structured reference that refers to the value in the specified column for the current row. This syntax allows Excel to automatically fill the formula down the entire column, creating a calculated column.

Question 16: How does Advanced Filter differ from AutoFilter?

Can extract to another location, use complex AND/OR criteria, find unique records (Correct answer)
Advanced Filter is slower
Only works with Tables
Identical functionality

Correct answer: Can extract to another location, use complex AND/OR criteria, find unique records

Advanced Filter supports complex multi-field AND/OR criteria, copying results elsewhere, and unique records.

Question 17: What is the difference between deleting contents and deleting cells?

No difference
Deleting cells is permanent
Contents clears values but keeps cells; deleting cells removes them and shifts neighbors (Correct answer)
Contents removes formatting

Correct answer: Contents clears values but keeps cells; deleting cells removes them and shifts neighbors

Delete key clears values; Right-click > Delete removes the cell and shifts adjacent cells.

Question 18: The formula =LEFT("PracticeTest", 8) returns which string?

racticeT
PracticeT
Practice (Correct answer)
TestGeek

Correct answer: Practice

LEFT extracts 8 characters from the start of the text string, yielding "Practice".

Question 19: How do you use Go To Special to select specific cell types?

Home > Select Special
Only available in VBA
Ctrl+G then 'Special' to select by type (formulas, blanks, constants) (Correct answer)
View > Go To Special

Correct answer: Ctrl+G then 'Special' to select by type (formulas, blanks, constants)

Press Ctrl+G, click Special, then choose criteria like formulas, blanks, or constants.

Question 20: What does 'Value Field Settings' allow you to configure for a PivotTable Values field?

The display name, summary function (Sum/Count/Average), and number format (Correct answer)
The source data range used by the field
The conditional formatting rules applied to the field
The sort order and custom grouping of the field

Correct answer: The display name, summary function (Sum/Count/Average), and number format

Value Field Settings lets you change the aggregation function and apply number formatting for a specific field in the Values area.

Question 21: What does refreshing a PivotTable do?

Deletes all filters applied to the PivotTable
Converts the PivotTable to a regular table
Updates the PivotTable to reflect changes in the source data (Correct answer)
Resets the PivotTable layout to the default

Correct answer: Updates the PivotTable to reflect changes in the source data

Refreshing updates the PivotTable to incorporate any additions, deletions, or changes made to the source data.

Question 22: A user wants to create a formula that first calculates the total sales (Price * Quantity) and then applies a discount if the total is over $1,000. To avoid calculating the total sales twice within an IF statement, which function is most efficient for defining the total sales as a named variable within the formula?

LET (Correct answer)
DEFINE
VAR
IFS

Correct answer: LET

The LET function is designed to declare and assign values to variables within a formula's scope. This improves readability and performance by preventing redundant calculations. The user can define 'TotalSales' as Price * Quantity once and then reference 'TotalSales' in the subsequent calculation.

Question 23: Which keyboard shortcut is used to refresh a PivotTable?

Alt+F5 (Correct answer)
Ctrl+Shift+R
Ctrl+R
F9

Correct answer: Alt+F5

Alt+F5 refreshes the active PivotTable, updating it to reflect any changes in the source data.

Question 24: A user wants to copy a range of cells containing formulas (A1:A10) to a new location (B1:B10), but needs to paste only the calculated values, not the formulas themselves. Which Paste Special option should be used?

Transpose
Formulas
Values (Correct answer)
Formats

Correct answer: Values

The 'Values' option in Paste Special is used to paste the static, calculated results of formulas. This action removes the underlying formulas, leaving only the visible data.

Question 25: What does the Transpose paste option do?

Converts text to numbers
Reverses data order
Translates data
Switches rows and columns (Correct answer)

Correct answer: Switches rows and columns

Transpose switches orientation — rows become columns and vice versa.

Question 26: What are Workbook Properties used for?

Only in Explorer
Storing metadata like title, author, keywords via File > Info (Correct answer)
Security settings
Calculation settings

Correct answer: Storing metadata like title, author, keywords via File > Info

Properties store metadata that helps organize and find files.

Question 27: How do you link data between worksheets?

Requires VBA
Type = then click cell on another sheet, creating =Sheet2!A1 (Correct answer)
Copy and paste values
Use LINK function

Correct answer: Type = then click cell on another sheet, creating =Sheet2!A1

Reference another sheet's cell with =SheetName!CellAddress syntax.

Question 28: What does the formula =IFERROR(A1/B1, "Error") return when B1 is 0?

FALSE
Error (Correct answer)
0
#DIV/0!

Correct answer: Error

IFERROR returns the second argument whenever the first argument produces any error, including #DIV/0!.

Question 29: Which tool lets you add clickable buttons that filter both a PivotTable and its PivotChart at the same time?

Conditional Formatting
Slicer (Correct answer)
Sparklines
Data Validation list

Correct answer: Slicer

Slicers provide visual filters connected to one or more PivotTables and their charts.

Question 30: Which of the following is NOT one of the four field areas in the PivotTable Fields pane?

Headers (Correct answer)
Values
Rows
Filters

Correct answer: Headers

The four PivotTable field areas are Rows, Columns, Values, and Filters — Headers is not a PivotTable field area.

Question 31: How do you create a workbook template?

Save as .xlsx
Design tab > Save Template
Only in Word
Save as Excel Template (.xltx) preserving formatting, formulas, structure (Correct answer)

Correct answer: Save as Excel Template (.xltx) preserving formatting, formulas, structure

Saving as .xltx creates a reusable template; new workbooks start as copies.

Question 32: Which of the following chart elements provides a key to identify the different data series represented by colors, patterns, or symbols in a chart?

Axis Titles
Legend (Correct answer)
Chart Title
Data Labels

Correct answer: Legend

The legend is a key component of a chart that explains what the different colors, patterns, or symbols used for the data series represent, making the chart easier to understand.

Question 33: After creating a line chart that shows sales data over time, you want to add a visual indicator to show the general direction of the data and help with forecasting. What chart element should you add?

Gridlines
Data Labels
A Trendline (Correct answer)
A Legend

Correct answer: A Trendline

A trendline is used to display trends in your data series and can help in making predictions. It is a line that best fits the data points on a chart and is commonly used for regression analysis and forecasting future values.

Question 34: Which function calculates the number of days between two dates?

DATEDIF (Correct answer)
DATEIF
NETDAYS
DAYS

Correct answer: DATEDIF

DATEDIF calculates the difference between two dates in specified units (days, months, or years).

Question 35: New records were added to the source table of a PivotChart, but the chart has not changed. What should you do?

Change the chart type
Create a new PivotChart
Click Refresh on the PivotChart Analyze tab (Correct answer)
Reapply the chart style

Correct answer: Click Refresh on the PivotChart Analyze tab

PivotCharts do not update until the PivotTable cache is refreshed.

Microsoft Excel (MO-210) Certification

The Microsoft Office Specialist Excel (MO-210) exam certifies proficiency in creating and managing worksheets, applying formulas, creating charts, and formatting data in Excel.

Questions

50 min

Time Limit

70%

To Pass

Checking for saved progress...

Exam Rules

You can skip questions and return to them later
Flag questions for review before submitting
No feedback shown until you submit the entire exam
Unanswered questions count as wrong — answer everything
10 pretest questions are mixed in and don't affect your score
Timer auto-submits when time runs out
Your progress is auto-saved every 30 seconds

These practice questions were written with AI tools. They are not real exam questions. Exam facts are checked against the official sources listed below. Not reviewed by a subject-matter expert. Editorial policy

Sources & references

Exam facts on this page (fees, format, dates, passing scores) are checked against these official sources; the date is our last check. Editorial policy

learn.microsoft.com — Retake policy, test delivery, time limit, eligibility · verified Oct 2026
certiport.pearsonvue.com — Exam format, score validity, passing score, score release, exam information · verified Oct 2026