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

Question 1: How does the Watch Window help with large spreadsheets?

Displays selected cells' values in a floating window visible while navigating (Correct answer)
Tracks other users' edits
Monitors file size
Shows print preview

Correct answer: Displays selected cells' values in a floating window visible while navigating

Watch Window shows cell values and formulas in a persistent panel across sheets.

Question 2: How do you create a workbook template?

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

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

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

Question 3: When would you choose a bubble chart over a scatter chart?

Categorical data
No correlation in data
Need to represent a third variable using bubble size (Correct answer)
Fewer than 5 data points

Correct answer: Need to represent a third variable using bubble size

Bubble charts add a third dimension via bubble size to scatter plots.

Question 4: 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 only one hidden sheet to unhide at a time. (Correct answer)
A dialog box will appear, allowing the user to select multiple worksheets to unhide simultaneously.
All hidden worksheets will immediately become visible.
The user will be prompted to enter a password before any sheets can be unhidden.

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 5: What is the primary purpose of using the 'Protect Workbook' feature in Excel?

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

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 6: When a PivotTable is filtered using a Slicer, what happens to its associated PivotChart?

The PivotChart is disconnected from the PivotTable
The PivotChart remains unchanged until manually refreshed
A warning message appears asking whether the chart should be updated
The PivotChart is automatically updated to reflect the filtered data (Correct answer)

Correct answer: The PivotChart is automatically updated to reflect the filtered data

PivotCharts are dynamically linked to their PivotTable, so any filter changes are immediately and automatically reflected in the chart.

Question 7: How does Remove Duplicates work?

Counts duplicates
Highlights duplicates
Moves duplicates to new sheet
Permanently deletes duplicate rows, keeping first occurrence (Correct answer)

Correct answer: Permanently deletes duplicate rows, keeping first occurrence

Remove Duplicates permanently deletes rows with duplicate values in selected columns.

Question 8: What happens when you double-click a value cell in a PivotTable?

Excel drills down and creates a new sheet showing the underlying detail records (Correct answer)
A dialog box opens to change the value
The cell is merged with adjacent cells
The value is highlighted for editing

Correct answer: Excel drills down and creates a new sheet showing the underlying detail records

Double-clicking a value cell drills down to show the source records that make up that aggregated value on a new worksheet.

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

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

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

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

Question 10: What does refreshing a PivotTable do?

Updates the PivotTable to reflect changes in the source data (Correct answer)
Converts the PivotTable to a regular table
Deletes all filters applied to the PivotTable
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 11: What is the purpose of the IFERROR function?

Converts error values to zero
Prevents formulas from being entered
Highlights cells that contain errors
Returns a specified value if a formula produces an error (Correct answer)

Correct answer: Returns a specified value if a formula produces an error

IFERROR evaluates a formula and returns a custom value instead of displaying an error message.

Question 12: What is the purpose of grouping items in a PivotTable?

To hide items without removing them from the source data
To merge selected rows into a single cell
To organize items into custom categories or time periods for more meaningful analysis (Correct answer)
To move items to a different column in the PivotTable

Correct answer: To organize items into custom categories or time periods for more meaningful analysis

Grouping lets you combine related items (such as months into quarters, or numeric ranges into buckets) to make the PivotTable easier to analyze.

Question 13: How do structured references differ from cell references?

Only work in VBA
Use table and column names like Table1[@Sales] instead of B2 (Correct answer)
Another name for absolute references
Use numbers instead of letters

Correct answer: Use table and column names like Table1[@Sales] instead of B2

Structured references use descriptive names making formulas readable and resilient to changes.

Question 14: When you sort a table by a column in Excel, what happens to the other columns in the same row?

They are sorted alphabetically as well
They are hidden until the sort is cleared
They move together with the sorted column to keep each row's data intact (Correct answer)
They remain in their original position

Correct answer: They move together with the sorted column to keep each row's data intact

Excel sorts entire rows together, so related data in each row stays aligned regardless of which column is sorted.

Question 15: A financial analyst needs to determine the optimal product mix to maximize profit, given constraints on production capacity, labor hours, and raw materials. Which Excel tool is most suitable for solving this type of optimization problem?

Scenario Manager
Goal Seek
Data Table
Solver (Correct answer)

Correct answer: Solver

Solver is the appropriate tool for this scenario because it is designed to find an optimal value (maximum, minimum, or a specific value) for a formula in one cell, called the objective cell, subject to constraints on the values of other formula cells on a worksheet. Goal Seek, by contrast, only works with a single variable input and a single outcome.

Question 16: A project manager is tracking weekly performance data for several team members. To provide a quick, at-a-glance visual summary of each person's trend directly next to their name and data, which Excel feature would be most efficient?

A Treemap chart
Conditional Formatting Data Bars
A PivotChart
Sparklines (Correct answer)

Correct answer: Sparklines

Sparklines are miniature charts that reside within a single cell, making them perfect for providing a compact, quick visual representation of a data trend next to the source data. They are designed for exactly this type of row-by-row trend analysis without the complexity of a full chart.

Question 17: Which of the following correctly describes Grand Totals in a PivotTable?

Totals calculated only for the currently visible rows
Summary values at the bottom row and/or rightmost column that aggregate all data (Correct answer)
Averages displayed at the top of each column in the PivotTable
Totals that appear only when a filter is actively applied

