Microsoft Office Specialist: Excel Expert (Microsoft 365 Apps) — Practice Set 2

Question 1

You are working in Microsoft Excel. You need to protect a worksheet from editing but allow specific users to edit only the range B2:B20 without a password prompt for the rest of the sheet. What should you configure before protecting the sheet?

Accepted Answer

Review > Allow Edit Ranges lets you define one or more ranges that remain editable (optionally with their own password) even after the sheet is protected. Option B alone only marks cells as locked/unlocked but doesn't set up range-specific permissions or passwords, and locking has no effect until sheet protection is turned on.

Question 2

You are working in Microsoft Excel. A workbook contains formulas that reference cells in a separate, closed workbook named Budget2026.xlsx. You want to see all such external references and either update or remove them. Which feature should you use?

Accepted Answer

Data > Edit Links (Data tab, Queries & Connections group, under Edit Links to Files) lists every external workbook reference used in formulas, and lets you update the values, change the source, open the source workbook, or break the link so the formulas convert to their last calculated static values.

Question 3

You are working in Microsoft Excel. You have finished editing a workbook and want to signal to others that it is complete and discourage further edits, while still allowing them to view it. Which command should you use?

Accepted Answer

Mark as Final sets the workbook to read-only status and displays a banner indicating the document is final, discouraging (but not fully preventing) further edits — a reader can still choose to re-enable editing. It does not require a password, unlike encryption or sheet protection.

Question 4

You are working in Microsoft Excel. You want to apply conditional formatting that shows a red down arrow, yellow flat arrow, or green up arrow next to each cell in a column of quarterly performance scores. Which conditional formatting option should you apply?

Accepted Answer

Icon Sets place a small icon (such as arrows, traffic lights, or flags) inside or beside each cell based on its relative value. The 3 Arrows icon set specifically uses red, yellow, and green arrows to indicate low, middle, and high values — exactly the visual described.

Question 5

You are working in Microsoft Excel. You want to set up data validation on cell C2 so that it only accepts a number greater than the value in C1, and shows a custom error message when an invalid entry is made. Which two configuration steps are required? Choose 2.

Accepted Answer

Data Validation's Custom option with the formula =C2>C1 validates the entry against another cell's value. The Error Alert tab lets you define the title and message shown when a user enters invalid data. Conditional Formatting (C) only changes appearance and does not block invalid entries.

Question 6

You are working in Microsoft Excel. You have a column listing project stages (Planning, Design, Build, Test, Deploy) and want the rows to sort in that specific business order rather than alphabetically. What should you do?

Accepted Answer

Data > Sort, then setting the Order dropdown to Custom List lets you create or select a list defining the exact sequence (Planning, Design, Build, Test, Deploy). Excel then sorts rows according to that custom order instead of alphabetical order. A helper column (B) also works but is less direct than the built-in custom list feature.

Question 7

A regional operations manager is working in Microsoft Excel. He has four worksheets, one for each region, each containing a Sales column in the same layout. He wants a single summary sheet that automatically sums the Sales figures from all four sheets by matching row position. Which feature should he use?

Accepted Answer

Data > Consolidate lets you combine data from multiple ranges (on the same or different sheets) using a summary function such as Sum, matching by position or by labels. Adding each region sheet's Sales range as a reference and choosing Sum produces the combined summary automatically, without manually typing multi-sheet formulas.

Question 8

You are working in Microsoft Excel Power Query. You want to combine a Customers query and an Orders query so that every order row also shows the customer's name, keeping all order rows even if a customer has no matching record. Which two actions are required? Choose 2.

Accepted Answer

Merge Queries combines two queries side by side based on matching columns; selecting Orders as the primary (left) table and Customers as the right table with a Left Outer join keeps every row from Orders and attaches matching customer data where it exists. Append (D) stacks rows instead of joining columns, and Inner join (C) would drop unmatched orders, which is not desired here.

Question 9

You are working in Microsoft Excel. A product currently costs $40 to produce and sells for $65, with fixed monthly costs of $12,000. You want to find how many units must be sold to break even, given a known profit formula. Which tool should you use?

