I85PREP
⌘ K
MOS Excel Expert (Office 2019) study materials
MOS Excel Expert (Office 2019) study resources

MOS Excel Expert (Office 2019) practice questions

One question at a time. Build your MOS Excel Expert (Office 2019) understanding.

First 50 of 200 questions. Each one lists the choices, the correct answer, and an explanation. The checker at the top of this page saves progress in this browser when it is available.

Continue in the practice tool above to load more questions, 50 at a time.

Question 1Manage workbook options and settings

A workbook must open with cell A1 of the sheet named Dashboard active, scrolled to the top-left, and with a specific zoom level. Which combination of saved workbook settings accomplishes this without VBA?

  1. Save the workbook while Dashboard is the active sheet with A1 selected, zoom set, and the view saved as part of the file Correct answer.
  2. Insert a hyperlink to the Dashboard sheet on every other worksheet
  3. Set the workbook to open in Protected View and select the sheet manually after opening
  4. Name the Dashboard sheet with a leading space so it sorts first in the tab list

Explanation

Excel stores the active sheet, the active cell and selection, the scroll position, and the zoom level of each window when the file is saved, so the file reopens exactly as it was left. No formula or macro is needed to restore this state.

Question 2Manage workbook options and settings

You need Excel to store a document property that records the project code and displays it in the Info tab of Backstage view. Where do you define this custom property?

  1. Page Layout > Themes > Custom properties
  2. Review > Protect Workbook > Add property
  3. File > Info > Properties > Advanced Properties > Custom tab Correct answer.
  4. Data > Get Data > Properties > Custom metadata

Explanation

Custom document properties are created in the Advanced Properties dialog, reached from File > Info > Properties, on the Custom tab, where you supply a name and a value. These values appear in Backstage view and can be inserted into cells with the Field command.

Question 3Manage workbook options and settings

Users report that the formula bar is hidden in Excel. Which setting location in the Excel Options dialog box lets you control its visibility?

  1. File > Options > Save > Save workbooks
  2. File > Options > Advanced > Display Correct answer.
  3. File > Options > General > Start up options
  4. View tab > Show group

Explanation

The Display section of Advanced options contains the Show formula bar checkbox. Selecting it displays the formula bar, and clearing it hides the bar. This is an application-level setting. Ribbon visibility is controlled separately through Ribbon Display Options or the ribbon collapse shortcut.

Question 4Manage workbook options and settings

To keep a workbook from storing the personal information that appears in file properties and the document inspector, which option do you clear?

  1. Embed fonts in the file
  2. Remove personal information from file properties on save Correct answer.
  3. Always create backup copy
  4. Save AutoRecover information every 10 minutes

Explanation

Clearing the option to remove personal information from file properties on save causes Excel to strip author names and related metadata when the file is saved. It sits in the Save category of Excel Options next to the AutoRecover and backup settings.

Question 5Manage workbook options and settings

A workbook contains a macro that must run automatically when the file is opened. Which file type should the workbook use so the macro is retained?

  1. Excel Macro-Enabled Workbook (.xlsm) Correct answer.
  2. Excel Workbook (.xlsx)
  3. Excel Template (.xltx)
  4. Strict Open XML Spreadsheet (.xlsx)

Explanation

Macros are stored only in macro-enabled formats such as .xlsm, .xlsb, and the legacy .xls; the standard .xlsx formats strip VBA code on save. Saving as a macro-enabled workbook preserves the code and allows it to run subject to Trust Center macro settings.

Question 6Manage workbook options and settings

A worksheet is protected and you want users to be able to select and sort data in unlocked ranges while preventing formatting changes. Which dialog provides these granular controls?

  1. Home > Format > Protect Sheet from the Cells group
  2. Review > Protect Sheet and its allowed-actions list Correct answer.
  3. File > Info > Protect Workbook > Encrypt with Password
  4. Review > Protect Workbook with the Windows option

Explanation

