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

Question 1

You are working in Microsoft Word (Office 2019), preparing a legal contract template that must not be reformatted or restructured by the paralegals who will fill it in, though they must be able to type into designated blanks. What should you configure?

Accepted Answer

Restrict Editing with 'Filling in forms' selected as the exclusive allowed editing type, then enforced with a password, locks the document's structure and fixed text while still letting users type into content controls/form fields — exactly the contract-template scenario described. Mark as Final only sets a soft, easily-removed advisory flag with no enforcement password. Macro Settings governs whether macros run, not editing permissions. Compare merges two document versions and does not apply ongoing protection.

Question 2

You are working in Microsoft Word (Office 2019). Multiple departments each keep their own custom macros, building blocks, and AutoText entries that they don't want other departments to accidentally load. You need to create a self-contained set of custom styles and building blocks that travels with only one specific document type, not the global Normal template. What should you configure?

Accepted Answer

A custom .dotx (or .dotm if macros are needed) template can store its own styles and building blocks separately from Normal.dotm, so documents based on that specific template inherit only that department's building blocks, keeping departments isolated from each other. Saving to Normal.dotm (B) makes the content globally available to every document regardless of department. Quick Parts/AutoText stored only in Normal (C) has the same global-scope problem. Manual copy-paste (D) is not a real building-block distribution mechanism and doesn't scale.

Question 3

You are working in Microsoft Word (Office 2019). You need to find every instance of the word 'colour' in a long document and replace it with 'color', but only where 'colour' appears as a whole word (not inside 'discolour' or 'colourful'). What is the correct approach?

Accepted Answer

The 'Find whole words only' option in Find and Replace's advanced search options restricts matches to the exact standalone word, preventing partial matches inside longer words like 'discolour' or 'colourful' from being altered. Replace All without that option (B) would incorrectly also change the 'colour' substring inside those longer words, corrupting them. Manual retyping (C) is inefficient and error-prone across a long document. AutoCorrect (D) applies as you type new text and does not retroactively fix existing text already in the document.

Question 4

You are working in Microsoft Word (Office 2019) on a 60-page business plan. You need to apply consistent character formatting rules across the whole document — such as ensuring every instance of a defined term is italicized — and you also want to quickly locate and jump between paragraphs formatted with a particular style, like all 'Warning' callouts. Which TWO features correctly support these two goals? Choose 2.

Accepted Answer

Find and Replace's Format option lets you search for and reformat text based on specific formatting criteria (such as finding a term and applying italics), addressing the consistent-formatting-rule goal. Advanced Find with 'Find Style' set to a specific paragraph style like 'Warning' lets you step through every paragraph using that style regardless of its text content, addressing the jump-between-styled-paragraphs goal. The Navigation Pane's Headings browse mode (B) only surfaces heading-styled paragraphs, not an arbitrary custom style like 'Warning'. Whole Page zoom (D) is a display convenience with no search capability. AutoCorrect (E) only affects newly typed text going forward, not existing formatting rules across a document.

Question 5

You are working in Microsoft Word (Office 2019) on a technical manual. You need every heading, once numbered by Word's outline numbering, to also automatically update a 'List of Sections' page that lists each heading with its page number, and this list must refresh whenever headings change. What should you insert?

Accepted Answer

A Table of Contents field (References > Table of Contents), built from the document's heading styles, automatically compiles a list of headings with accurate page numbers and can be refreshed (F9 or Update Table) whenever content or pagination changes. A manually typed list (B) has hard-coded numbers that go stale as soon as the document is edited or repaginated. Bookmarks (C) mark locations for navigation/cross-referencing but do not themselves generate a formatted, page-numbered list. A footnote (D) is a note tied to a specific point in the text, not a navigable summary list.

Question 6

You are working in Microsoft Word (Office 2019). You are creating a specialized brochure template and want a text field that a user can click and type a custom message into, with placeholder instructional text ('Click here to enter your message') that disappears once they start typing. What should you insert?