Accepted Answer

Goal Seek (Data > What-If Analysis > Goal Seek) is designed to find the input value needed for a formula to reach a target result. Setting the profit formula cell's target value to 0 and changing the units-sold cell finds the break-even quantity directly. Scenario Manager compares predefined input sets rather than solving for a target, and Solver is better suited to optimization with constraints.

Question 10

You are working in Microsoft Excel. You want to highlight an entire row in green whenever the value in that row's Status column (column D) equals 'Complete'. Which approach is correct?

Accepted Answer

A conditional formatting rule using a formula with a mixed reference ($D2, column locked but row relative) applied across the whole data range evaluates column D for every row and, when true, formats the entire row. Highlighting only column D (B) would color just that column, not the full row.

Question 11

You are working in Microsoft Excel. You want to return every row from a table where the Region column equals 'West' and the Amount column exceeds 1000, spilled automatically into adjacent cells. Which function should you use?

Accepted Answer

FILTER(array, include) returns an array of rows meeting the include condition, spilling the results into adjacent cells. Multiplying two boolean conditions, (Region="West")*(Amount>1000), applies an AND logic across both criteria. VLOOKUP only returns a single match and SORT merely reorders rows without filtering.

Question 12

You are working in Microsoft Excel. You want to create a reusable custom function called ConvertToFahrenheit that takes a Celsius value and calculates (Celsius * 9/5) + 32, without writing VBA code. Which feature should you use?

Accepted Answer

LAMBDA lets you define a custom, reusable formula-based function directly in Excel, entirely with formulas — no VBA required. Defining =LAMBDA(celsius, (celsius*9/5)+32) and saving it in Name Manager under the name ConvertToFahrenheit makes it callable like any built-in function, e.g. =ConvertToFahrenheit(A2).

Question 13

You are working in Microsoft Excel. You need to calculate the number of working days (excluding weekends) between a project start date in A2 and end date in B2, also excluding a list of company holidays in the range D2:D10. Which formula is correct?

Accepted Answer

NETWORKDAYS(start_date, end_date, [holidays]) returns the count of whole working days between two dates, automatically excluding weekends and any dates listed in the optional holidays range. WORKDAY instead returns a date that is a number of workdays from a start date, which answers a different question.

Question 14

You are working in Microsoft Excel. You have a column of customer names with many duplicates and want a spilled list of distinct names sorted alphabetically. Which formula is correct?

Accepted Answer

UNIQUE(A2:A200) returns an array of the distinct values in the range, and wrapping it with SORT arranges those distinct values alphabetically, spilling both results into adjacent cells automatically. This combination is a common dynamic-array pattern for building clean lookup or reporting lists.

Question 15

An analyst is working in Microsoft Excel. She wants to record a macro that applies bold formatting to whatever cell is currently selected, so the macro can be reused on any cell regardless of its address. Before clicking Record Macro, what must she do to ensure the macro works this way?

Accepted Answer

By default, Excel records macros using absolute cell references, so a recorded action always targets the exact cell recorded, regardless of the current selection. Toggling 'Use Relative References' (Developer tab) before recording makes Excel record the action relative to the currently selected cell, so the macro can be replayed on any cell.

Question 16

You are working in Microsoft Excel and want to use the LET function to simplify a long formula that calculates the same subtotal multiple times. Which two statements about LET are correct? Choose 2.

Accepted Answer

LET(name, value, ..., calculation) assigns names to intermediate values within a single formula, allowing them to be reused without recalculating the underlying expression each time, which improves both readability and calculation performance. It does not create a persistent named range in Name Manager (C) and has nothing to do with VBA or PivotTables.

Question 17

You are working in Microsoft Excel. You have a PivotTable summarizing sales by region, and you want to reference the total for the 'West' region in a formula outside the PivotTable, in a way that stays accurate even if the PivotTable layout changes. Which function should you use?

Accepted Answer

GETPIVOTDATA retrieves data stored in a PivotTable by referencing the field and item names rather than fixed cell positions, so it continues to return the correct value even if the PivotTable is refreshed, filtered, or rearranged. VLOOKUP, INDEX/MATCH, and SUMIF rely on the cells staying in fixed positions, which can break when a PivotTable layout changes.