The Protect Sheet dialog includes a list of allowed actions, such as sorting, filtering, and formatting, that remain available to users while the sheet is locked. Cell-level permissions come from the locked and unlocked states set in the Format Cells dialog.

Question 7Manage workbook options and settings

You want every new workbook created on this computer to use Calibri 11 for the Normal style and a specific default number of sheets. Where are these defaults configured?

  1. File > Options > Formulas > Working with formulas
  2. Page Layout > Themes > Fonts
  3. File > Options > General, under When creating new workbooks Correct answer.
  4. Home > Cell Styles > Modify

Explanation

The General category of Excel Options contains the settings for the default font and size and for the number of sheets included in new workbooks. These defaults apply to workbooks created afterward on that installation.

Question 8Manage workbook options and settings

A workbook must be distributed so that recipients cannot change its structure but can still open it without a password. Which protection approach meets this requirement?

  1. Encrypt the file with a password and share the password separately
  2. Set the workbook to read-only recommended
  3. Mark the file as Final in Backstage view
  4. Apply Protect Workbook with the Structure option and a password withheld from recipients, without setting a password to open the file Correct answer.

Explanation

Protect Workbook with Structure protection restricts inserting, deleting, renaming, moving, hiding, or unhiding sheets. Its password is required to remove that protection, not to open the workbook. Withhold the structure password from recipients and leave the file-opening password unset. Worksheet protection controls cell editing separately.

Question 9Manage workbook options and settings

You need Excel to recalculate a workbook only when you press F9, but formulas must still update whenever the file is opened. Which setting achieves this?

  1. Enable multi-threaded calculation with one thread
  2. Set Calculation Options to Manual and leave the recalculation-on-open behavior unchanged Correct answer.
  3. Set Calculation Options to Automatic and disable iterative calculation
  4. Set Calculation Options to Manual and enable Recalculate workbook before saving

Explanation

Manual calculation stops automatic recalculation during editing, but Excel still performs a full calculation when the workbook is opened unless that behavior is disabled. Saving the file also forces a calculation, so formulas remain current on disk.

Question 10Manage workbook options and settings

A workbook will be shared with users who have older versions of Excel, and you want to confirm which features will be lost. Which built-in tool should you run?

  1. Accessibility Checker
  2. Document Inspector
  3. Check Compatibility Correct answer.
  4. Trust Center add-in diagnostics

Explanation

The Check Compatibility command on the File > Info tab lists features in the workbook that are not supported by earlier Excel versions and identifies the affected locations. It is the standard way to audit a file before saving to a legacy format.

Question 11Manage workbook options and settings

You must prevent users from editing a specific range of cells on an otherwise editable sheet. What is the correct sequence?

  1. Apply a data validation rule that blocks entry to the cells
  2. Unlock all cells, then lock only the target range, then protect the sheet Correct answer.
  3. Protect the sheet, then lock the target range
  4. Lock all cells and protect the workbook structure

Explanation

Cell locking takes effect only after sheet protection is enabled, and every cell is locked by default. To restrict one range, you unlock the cells users may edit, keep the target range locked, and then apply Protect Sheet.

Question 12Manage workbook options and settings

You want to save a workbook so that each time it is opened it creates a fresh copy with a date-based name. Which file type supports this behavior?

  1. Excel 97-2003 Workbook (.xls)
  2. Excel Template (.xltx) Correct answer.
  3. Excel Binary Workbook (.xlsb)
  4. Excel Add-in (.xlam)

Explanation

Opening a template creates a new unsaved workbook based on it, leaving the template file unchanged, which suits recurring forms and reports. Add-ins and regular workbook formats open the file itself for editing.

Question 13Manage workbook options and settings

A user needs to add a trusted location so that macro-enabled workbooks in a network folder open without the security warning. Which path configures this?

  1. Developer > Macro Security > Digital Signatures
  2. File > Options > Save > AutoRecover file location
  3. File > Info > Protect Workbook > Restrict Access
  4. File > Options > Trust Center > Trust Center Settings > Trusted Locations Correct answer.

