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

Question 1

You are working in Microsoft Excel. You want to prevent other users from inserting, deleting, renaming, moving, or unhiding worksheets in a shared workbook, while still allowing them to edit cell contents freely. What should you do?

Accepted Answer

Review > Protect Workbook (Structure) locks the arrangement of worksheets themselves — preventing users from adding, deleting, renaming, moving, hiding, or unhiding sheets — while leaving cell content on each sheet fully editable. Protect Sheet (B) instead restricts editing within a sheet's cells, which is the opposite of what's needed here.

Question 2

You are working in Microsoft Excel. You want numbers to display as $1.2M or $850K automatically depending on their size, without changing the underlying values, using a custom number format code. Which approach correctly displays large numbers abbreviated with M or K suffixes?

Accepted Answer

In a custom number format code, each trailing comma scales the displayed value down by a factor of 1,000 without altering the actual stored number. A format like "$0.0,,\"M\"" (two trailing commas) divides the display by 1,000,000 and appends a literal 'M', showing 1,200,000 as $1.2M while the cell still holds the real value for calculations.

Question 3

You are working in Microsoft Excel. You have a large worksheet and want to ensure that only the range A1:F40 prints when the workbook is sent to the printer, regardless of what other data exists further down the sheet. What should you do?

Accepted Answer

Page Layout > Print Area > Set Print Area, after selecting the desired range, restricts printing to only that range every time the sheet is printed, without needing to hide, delete, or manually specify the range on each print attempt.

Question 4

You are working in Microsoft Excel. A range has Data Validation rules applied, but some cells already contain values entered before the rule existed, which now violate the rule. You want to visually see exactly which existing cells fail validation. What should you do?

Accepted Answer

Data > Data Validation > (drop-down arrow next to the Data Validation button) > Circle Invalid Data draws a red oval around every existing cell in the range that does not satisfy the current validation rule, making it easy to spot and fix legacy entries without manually re-checking each cell.

Question 5

You are working in Microsoft Excel Power Query. You have a folder containing 24 CSV files, one per month over two years, all with identical column layouts, and you want to load them into Excel as one combined table. Which two steps accomplish this? Choose 2.

Accepted Answer

Data > Get Data > From File > From Folder scans a folder and lists every file found. Choosing Combine & Transform (or Combine Files, depending on Excel version) on the resulting list automatically applies the same transformation steps to each file and stacks their contents into a single combined table — far faster and less error-prone than manually copying 24 files.

Question 6

You are working in Microsoft Excel. A column of dates was imported from another system and each value is left-aligned, indicating it is stored as text rather than a real date, which breaks date-based formulas. Which approach correctly converts the text to real Excel dates?

Accepted Answer

Running Data > Text to Columns on a single column of text-formatted dates, even without splitting delimiters, and setting the column data format to Date on the final step, forces Excel to reparse and convert each text entry into a genuine date serial value. Simply applying a Date number format (B) to text has no effect because a number format does not change the cell's underlying stored value or data type.

Question 7

A homeowner is working in Microsoft Excel to plan a loan. She has a formula in B10 using PMT that calculates her monthly payment based on a loan amount in B1, a loan term in B2, and an annual interest rate in B3. She knows the maximum monthly payment she can afford and wants to find the highest interest rate she could accept while still keeping the payment at that maximum. Which tool should she use?

Accepted Answer

Goal Seek finds the input value required for a formula to reach a specific target. Setting B10 (the PMT formula result) to the target monthly payment value, by changing B3 (the interest rate), calculates exactly the interest rate at which the payment matches the affordable maximum — a single-target, single-variable problem that Goal Seek is built for.

Question 8

You are working in Microsoft Excel Power Query. An imported column contains numeric text values, but a few rows show an Error value because the source had non-numeric placeholders like 'N/A'. You want to convert the column to the Whole Number type and remove only the rows that error out during that conversion. Which two steps are required? Choose 2.

Accepted Answer

Changing the column's type to Whole Number converts convertible text values and flags any non-numeric entries (like 'N/A') as Error. Then, Home > Remove Rows > Remove Errors deletes exactly those rows containing errors from the query, leaving only successfully converted numeric rows — Remove Duplicates (C) addresses a completely different issue.

Question 9

You are working in Microsoft Excel. You want to sort a dataset first by Department (A to Z), then by Job Title (A to Z) within each department, and finally by Salary (largest to smallest) within each job title. What is the correct approach?

Accepted Answer

The Sort dialog (Data > Sort) allows multiple sort levels: the first 'Sort by' row plus additional levels added with the Add Level button. Configuring Department first (A to Z), then Job Title (A to Z), then Salary (Largest to Smallest) sorts the entire dataset correctly in one operation, respecting the priority order of the levels as listed.

