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

Question 1

You are working in Microsoft Excel. A workbook contains a formula that intentionally references its own cell as part of a circular calculation used to model gradual convergence, such as a compounding interest loop. Excel currently blocks this with a circular reference warning. What should you change?

Accepted Answer

File > Options > Formulas > Enable Iterative Calculation allows Excel to recalculate circular formulas repeatedly, up to the specified maximum iterations or until the change between results falls below the maximum change threshold, instead of raising a circular reference error. Manual calculation (B) only changes when recalculation happens, not whether circular references are permitted.

Question 2

You are working in Microsoft Excel. Before sharing a workbook externally, you want to check for and remove hidden metadata such as author names, comments, and hidden worksheets that you may have forgotten about. Which feature should you use?

Accepted Answer

File > Info > Check for Issues > Inspect Document scans the workbook for items such as document properties, comments, hidden worksheets, hidden rows/columns, and embedded documents, then lets you remove any of them with one click before sharing the file externally.

Question 3

You are working in Microsoft Excel. You frequently switch between a filtered view showing only 'Open' orders and an unfiltered view of all orders, and want to save and quickly restore both view configurations, including filters and column widths. Which feature should you use?

Accepted Answer

Custom Views (View > Custom Views > Add) captures the current display and print settings — including filters, hidden rows/columns, and column widths — under a named view. You can then switch instantly between saved views such as 'All Orders' and 'Open Orders Only' without recreating the filter each time.

Question 4

You are working in Microsoft Excel. Column A contains full email addresses like 'jsmith@contoso.com', and you want a new column showing just the username portion before the @ symbol. After typing 'jsmith' in the first cell of the new column and starting to type the second one, Excel suggests the rest of the pattern automatically. Which feature is this?

Accepted Answer

Flash Fill (Data > Flash Fill, or Ctrl+E) detects a pattern from an example you type and automatically fills the rest of the column using that same pattern, such as extracting the username portion before an @ symbol, without requiring a formula.

Question 5