Explanation

Trusted Locations are managed in the Trust Center settings, where you can add a folder path and optionally include subfolders. Files opened from a trusted location bypass the macro warning because the location is treated as safe.

Question 14Manage workbook options and settings

You need to inspect a workbook before publishing it to find hidden worksheets, comments, and personal information. Which feature performs this audit?

  1. Protect Workbook with a password
  2. Show Formulas on the Formulas tab
  3. Check Compatibility in the Info tab
  4. Inspect Document in the Document Inspector Correct answer.

Explanation

The Document Inspector scans for hidden rows and columns, hidden worksheets, comments, document properties, and other metadata, and lets you remove what it finds. It is run from File > Info > Check for Issues.

Question 15Manage workbook options and settings

A workbook contains links to an external file that is no longer available, and you want to break the links while keeping the last calculated values. Which command do you use?

  1. Data > Queries & Connections > Edit Links > Break Link Correct answer.
  2. Data > Refresh All
  3. Formulas > Calculation Options > Manual
  4. Review > Protect Workbook > Structure

Explanation

The Edit Links dialog lists external sources and provides Break Link, which converts the linked formulas to their current values and removes the dependency. The source list is reached from the Queries & Connections group on the Data tab.

Question 16Manage workbook options and settings

You want a workbook to open in a specific folder each time so users can quickly find related files. Which setting controls this?

  1. File > Options > Advanced > Display recent documents
  2. File > Options > Save > Save files in this format
  3. File > Options > General > Default view for new sheets
  4. File > Options > Save > Default local file location Correct answer.

Explanation

The default local file location on the Save tab sets the folder that the Open and Save As dialogs use first. It is an application setting, so it applies to all workbooks opened on that installation.

Question 17Manage workbook options and settings

You need to record a sequence of formatting steps once and replay it on many workbooks. Which approach stores the steps without writing VBA?

  1. Add the steps to the Quick Access Toolbar as separate buttons
  2. Create a custom cell style in the current workbook
  3. Save the workbook as an Excel Template
  4. Record a macro into the Personal Macro Workbook Correct answer.

Explanation

The macro recorder captures keystrokes and commands as VBA and can store them in the Personal Macro Workbook so they are available in every session. Saving to that workbook makes the recorded macro global rather than tied to one file.

Question 18Manage workbook options and settings

You want to add a custom group to the ribbon containing your most-used commands for all workbooks on this computer. Which approach is supported in Excel 2019?

  1. Customize the Ribbon in Excel Options and add a new group to an existing tab Correct answer.
  2. Edit the Normal template's ribbon XML directly from the Backstage view
  3. Enable the Developer tab and record a macro that builds the ribbon
  4. Use the Quick Access Toolbar import/export to create a ribbon tab

Explanation

The Customize Ribbon category of Excel Options lets you create new groups on built-in or custom tabs and populate them with commands, and the layout can be exported and imported. Ribbon XML editing requires a custom add-in file, not a direct Backstage edit.

Question 19Manage workbook options and settings

A workbook opens with a security warning that macros are disabled even though the file is signed. Which action resolves the warning while keeping macro security enabled?

  1. Lower the macro setting to Enable all macros
  2. Move the file to the Desktop
  3. Add the signer's certificate to the Trusted Publishers list Correct answer.
  4. Disable Protected View for all files

Explanation

When a macro is signed by a trusted publisher, Excel runs it without prompting, provided the certificate is in the Trusted Publishers store. This preserves the security model while allowing the signed code to run.

Question 20Manage workbook options and settings

A workbook with manual calculation and many volatile formulas opens slowly. Which change most directly reduces the recalculation performed at open time while keeping results accurate on disk?

  1. Disable multi-threaded calculation
  2. Save the file after a full calculation and keep manual calculation enabled Correct answer.
  3. Set the workbook to recalculate before saving and disable calculation on open
  4. Convert formulas to values before every save

