MOS Excel Expert (Office 2019) common mistakes
MOS Excel Expert (Office 2019) · Understand the traps and how to avoid them.
Editing the workbook directly while recording a macro, so the macro records cell references and values from the practice data instead of relative, reusable logic.
Candidates record a macro once to 'see what happens' and assume Excel captures intent, but the recorder captures every keystroke, including absolute cell addresses, scroll positions and selected ranges tied to the current sheet.
What to do instead
Before recording, plan the exact steps and decide which references must be relative. Record in a blank scratch workbook, then open the VBA editor to inspect the generated code, replace hard-coded addresses with relative references or variables, and test the macro on a copy of different data.
Delivering a workbook that references a personal macro workbook or a path on the local C: drive, so macros fail on the grader's machine.
Personal macros and recorded file paths work silently on the author's computer, and candidates rarely test on a second machine or after moving the file.
What to do instead
Store all procedures in standard modules inside the workbook itself, save as .xlsm, and reference other files only through ThisWorkbook.Path or a folder dialog. Reopen the file from a different directory and run every macro before submitting.
Using VLOOKUP where the lookup column is not the leftmost column, or omitting the fourth argument so approximate matching returns wrong rows.
VLOOKUP can only search the first column of the table array, and the optional range_lookup defaults to TRUE, which returns the nearest lower match on unsorted or text data without raising an error.
What to do instead
Use INDEX with MATCH when the key is not leftmost, and always supply FALSE (or 0) for exact matching. Verify by testing a value that does not exist in the list; an exact-match formula should return #N/A rather than a plausible wrong answer.
Building nested IF chains for multi-condition logic, hitting the 64-level nesting limit or producing formulas that are impossible to audit.
Nested IF is the first tool most candidates learn, and it appears to work on small test sets, so the structural problem only surfaces with real data or additional conditions.
What to do instead
Replace deep nesting with IFS for simple ladders, or with INDEX/MATCH or XLOOKUP against a lookup table for value mapping. For compound conditions use boolean multiplication or SUMIFS/COUNTIFS instead of stacked IFs.
Creating a PivotTable from a range that will grow, then reporting stale totals after new rows are added.
A PivotTable built on a fixed range does not automatically absorb new records, and candidates refresh the pivot without noticing the source range never expanded.
What to do instead
Convert the source to an Excel Table (Ctrl+T) and build the PivotTable from the table name, so the source expands automatically. Alternatively define a dynamic named range with OFFSET/COUNTA or use the whole-column reference. Refresh and confirm the row count matches the source.
Placing calculated fields where a calculated item is required, or vice versa, producing doubled values or errors in the PivotTable.
Both commands sit in the same menu and look interchangeable, but calculated fields operate on summed underlying data while calculated items operate on existing row or column labels, so the wrong choice silently distorts totals.
What to do instead
Use a calculated field when the formula combines fields (for example, profit as revenue minus cost). Use a calculated item only when combining categories within one field. After inserting either, cross-check the grand total against the raw data with SUMIFS.
Changing chart source data by dragging the blue and purple range handles and accidentally including header rows, totals, or blank rows.
The handles are easy to grab imprecisely, and Excel accepts the selection without warning, so series names and axis labels shift or an extra 'Total' series appears.
What to do instead
Open Select Data and edit each series explicitly, setting the series name, values and category labels by cell reference. Remove any total row from the source or exclude it deliberately, and check that the legend shows exactly the intended series.
Applying conditional formatting rules that conflict or overlap, so the visible highlight does not match the stated rule.
Rules are evaluated in priority order and 'Stop If True' is often left unchecked, so a lower-priority rule can override or combine with the intended one, and copied rules carry relative references that shift.
What to do instead
Open Manage Rules, review the order and the Applies To range for each rule, and set Stop If True where a rule should be final. Use absolute references in formulas where the range must not shift, and test with values just above and below each threshold.
Importing or pasting data and leaving numbers stored as text, dates as text, or duplicate records, then building formulas on that unclean data.
Text-formatted numbers look correct on screen but are ignored by SUM, COUNT and lookup functions, and trailing spaces or inconsistent date formats survive a plain paste.
What to do instead
Use Text to Columns or VALUE to convert numbers, DATEVALUE or Power Query to normalise dates, TRIM and CLEAN to strip stray characters, and Remove Duplicates on the key column. Confirm with ISNUMBER and COUNT versus COUNTA that every value is genuinely numeric.
Leaving workbook calculation on Manual, or failing to set protection and calculation options deliberately, so results shown on screen are stale.
Manual calculation is sometimes switched on to speed up a large model and never restored, and protected sheets block the grader from recalculating or editing expected cells.
What to do instead
Check Formulas > Calculation Options and set it to Automatic before saving. Press F9 to force a full recalculation and compare key totals, then review sheet protection to ensure only intended cells are locked and no forgotten password blocks editing.