Accepted Answer

Content controls (Developer tab > Controls) such as Rich Text or Plain Text include a configurable placeholder text property that displays instructional gray text until the user clicks in and begins typing, at which point it is automatically replaced — exactly the described behavior for a fillable brochure field. A plain text box with manually typed placeholder text (B) requires users to remember to delete the instructions themselves, which is unreliable. A comment balloon (C) is a review annotation, not an interactive fillable field. A footnote (D) is unrelated to interactive form input.

Question 7

You are working in Microsoft Word (Office 2019) preparing a mail merge for a mass mailing. You need each merged letter's greeting line to read 'Dear Dr. Smith,' for recipients with a title of 'Dr.' but 'Dear Mr. Smith,' for recipients titled 'Mr.', all pulled dynamically from a single Title field in the data source, without manually editing each letter afterward. What should you insert into the merge document?

Accepted Answer

Mail Merge's Rules > If...Then...Else (which inserts an IF field) lets you build conditional logic directly into the merge document, evaluating a merge field's value per recipient and inserting different text accordingly — exactly matching a title-dependent greeting requirement. A plain «Title» field alone (B) just inserts the raw value with no conditional wording control. Bookmarks (C) mark static locations and have no per-recipient conditional capability. Maintaining separate data sources per title (D) is needless duplication when a single IF rule handles it within one merge.

Question 8

You are working in Microsoft Word (Office 2019). You have recorded a macro that reformats selected text into your company's standard heading style, and you want your colleagues to be able to run it by clicking a single custom button added to the Quick Access Toolbar, rather than opening the Macros dialog each time. What should you do?

Accepted Answer

Word Options > Quick Access Toolbar lets you browse commands filtered to 'Macros', select the specific recorded macro, and add it as a one-click button on the QAT, giving colleagues a persistent, easy way to run it without opening the Macros dialog. Footnote reference marks (B) are unrelated to running code. Quick Parts building blocks (C) store reusable content like text/graphics, not executable macros. Simply renaming a macro (D) does not make it run automatically or add a toolbar button; auto-run behavior requires naming it AutoOpen/AutoExec or using document events, which is a different mechanism than what's being asked.

Question 9

You are working in Microsoft Excel (Office 2019) on a large financial model with hundreds of interlinked formulas. Recalculating after every cell edit has made the workbook sluggish, so you want Excel to only recalculate when you explicitly press F9, while still allowing normal editing. What should you configure?

Accepted Answer

Formulas > Calculation Options > Manual switches the workbook's recalculation mode so formulas only recalculate when the user presses F9 (or via Calculate Now/Calculate Sheet), which directly addresses performance issues from constant automatic recalculation in large models. Disabling AutoSave (B) controls cloud auto-saving of the file, unrelated to formula recalculation timing. Protect Workbook (C) restricts structural changes like adding/deleting sheets, not calculation behavior. Freeze Panes (D) is a scrolling/display convenience with no effect on calculation.

Question 10

You are working in Microsoft Excel (Office 2019). You need to combine data from four regional worksheets (each with the same layout: Product in column A, Units Sold in column B) into a single summary showing total units sold per product across all four regions, without manually re-typing totals. What is the most efficient built-in feature to use?

Accepted Answer

Data > Consolidate is purpose-built for combining and summarizing data from multiple ranges or worksheets with matching layouts, using a function like Sum to total matching labels (products) automatically across all referenced ranges. Manually copying and pasting then summing (B) achieves a similar end result but is slower, more error-prone, and does not update automatically if source data changes, whereas Consolidate can optionally link to source data for updates. Find and Replace (C) searches/replaces text and cannot aggregate numeric totals. Conditional Formatting (D) only changes visual appearance and performs no aggregation.

Question 11

You are working in Microsoft Excel (Office 2019). You need a data entry cell to only accept whole numbers between 1 and 100, and if someone enters an out-of-range value, Excel should display a custom message reading 'Enter a value from 1 to 100.' What should you configure?

