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

Question 1

You are working in Microsoft Excel. Your organization requires that macros from unknown sources be blocked by default, while macros in files stored in a specific trusted network folder run without warnings. Where should you configure this?

Accepted Answer

Trust Center Settings > Trusted Locations lets you designate specific folders (such as a shared network folder) whose files are exempt from macro security warnings, while macros from any other unknown location remain blocked or prompt a warning based on the Macro Settings. This gives targeted trust without disabling security workbook-wide.

Question 2

You are working in Microsoft Excel with a workbook saved to OneDrive, and AutoSave is turned on. A colleague accidentally deleted a large block of data two hours ago and the file has since been saved multiple times. How can you recover the workbook as it existed before the deletion?

Accepted Answer

File > Info > Version History (available for files stored on OneDrive or SharePoint) lists automatically saved versions over time, letting you view or restore the workbook to a point before the accidental deletion. Ctrl+Z only undoes actions within the current session and would not help two hours and multiple saves later.

Question 3

You are working in Microsoft Excel. You have recorded macros in a workbook and want to save it in a way that preserves the macros for future use. Which file format should you choose in the Save As dialog?

Accepted Answer

The standard .xlsx format does not support VBA macros and will strip them out (or prompt to save without them) when saving. Excel Macro-Enabled Workbook (.xlsm) format is specifically designed to retain VBA code, so macros remain available the next time the file is opened.

Question 4

You are working in Microsoft Excel. You have a column of stock ticker symbols and want Excel to automatically recognize them as company records so you can pull in the current stock price and company name as linked fields. Which feature should you use?

Accepted Answer

Selecting the ticker symbols and applying Data > Data Types > Stocks converts them into a rich Linked Data Type that connects to live financial data. Each cell then shows an icon you can expand, or you can extract specific fields (such as Price or Company Name) into new columns using the field-insertion button or formulas like =A2.Price.

Question 5

You are working in Microsoft Excel. You want cell C2 to only accept entries from a fixed list of department names stored in a named range called DeptList, elsewhere in the workbook. Which two steps are required? Choose 2.

Accepted Answer

First, the department names must be defined as a named range (DeptList) via Formulas > Define Name. Then, in Data Validation for C2, setting Allow to List and Source to =DeptList restricts entries to only the values in that named range, and the drop-down arrow shows the list for easy selection.

Question 6

You are working in Microsoft Excel. You have a large dataset with detail rows beneath each region's subtotal, and you want to be able to collapse the detail rows under each region using a small +/- button, without deleting any data. What should you use?

Accepted Answer

Selecting the detail rows and using Data > Group creates an outline level with a collapsible +/- button in the row margin, letting users show or hide the detail rows under each region subtotal interactively. Hiding rows manually (B) removes the interactive collapse control and is not tied to an outline structure.

Question 7

An accounting clerk is working in Microsoft Excel. He has twelve worksheets named Jan through Dec, each with the same layout, and a Summary sheet where cell B2 should show the total of cell B2 across all twelve monthly sheets. He wants this to update automatically if any monthly figure changes. Which formula should he enter in the Summary sheet's B2?

Accepted Answer

A 3-D reference such as =SUM(Jan:Dec!B2) sums the same cell (B2) across every worksheet from Jan through Dec in the sheet tab order, and updates automatically whenever any monthly B2 value changes. This is more efficient and scalable than listing every sheet individually as in option B.

Question 8

You are working in Microsoft Excel Power Query. You have a table with a Product column and twelve month columns (Jan through Dec) holding sales figures, and you want to reshape it into three columns: Product, Month, and Sales, with one row per product-month combination. Which two steps accomplish this? Choose 2.

Accepted Answer

Selecting the twelve month columns and applying Transform > Unpivot Columns converts those selected column headers into rows, creating an 'Attribute' column (renamed to Month) and a 'Value' column (renamed to Sales), while the unselected Product column remains as an identifier repeated for each new row — reshaping wide monthly data into a long, tidy format.

Question 9

You are working in Microsoft Excel. A range has two conditional formatting rules: one highlights cells red if the value is negative, and another applies a green color scale to the entire range. You want cells that meet the red rule to show only red, ignoring the color scale for those cells. What should you do?

Accepted Answer