Correct answer: Summary values at the bottom row and/or rightmost column that aggregate all data

Grand Totals are overall summary rows and/or columns at the edges of the PivotTable that sum or aggregate all values across every row or column.

Question 18: What does the Fill Handle do?

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

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 19: What does 'PivotTable' allow you to do in Excel?

Summarize and analyze large data sets interactively (Correct answer)
Merge multiple worksheets
Rotate charts to new orientations
Create drop-down menus

Correct answer: Summarize and analyze large data sets interactively

A PivotTable is an interactive tool that quickly summarizes, groups, and analyzes large datasets without changing the original data.

Question 20: What does Flash Fill do?

Detects patterns in your data entry and fills remaining cells (Correct answer)
Applies fill animation
Applies fill color quickly
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 21: Which function extracts a specified number of characters from the left side of a text string?

RIGHT
MID
LEFT (Correct answer)
FIND

Correct answer: LEFT

LEFT(text, num_chars) returns the first num_chars characters from the beginning of a text string.

Question 22: What is a PivotTable in Microsoft Excel?

A chart type that rotates around a central axis
An interactive tool that summarizes large data sets into a compact, organized format (Correct answer)
A table with locked cells that cannot be edited
A table that automatically converts data to percentages

Correct answer: An interactive tool that summarizes large data sets into a compact, organized format

A PivotTable is an interactive data summarization tool that reorganizes and aggregates large data sets for easier analysis.

Question 23: Which keyboard shortcut inserts the current date as a static value in a cell?

Ctrl+; (Correct answer)
Ctrl+Shift+T
Ctrl+Shift+:
Ctrl+D

Correct answer: Ctrl+;

Ctrl+; inserts today's date as a fixed value that does not change when the workbook is recalculated.

Question 24: How do you name a range of cells?

Home tab Name button
Names assigned automatically
Select range, type name in Name Box, press Enter (Correct answer)
Right-click > Name Range

Correct answer: Select range, type name in Name Box, press Enter

Select cells, click the Name Box (left of formula bar), type the name, press Enter.

Question 25: You are working with a large dataset and need to view rows at the top of the worksheet (headers) and rows at the bottom of the worksheet (totals) simultaneously. Which feature in Excel allows you to divide the worksheet into different panes that scroll independently?

Split (Correct answer)
Freeze Panes
Arrange All
New Window

Correct answer: Split

The Split command on the View tab divides the worksheet into two or four separate panes, each with its own scroll bars. This allows you to scroll through one pane while keeping another pane, such as the one with headers or totals, visible.

Question 26: What are Workbook Properties used for?

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

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

Properties store metadata that helps organize and find files.

Question 27: What is the benefit of a funnel chart?

Showing data through filters
Water flow simulations
Displaying sequential stages with decreasing values like a sales pipeline (Correct answer)
Interactive chart filtering

Correct answer: Displaying sequential stages with decreasing values like a sales pipeline

Funnel charts show values decreasing across sequential stages.

Question 28: What does Text to Columns do?

Wraps text in cells
Converts column charts to text
Converts text numbers to numbers
Splits one column into multiple based on delimiter or fixed width (Correct answer)

Correct answer: Splits one column into multiple based on delimiter or fixed width

Text to Columns splits text using delimiters or fixed character positions.

Question 29: 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?

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

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 30: The purpose of the word "=SUM" at the start of an Excel spreadsheet formula is

To inform the computer that an arithmetic function will occur.
To tell the person viewing that this is a function and it should be added together.
To calculate all the data correctly without any mistakes.
To add all the data together using addition only. (Correct answer)

Correct answer: To add all the data together using addition only.

The 'SUM' function in Excel is specifically designed to calculate the total of a range of numbers. When used in a formula like '=SUM(A1:A5)', it instructs Excel to add up all the numerical values within the specified range. This is a fundamental function for aggregation and basic arithmetic in spreadsheets.

Question 31: How do you manage External Links to other workbooks?

File > Properties
Manage themselves
Data > Edit Links shows all external references with update, change, break options (Correct answer)
Not tracked

Correct answer: Data > Edit Links shows all external references with update, change, break options

Edit Links displays all external references with options to update, change source, or break links.

Question 32: 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
Conditional Formatting
Go To Special (Correct answer)

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 33: What is the maximum number of rows in an Excel worksheet (Excel 2007 and later)?

256
16,384
1,048,576 (Correct answer)
65,536

Correct answer: 1,048,576

Starting with Excel 2007, worksheets support up to 1,048,576 rows (2^20), a significant increase from the 65,536 row limit in earlier versions.

Question 34: What happens to a PivotChart when the underlying PivotTable is deleted?

The chart shows an error and cannot be opened
The chart is deleted automatically
The chart is unaffected and still updates
The chart becomes a static regular chart with the last data (Correct answer)

Correct answer: The chart becomes a static regular chart with the last data

Without its PivotTable, the chart converts to a standard chart with fixed data.

Question 35: How do you create a reusable chart template?

Export as image and reinsert
Format Painter on the chart
Only available in PowerPoint
Right-click chart, Save as Template (.crtx file) (Correct answer)

Correct answer: Right-click chart, Save as Template (.crtx file)

Right-click > Save as Template saves formatting as a .crtx file for reuse.

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