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

MOS Excel Expert (Office 2019): Manage workbook options and settings

MOS Excel Expert (Office 2019) · Work through your exam, one topic at a time.

All guide sections

Manage workbook options and settings

What Domain 1 tests

This domain covers workbook-level controls rather than cell content: calculation behavior, macro security, protection, versioning, document properties, and language/proofing settings. Questions often describe a symptom, such as slow recalculation, blocked macros, or a protected sheet, and ask which setting fixes it.

Core rules

  • Calculation mode (Formulas > Calculation Options): Automatic, Automatic Except for Data Tables, Manual. F9 recalculates all open workbooks; Shift+F9 recalculates the active sheet; Ctrl+Alt+F9 forces a full rebuild including dependencies.
  • Default font is set in File > Options > General and affects only new workbooks, not the open one.
  • Macros live in modules, not worksheets. Copy them by dragging a module in the VBA Project Explorer, right-click > Move/Copy, or export/import a .bas file. A macro-enabled workbook must be saved as .xlsm.
  • Trust Center macro settings include Disable all macros with notification (the default), Disable all except digitally signed, and Enable all. Trusted Locations bypass the prompt.
  • Protection: Protect Sheet limits cell editing; Protect Workbook limits structure (insert/delete/rename sheets) and window layout. Allow Users to Edit Ranges grants password-protected exceptions.
  • Versions: AutoRecover, Recover Unsaved Workbooks, and Version History for files on OneDrive or SharePoint.

Worked example

A model contains a 50,000-row what-if data table and recalculates after every keystroke. Choose Automatic Except for Data Tables. Formulas still update automatically, but the data table waits for F9. Choosing Manual instead makes every formula go stale and volatile functions stop refreshing until you recalculate.

Traps

  • Changing the default font does not restyle the current workbook.
  • Saving a macro workbook as .xlsx silently removes the VBA project.
  • Worksheet protection is not security and does not encrypt the file; use File > Info > Protect Workbook > Encrypt with Password for that.
  • Copying a worksheet does not copy its macros.