In the Conditional Formatting Rules Manager, rules are evaluated in priority order (top to bottom for cell-value-type rules). Checking 'Stop If True' on the red highlight rule, with that rule positioned above the color scale rule, causes Excel to apply the red format and stop evaluating lower-priority rules for cells where the red condition is true, so the color scale is skipped for those cells.

Question 10

You are working in Microsoft Excel with data formatted as an Excel Table. You want to add a row at the bottom that shows the average of the Price column, instead of the default sum. What should you do?

Accepted Answer

Enabling Table Design > Total Row adds a row at the bottom of the table with a default Sum for numeric columns. Clicking the cell under the Price column reveals a drop-down where you can choose a different aggregation, such as Average, Count, Max, or Min, without leaving the table structure.

Question 11

You are working in Microsoft Excel. You need a formula that assigns a letter grade based on a score in A2: 90+ is 'A', 80-89 is 'B', 70-79 is 'C', and anything below 70 is 'F'. Which formula is the clearest way to express this using a function designed for multiple conditions?

Accepted Answer

IFS(condition1, value1, condition2, value2, ..., [default]) evaluates each condition in order and returns the value for the first TRUE condition, avoiding the deeply nested parentheses of option B. Ending with TRUE,"F" acts as a catch-all default. While B (nested IF) also works, IFS is the function specifically designed for clearer multi-condition logic.

Question 12

You are working in Microsoft Excel using a legacy array formula (entered with Ctrl+Shift+Enter) to sum column C only where column A equals 'East' OR column A equals 'North'. Which formula is correct?

Accepted Answer

In array-formula logic, addition (+) between two boolean array conditions functions as OR, since TRUE (1) results whenever either condition is met. {=SUM(IF((A2:A100="East")+(A2:A100="North"),C2:C100))} therefore sums C2:C100 where the row is East OR North. Multiplication (*) as in option B functions as AND, which cannot be true for both conditions simultaneously here since a cell cannot equal both text values at once, so it would return an incorrect (empty) result.

Question 13

You are working in Microsoft Excel. You want to look up a product code in column A that may be entered with varying suffixes, such as searching for 'SKU-100*' to match 'SKU-100-RED' or 'SKU-100-BLUE', and return the corresponding price from column D using XLOOKUP. Which setting is required?

Accepted Answer

XLOOKUP's match_mode argument set to 2 enables wildcard matching, allowing characters like * (any sequence) or ? (single character) in the lookup value. =XLOOKUP("SKU-100*", A2:A100, D2:D100, , 2) would match any product code starting with 'SKU-100', returning the price for the first match found.

Question 14

You are working in Microsoft Excel. You have a Date of Birth in cell A2 and want to calculate a person's current age in complete years. Which formula is correct?

Accepted Answer

DATEDIF(start_date, end_date, "Y") returns the number of complete years between two dates, correctly handling whether the birthday has occurred yet this year. Option B (subtracting YEAR values) overstates age if the birthday hasn't occurred yet this year, since it only compares calendar years, not complete elapsed years.

Question 15

A developer is working in Microsoft Excel VBA. Her macro loops through several worksheet names and performs formatting on each, but the macro should continue running smoothly even if one of the expected worksheets doesn't exist in a particular workbook, logging the issue instead of crashing. What VBA construct should she use?

Accepted Answer

On Error Resume Next tells VBA to continue execution after a runtime error instead of halting, letting the code check Err.Number immediately afterward to detect and log a specific issue (like a missing worksheet) before proceeding. On Error GoTo 0 afterward resets to default error handling for subsequent code. VBA does not support Try...Catch syntax (C), which is used in languages like C# or Java.

Question 16

You are working in Microsoft Excel VBA. You want your macro to prompt the user to type in a value (such as a discount percentage) and then use that value in a calculation. Which two statements about InputBox are correct? Choose 2.

Accepted Answer

InputBox(prompt, [title], [default]) opens a dialog box prompting the user for input, and the result can be assigned to a variable, e.g. Dim pct As String: pct = InputBox("Enter discount %"). Because InputBox returns a String by default, converting it with a function like CDbl or Val is often necessary before using it in a numeric calculation. It can be called from any procedure, not only event handlers.

Question 17

You are working in Microsoft Excel. You want to determine the optimal number of units of two products to manufacture, maximizing total profit, subject to constraints on available labor hours and raw material quantity. Which built-in tool is designed for this type of optimization?

Accepted Answer

