Microsoft Office Specialist: Excel Expert (Office 2019) — Practice Set 1

Question 1

You have written a macro in Workbook_A.xlsm that you also need to run from Workbook_B.xlsm. You want to move the macro's code into Workbook_B without retyping it. What is the correct way to accomplish this?

Accepted Answer

In the Visual Basic Editor (Alt+F11), each open workbook appears as a separate project in the Project Explorer. Dragging a module from one project onto another copies that module's VBA code into the destination workbook, which is the supported way to copy macros between workbooks. Copying output values only copies results, not code; renaming a file does not merge macros; and Find and Replace cannot relocate VBA code between workbooks.

Question 2

A workbook contains macros, but every time you open it Excel disables them and shows a security warning bar. You trust this file and want the macros to run without being prompted every time. What should you do?

Accepted Answer

Clicking Enable Content on the Message Bar allows macros for that session, and adding the containing folder to File > Options > Trust Center > Trusted Locations removes the warning permanently for files stored there. Saving as .xlsx would strip the macros entirely since that format does not support VBA, deleting the VBA project removes the macro rather than enabling it, and AutoSave has no bearing on macro security.

Question 3

You are preparing a workbook for collaborative review. You want to prevent reviewers from being able to insert, delete, or rename worksheets, and you also want to restrict editing on the Summary worksheet to a single unlocked input range. Which two actions accomplish this? (Choose two.)

Accepted Answer

Protect Workbook (structure) prevents reviewers from adding, deleting, renaming, moving, or unhiding sheets. To restrict editing to only one range on a sheet, you first unlock that specific range's cells (all cells are locked by default) and then apply Protect Sheet, which enforces locking only on the still-locked cells. Encrypting with a password only controls who can open the file, not what they can edit once inside, and hiding the worksheet would prevent reviewers from seeing it at all rather than allowing controlled editing.

Question 4

Your workbook is shared with colleagues in Germany who need the worksheet's spell-check and formula function names to display and validate in German, while you continue working in English on the same file. What should you configure?

Accepted Answer

File > Options > Language lets each user configure their own editing, display, help, and proofing languages independently of the workbook's content — this is a per-installation Excel setting, not a workbook-embedded one, so each colleague configures German locally while others keep English. Manually translating text changes the actual data rather than the interface language, duplicating the file creates a maintenance problem with two versions, and regional date formatting alone does not affect function names or proofing language.

Question 5

Column A contains full email addresses like 'jane.doe@contoso.com', and you want column B to automatically show just the username portion 'jane.doe' as you begin typing the pattern for the first couple of rows. Which feature recognizes the pattern and completes the rest of the column?

Accepted Answer

Flash Fill (Data > Flash Fill, or Ctrl+E) detects a pattern from one or two manually entered examples and automatically fills the rest of the column to match, which is ideal for extracting or reformatting parts of text like usernames from email addresses. Fill Series extends numeric, date, or custom list patterns rather than parsing text; AutoComplete only suggests matches to existing entries typed in the same column; and Format Painter copies formatting, not data.

Question 6

You need a custom number format that displays positive numbers in blue, negative numbers in red surrounded by parentheses, and zero values as the text 'N/A'. What is the correct approach?

Accepted Answer

A custom number format supports up to four sections separated by semicolons — positive;negative;zero;text — and each section can include a color code in brackets such as [Blue] or [Red], plus literal text in quotes, letting you fully define how each category displays in one format string. Custom formats absolutely can control color, manual recoloring does not scale or update dynamically, and the built-in Accounting format does not natively show 'N/A' for zero or colorize by sign.

Question 7

You want to restrict entry in a cell so that users can only type a date that falls within the current fiscal year, and show a custom error message if they try to enter something outside that range. Which feature should you configure?

Accepted Answer

Data > Data Validation lets you restrict input using criteria such as 'Date between' two boundary dates, and the Error Alert tab lets you define a custom title and message shown when an invalid entry is attempted, actively preventing bad data entry. Conditional Formatting only changes the appearance of existing values after entry rather than restricting what can be typed, the Date number format only changes display, and Freeze Panes only affects scrolling behavior.

Question 8

