MO-200 common mistakes
MO-200 · Understand the traps and how to avoid them.
Inserting a new worksheet at the end of the workbook instead of before the active sheet or at a specific position, and failing to set the tab colour or rename it via the sheet tab.
Candidates click the plus button at the end of the tab bar, which always appends, and then rename the sheet by editing cell A1 rather than the tab. They also forget that the default number of sheets and the active sheet position are controlled in Excel Options.
What to do instead
Right-click an existing tab and choose Insert > Worksheet to place a new sheet before the selected one, or use the plus button and then drag the tab to the required position. Rename by double-clicking the tab or right-click > Rename. Set tab colour via right-click > Tab Color. Verify sheet order in the task before moving on.
Copying a worksheet within the same workbook and leaving the copy named 'Sheet1 (2)' instead of giving it the exact name specified, or moving a sheet to a new workbook without preserving formulas that reference other sheets.
The Move or Copy dialog defaults to creating a copy at the end with a '(2)' suffix; candidates confirm without renaming. When moving a sheet to a new workbook, cross-sheet references break because the source workbook is no longer open, and candidates do not check for #REF! errors.
What to do instead
In the Move or Copy dialog tick 'Create a copy', choose the position, then immediately rename the copied tab. Before moving a sheet to a new workbook, convert cross-sheet references to values or ensure the referenced sheets are also moved. After any move, inspect formulas for broken references.
Hiding rows or columns by setting the font colour to white or by using a filter, rather than using Hide, and then failing to unhide all rows/columns before the final check.
Candidates confuse visual concealment with the Hide command. Filtering hides rows but leaves the filter active, so data appears missing; white font leaves values present but invisible. Unhiding is often forgotten when the task says 'display all data'.
What to do instead
Select entire rows or columns, right-click, and choose Hide. To unhide, select the surrounding rows/columns, right-click, and choose Unhide; or use Format > Hide & Unhide. Clear any active filters via Data > Clear. Confirm with the Name Box or by selecting all cells and using Format > AutoFit.
Using the fill handle to copy a formula down a column and assuming relative references adjust correctly, when the task requires mixed references (e.g., $A2 or A$2) so that one part stays fixed.
The default drag copies relative references, which shift both row and column. Candidates do not analyse which part of the reference must remain constant, so totals or lookup ranges drift and produce wrong values or #REF! errors.
What to do instead
Before filling, decide which component must be locked and insert the dollar sign accordingly: $A$2 locks both, $A2 locks the column, A$2 locks the row. Use F4 to cycle reference types while editing. Test by filling two or three cells and comparing results before filling the whole range.
Pasting data with Paste Special but choosing the wrong option (e.g., Paste All instead of Values, or Transpose when not required), leaving formulas, formatting, or links that the task did not ask for.
Candidates use Ctrl+V out of habit or pick the first Paste Special option. They also forget that pasting values over formulas destroys the formulas, or that pasting formats overwrites the destination formatting.
What to do instead
Use Ctrl+Alt+V (or right-click > Paste Special) and select the exact option: Values, Formats, Formulas, or Transpose. If the task says 'keep only the values', choose Values. If it says 'keep the formulas but not the formatting', choose Formulas. Verify the result in the formula bar and with Format Cells.
Applying conditional formatting to the wrong range or using a rule that highlights the wrong cells because the formula uses relative references that shift across the applied range.
Candidates select a single cell and apply the rule, then use Format Painter or drag, which extends the rule with shifted references. They also forget that conditional formatting formulas are evaluated relative to the top-left cell of the applied range.
What to do instead
Select the entire target range first, then create the rule. In 'Use a formula to determine which cells to format', write the formula as it applies to the active (top-left) cell, using mixed or absolute references where needed (e.g., =$B2>100). Use Manage Rules to inspect the 'Applies to' box and correct it.
Creating a table but leaving the default name 'Table1' instead of the required name, or failing to toggle 'My table has headers', causing the first data row to be treated as headers or duplicated header names.
The Create Table dialog pre-fills the name and the header checkbox based on the selection; candidates accept defaults. If headers are not ticked, Excel adds 'Column1' etc., and structured references later fail. Renaming is done in Table Design, which candidates overlook.
What to do instead
Before confirming, tick 'My table has headers' if the first row contains labels. After creating, go to Table Design > Table Name and type the exact name (no spaces). Check that structured references in formulas update to the new name. If headers are wrong, convert to range, fix, and recreate the table.
Adding a total row to a table and then manually typing a SUM formula below the table, creating duplicate totals or breaking the table's structured reference.
Candidates do not know that Table Design > Total Row inserts a structured total that automatically uses SUBTOTAL and respects filters. They type a formula in the row beneath, which is outside the table and not included in table operations.
What to do instead
Select any cell in the table, go to Table Design, tick Total Row, then choose the function and column from the drop-down in the total row. If a manual formula is required, use structured references such as =SUM(Table1[Sales]) inside the table, not a cell reference below it.
Using a lookup function without locking the lookup range, or choosing VLOOKUP when the lookup column is to the right of the return column, resulting in #N/A or wrong values after filling.
Candidates write =VLOOKUP(A2, B2:D10, 3, FALSE) and drag down; the range shifts to B3:D11 and beyond, so later rows look up the wrong area. They also forget that VLOOKUP cannot look left, and omit the exact-match argument.
What to do instead
Lock the table array with absolute references: =VLOOKUP($A2,$B$2:$D$10,3,FALSE). For left lookups use INDEX/MATCH or XLOOKUP. Always include the fourth argument FALSE (or 0) for exact match. Convert the source to a table and use structured references to avoid range drift.
Creating a chart that includes the wrong data series or category labels because the selection omitted headers or included totals, and then failing to switch the row/column orientation.
Candidates select a block of cells without the header row, so series are named 'Series1' and categories are numbers. They also include a total row, which distorts the scale. Excel's default orientation may plot series by row when the task expects by column.
What to do instead
Select the data including headers and excluding totals before inserting the chart. If the chart is already created, use Chart Design > Select Data to correct the range, and use Switch Row/Column to fix orientation. Edit series names directly in the Select Data dialog if needed.