Solver is designed for optimization problems with multiple changing cells (such as units of two products) and multiple constraints (labor hours, raw material limits). You define an objective cell to maximize (profit), the changing cells, and constraint formulas, and Solver finds the optimal combination — something Goal Seek (single target, single variable) and Scenario Manager (manually defined sets) cannot do.

Question 18

You are working in Microsoft Excel. You have a column listing sales totals by country and want a chart that shades each country on a world map according to its sales value. Which chart type should you use?

Accepted Answer

A Map chart (Insert > Charts > Maps > Filled Map) recognizes geographic names (such as countries or regions) and shades each area on a map based on the associated value, using a color gradient similar to a color scale — the appropriate chart type for visualizing sales by geography.

Question 19

You are working in Microsoft Excel. You want a single chart showing monthly Website Visits as columns and Conversion Rate as a line overlaid on the same chart, sharing the category axis but ideally each with an appropriate scale. What chart type should you create?

Accepted Answer

A Combo chart lets you assign different chart types to different series in the same chart — Clustered Column for Visits and Line for Conversion Rate — and place the line series on a secondary axis so its different scale (a percentage) doesn't get flattened by the much larger Visits values sharing a single axis.

Question 20

You are working in Microsoft Excel. You have inserted a Slicer for a PivotTable and want to change its visual appearance to match your report's color scheme, and control how many columns of buttons it displays. Which two actions should you take? Choose 2.

Accepted Answer

With a Slicer selected, the contextual Slicer (Design) tab provides a Slicer Styles gallery for color/appearance customization, and a Buttons group where you can set the number of Columns to control how the filter buttons are arranged, letting the Slicer's layout and look match your report design.

Question 21

An inventory manager is working in Microsoft Excel. His PivotTable is based on a plain cell range (A1:D500), and each week new rows are appended below row 500. Every week he must manually update the PivotTable's source data range before refreshing. How can he eliminate this manual step going forward?

Accepted Answer

An Excel Table automatically expands to include new rows added directly beneath it. If the PivotTable's source data references the Table (rather than a fixed cell range), any new appended rows become part of the Table automatically, so simply refreshing the PivotTable (Analyze > Refresh) picks up the new data without ever needing to manually edit the source range again.

Question 22

You are working in Microsoft Excel. Your PivotTable groups sales by Region and then by Salesperson within each region, and you want a blank row inserted after each region's group of salespeople for better readability when printed. What should you do?

Accepted Answer

PivotTable Design > Blank Rows > Insert Blank Line after Each Item automatically adds a blank row following each outer group (such as each Region), improving readability and print layout without manually editing the PivotTable, which would break automatically whenever the PivotTable refreshes or is rearranged.

Question 23

You are working in Microsoft Excel. You have daily closing stock prices for a year in one row per stock, and you want a compact in-cell indicator showing which days were up (green) versus down (red), without showing the magnitude of change. Which two statements about the appropriate Sparkline type are correct? Choose 2.

Accepted Answer

The Win/Loss Sparkline type plots each value as a uniform-height block above or below the axis depending on whether it's positive or negative, ignoring magnitude — ideal for showing simple up/down patterns. Sparkline Tools (Design tab) > Style lets you assign distinct colors to the 'high point,' 'low point,' or general markers, commonly used to color wins green and losses red. It works directly on raw positive/negative values without needing pre-conversion to +1/-1.

Question 24

An analyst is working in Microsoft Excel with a PivotTable showing revenue by product. He wants to add a computed measure that is available only within this specific PivotTable and does not modify the source data, versus a colleague who instead wants to add a brand-new named item within an existing field, computed from other items. Which pairing correctly matches each need to the right PivotTable feature?

Accepted Answer

A Calculated Field adds a new virtual value field (a formula combining other fields, such as Profit = Revenue - Cost) to the Values area, without altering source data — matching the analyst's need for a new computed measure. A Calculated Item instead adds a new named item within an existing field (such as adding 'Combined' as a new item within the Product field, computed from other product items) — matching the colleague's need.

Question 25

You are working in Microsoft Excel. You have a Pie chart showing market share by competitor, and you want each slice to display both its raw dollar value and its percentage of the whole. What should you do?

Accepted Answer

Chart Design > Add Chart Element > Data Labels > More Data Label Options opens a pane where multiple label content checkboxes (Value, Percentage, Category Name, etc.) can be enabled simultaneously. Checking both Value and Percentage displays each slice's raw amount and its share of the total directly on the chart.

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