A regional sales list is grouped by Region and sorted, and you want Excel to automatically insert a SUM row after each region's rows plus a grand total at the bottom, without writing formulas yourself. Which feature should you use?

Accepted Answer

Data > Subtotal automatically inserts subtotal rows at each change in a specified grouping column (such as Region) and a grand total row, using the summary function you select, and it also creates outline grouping so the detail can be collapsed. Insert > Table converts the range into a structured table but does not insert subtotal rows on its own; Consolidate combines data from multiple ranges or sheets rather than subtotaling within one sorted list; and a single AutoSum only totals the entire column once, not per region.

Question 9

You maintain a 5,000-row customer list where the same customer occasionally appears more than once because of duplicate data entry, with all fields identical for the duplicate rows. You need to permanently delete the extra duplicate rows, keeping only one instance of each customer, before sending the file to a downstream system. What should you do?

Accepted Answer

Data > Remove Duplicates lets you select which columns must match for a row to be considered a duplicate, and it permanently deletes the extra matching rows while keeping the first occurrence — exactly what's needed before sending clean data downstream. Manually deleting after filtering is slow and error-prone on 5,000 rows; Conditional Formatting only highlights duplicates visually without removing them; and sorting alone does not delete anything, it just makes duplicates easier to see.

Question 10

You want a conditional formatting rule that highlights an entire row in a sales table whenever that row's Amount (column D) exceeds the average of all Amount values in the table, and you want the rule to keep working correctly as new rows are added. What is the correct rule setup?

Accepted Answer

To highlight an entire row based on one column's value, the conditional formatting formula must use a mixed reference — a relative column reference with an absolute row-lock removed only on the row ($D2, not $D$2) — combined with AVERAGE over an absolute range, so the comparison recalculates dynamically as data changes while applying to every column in that row. Highlight Cells Rules > Greater Than requires a static typed-in value that goes stale as data changes; Top/Bottom Rules > Above Average applied only to column D would highlight only that single cell, not the whole row; and manual sorting/bolding is static and breaks as soon as new data is added.

Question 11

You need a formula that returns 'Excellent' if a score is 90 or above, 'Good' if it's between 70 and 89, and 'Needs Improvement' otherwise. Which function is the cleanest way to express this three-way tiered result in Excel 2019 without deeply nesting IF statements?

Accepted Answer

IFS() evaluates a series of condition/result pairs in order and returns the result for the first TRUE condition, making tiered logic like grade bands far more readable than nested IF() calls, and a final TRUE condition acts as a catch-all default. SWITCH() matches an expression against exact discrete values rather than ranges/conditions, so it cannot test 'greater than or equal to' bands. SUMIF() sums numeric values matching criteria, and AND() returns only TRUE/FALSE, neither of which can produce a text result like this.

Question 12

You need to sum the Amount column only for rows where Region is 'West' AND Status is 'Closed'. Which function correctly applies both conditions together?

Accepted Answer

SUMIFS() takes a sum range followed by pairs of criteria ranges and criteria, and requires ALL conditions to be true (an AND relationship) for a row to be included in the sum, which is exactly what's needed for 'Region is West AND Status is Closed'. SUMIF() only supports a single condition and its argument order does not accept two separate criteria pairs; a plain SUM() cannot apply criteria at all; and COUNTIFS() would correctly apply both conditions but returns a count of matching rows, not a sum of Amount.

Question 13

You have a product ID in cell B2 and need to retrieve the matching product name from a lookup table where the Product ID column is to the RIGHT of the Product Name column. VLOOKUP cannot look leftward. Which combination of functions correctly retrieves the value in this situation?

Accepted Answer

INDEX/MATCH is direction-independent: MATCH(B2,IDRange,0) finds the row position of B2 within the ID column, and INDEX(NameRange,...) returns the value at that same row position from the Name column, regardless of which column is physically to the left or right of the other. VLOOKUP always searches the leftmost column of its table array and cannot use a negative column index to look left, HLOOKUP is designed for horizontally-arranged data (searching across a row, not down a column), and MATCH's third argument is a match type number, not a range.

Question 14

