Microsoft Excel Expert (MOS) Practice Exam
Exam Coverage — 10 Most Tested Areas
1. Manage Workbook Options and Settings — Template creation (.xltx),
workbook protection, collaboration preparation, language and regional
options, external data references
2. Manage and Format Data — Data validation (custom formulas, lists, date
restrictions), custom number formats, advanced conditional formatting,
filtering, data filling techniques, remove duplicates
3. Create Advanced Formulas — Nested logical functions (IF, AND, OR, IFS,
SWITCH), lookup functions (VLOOKUP, HLOOKUP, INDEX/MATCH, XLOOKUP),
error handling (IFERROR, IFNA), date/time functions (WORKDAY,
NETWORKDAYS, EOMONTH), array formulas
4. Create and Manage Macros — Recording macros, VBA basics, macro security
settings, assigning macros to buttons, workbook-level automation, personal
macro workbook
, 5. Manage Advanced Charts — Dual-axis charts, combination charts, trendlines,
waterfall charts, sparklines, custom chart templates, dynamic chart ranges
6. Create and Modify PivotTables — Advanced PivotTable features, calculated
fields, grouping, slicers, timelines, GETPIVOTDATA function, PivotTable source
management
7. Create and Modify PivotCharts — PivotChart formatting, filtering with
PivotCharts, connecting slicers to multiple PivotTables, chart type changes
8. Perform Data Analysis — What-If tools (Goal Seek, Solver, Scenario Manager,
Data Tables), consolidation across worksheets, forecast sheets, descriptive
statistics
9. Troubleshoot Formulas — Evaluate Formula tool, Trace
Precedents/Dependents, Watch Window, error checking, formula auditing,
circular reference resolution
10. Prepare Workbooks for Distribution — Inspect Document, Mark as Final,
password protection, restrict editing, digital signatures, workbook metadata
management
,Microsoft Excel Expert (MOS) Practice Exam
Question 1
Which function would you use to calculate the net working days between two
dates while excluding a custom list of company holidays specified in a separate
range?
A) =NETWORKDAYS(StartDate, EndDate)
B) =WORKDAY(StartDate, Days, Holidays)
C) =NETWORKDAYS(StartDate, EndDate, Holidays)
D) =WORKDAY(StartDate, EndDate, Holidays)
Correct Answer: C
Rationale: The NETWORKDAYS function calculates the number of whole working
days between two dates while excluding weekends and any dates identified in
the optional Holidays argument 2 8 . Option A omits the holiday exclusion,
Option B returns a future date rather than a count, and Option D incorrectly uses
WORKDAY which returns a serial date, not a day count.
, Question 2
When creating a custom business template that must be available every time you
open the New File dialog, where should you save the workbook file?
A) In the default template location with an .xltx extension
B) In any folder with a standard .xlsx extension
C) In the Documents folder with a .csv extension
D) In the Program Files directory with an .xlsb extension
Correct Answer: A
Rationale: Saving the workbook as an Excel Template (.xltx) in the default
template folder makes it appear in the New File dialog under Personal
Templates 6 9 . Standard .xlsx files do not appear as templates, and .csv files
lose formatting and formulas entirely.
Exam Coverage — 10 Most Tested Areas
1. Manage Workbook Options and Settings — Template creation (.xltx),
workbook protection, collaboration preparation, language and regional
options, external data references
2. Manage and Format Data — Data validation (custom formulas, lists, date
restrictions), custom number formats, advanced conditional formatting,
filtering, data filling techniques, remove duplicates
3. Create Advanced Formulas — Nested logical functions (IF, AND, OR, IFS,
SWITCH), lookup functions (VLOOKUP, HLOOKUP, INDEX/MATCH, XLOOKUP),
error handling (IFERROR, IFNA), date/time functions (WORKDAY,
NETWORKDAYS, EOMONTH), array formulas
4. Create and Manage Macros — Recording macros, VBA basics, macro security
settings, assigning macros to buttons, workbook-level automation, personal
macro workbook
, 5. Manage Advanced Charts — Dual-axis charts, combination charts, trendlines,
waterfall charts, sparklines, custom chart templates, dynamic chart ranges
6. Create and Modify PivotTables — Advanced PivotTable features, calculated
fields, grouping, slicers, timelines, GETPIVOTDATA function, PivotTable source
management
7. Create and Modify PivotCharts — PivotChart formatting, filtering with
PivotCharts, connecting slicers to multiple PivotTables, chart type changes
8. Perform Data Analysis — What-If tools (Goal Seek, Solver, Scenario Manager,
Data Tables), consolidation across worksheets, forecast sheets, descriptive
statistics
9. Troubleshoot Formulas — Evaluate Formula tool, Trace
Precedents/Dependents, Watch Window, error checking, formula auditing,
circular reference resolution
10. Prepare Workbooks for Distribution — Inspect Document, Mark as Final,
password protection, restrict editing, digital signatures, workbook metadata
management
,Microsoft Excel Expert (MOS) Practice Exam
Question 1
Which function would you use to calculate the net working days between two
dates while excluding a custom list of company holidays specified in a separate
range?
A) =NETWORKDAYS(StartDate, EndDate)
B) =WORKDAY(StartDate, Days, Holidays)
C) =NETWORKDAYS(StartDate, EndDate, Holidays)
D) =WORKDAY(StartDate, EndDate, Holidays)
Correct Answer: C
Rationale: The NETWORKDAYS function calculates the number of whole working
days between two dates while excluding weekends and any dates identified in
the optional Holidays argument 2 8 . Option A omits the holiday exclusion,
Option B returns a future date rather than a count, and Option D incorrectly uses
WORKDAY which returns a serial date, not a day count.
, Question 2
When creating a custom business template that must be available every time you
open the New File dialog, where should you save the workbook file?
A) In the default template location with an .xltx extension
B) In any folder with a standard .xlsx extension
C) In the Documents folder with a .csv extension
D) In the Program Files directory with an .xlsb extension
Correct Answer: A
Rationale: Saving the workbook as an Excel Template (.xltx) in the default
template folder makes it appear in the New File dialog under Personal
Templates 6 9 . Standard .xlsx files do not appear as templates, and .csv files
lose formatting and formulas entirely.