Accepted Answer

Data Validation with 'Whole number' and a Minimum/Maximum range actively restricts what can be entered into the cell and, combined with a custom Error Alert message on the same dialog's Error Alert tab, displays the specified message when an invalid value is attempted — directly satisfying both the restriction and custom message requirements. Conditional Formatting (B) only changes the cell's appearance after entry; it does not block invalid input or show an error message. Number formatting as Integer (C) affects display/rounding of numbers, not entry validation, and would not reject out-of-range values or non-numeric input. A comment (D) is just an informational note that does not enforce or validate anything.

Question 12

You are working in Microsoft Excel (Office 2019). You have a large product list in column A on Sheet1 and need to look up each product's price from a Prices table on Sheet2, where the lookup value could appear in any column (not necessarily the leftmost), and the returned price column could be to the left of the lookup column. Which formula approach correctly handles this?

Accepted Answer

INDEX/MATCH is not restricted by column order — MATCH locates the row position of the lookup value in the Product column, and INDEX then returns the corresponding value from the Price column regardless of whether Price is to the left or right of Product, making it the correct tool when the return column may precede the lookup column. VLOOKUP (B) can only look up values in the leftmost column of its table array and return a value from a column to its right, so it fails if the price column is to the left of the product column. HLOOKUP (C) performs a horizontal lookup across a row, not a vertical column-based lookup, and doesn't fit this scenario. LEFT (D) extracts characters from a text string and performs no lookup at all.

Question 13

You are working in Microsoft Excel (Office 2019). You need a formula that returns 'Pass' if a student's score in B2 is 60 or higher, 'Review' if it's between 40 and 59, and 'Fail' if it's below 40. What formula structure correctly implements this three-way outcome?

Accepted Answer

Nesting a second IF inside the false branch of the first IF creates a three-way decision: if the score is 60+, return 'Pass'; otherwise, check if it's 40+, returning 'Review'; otherwise, the final 'Fail' fallback applies. Standard IF only accepts three arguments (condition, true result, false result) — it has no built-in four-argument form as shown in B, which would actually produce a formula error. AND (C) returns TRUE/FALSE from evaluating multiple conditions and is not used to branch between different text outputs like this. Adding two separate IF results together (D) is invalid for concatenating text results and would produce an error or unintended behavior, and also fails to cover the 'Review' middle case at all.

Question 14

You are working in Microsoft Excel (Office 2019). You recorded a macro that formats a monthly report and saved the workbook, but the next day the macro no longer runs and you get a security warning bar instead. You need macros to keep working for this specific trusted workbook going forward without lowering security for all other workbooks. Which TWO actions correctly address this? Choose 2.

Accepted Answer

Saving as .xlsm (Macro-Enabled Workbook) is required for a workbook containing VBA macros to retain that code at all — a plain .xlsx silently strips macros on save. Adding the specific folder as a Trusted Location (or clicking Enable Content for that file) lets macros run for that trusted file/location without changing the global security posture for all other workbooks. Setting 'Enable all macros without notification' globally (C) does keep this macro working but insecurely disables macro warnings for every workbook opened on the machine, which the question explicitly says to avoid. Renaming to the legacy .xls extension (D) does not preserve macros and is not a supported way to store VBA code going forward. Deleting the macro (E) abandons automation entirely rather than fixing the trust issue.

Question 15

You are working in Microsoft Excel (Office 2019). You have a chart showing Revenue (in millions) and Growth Rate (in percent) for the same months, but plotting both on one axis makes the small percentage values invisible next to the large revenue figures. You need the Growth Rate series to display against its own separate scale on the right side of the chart. What should you configure?

Accepted Answer