You need a formula in cell C2 that calculates the deadline date as 10 working days after the start date in B2, automatically skipping weekends (but not accounting for holidays, which are not tracked in this workbook). Which function should you use?

Accepted Answer

WORKDAY(start_date, days) returns the date that is a given number of working days after (or before, with a negative number) the start date, automatically excluding Saturdays and Sundays, and optionally excluding a supplied holiday list. Simply adding 10 to the date (B2+10) counts calendar days including weekends, WEEKDAY() returns a number representing the day of the week for a given date rather than calculating a new date, and TODAY()+10 ignores the actual start date in B2 entirely.

Question 15

You have three separate monthly expense worksheets (Jan, Feb, Mar) with the same category layout but different values, and you want a single summary sheet that automatically sums matching categories across all three sheets. Which feature is specifically designed for this kind of multi-range aggregation?

Accepted Answer

Data > Consolidate is built specifically to combine and summarize data from multiple ranges — whether on the same sheet, different sheets, or even different workbooks — by matching row/column labels and applying a summary function like Sum, which fits combining Jan/Feb/Mar expense sheets exactly. A PivotTable from a single sheet can't pull in the other two sheets' data without first combining them; Goal Seek performs single-cell what-if analysis, not multi-sheet aggregation; and Move or Copy simply relocates or duplicates sheet tabs, it does not sum their contents.

Question 16

You know you want a loan's final balance to reach exactly $0 after 60 monthly payments, but you're not sure what the required monthly payment amount needs to be to achieve that target. Which tool directly solves for the unknown input value needed to reach a specific target result?

Accepted Answer

Goal Seek works backward from a desired result: you specify the formula cell, the target value you want it to equal (such as a $0 ending balance), and which single input cell Excel should adjust, and it iteratively solves for that input. Scenario Manager instead lets you save and switch between multiple sets of predefined input values rather than solving for an unknown; PMT() calculates a payment directly only when you already know rate, term, and present value, not when solving backward from a target balance; and Consolidate aggregates data from multiple ranges, unrelated to what-if solving.

Question 17

A formula returns #REF! and you want to visually trace which cells feed into it before you attempt to fix it. Which tool draws blue tracer arrows from the source cells directly to the formula cell?

Accepted Answer

Trace Precedents draws arrows pointing from the cells that feed INTO the selected formula, letting you visually follow which upstream cells contribute to its result — exactly what's needed before diagnosing a #REF! error. Trace Dependents does the opposite, showing which downstream formulas depend on the selected cell. The Watch Window lets you monitor specific cells' current values in a floating pane without drawing arrows, and Evaluate Formula steps through a single formula's calculation piece by piece rather than mapping cell relationships visually.

Question 18

You recorded a macro named 'FormatReport' using the Macro Recorder and now need to make two changes: rename it to 'MonthlyFormat' and tweak one line of its code to reference a different cell range. Which two actions correctly accomplish this without re-recording from scratch? (Choose two.)

Accepted Answer

Developer > Macros > Edit opens the VBA editor directly on that macro's code, where you can freely modify any line, including cell range references. To rename a macro, you edit the 'Sub FormatReport()' line itself to 'Sub MonthlyFormat()' in the VBA editor, since the Macro dialog's rename capability is limited compared to directly editing the procedure name. The Macros dialog's Options button only lets you set a shortcut key and description, not rename the macro. Deleting and starting over discards the working code unnecessarily when targeted edits would preserve it.

Question 19

You have a chart plotting monthly Revenue (in millions) and Units Sold (in the thousands) on the same chart, but Units Sold appears as a nearly flat line because Revenue's scale dwarfs it. What should you do so both series are readable?

Accepted Answer

Assigning one series to a Secondary Axis (right-click the series > Format Data Series > Series Options > Secondary Axis) gives it its own independent scale, so a series with much smaller values (like thousands of units) becomes clearly visible alongside a series with much larger values (like millions in revenue) instead of being flattened by a shared scale. A Pie chart cannot show two independent numeric series with a time trend at all, deleting the series removes needed data rather than fixing readability, and font size has no effect on axis scaling.

Question 20