Explanation

Saving a manually calculated workbook forces a full calculation, so the stored values are current, and the file can open without recalculating if calculation on open is not required. This balances accuracy on disk with faster opening.

Question 21Manage workbook options and settings

Two users edit a workbook stored on a network share, and you want to know which changes were made without enabling co-authoring. Which feature should be enabled before sharing?

  1. Mark the file as Final
  2. Track Changes in the workbook Correct answer.
  3. Encrypt the workbook with a password
  4. Protect Workbook with the Structure option

Explanation

Track Changes records insertions, deletions, and formatting edits along with the user and time, and it can be reviewed or accepted later. It is designed for shared workbooks where an audit trail of edits is needed.

Question 22Manage workbook options and settings

A workbook must be delivered to a client who should see values but never the formulas, and the file must remain a valid Excel workbook. Which combination achieves this?

  1. Convert formulas to values and keep the formulas in a hidden sheet
  2. Save as .xlsb and lock the VBA project
  3. Hide formulas with the Hidden attribute, protect the sheet, and save as .xlsx Correct answer.
  4. Apply Protect Workbook with a password and mark the file as Final

Explanation

Marking cells as Hidden in Format Cells conceals formulas in the formula bar only when the sheet is protected, and the standard .xlsx format keeps the file usable in Excel. This hides formula logic while leaving calculated values visible.

Question 23Manage workbook options and settings

A user reports that AutoRecover files are not being created for a large workbook. Which setting should be verified first?

  1. The Save AutoRecover information every check box and interval on the Save tab Correct answer.
  2. The Show formula bar option on the Advanced tab
  3. The Enable fill handle option on the Advanced tab
  4. The default font size on the General tab

Explanation

AutoRecover depends on the Save AutoRecover information every option being enabled with a nonzero interval, both found on the Save tab of Excel Options. If it is cleared or the interval is too long, recovery copies are not written as expected.

Question 24Manage workbook options and settings

You want to distribute a workbook so it opens with a specific sheet visible and all other sheets hidden, yet users can unhide them if authorized. Which approach fits?

  1. Move the other sheets to a new workbook and link them externally
  2. Set the other sheets' tab color to white
  3. Delete the other sheets before distribution
  4. Hide the other sheets and protect the workbook structure with a password Correct answer.

Explanation

Hiding worksheets removes their tabs, and protecting the workbook structure prevents users from unhiding them without the password. Users with the password can unprotect the structure and restore the sheets, which matches the authorized-access requirement.

Question 25Manage workbook options and settings

You want Excel to display the full path of the current workbook in the title bar so that you can confirm which copy of a similarly named file is open. Which option in the Excel Options dialog box controls this?

  1. Trust Center, under the Protected View section
  2. General, under the When creating new workbooks section
  3. Save, under the Save workbooks section
  4. Advanced, under the Display section, Show full path in title bar Correct answer.

Explanation

The Show full path in title bar check box lives in the Display group of the Advanced category in Excel Options. When enabled, the title bar shows the complete folder path plus the file name instead of only the file name, which disambiguates identically named workbooks. The other categories listed contain unrelated creation, saving, and security settings.

Question 26Manage workbook options and settings

A workbook you maintain opens with a yellow security bar warning that active content has been blocked. You have verified the file is safe and want to allow its macros for this session only, without changing any Trust Center settings. Which action accomplishes this?

  1. Choose File > Info > Enable Content > Advanced Options, select Enable content for this session, and click OK Correct answer.
  2. Unblock the file in its file properties dialog box
  3. Change the Macro Settings to Disable all macros with notification
  4. Add the file's folder as a trusted location

Explanation

Use Advanced Options under File > Info > Enable Content, then select Enable content for this session in the security dialog. This grants temporary permission until the workbook closes. The ordinary message-bar Enable Content action can make the document trusted for later openings, so it does not guarantee session-only permission.

