MO-211 common mistakes
MO-211 · Understand the traps and how to avoid them.
Records a macro, saves the workbook with the default .xlsx type, dismisses the compatibility warning, and the macro is missing when the file is reopened.
Save As keeps the previously used file type (or Excel Workbook) as the default, and .xlsx cannot store VBA; the warning appears once and is easy to dismiss in a hurry.
What to do instead
Choose Excel Macro-Enabled Workbook (*.xlsm) in the Save As type list, or pre-set File > Options > Save > 'Save files in this format' to .xlsm before recording; verify by closing and reopening the file and checking Developer > Macros lists the procedure.
Protects a worksheet and then finds the intended input cells cannot be typed into, or finds protection can be switched off with no password.
Every cell is marked Locked by default and Locked has no effect until Protect Sheet is applied, so candidates clear or set the flag at the wrong moment and often protect without a password.
What to do instead
Before protecting: select the input cells, Format Cells > Protection, clear Locked (and Hidden only on formula cells); then Review > Protect Sheet, enter a password, and tick exactly the allowed actions (e.g., 'Select unlocked cells').
Applies a formula-based conditional formatting rule to a multi-row range and the highlight lands on the wrong rows, or only the top-left cell ever formats.
The formula is interpreted relative to the active cell of the selection, and people apply the rule to one cell first and then use the Format Painter or drag it down, which copies the rule instead of extending the applied range.
What to do instead
Select the whole range first and make the top-left cell the active cell, then Home > Conditional Formatting > New Rule > Use a formula and write the rule for that top row with mixed anchors (e.g., =$D2>100 with the column locked and the row relative); verify by opening Manage Rules and reading the 'Applies to' box.
Builds a custom number format that displays the raw value unchanged, drops the negative section, or shows literal text as #####-style garbage.
Custom formats can hold up to four semicolon-separated sections (positive;negative;zero;text) and treat unquoted characters as format codes, so a missing or misplaced section silently falls back to the default display.
What to do instead
Write the full section structure in Format Cells > Custom, e.g. #,##0.00;Red;"-";@, quote any literal text, preview it against a positive, a negative, a zero and a text cell, and remember the format only changes display, never the stored value.
Records a macro while a fixed range is selected, so the generated code contains literal addresses such as Range("B2:B10").Select and the macro only works on the original sheet layout.
The Macro Recorder captures absolute selections by default and does not generalise them to the data actually present.
What to do instead
Click Developer > Use Relative References before recording, and afterwards open the VBA editor and replace hard-coded addresses with offset-based or dynamic references (ActiveCell.Offset, Selection, a last-row/used-range calculation) before testing on a fresh copy of the data.
Uses VLOOKUP where the value to return sits to the left of the lookup column, or omits the fourth argument, and reports #N/A or a near-match instead of the exact value.
VLOOKUP can only search the leftmost column of its table array, and leaving range_lookup blank defaults to approximate match (TRUE), which returns the nearest lower value on unsorted data.
What to do instead
Always supply FALSE or 0 for exact match (or use XLOOKUP, where exact match is the default), and when the return column is to the left, switch to INDEX/MATCH or XLOOKUP instead of re-arranging the source data.
Debugs a long nested formula or a #VALUE!/#REF! error by retyping the whole formula repeatedly and guessing which argument is failing.
Nested functions hide their intermediate results, so the failing inner argument is invisible in the formula bar.
What to do instead
Use Formulas > Evaluate Formula to step through each argument, Trace Precedents/Dependents to see which cells feed the error, select a sub-expression and press F9 to evaluate it in place (then Esc to cancel), and add cells to the Watch Window for a long formula chain.
Edits the source data, sees the PivotTable still showing old totals, and 'corrects' the pivot by typing over the numbers.
A PivotTable reports from a cached copy of the source taken when the pivot was created; the cache is not updated automatically on every source edit.
What to do instead
Use PivotTable Analyze > Refresh, or Refresh All (Ctrl+Alt+F5), and enable 'Refresh data when opening the file' in PivotTable Options > Data; if values look typed-in, check that the cells are not manual overrides of the pivot layout.
Creates a PivotTable from a fixed range such as $A$1:$D$50, then adds rows below the source and refreshes, but the new records never appear in the pivot.
The pivot's source is a static address string, not a growing definition, so rows outside that address are outside the cache.
What to do instead
Convert the source to an Excel Table (Ctrl+T, give it a name) and build the pivot from the table name so the source grows with the data, or use PivotTable Analyze > Change Data Source after the range changes.
Selects an entire block including totals and labels to insert a chart, gets an extra series plotting the grand total and series in the wrong orientation, and then fixes it by typing data labels by hand.
Excel guesses the series orientation (rows vs columns) and treats every numeric column in the selection as a data series.
What to do instead
Select only the label and value columns (hold Ctrl to select non-adjacent ranges), then use Chart Design > Select Data to add or remove series explicitly and Switch Row/Column to correct orientation, avoiding manual labels that no longer follow the data.