You want a single chart that visually shows how a starting value increases and decreases through a series of intermediate positive and negative changes to arrive at a final total, such as a profit bridge from Revenue to Net Income. Which chart type is specifically designed for this?

Accepted Answer

A Waterfall chart is purpose-built to show a running total as values are added or subtracted in sequence, with floating bars that visually connect each step from a starting value through intermediate increases/decreases to a final result — ideal for a profit bridge. A Pie chart shows proportional parts of a single whole at one point in time, a Radar chart compares multiple variables across categories on a circular axis, and a standard Line chart shows trend over time but does not visually represent cumulative step-by-step contributions the way a waterfall does.

Question 21

You have a large PivotTable summarizing sales by Region, Product, and Salesperson, and you want to add clickable buttons that let end users quickly filter the PivotTable to show only a specific Region with a single click. Which feature should you add?

Accepted Answer

PivotTable Analyze/Insert > Slicer adds a floating, clickable button panel for a chosen field (like Region) that visually filters the PivotTable when a user clicks a value, and can also be connected to filter a linked PivotChart simultaneously — exactly the interactive one-click filtering being described. A calculated field adds a new computed value to the PivotTable's data area, not a filtering control; an empty PivotChart with no fields shows no useful visualization; and conditional formatting changes appearance based on values, it does not filter which rows display.

Question 22

In a PivotTable that has Amount in the Values area, you need to add a new field called 'Bonus' that is computed as 5% of Amount, and you want this new field to be usable inside the PivotTable like any other value field, updating automatically as the underlying data refreshes. What should you do?

Accepted Answer

PivotTable Analyze > Fields, Items & Sets > Calculated Field lets you define a new virtual field using a formula that references existing PivotTable fields (like Amount*0.05), and this new Bonus field then behaves like any other value field inside the PivotTable, recalculating automatically as the source data and PivotTable refresh. Adding a helper column to the source data works but requires modifying the raw dataset and isn't a PivotTable-native calculated field; typing a formula in an adjacent cell creates a static value outside the PivotTable structure that won't respond to filtering/grouping; and conditional formatting only changes visual appearance, it cannot generate a new calculated value.

Question 23

Your PivotTable groups order dates individually by day, producing hundreds of columns. You want the PivotTable to instead show data grouped by Quarter to make trends easier to see. What should you do?

Accepted Answer

Right-clicking a date field in a PivotTable and choosing Group lets you roll individual dates up into larger buckets like Months, Quarters, or Years, replacing the hundreds of individual day columns with a much more readable quarterly summary. Manually deleting columns would permanently remove underlying detail data rather than intelligently regrouping it, changing the PivotChart type affects only the visualization style and does not regroup the underlying PivotTable's date field, and conditional formatting only affects appearance, not grouping structure.

Question 24

You built a PivotChart from a PivotTable, and now a stakeholder wants to click on a single column representing a specific quarter to see the individual transaction rows that make up that total. Which PivotChart capability allows this?

Accepted Answer

Double-clicking a data point in a PivotChart (or the corresponding PivotTable cell) drills down and displays the individual detail records that were aggregated into that summarized value, on a new worksheet — this is the built-in 'Show Details' drill-down behavior. Applying a different chart style only changes visual formatting, adding a Slicer for an unrelated field filters data but does not reveal underlying detail rows, and alphabetical sorting reorders displayed categories without exposing the individual transactions behind any one total.

Question 25

You've created a PivotChart with a Column style showing Sales by Product Category, and a stakeholder asks you to change it so each category's bar segments are also broken down by Region within the same bar, stacked on top of each other, while keeping totals easy to compare across categories. What should you do?

Accepted Answer

Switching to a Stacked Column chart type causes each category's single bar to be subdivided into colored segments for each value in the Legend (Series) field, so adding Region there produces stacked segments within each Product Category bar while the total bar height still allows easy comparison across categories — this combines manipulating existing PivotChart options with the correct chart type. Changing only the color theme or applying a different chart style changes appearance but not the stacking structure requested, and manually drawing shapes abandons the live, data-connected PivotChart entirely, which is unnecessary and unmaintainable.

MO-201 — Microsoft Office Specialist: Excel Expert (Office 2019)