Question 27Manage workbook options and settings

You need to change the default number of worksheets that appear when a new blank workbook is created on your computer. Which Excel Options category contains this setting?

  1. Formulas
  2. Add-ins
  3. Proofing
  4. General Correct answer.

Explanation

The setting labeled Include this many sheets appears in the When creating new workbooks section of the General category in Excel Options. Changing it affects new workbooks created afterward, not workbooks that already exist. The Formulas, Proofing, and Add-ins categories hold calculation, autocorrect, and add-in management options respectively.

Question 28Manage workbook options and settings

A colleague sends a workbook that references a data source on a network share. Each time you open it, Excel asks whether to update links. You want to stop the prompt for this workbook and keep the existing cached values. Which change should you make?

  1. Use Edit Links and choose Break Link Correct answer.
  2. Mark the workbook as Final
  3. Set the workbook calculation mode to Manual
  4. In Excel Options, clear the Ask to update automatic links check box

Explanation

Breaking the link converts the external references to their last stored values and removes the link, so no update prompt appears and cached data remains. This is a per-link action performed in the Edit Links dialog box. Calculation mode, a global prompt setting, and marking the file final do not remove the external reference itself.

Question 29Manage workbook options and settings

You are configuring Excel so that a specific add-in loads automatically every time Excel starts, but you do not want it to slow the initial startup of every session. Which load behavior in the Add-ins management area should you choose?

  1. Load at startup
  2. Disabled item
  3. Load on demand Correct answer.
  4. Not loaded

Explanation

The Load on demand behavior registers the add-in but defers loading it until a command that requires it is used, which avoids startup cost. Load at startup makes the add-in available immediately but adds to launch time. Not loaded and disabled entries leave the add-in unavailable until someone changes the setting.

Question 30Manage workbook options and settings

You want to confirm which add-ins are installed, their publisher, and their file location before deciding whether to keep them enabled. Which Excel Options category should you open?

  1. Add-ins Correct answer.
  2. Advanced
  3. Trust Center
  4. Customize Ribbon

Explanation

The Add-ins category lists active and inactive add-ins with their name, location, and type, and provides the Manage drop-down for changing load behavior. It is the central place to review what is installed before enabling or removing anything. Trust Center covers security, while Customize Ribbon and Advanced cover interface and behavior settings.

Question 31Manage workbook options and settings

A workbook must be saved so that it opens in a read-only state that anyone can remove by choosing Edit Anyway, discouraging accidental edits without locking the file with a password. Which approach produces this behavior?

  1. Save the workbook as Read-Only Recommended Correct answer.
  2. Set the file attribute to read-only in Windows Explorer
  3. Encrypt the workbook with a password to open
  4. Apply the Mark as Final command to the workbook

Explanation

The Read-Only Recommended option in the General Options area of the Save As dialog box prompts users with a recommendation to open read-only, and they can decline by choosing to edit. Mark as Final is similar in spirit but is a status marker rather than an open prompt. Password encryption and file system attributes enforce restrictions rather than merely recommending them.

Question 32Manage workbook options and settings

You are preparing a workbook for distribution and want to remove document properties, personal information in comments, and hidden rows before sending it. Which Excel feature performs this cleanup and produces a report of what was removed?

  1. Accessibility Checker
  2. Document Inspector Correct answer.
  3. Check Compatibility
  4. Protect Workbook

Explanation

Document Inspector scans for hidden rows and columns, comments, document properties, and other personal or hidden content, lets you choose what to remove, and reports the results. It is the standard pre-distribution cleanup tool. Check Compatibility lists version-specific feature loss, Protect Workbook restricts structure changes, and Accessibility Checker flags content that is hard for assistive technology to read.

Question 33Manage workbook options and settings