Question 10

You are working in Microsoft Excel. You need to filter a dataset to show rows where the Region is 'East' OR the Sales Amount is greater than 5000, a condition that basic AutoFilter cannot express across two different columns. Which feature should you use?

Accepted Answer

In an Advanced Filter criteria range, conditions placed on the same row are combined with AND, while conditions placed on separate rows are combined with OR. Setting up a criteria range with Region="East" on one row and Sales Amount>5000 on a separate row lets Data > Advanced correctly filter for rows matching either condition — logic that standard AutoFilter cannot express across two different columns.

Question 11

You are working in Microsoft Excel. Cell A2 contains the text "Smith,John,Sales" and you want to split it into three spilled cells based on the comma delimiter, without using Text to Columns. Which function is best suited for this?

Accepted Answer

TEXTSPLIT(text, col_delimiter, [row_delimiter]) splits a text string by a specified delimiter into a spilled array across columns (or rows). =TEXTSPLIT(A2,",") splits "Smith,John,Sales" into three separate spilled cells: Smith, John, and Sales, using a single dynamic-array formula instead of the static Text to Columns wizard.

Question 12

You are working in Microsoft Excel. You have an order list with a Customer column and an Amount column, and you want a spilled, alphabetically sorted list of distinct customer names, but only including customers whose orders exceeded 1000 in at least one row. Which formula structure is correct?

Accepted Answer

Working from the inside out: FILTER(A2:A200,B2:B200>1000) first returns only the customer names from rows where Amount exceeds 1000 (with duplicates, since a customer may have multiple qualifying orders); UNIQUE() then removes duplicate names from that filtered list; SORT() finally arranges the distinct names alphabetically. Option C applies FILTER's condition array to an already-shrunk UNIQUE array of mismatched size, which does not align correctly and produces incorrect results.

Question 13

You are working in Microsoft Excel with a small, ascending-sorted commission-rate table in a single column pair (rate breakpoints in column A, commission rates in column B), and you want the simplest formula to find the correct commission rate for a given sales figure using an approximate match. Which legacy function is designed exactly for this single-row-or-column lookup?

Accepted Answer

The vector form of LOOKUP(lookup_value, lookup_vector, result_vector) is designed for exactly this scenario: a single sorted ascending vector of breakpoints matched approximately against a result vector, commonly used for commission or tax bracket tables. It requires the lookup vector to be sorted ascending, unlike VLOOKUP/XLOOKUP's more flexible but differently structured approximate match modes.

Question 14

You are working in Microsoft Excel. You want a lookup formula that returns 'Not Found' cleanly whenever XLOOKUP cannot find a match, without relying on XLOOKUP's own if_not_found argument. Which approach achieves the same result?

Accepted Answer

IFNA(value, value_if_na) returns a fallback value specifically when the wrapped formula returns the #N/A error, which is what XLOOKUP returns by default when no match is found and no if_not_found argument is supplied. =IFNA(XLOOKUP(...),"Not Found") achieves the same clean fallback without needing XLOOKUP's built-in if_not_found parameter.

Question 15

A logistics planner is working in Microsoft Excel. She ships goods from three warehouses to five stores, each combination having a different shipping cost per unit, and each warehouse has a maximum supply while each store has a minimum demand that must be met. She wants Excel to determine the shipment quantities for all fifteen combinations that minimize total shipping cost while satisfying every supply and demand constraint. Which tool is appropriate?

Accepted Answer

This is a classic multi-variable, multi-constraint optimization (transportation) problem — exactly what Solver is designed to handle. Setting the objective cell to minimize total shipping cost, defining all fifteen shipment-quantity cells as changing cells, and adding constraints for each warehouse's maximum supply and each store's minimum demand lets Solver find the cost-minimizing solution across all combinations simultaneously, which Goal Seek (single variable) and Scenario Manager (manually enumerated cases) cannot do.

Question 16

You are working in Microsoft Excel VBA. Before your macro permanently deletes a range of data, you want to ask the user to confirm, and only proceed if they click Yes. Which two elements of the MsgBox function are needed to implement this? Choose 2.

Accepted Answer

MsgBox(prompt, vbYesNo) displays a message box with Yes and No buttons. The function's return value, which corresponds to the button clicked, must be captured in a variable (e.g., response = MsgBox(...)) and then compared against the constant vbYes in an If statement to decide whether to proceed with the deletion — MsgBox does return a value, contrary to option C.

Question 17

You are working in Microsoft Excel. You have a macro named QuickFormat that you run several times per day, and you want to trigger it instantly with a keyboard shortcut instead of opening the Macro dialog each time. Where do you assign this shortcut?

Accepted Answer