Question 18

You are working in Microsoft Excel. You want a chart title that automatically updates to display the current value of cell A1, such as 'Sales Report — Q3'. What should you do?

Accepted Answer

After selecting the chart title, typing an equals sign and referencing a cell (e.g., =Sheet1!$A$1) in the formula bar links the title to that cell's contents. The title then updates automatically whenever the referenced cell changes, avoiding the need to manually retype it.

Question 19

You are working in Microsoft Excel. You have a chart comparing monthly Revenue (in thousands) and Profit Margin (as a percentage). Because the two series have very different scales, the Profit Margin line is barely visible. What should you do?

Accepted Answer

A combo chart lets you assign one series (such as Profit Margin) to a secondary vertical axis with its own scale, while the other series (Revenue) remains on the primary axis. This makes both series clearly visible despite their different magnitudes, which a single shared axis cannot achieve.

Question 20

You are working in Microsoft Excel. You have three PivotTables built from the same underlying Data Model, and you want a single Slicer to filter all three PivotTables simultaneously. Which two steps are required? Choose 2.

Accepted Answer

A Slicer is first inserted from any one PivotTable that shares the field. Then, using Report Connections (accessible by right-clicking the Slicer or via PivotTable Analyze > Filter Connections), you can check additional PivotTables built on the same source so the Slicer filters all of them simultaneously — a single Slicer, multiple connected reports.

Question 21

A finance manager is working in Microsoft Excel. Her PivotTable has a Region field with items 'North', 'South', 'East', and 'West'. She wants to add a new item called 'Combined East-West' that automatically shows the sum of the East and West items, without altering the source data. What should she do?

Accepted Answer

A Calculated Item (PivotTable Analyze > Fields, Items & Sets > Calculated Item) creates a new virtual item within an existing field, computed from other items in that same field using a formula such as =East+West. Grouping (D) would combine East and West into one bucket rather than adding a distinct combined item alongside the originals, and it would prevent viewing East and West individually.

Question 22

You are working in Microsoft Excel. You have a PivotTable showing individual customer ages and want to group them into 10-year bands (0-9, 10-19, 20-29, etc.) instead of listing every age separately. What should you do?

Accepted Answer

Right-clicking a numeric field in a PivotTable and choosing Group opens the Grouping dialog, where you can set 'By' to an interval such as 10. Excel then automatically creates bands like 0-9, 10-19, 20-29, replacing individual age values with grouped ranges in the PivotTable rows.

Question 23

You are working in Microsoft Excel. You want your PivotTable to display each row field's items in separate, fully labeled columns (rather than the default indented Compact form) and repeat parent item labels on every row. Which two actions should you take? Choose 2.

Accepted Answer

Show in Tabular Form displays each field in its own column with full headers rather than the indented Compact layout, and Repeat All Item Labels fills in the parent category on every row instead of leaving it blank after the first occurrence. Together these produce a fully labeled, flat-style table suitable for further analysis or export.

Question 24

A sales director is working in Microsoft Excel. His PivotTable lists monthly sales values, and he wants each month's value shown as an accumulating cumulative total through the year, rather than the standalone monthly figure. What should he do?

Accepted Answer

Show Values As > Running Total In, with the Month field selected as the base, automatically accumulates each month's value on top of the previous months' totals, producing a year-to-date cumulative figure directly within the PivotTable without any manual formulas.

Question 25

You are working in Microsoft Excel. You want to create a chart that visually shows how a starting cash balance increases and decreases across several transactions, ending at a final balance, with connector bars between the running totals. Which chart type is best suited for this?

Accepted Answer

A Waterfall chart (Insert > Charts > Waterfall) is purpose-built to show a sequence of positive and negative changes leading from a starting value to a final value, with floating columns and optional connector lines illustrating each increase or decrease — ideal for cash flow or budget bridge visualizations.

MO-211 — Microsoft Office Specialist: Excel Expert (Microsoft 365 Apps)