A workbook contains a custom view that stores a specific print area, hidden rows, and column widths. After you change the print area, you want the stored view to reflect the new setting. What must you do?

  1. Convert the workbook to the binary .xlsb format
  2. Recreate the custom view after making the changes Correct answer.
  3. Save the workbook; custom views update automatically
  4. Protect the workbook before saving

Explanation

A custom view is a snapshot of print and display settings taken when it is created, so later changes are not captured. To store the new print area you must delete and recreate the view with the desired settings in place. Saving, protecting, or changing file format does not refresh the stored snapshot.

Question 34Manage workbook options and settings

You need to change the default file format Excel uses when saving new workbooks so that files are compatible with an older Excel version without prompting each time. Where is this configured?

  1. Excel Options, Advanced, When calculating this workbook
  2. Excel Options, Save, Save files in this format Correct answer.
  3. File, Export, Change File Type
  4. File, Info, Protect Workbook

Explanation

The Save category of Excel Options includes the Save files in this format drop-down, which sets the default format used by the Save command. Setting it to the older format avoids per-save prompts about compatibility. Info and Export areas offer one-time conversions, and the Advanced calculation section is unrelated.

Question 35Manage workbook options and settings

A workbook contains a formula that references a defined name pointing to an external workbook. You want to see every external reference in the file, including those embedded in defined names, before deciding which to keep. Which approach reveals them most completely?

  1. Use the Check Compatibility command
  2. Review the Formulas, Name Manager entries and the Edit Links dialog box Correct answer.
  3. Run the Accessibility Checker
  4. Open the workbook in Protected View

Explanation

External references can hide in defined names as well as in cell formulas, so Name Manager and the Edit Links dialog box together give a complete picture. Name Manager shows the Refers To text for each name, while Edit Links lists the source workbooks and their status. The other tools address accessibility, sandboxed viewing, and version compatibility rather than link inventory.

Question 36Manage workbook options and settings

You want a workbook to open with a specific zoom level and with the formula bar hidden for a presentation, while other workbooks keep normal settings. Which action applies these display options only to that workbook?

  1. Change the zoom and formula bar visibility, then save the workbook Correct answer.
  2. Apply a custom view and set it as the default for all workbooks
  3. Change the settings in Excel Options, Advanced, Display
  4. Change the settings in Excel Options, General

Explanation

Zoom level and formula bar visibility are saved as part of the workbook's own view settings, so adjusting them and saving stores them in that file only. Excel Options display settings apply globally to the application. A custom view applies only when explicitly shown and is not a global default.

Question 37Manage and format data

A worksheet column contains order dates entered as text in the format 2024-03-15. You need real date serial values so you can sort chronologically and use date functions. Which approach converts the text to true dates while keeping the original day-month-year order?

  1. Use Data > Text to Columns with Delimited, choose the hyphen as delimiter, then set the destination column format to Date (YMD). Correct answer.
  2. Wrap each cell in the VALUE function and then apply the Long Date format.
  3. Apply the Date number format from the Number Format dropdown to the existing text cells.
  4. Run Data > Flash Fill after typing one reformatted date in the adjacent column.

Explanation

Text to Columns with a hyphen delimiter splits each string into year, month and day parts and lets you assign a Date column format in the final step, producing genuine serial values. Changing number format alone never converts text, and Flash Fill only copies a text pattern.

Question 38Manage and format data

You paste a list of 500 customer records and several cells in the Phone column show a small green triangle with the message 'The number in this cell is formatted as text'. What is the most direct way to convert the whole column to numbers in place?

  1. Select the column and use Home > Clear > Clear Formats.
  2. Select the column and choose Data > Remove Duplicates.
  3. Select the column and choose Data > Text to Columns, then click Finish without changing any delimiter settings. Correct answer.
  4. Select the column and press Ctrl+Shift+1 to apply the comma number format.

Explanation

Running Text to Columns and immediately finishing re-parses each cell using the General format, which coerces numeric-looking text into real numbers in the same cells. Formatting commands and clearing formats do not change the stored data type.

