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

Question 1: What is the primary purpose of using the 'Protect Workbook' feature in Excel?

To prevent users from editing the contents of specific cells.
To hide specific formulas from being viewed in the formula bar.
To control the structure of the workbook, such as preventing worksheets from being added, deleted, or renamed. (Correct answer)
To encrypt the file with a password to prevent it from being opened.

Correct answer: To control the structure of the workbook, such as preventing worksheets from being added, deleted, or renamed.

Protecting the workbook focuses on the workbook's structure. It prevents users from changing the organization of the worksheets by adding, deleting, renaming, moving, hiding, or unhiding them. Protecting a worksheet, in contrast, controls the editing of cell contents within that sheet.

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

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

Correct answer: Alt+F5

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

Question 3: A user has finished their analysis using an Excel table and now needs to send the worksheet to a colleague who is using a much older version of Excel that does not support table features. The user wants to remove the table functionality (e.g., structured references, automatic expansion) but keep all the data and cell formatting. What is the most direct way to do this?

On the 'Table Design' tab, in the 'Tools' group, click 'Convert to Range'. (Correct answer)
Copy the table and use 'Paste Special' with the 'Values' option.
Delete the table's name from the Name Manager.
Select the table and use the 'Clear Formats' button on the Home tab.

Correct answer: On the 'Table Design' tab, in the 'Tools' group, click 'Convert to Range'.

The 'Convert to Range' command is the designated feature for removing the underlying table object and its functionality while preserving the data and the visual formatting that was applied by the table style.

Question 4: Which of the following is an advantage of converting a range of data into an Excel table?

B) It automatically applies a single, unchangeable format.
D) It removes the ability to sort or filter the data. (Correct answer)
C) Formulas automatically fill down new rows and structured references adjust dynamically.
A) It prevents any changes to the data within the table.

Correct answer: D) It removes the ability to sort or filter the data.

A key benefit of Excel tables is their dynamic nature. When you enter a formula in a column, it can automatically populate down to all other rows (a calculated column). Also, formulas that reference table data using structured references (e.g., `TableName[ColumnName]`) automatically expand to include new rows added to the table.

Question 5: A user has several worksheets with identical layouts and wants to apply the same formatting change to all of them simultaneously. Which of the following is the most efficient method to achieve this?

Record a macro of the formatting change and run it on each worksheet.
Group the worksheets together before applying the formatting change. (Correct answer)
Copy and paste the formatting from the first sheet to each subsequent sheet individually.
Use the Format Painter tool on each worksheet one at a time.

Correct answer: Group the worksheets together before applying the formatting change.

Grouping worksheets allows you to perform actions, such as formatting, on all selected sheets at once. Any change made to one sheet in the group is automatically applied to all other sheets in the group. This is far more efficient than applying changes to each sheet individually.

Question 6: What is AGGREGATE's advantage over SUM and AVERAGE?

It works across workbooks
It runs faster
It can ignore errors, hidden rows, and nested SUBTOTAL results (Correct answer)
It supports more data types

Correct answer: It can ignore errors, hidden rows, and nested SUBTOTAL results

AGGREGATE performs 19 different functions while optionally ignoring error values, hidden rows, and nested functions.

Question 7: To display Sales by Region as separate lines over Month on a PivotChart, where should the Region field be placed?

Legend (Series) (Correct answer)
Values
Filters
Axis (Categories)

Correct answer: Legend (Series)

Each series in a chart comes from the Legend area, which maps to Columns.

Question 8: Which formula converts the text in A1 to have only the first letter of each word capitalized?

=UPPER(A1)
=PROPER(A1) (Correct answer)
=LOWER(A1)
=CAPITAL(A1)

Correct answer: =PROPER(A1)

PROPER capitalizes the first letter of each word and lowercases the rest.

Question 9: When using the 'Arrange All' command to view multiple open workbooks, which option will display the windows in an overlapping fashion from top to bottom?

Horizontal
Tiled
Cascade (Correct answer)
Vertical

Correct answer: Cascade

The 'Cascade' option in the Arrange Windows dialog box arranges the open workbook windows so that they overlap, with the title bar of each window remaining visible. Tiled, Horizontal, and Vertical options arrange the windows without overlapping.