In the Macro dialog (Developer > Macros or Alt+F8), selecting a macro and clicking Options opens a dialog where you can assign a Ctrl (or Ctrl+Shift) keyboard shortcut key. Once assigned, pressing that key combination runs QuickFormat instantly without opening any dialog boxes.

Question 18

You are working in Microsoft Excel. You have 500 exam scores and want a chart showing how many students fall into each 10-point score range (0-9, 10-19, and so on), visualizing the frequency distribution. Which chart type is purpose-built for this?

Accepted Answer

A Histogram chart (Insert > Charts > Statistical Chart > Histogram) automatically groups continuous numeric data into bins and displays the frequency (count) of values falling into each bin as columns, which is exactly the frequency-distribution visualization needed for a large set of exam scores.

Question 19

You are working in Microsoft Excel. You have monthly sales for the past two years in a single column and want a quick single-cell formula estimate of next month's sales based on the historical trend and seasonality, without generating an entire new worksheet or chart. Which function should you use?

Accepted Answer

FORECAST.ETS(target_date, values, timeline, [seasonality], ...) calculates a single forecasted value for a future date using exponential smoothing, accounting for seasonality patterns in the historical data, directly within one cell. The Forecast Sheet feature (D) does something similar but generates an entire new worksheet with a table and chart, which is more than needed for a quick single-cell estimate.

Question 20

You are working in Microsoft Excel with a PivotChart based on a PivotTable. You want users to be able to filter the chart's data directly by clicking buttons on the chart itself, without needing to interact with the underlying PivotTable. Which two statements about PivotChart field buttons are correct? Choose 2.

Accepted Answer

A PivotChart automatically includes field buttons that mirror the PivotTable's Filters, Legend, and Axis fields, letting users click a button and choose items to filter directly on the chart. PivotChart Analyze (or Design) > Field Buttons provides a toggle to show or hide all or specific field buttons if you prefer a cleaner look — no VBA is required, and this feature is exclusive to PivotCharts, not standard charts.

Question 21

A regional director is working in Microsoft Excel with a PivotTable summarizing total sales by store. She wants to quickly see every individual transaction row that makes up the total shown for a specific store, without writing any formulas. What should she do?

Accepted Answer

Double-clicking any value cell in a PivotTable triggers Excel's built-in 'Show Details' behavior, which automatically inserts a new worksheet containing all the individual source rows that were aggregated into that specific value — an instant drill-down with no formulas or manual filtering required.

Question 22

You are working in Microsoft Excel. A line chart of daily website traffic is very noisy from day to day, making the overall trend hard to see. You want to overlay a smoothed line representing the average of each point and the preceding 6 days, directly on the chart. What should you add?

Accepted Answer

A Moving Average trendline plots, for each point, the average of that point and a specified number of preceding points (set via the Period option). Setting the period to 7 smooths out daily noise by averaging each day with the prior 6 days, revealing the underlying trend — unlike a Linear trendline, which fits a single straight line across the whole dataset rather than a rolling smoothed line.

Question 23

You are working in Microsoft Excel. You have daily stock data with High, Low, and Close prices and want a chart showing the price range and closing value for each day. Which two facts about building a Stock chart in Excel are correct? Choose 2.

Accepted Answer

Excel's High-Low-Close stock chart variant expects the source columns arranged in that specific order to correctly draw the vertical range line (high to low) with a tick mark for the close. This chart type, along with other variants like Open-High-Low-Close, is found under Insert > Charts > Insert Waterfall, Funnel, Stock, Surface, or Radar Chart. Stock charts work from a plain worksheet range and do not require a PivotTable.

Question 24

A presenter is working in Microsoft Excel and about to project a PivotTable on screen during a meeting. He wants to temporarily hide the small expand/collapse (+/-) buttons next to each group so the table looks cleaner for the audience, without removing the grouping itself. What should he do?

Accepted Answer

PivotTable Analyze (or PivotTable Tools) > +/- Buttons is a toggle that shows or hides the expand/collapse buttons throughout the PivotTable, purely as a display preference. Turning it off keeps the underlying grouping and collapsed/expanded state fully intact — it simply removes the buttons visually, which is far simpler than ungrouping and regrouping fields.

Question 25

You are working in Microsoft Excel. You have spent significant time customizing a combo chart's colors, fonts, and layout, and want to reuse this exact formatting for future charts in other workbooks without recreating it from scratch each time. What should you do?

Accepted Answer

Right-click a formatted chart and choose Save as Template to save its type and formatting as a reusable .crtx chart template. This template then appears under All Charts > Templates when inserting a new chart in any workbook, letting you apply the same customized look instantly instead of manually recreating colors, fonts, and layout each time.

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