Paste Special and Auto Fill

Why paste and fill skills score points

Certiport’s MO-210 objective domain under Manage Data Cells and Ranges includes paste data by using special paste options and fill cells by using Auto Fill. Real projects mix imported data, formulas, and formatting. If you use plain Ctrl+V, you often bring formulas you did not want, wipe number formats, or paste links that break when sheets move. Graders check the result: values where formulas should not remain, transposed layouts matching the sample, filled series that continue correctly, and formulas that recalculate down a column.

Task wording sounds like: "Paste only the values from the Calculation sheet into A2:A20 on Summary," "Transpose the headers in row 1 into column A," "Fill the formula in B2 down through B50," or "Use Flash Fill to create full names in column C." Match the tool to the words—values, formulas, formats, transpose, fill, series, Flash Fill—instead of default paste every time.

Opening Paste Special

1. Copy the source range (Ctrl+C).
2. Select the upper-left cell of the destination.
3. Open Paste Special with any of these:
   • Home tab → Paste drop-down (clipboard icon menu) → Paste Special…
   • Right-click destination → Paste Special…
   • Keyboard: Ctrl+Alt+V (Windows); on Mac, often Control+Command+V or the Paste menu equivalent
4. Choose the option the task requires → OK.

You can also use the Paste gallery icons (Values, Formulas, Transpose, and so on) for one-click pastes when the icon matches the instruction exactly.

Paste Special options you must know

Operation radio buttons (Add, Subtract, Multiply, Divide) combine the copied values with existing destination values. Associate tasks occasionally say "Add the bonus amounts to the existing totals using Paste Special." Select Values (or All) plus Add, then OK.

Values vs Formulas — the classic trap

Suppose B2 contains =A2*1.08 and displays 108. If you paste Formulas into another sheet, the destination still calculates (and relative references adjust). If you paste Values, the cell stores 108 with no formula. When a later step says "ensure the Summary sheet does not recalculate if Prices change," you need Values. When it says "copy the commission formula to the new region block," you need Formulas (or Auto Fill).

Formats alone never changes numbers—useful when a styled header range should decorate a different block of data without overwriting entries.

Transpose walkthrough

Descriptor: "Copy the labels in A1:F1 and paste them as a vertical list starting at H2."

1. Select A1:F1 → Ctrl+C.
2. Select H2.
3. Paste Special → check Transpose → OK (often with Values or All depending on whether you need formats).

Six horizontal labels become H2:H7. If you transpose a block with formulas, references still adjust—verify one cell after paste. Destination must have enough empty cells; overlapping source and destination incorrectly causes an error or partial overwrite.

Auto Fill: formulas and series

The fill handle is the small square at the bottom-right of a selection.

Filling formulas

1. Enter a correct formula in the first cell (for example =C2-D2 in E2).
2. Drag the fill handle down (or double-click it when contiguous data sits to the left so Excel fills to the last adjacent row).
3. Confirm relative references shifted (E3 becomes =C3-D3).

Absolute references ($C$2) stay fixed while relative parts move—Domain 4 covers that in depth, but you must recognize when a filled formula "breaks" because a rate cell was not absolute.

Filling series

Auto Fill recognizes many patterns:

• Numbers: 1, 2 → drag continues 3, 4, … (select both starters first for a clear step)
• Days / weekdays / months / quarters when you type a starting label
• Custom lists defined in Excel Options (regions, product lines) when the workbook or machine has them

Right-click–dragging the fill handle opens a shortcut menu: Copy Cells, Fill Series, Fill Formatting Only, Fill Without Formatting, Flash Fill, and growth options. If a drag copies Jan into every cell instead of advancing months, choose Fill Series from that menu (or type two months, select both, then drag).

Home → Fill (Editing group) also offers Down, Right, Up, Left, Across Worksheets, Series, Justify, and Flash Fill when the handle is awkward.

Exam scenario: series + formula

Descriptor: "In A2:A13 enter the months January through December. In B2 enter a formula that multiplies A2’s corresponding sales by the tax rate in $G$1, then fill the formula through B13."

1. Type January in A2 → drag fill handle to A13 (or Fill Series).
2. In B2 enter =Sales!B2*$G$1 (or whatever sheet/cell the project names).
3. Double-click or drag the fill handle through B13.
4. Spot-check B13 references before moving on.

Flash Fill (Ctrl+E) as a related fill skill

Flash Fill watches an example you type beside existing data and completes the pattern for remaining rows. It is under Data → Flash Fill and the fill-handle menu; the keyboard shortcut is Ctrl+E.

Scope note: the MO-210 objective domain names “Fill cells by using Auto Fill” — it does not list Flash Fill as a sub-objective. Flash Fill is worth knowing because it saves real time and because Excel offers it on the same fill menus, but on a scored task the safe default is Auto Fill or the text functions in Domain 4 (LEFT, RIGHT, MID, CONCAT, TEXTJOIN) unless the prompt names Flash Fill outright.

Associate-appropriate uses:

• Combine first and last name columns into a Full Name column after you type one correct example
• Split an email local-part from a full address when you demonstrate the first result
• Reformat phone numbers or IDs to a shown pattern

Flash Fill produces static text, not formulas. If the task later requires the result to update when source cells change, use a formula (CONCAT, TEXTJOIN, LEFT/RIGHT) instead. If the task says "use Flash Fill," formulas alone may not match the scoring key even if the text looks identical.

Tips:

• Provide a clear example in the first result cell, then Ctrl+E.
• If Flash Fill guesses wrong, correct a second example and run it again, or undo and refine.
• Data must be adjacent and consistently patterned; messy source columns confuse detection.

Choosing the right tool quickly

Common MO-210 traps

• Using Ctrl+V when the descriptor said values only—formulas remain and fail the check.
• Transposing onto a range that is too small or overlaps the source.
• Filling a formula that should lock a tax rate but uses a relative reference, so every row points at the wrong cell.
• Dragging Auto Fill as Copy Cells when you needed a series.
• Using Flash Fill when the project required a live formula—or the reverse.
• Forgetting that Paste Formats does not bring validation or comments unless those options are selected.

Practice Ctrl+Alt+V, the Paste gallery, fill-handle double-click, right-click fill options, and Ctrl+E until each feels automatic. On a 50-minute performance exam, these operations are usually one step inside a larger project—speed and precision matter more than memorizing every Paste Special checkbox.