You are working in Microsoft Excel. You want cell B2 to offer a drop-down list of cities that depends on the country selected in A2 (for example, selecting 'Canada' in A2 shows only Canadian cities in B2's list). Which two steps are required to build this cascading drop-down? Choose 2.

Accepted Answer

Creating named ranges that exactly match each country name (e.g., a range named 'Canada' listing Canadian cities) lets INDIRECT($A$2) resolve to the correct named range based on A2's value. Setting B2's Data Validation List source to =INDIRECT($A$2) then dynamically shows only the cities belonging to the selected country — the standard technique for cascading drop-downs.

Question 6

You are working in Microsoft Excel. You have a column of monthly profit/loss values that include both positive and negative numbers, and you want a horizontal in-cell bar for each value where positive bars extend right and negative bars extend left from a zero midpoint. Which feature achieves this?

Accepted Answer

Conditional Formatting > Data Bars renders an in-cell gradient or solid bar proportional to each value. When the range contains both positive and negative numbers, Excel automatically anchors the bars at a zero midpoint, extending positive values to the right and negative values to the left, giving an immediate visual read of profit versus loss.

Question 7

A budget planner is working in Microsoft Excel. She wants to compare three named input sets — 'Best Case', 'Expected Case', and 'Worst Case' — each changing the same three input cells (unit price, unit cost, and volume), and then produce a single summary report comparing the resulting profit under each named set. Which tool should she use?

Accepted Answer

Scenario Manager (Data > What-If Analysis > Scenario Manager) lets you define multiple named sets of input values for the same changing cells, switch between them, and generate a Scenario Summary report that displays the resulting output for each named scenario side by side — exactly the comparison described.

Question 8

You are working in Microsoft Excel. You want to build a two-variable Data Table that shows the resulting loan payment for different combinations of interest rate (across the top row) and loan term (down the left column), based on a single payment formula. Which two setup steps are required? Choose 2.

Accepted Answer

A two-variable Data Table requires the formula to sit in the top-left corner of the selected range, with one set of input values across the top row and another down the left column. Selecting the entire range and choosing Data > What-If Analysis > Data Table, then specifying the Row Input Cell (matching the top row values) and Column Input Cell (matching the left column values), lets Excel calculate every combination automatically.

Question 9

You are working in Microsoft Excel Power Query. You have twelve separate queries, one per month, all with identical column structures, and you want to combine them into a single table stacking all rows together. Which Power Query feature should you use?

Accepted Answer

Append Queries stacks the rows of two or more queries with matching (or similar) column structures into a single combined table, one below another. Merge Queries instead joins queries side by side by matching columns, which is not what's needed when the goal is to combine twelve months of identically structured rows into one table.

Question 10

You are working in Microsoft Excel. Some cells in a Status column were manually filled with yellow to flag them for review, and you want to filter the list to show only those yellow-flagged rows. What should you do?

Accepted Answer

The AutoFilter drop-down includes a 'Filter by Color' option that lists all fill colors used in that column and lets you filter to show only rows matching a selected color, such as yellow — a quick built-in way to isolate manually color-flagged rows without formulas.

Question 11

You are working in Microsoft Excel. You want to generate a spilled array of the numbers 1 through 12 down a column, to use as month numbers for a calendar template. Which function is best suited for this?

Accepted Answer

SEQUENCE(rows, columns, start, step) generates a spilled array of sequential numbers. =SEQUENCE(12,1,1,1) produces 12 rows, 1 column, starting at 1 and stepping by 1 — exactly the numbers 1 through 12 spilled down a column. RANDBETWEEN generates random (non-sequential) numbers, which is not appropriate here.

Question 12

You are working in Microsoft Excel. You want a spilled array listing employees sorted first by Department (ascending) and then by Salary (descending) within each department, without physically rearranging the source data. Which formula structure is correct?

Accepted Answer

SORTBY(array, by_array1, sort_order1, [by_array2, sort_order2], ...) sorts an array using one or more separate sort-key arrays, each with its own order. =SORTBY(A2:C50, B2:B50, 1, C2:C50, -1) sorts by the Department column ascending (1), then by the Salary column descending (-1) within each department — a multi-key sort that plain SORT cannot do directly using a separate key range like this.

Question 13

You are working in Microsoft Excel. A subscription started on the 15th of a month and renews every 6 months from the start date. You need a formula in B2 that calculates the next renewal date based on the start date in A2. Which function is correct?

Accepted Answer

EDATE(start_date, months) returns a date that is the specified number of months before or after the start date, keeping the same day of the month. =EDATE(A2,6) correctly returns a date 6 months after A2, still on the 15th. EOMONTH instead returns the last day of the resulting month, which would lose the 15th-of-month pattern.

Question 14

You are working in Microsoft Excel. You want to refer to the range A2:A100 in formulas throughout the workbook using the meaningful name 'SalesAmounts' instead of the cell range address. What should you do?

Accepted Answer

Formulas > Define Name (or the Name Box) lets you assign a meaningful name, such as SalesAmounts, to a selected range. That name can then be used directly in formulas (e.g., =SUM(SalesAmounts)) in place of the cell address, making formulas easier to read and maintain.

Question 15

A payroll analyst is working in Microsoft Excel and writing a VBA macro. The number of employee rows changes each pay period, so she needs the macro to loop through every used row in column A and apply a formula to column B, stopping automatically at the last used row rather than a fixed row number. Which VBA approach is correct?

Accepted Answer

Cells(Rows.Count, 1).End(xlUp).Row finds the last row with data in column A by starting from the bottom of the sheet and moving up until it hits a populated cell — a dynamic way to detect the true last used row regardless of how many employees are listed. A For i = 2 To LastRow loop then processes exactly the rows that contain data, unlike a fixed-count loop (B, C) or applying to the entire column (D), which could waste processing time or apply formulas to empty rows.

Question 16

You are working in Microsoft Excel. You want to combine the non-empty values from cells A2, B2, and C2 into a single cell, separated by commas, while automatically skipping any of the three cells that are blank. Which two facts about TEXTJOIN are correct for this task? Choose 2.

Accepted Answer

TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...) joins multiple text values using a specified delimiter as its first argument, and its second argument (ignore_empty) controls whether blank cells are skipped — setting it to TRUE excludes blanks from the joined result, unlike the false claim in option C. TEXTJOIN can join any number of values or ranges and does not depend on CONCAT.

Question 17

You are working in Microsoft Excel. You have recorded a macro named FormatReport and want to let users run it by clicking a button placed directly on the worksheet, rather than opening the Macro dialog box each time. What should you do?

Accepted Answer

Developer > Insert > Form Controls > Button lets you draw a clickable button directly on the worksheet. Immediately after drawing it, Excel prompts you to Assign Macro, where you select FormatReport. Clicking the button then runs the macro directly, without needing to open the Macro dialog box.