Question 39Manage and format data

You want to remove every row whose Status cell is blank, keeping the rest of the table intact. Which method accomplishes this cleanly?

  1. Sort the Status column A to Z, then delete the rows at the bottom of the column.
  2. Apply a filter on Status, then use Data > Remove Duplicates on the Status column.
  3. Apply a filter on the Status column, clear the (Blanks) checkbox, then delete the visible rows. Correct answer.
  4. Use Home > Find & Select > Go To Special > Blanks and press Delete on the keyboard.

Explanation

Filtering out blanks leaves only the rows you want to keep visible, and deleting those visible rows removes the unwanted records while preserving the remainder. Go To Special selects blank cells themselves, not their entire rows.

Question 40Manage and format data

A Price column shows values such as 12.5, 8 and 100 with no consistent decimal places. You need every value to display exactly two decimal places without altering the stored precision. What should you do?

  1. Use Home > Decrease Decimal until each cell shows two decimals.
  2. Apply the Number format with two decimal places from the Number Format dropdown. Correct answer.
  3. Multiply the range by 1 and apply the General format.
  4. Use the ROUND function with 2 as the num_digits argument on every cell.

Explanation

The Number format controls only how the value is displayed, so setting two decimal places shows 12.50 and 8.00 while the stored value keeps full precision. Rounding functions and manual decimal buttons change or misalign the displayed precision.

Question 41Manage and format data

You need cells containing negative currency amounts to display in red with parentheses, such as (1,250.00), while positive amounts stay plain. Which feature applies this consistently across the selected range?

  1. The Accounting number format with the symbol set to none.
  2. Conditional formatting with a rule that sets the font color to red for values below zero.
  3. A custom number format that includes a negative section with red color and parentheses. Correct answer.
  4. Cell styles from the Good, Bad and Neutral category.

Explanation

Custom number formats can define separate positive, negative, zero and text sections, and the negative section can specify red color and parentheses so the display changes automatically. Conditional formatting changes appearance but not the underlying number format syntax.

Question 42Manage and format data

A Date column must display dates as '15-Mar-2024'. Which custom number format code produces this result for a true date value?

  1. yyyy-mm-dd
  2. mm-dd-yyyy
  3. d-mmmm-yy
  4. dd-mmm-yyyy Correct answer.

Explanation

In custom date codes, dd gives a two-digit day, mmm gives the three-letter month abbreviation and yyyy gives the four-digit year, so dd-mmm-yyyy yields 15-Mar-2024. The other codes produce different day, month or year representations.

Question 43Manage and format data

You want to highlight entire rows where the value in column D exceeds 5000, with the highlight spanning columns A through F. Which conditional formatting setup is correct?

  1. Select D2:D100, create a formula rule =D2>5000, and apply a fill color.
  2. Select A2:F100, create a cell value rule greater than 5000, and apply a fill color.
  3. Select D2:D100, create a cell value rule greater than 5000, and copy the format to A:F.
  4. Select A2:F100, create a formula rule =$D2>5000, and apply a fill color. Correct answer.

Explanation

A formula rule evaluated over the whole block with a mixed reference $D2 locks the column to D while letting the row adjust, so each row is tested against its own D value and the fill spans A:F. Cell value rules test the selected cells themselves rather than a separate column.

Question 44Manage and format data

You need to flag duplicate invoice numbers across a long list, highlighting only the repeated entries. Which built-in conditional formatting rule handles this without a helper column?

  1. Highlight Cells Rules > Duplicate Values applied to the invoice number range. Correct answer.
  2. Top/Bottom Rules > Above Average applied to the invoice number range.
  3. Highlight Cells Rules > Text that Contains with the invoice prefix.
  4. Data Bars applied to the invoice number range.

Explanation