Formatting the Growth Rate data series and assigning it to a Secondary Axis creates an independent right-side vertical scale suited to its smaller value range, while Revenue keeps using the primary left-side axis — allowing both series to be clearly readable on the same chart despite very different magnitudes. A Pie chart (B) cannot display two different time-series measures with different scales at all. Increasing label font size (C) doesn't fix the underlying scale problem where the line is visually flattened near zero. Deleting the Revenue series (D) removes needed data instead of solving the dual-scale display issue.

Question 16

You are working in Microsoft Excel (Office 2019). You have a PivotTable summarizing sales by region and product, and you need to visually spotlight only the interactive filtering controls for Region so users can click a button to filter the PivotTable without opening the field list dropdown. What should you insert?

Accepted Answer

A Slicer, inserted from PivotTable Analyze/Options > Insert Slicer and choosing the Region field, creates a floating panel of clickable buttons that visually and interactively filter the connected PivotTable (and optionally other PivotTables/PivotCharts) without needing to open a dropdown menu. Standard AutoFilter dropdowns (B) work differently on PivotTables and don't offer the large clickable-button interface a slicer provides. A disconnected PivotChart (C) would not filter the existing PivotTable at all unless explicitly connected. Conditional Formatting (D) only affects visual appearance and provides no filtering interactivity.

Question 17

You are working in Access 2019. Your database file has grown large after months of edits and deletions, and it now feels sluggish and takes up more disk space than the actual data should require. What should you do to reclaim space and improve performance?

Accepted Answer

Compact and Repair Database reorganizes how the file stores data, reclaiming space left behind by deleted records and objects, and can also fix minor corruption — directly addressing bloat and performance issues in a heavily-edited database. Deleting and recreating tables (B) is drastic, destroys existing data/relationships, and is not a maintenance operation. Exporting to Excel and back (C) risks losing relationships, data types, and other Access-specific object properties. Renaming the file (D) has no effect on internal file size or performance.

Question 18

You want to secure a sensitive Access database so anyone who tries to open the file is prompted for a password before Access will even display the Navigation Pane. What should you use?

Accepted Answer

Encrypt with Password (opened in Exclusive mode via File > Open with the Exclusive option, then File > Info > Encrypt with Password) sets a database-open password that Access requires before the file will open at all, protecting the entire database. Setting fields' Required property (B) only forces data entry into those fields; it has nothing to do with file-level access security. Hiding Navigation Pane objects (C) is only a display convenience and provides no real security since hidden objects can easily be shown again. Conditional Formatting (D) is a visual formatting feature unrelated to authentication.

Question 19

You need the Notes field in a Contacts table to store lengthy free-form paragraphs (potentially several thousand characters), unlike the Company field which never exceeds 50 characters. What data type should the Notes field use?

Accepted Answer

Long Text (called Memo in earlier Access versions) is designed for large amounts of free-form text, supporting far more characters than the 255-character limit of Short Text, making it appropriate for lengthy notes. Short Text (B) is capped at 255 characters and would truncate or reject lengthy paragraph content. Number (C) stores only numeric values, not text. AutoNumber (D) auto-generates sequential unique identifiers and cannot hold typed narrative text at all.

Question 20

In the Orders table, you want the ShipDate field to only allow selection from a small pop-up calendar control when users click into it, rather than free-typed text, to reduce date entry errors. What property should you set on the field?

Accepted Answer

A field set to the Date/Time data type has a Show Date Picker property (defaulting to 'For dates') that displays a small calendar icon in Datasheet/Form view, letting users click to pick a date visually instead of typing it freehand, which reduces format errors. Short Text with an input mask (B) can constrain typed characters into a date-like pattern but does not provide the calendar picker interaction and stores the value as text rather than a true date. Required (C) only forces some value to be entered, not a specific entry method. Indexed with No Duplicates (D) enforces uniqueness across records, unrelated to how dates are entered.

Question 21

At the end of each quarter, you need to permanently move all Orders older than one year from the active Orders table into an ArchiveOrders table, then remove them from Orders so the active table stays lean. Which combination of query types accomplishes this?