Question 18

You are working in Microsoft Excel. You want to visualize how many leads move through each stage of a sales pipeline (Leads, Qualified, Proposal, Closed), where each stage typically has fewer records than the one before it. Which chart type best represents this narrowing progression?

Accepted Answer

A Funnel chart (Insert > Charts > Funnel) displays values that progressively decrease across stages, with each stage rendered as a horizontal bar whose width narrows as values shrink — the standard way to visualize a sales pipeline or any staged process with declining counts.

Question 19

You are working in Microsoft Excel. Your PivotTable lists sales by customer for hundreds of customers, and you want to display only the 10 customers with the highest total sales. What should you do?

Accepted Answer

The row field's filter drop-down includes Value Filters > Top 10, which lets you restrict the PivotTable to show only the top (or bottom) N items ranked by a chosen value field — in this case, the 10 customers with the highest total sales — while the PivotTable remains fully interactive and refreshable.

Question 20

You are working in Microsoft Excel. You have inserted Sparklines in cells E2:E10, one per row, and want them all to share the same vertical axis scale so their heights are visually comparable across rows. Which two statements about grouped Sparklines are correct? Choose 2.

Accepted Answer

When you select a range and insert Sparklines in one operation, Excel automatically treats them as a group, allowing group-wide settings. Sparkline Tools (Design tab) > Axis > Vertical Axis Min/Max Value Options offers a 'Same for All Sparklines' setting, which forces every Sparkline in the group to share one consistent vertical scale, making their relative heights directly comparable.

Question 21

A data analyst is working in Microsoft Excel with two tables loaded into the Data Model: 'Orders' (with a CustomerID column) and 'Customers' (with a CustomerID and CustomerName column). She wants a single PivotTable that shows total order amount grouped by CustomerName, without adding any VLOOKUP helper columns to either table. What must she do first?

Accepted Answer

Defining a relationship (Data tab > Relationships, or within Power Pivot's Manage Data Model) between Orders and Customers on the shared CustomerID column lets Excel treat both tables as a single logical data source. A PivotTable built from this Data Model can then use CustomerName from Customers alongside order amounts from Orders directly, with no VLOOKUP or manual copying required.

Question 22

You are working in Microsoft Excel. You have exam scores for 200 students and want a chart that visually shows the median, quartiles, and any outlier scores in a single compact view. Which chart type should you use?

Accepted Answer

A Box & Whisker chart (Insert > Charts > Statistical Chart > Box & Whisker) displays the distribution of a dataset by showing the median, first and third quartiles as a box, whiskers extending to the typical range, and individual points for outliers — the standard statistical chart for summarizing distribution and outliers in one view.

Question 23

You are working in Microsoft Excel. You have a PivotTable with a Region filter field, and you want Excel to automatically generate a separate worksheet, each showing the PivotTable filtered for one region. Which two steps accomplish this? Choose 2.

Accepted Answer

Show Report Filter Pages requires the field to first be placed in the PivotTable's Filters area. Then, PivotTable Analyze > Options drop-down > Show Report Filter Pages, selecting the Region field, automatically generates one new worksheet per region, each containing a copy of the PivotTable pre-filtered to that region — far faster than manually duplicating the PivotTable.

Question 24

A quality control engineer is working in Microsoft Excel. He has a column chart showing average measured length per production batch, and he wants to add visual indicators showing the standard deviation range above and below each column to represent measurement variability. What should he add to the chart?

Accepted Answer

Error Bars (Chart Design > Add Chart Element > Error Bars) display a range above and below each data point representing variability, such as standard deviation, standard error, or a fixed/percentage value. Selecting the Standard Deviation option shows exactly the measurement variability described, unlike a Trendline, which shows a fitted directional trend rather than variability around each point.

Question 25

You are working in Microsoft Excel. You have two years of monthly sales history and want Excel to automatically generate a new worksheet with a chart and table projecting sales for the next 12 months, including a confidence interval, using exponential smoothing. Which feature should you use?

Accepted Answer

Data > Forecast > Forecast Sheet uses the AAA exponential smoothing (ETS) algorithm to automatically generate a new worksheet containing both a forecast table and a chart, projecting future values (such as the next 12 months) along with upper and lower confidence interval bounds, based on historical data patterns and seasonality.

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