Question 10: What does Freeze Panes do?

Prevents editing
Locks rows/columns visible while scrolling the rest (Correct answer)
Saves current view
Prevents deletion

Correct answer: Locks rows/columns visible while scrolling the rest

Freeze Panes keeps specified rows and columns visible while scrolling.

Question 11: Which chart type is NOT available for a PivotChart?

Line
Column
XY Scatter (Correct answer)
Pie

Correct answer: XY Scatter

Scatter, bubble, and stock charts cannot be created as PivotCharts.

Question 12: How do you create a PivotTable from an Excel Table?

Click in Table, Insert > PivotTable — Table name auto-used as source (Correct answer)
Convert to range first
Cannot use Tables as source
Copy data to new sheet

Correct answer: Click in Table, Insert > PivotTable — Table name auto-used as source

Excel uses the Table name as source, so new rows are included on Refresh.

Question 13: What does the AutoFill handle look like in Excel?

A double-headed arrow on the cell border
A blue circle in the center of the cell
A green arrow pointing down
A small square at the bottom-right corner of a selected cell (Correct answer)

Correct answer: A small square at the bottom-right corner of a selected cell

The AutoFill handle is the small green square that appears at the bottom-right corner of a selected cell or range.

Question 14: A user has hidden several worksheets in a large workbook. To make them visible again, they right-click on a visible sheet tab and select 'Unhide'. What will happen next?

A dialog box will appear, allowing the user to select multiple worksheets to unhide simultaneously.
A dialog box will appear, allowing the user to select only one hidden sheet to unhide at a time. (Correct answer)
The user will be prompted to enter a password before any sheets can be unhidden.
All hidden worksheets will immediately become visible.

Correct answer: A dialog box will appear, allowing the user to select only one hidden sheet to unhide at a time.

When you use the 'Unhide' command, Excel displays the Unhide dialog box which lists all hidden sheets. However, the standard functionality only allows you to select and unhide one worksheet at a time from this list. You must repeat the process for each sheet you wish to unhide.

Question 15: Which function in Excel returns the number of cells in a range that contain numeric values?

COUNT (Correct answer)
SUM
COUNTIF
COUNTA

Correct answer: COUNT

COUNT counts only cells that contain numbers. COUNTA counts all non-empty cells regardless of data type, while COUNTIF counts cells meeting a specific condition.

Question 16: What are Workbook Properties used for?

Storing metadata like title, author, keywords via File > Info (Correct answer)
Only in Explorer
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 17: What is Data Validation used for?

Checking formula errors
Validating external connections
Testing macro code
Restricting data types and values that can be entered into cells (Correct answer)

Correct answer: Restricting data types and values that can be entered into cells

Data Validation creates rules controlling what users can enter.

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

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

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 19: How do trendlines help with data analysis?

They color-code data points
They connect first and last points only
They fit mathematical models and can project future values (Correct answer)
They highlight the median

Correct answer: They fit mathematical models and can project future values

Trendlines fit equations to data and can forecast forward or backward.

Question 20: What does pressing F4 do when editing a cell formula in Excel?

Opens the Find and Replace dialog
Repeats the last action
Toggles between absolute and relative cell reference modes for the selected reference (Correct answer)
Closes the formula bar

Correct answer: Toggles between absolute and relative cell reference modes for the selected reference

While your cursor is on a cell reference in a formula, F4 cycles through all four reference types: relative, absolute, mixed row, and mixed column.

Question 21: You frequently need to enter a specific, non-sequential list of department names into various worksheets (e.g., 'Sales', 'Marketing', 'HR', 'IT', 'Finance'). To save time and ensure consistency, you want to be able to type the first department name and use the Fill Handle to automatically populate the rest. What Excel feature should you use to set this up?

Data Validation List
Custom List (Correct answer)
Named Range
Conditional Formatting

Correct answer: Custom List

Excel's Custom List feature allows you to define your own non-alphabetic or non-numeric series for use with the AutoFill (Fill Handle) functionality. Once a custom list is created, typing any entry from the list and dragging the fill handle will populate the rest of the series in the defined order.

Question 22: What does Flash Fill do?

Applies fill color quickly
Detects patterns in your data entry and fills remaining cells (Correct answer)
Applies fill animation
Fills with random data