Accepted Answer

An Append query copies matching records into an existing target table (ArchiveOrders) without touching the source, and a separately run Delete query (using the same date criteria) then removes those now-archived rows from Orders — together implementing a true 'move' operation. A Select query with manual deletion (B) works for a small one-off case but is not a repeatable, reliable quarterly process compared to saved action queries. A Make Table query (C) creates a brand-new table from query results but does not delete anything from the source table automatically — that's a common misconception. An Update query (D) modifies field values in place; it does not move records between tables or delete them.

Question 22

You are building a query against the Orders table to give a discount department manager insight into pricing. You need the query to show OrderID, OrderAmount, and a new calculated column named Discounted that equals OrderAmount multiplied by 0.9, and results should only include orders where OrderAmount is greater than 1000. Which TWO actions correctly build this in Query Design view? Choose 2.

Accepted Answer

Typing an expression like 'Discounted: [OrderAmount]*0.9' in a blank Field cell creates a calculated field named Discounted that computes 90% of OrderAmount per row, dynamically, without altering the underlying table. Typing >1000 in the Criteria row beneath OrderAmount filters results to only orders exceeding that threshold. Typing 'Discounted' as static repeated text (C) would not calculate anything — it would just show the literal word in every row. Make Table (D) would create a new physical table rather than simply computing a query column, which is unnecessary and changes the nature of the request. Setting a Sum total (E) would summarize/aggregate the query into totals, losing the individual per-order detail rows the manager needs to see.

Question 23

On the OrderEntryForm, staff need a dropdown list bound to the CustomerID field that displays customer names (not raw ID numbers) while still storing the numeric CustomerID value behind the scenes. What control should you use?

Accepted Answer

A Combo Box can be bound to the numeric CustomerID field while its Row Source query supplies both the ID (stored/hidden bound column) and a friendly display column like CustomerName, letting users pick by name while Access stores the correct underlying ID — exactly matching the requirement. A plain Text Box bound to CustomerID (B) would just show and require typing the raw numeric ID, not a friendly name. A Label (C) is static, non-interactive text and cannot be bound to store a selected value. An Image control (D) displays pictures, not selectable list data.

Question 24

On the InvoiceSummary report, you need every group of orders for the same Customer to start printing at the top of a new page, rather than potentially splitting across the bottom of one page and the top of the next. What should you configure?

Accepted Answer

Setting the Customer group header's 'Force New Page' property to 'Before Section' in the Group, Sort, and Total pane ensures each new Customer group always begins at the top of a fresh page, preventing a group from being split across a page boundary. A manual page break after every tenth record (B) is arbitrary and unrelated to actual group boundaries, so it would not reliably align with where each customer's records start and end. Landscape orientation (C) changes page shape but does not control group pagination. Reducing font size (D) might reduce (but not guarantee elimination of) splitting and degrades readability, rather than directly controlling page-break behavior at group boundaries.

Question 25

You are finalizing the AnnualSalesReport, which is grouped by Region and then by SalesRep within each region. Leadership wants to see, at the end of each SalesRep's records, a subtotal for that rep, and at the end of each Region, a grand subtotal for the whole region, both automatically recalculating if data changes. What should you configure in the Group, Sort, and Total pane?

Accepted Answer

The Group, Sort, and Total pane supports nested (multi-level) grouping — a Region group as the outer level and a SalesRep group nested inside it — and each group level can independently have its own Sum total configured to appear in that level's group footer, giving both a per-rep subtotal and a per-region grand subtotal, both of which recalculate automatically as underlying data changes. Claiming only single-level grouping is possible (B) is incorrect; Access reports fully support nested group levels with independent totals. Manually placed static text boxes (C) would not recalculate automatically and are unreliable as data changes or record counts shift. Conditional Formatting (D) only changes visual appearance and computes no totals at all.

MO-101 / MO-201 / MO-500 — Microsoft Office Specialist: Expert (Office 2019)