The Duplicate Values rule compares each cell against the rest of the selected range and formats the repeats, requiring no helper column. The other rules evaluate magnitude, averages or text patterns rather than uniqueness.

Question 45Manage and format data

A conditional formatting rule you applied earlier no longer seems to take effect on some cells that now meet the condition. Other rules exist on the same range. What is the most likely explanation?

  1. The workbook calculation mode was set to Manual.
  2. The cells contain formulas rather than constants.
  3. The cells were formatted with a table style that overrides all conditional formats.
  4. Rule precedence: a higher-priority rule with Stop If True is preventing lower rules from applying. Correct answer.

Explanation

Conditional formats are evaluated in the order shown in Manage Rules, and a rule marked Stop If True halts evaluation so lower-priority rules never apply. Reordering rules or clearing Stop If True restores the intended behavior.

Question 46Manage and format data

You need to apply the same currency format, header fill and column widths from one worksheet to several others that have identical structure. What is the most efficient built-in approach?

  1. Use Data > Consolidate across the worksheets.
  2. Apply the General format to all sheets and let Excel infer the styling.
  3. Retype the formats on each sheet manually to avoid errors.
  4. Copy the formatted sheet, then use Paste Special > Formats onto the target ranges, or group the sheets and format once. Correct answer.

Explanation

Grouping worksheets lets a single formatting action apply to every selected sheet, and Paste Special > Formats copies formatting without overwriting data. Both preserve consistency without retyping.

Question 47Manage and format data

A dataset has trailing spaces in a Name column, causing lookups to fail. Which sequence removes the extra spaces so the values match?

  1. Apply the Trim option in Data > Text to Columns on the Name column.
  2. Use the TRIM function in a helper column, then copy the results and paste as values over the originals. Correct answer.
  3. Apply the Text format to the column and re-enter the names.
  4. Use Find and Replace with a space in Find what and nothing in Replace, then repeat until no matches remain.

Explanation

TRIM removes leading, trailing and repeated internal spaces, and pasting the results as values replaces the originals with clean text that lookups can match. Find and Replace on a single space would also delete legitimate internal spaces between words.

Question 48Manage and format data

Two columns hold first and last names that you must combine into a single 'Last, First' column, and the source columns should then be removed. Which approach produces a clean result?

  1. Use Data > Text to Columns to join the two columns.
  2. Use a formula such as =B2&", "&A2, fill down, copy the results and Paste Special > Values, then delete the source columns. Correct answer.
  3. Use Home > Format > AutoFit Column Width on both columns.
  4. Use Data > Merge Cells on the two columns, then delete the originals.

Explanation

Concatenation with a comma and space builds the combined string, and pasting as values converts formulas to static text before the source columns are deleted. This avoids broken references once the originals are removed.

Question 49Manage and format data

You have a list of full names in one column and need to split them into separate First and Last columns at the space. Which tool performs this in place?

  1. Home > Wrap Text on the name column.
  2. Data > Group on the name column.
  3. Data > Flash Fill after typing the first name in one example cell.
  4. Data > Text to Columns with Delimited selected and Space as the delimiter. Correct answer.

Explanation

Text to Columns with a space delimiter splits each full name into adjacent columns at the space, producing separate first and last name fields. Flash Fill can also split names but relies on pattern inference rather than a defined delimiter.

Question 50Manage and format data

You must validate that entries in a Zip column are exactly five digits. The column is formatted as Text, and a named range of valid zips contains only five-digit codes stored as text with leading zeros preserved. Which Data Validation setup enforces this?

  1. Allow List with a named range of valid zips. Correct answer.
  2. Allow Text Length, equal to 5, applied to the Zip range.
  3. Allow Whole Number between 0 and 99999.
  4. Allow Date greater than today.

Explanation

List validation accepts only entries contained in the named range. Because that range contains five-digit ZIP codes stored as text, accepted entries have exactly five digits, including any leading zeros. Text Length equal to 5 checks character count but would also accept letters such as ABCDE.