Correct answer: Detects patterns in your data entry and fills remaining cells

Flash Fill recognizes patterns from examples you type and auto-fills the rest.

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

Ctrl+G then 'Special' to select by type (formulas, blanks, constants) (Correct answer)
Only available in VBA
Home > Select Special
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 24: What is the maximum number of worksheets in a workbook?

1,000
65,536
Limited only by available memory (Correct answer)
255

Correct answer: Limited only by available memory

The number of worksheets is limited only by computer memory.

Question 25: What is XLOOKUP's key advantage over VLOOKUP?

Can search left, defaults to exact match, no column index needed (Correct answer)
Works offline
Supports regex
Faster on large data

Correct answer: Can search left, defaults to exact match, no column index needed

XLOOKUP uses separate arrays (no column index), searches any direction, defaults exact match, has built-in error handling.

Question 26: Which tab in Excel's ribbon is used to insert a PivotTable?

Home
Data
Formulas
Insert (Correct answer)

Correct answer: Insert

PivotTables are inserted from the Insert tab by clicking the PivotTable button in the Tables group.

Question 27: In a large table of customer orders, a manager wants to identify and permanently delete all rows where the combination of 'CustomerID' and 'OrderDate' is identical, keeping only the first instance of each unique combination. What is the most direct tool in Excel for this task?

D) Sorting the data by 'CustomerID' and 'OrderDate' and then using the UNIQUE function
B) The Advanced Filter with the 'Unique records only' option
C) The Remove Duplicates tool on the Data tab (Correct answer)
A) Conditional Formatting to highlight duplicates, then manual deletion

Correct answer: C) The Remove Duplicates tool on the Data tab

The 'Remove Duplicates' tool is specifically designed for this purpose. It allows the user to select one or more columns to define what constitutes a duplicate record. It will then permanently delete the entire rows for the duplicate entries found based on the selected columns.

Question 28: What is Group and Outline used for?

Creating chart groups
Creating named ranges
Grouping cells for formatting
Collapsing and expanding sections of rows or columns (Correct answer)

Correct answer: Collapsing and expanding sections of rows or columns

Creates collapsible sections with +/- buttons for hiding or showing detail data.

Question 29: What does the 'Select Data Source' dialog control?

Adding, editing, removing data series and modifying axis labels (Correct answer)
Chart type selection
Chart color scheme
Data filtering

Correct answer: Adding, editing, removing data series and modifying axis labels

It manages which data series appear, their ranges, order, and axis labels.

Question 30: What is a PivotTable's primary purpose?

A formatted template
Summarizes large datasets by dragging fields into rows, columns, values, and filters (Correct answer)
Table with chart axis pivots
Rotates data 90 degrees

Correct answer: Summarizes large datasets by dragging fields into rows, columns, values, and filters

PivotTables provide drag-and-drop data summarization for large datasets.

Question 31: Which of the following is a reference to an absolute cell?

A2
#A2
A:2
$A$2 (Correct answer)

Correct answer: $A$2

An absolute cell reference in Excel is denoted by dollar signs ($) before both the column letter and the row number, such as $A$2. This type of reference ensures that when a formula is copied or filled to other cells, the reference to cell A2 remains fixed and does not change. This is crucial when you want a formula to always refer back to a specific cell, regardless of its new location.

Question 32: What does Application.ScreenUpdating control in VBA?

Formula recalculation timing
Chart animations
Screen refresh during macro execution for performance (Correct answer)
Screen resolution

Correct answer: Screen refresh during macro execution for performance

Setting it to False prevents screen redrawing during macro execution, speeding up macros significantly.

Question 33: When should you use a treemap instead of a pie chart?

Hierarchical data with many categories (Correct answer)
Time-based trends
Data has negative values
Comparing exactly two values

Correct answer: Hierarchical data with many categories

Treemaps use nested rectangles to show hierarchy and proportion with many categories.

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

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

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

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

Question 35: How does INDEX work with a single range?

Creates an index of values
Indexes alphabetically
Returns the value at a specified row position (Correct answer)
Returns the cell address

Correct answer: Returns the value at a specified row position

INDEX returns the value at a given row and optional column position within a range.

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