Copy and paste in Excel carries more than you see. A copied cell brings its formula, its formatting, its validation rules, its comment — the whole genetic code — and ordinary paste splices in all of it. Most paste disasters (“my formatting’s gone weird”, “the formula now points at nothing”) are just genetics you didn’t mean to transfer.
Paste Special — Ctrl+Alt+V, or the little dropdown under the
Paste button — is how you choose. It’s a dialog full of checkboxes,
but the working set is three moves and a party trick.
Move one: paste as values
The one everybody needs weekly. A column of XLOOKUP results, a spilled FILTER, a block of calculations — sometimes you need the answers to stop being formulas: before deleting the source data they point at, before sending the file to someone who shouldn’t see the workings, before the numbers must become a matter of record.
Copy the range, then paste Values onto itself (or elsewhere).
The formulas are gone; the numbers remain, frozen. Keyboard people:
Ctrl+C, then Ctrl+Alt+V, V, Enter — four keystrokes you’ll
use a thousand times.
One warning, because freezing is destructive by design: values can’t be un-frozen back into formulas. If the logic took an hour to build, freeze a copy and keep the living sheet. (And if you’re freezing to make a monthly snapshot, that’s a sign the real answer is a recorded query with a proper output table.)
Move two: transpose
Data arrives sideways — months running down when your report runs across, or a header row that should be a label column. Transpose flips rows to columns in one paste:
Two notes. Transpose pastes a copy — the original stays, and the
new block doesn’t update when the source changes. If you want a
transposed view that stays live, the formula
=TRANSPOSE(A1:D1) spills one, and it updates with its source.
And you can’t transpose onto a range that overlaps the copy —
paste to clear ground, then tidy.
Move three: paste arithmetic
The dialog’s strangest section — Add, Subtract, Multiply, Divide — performs arithmetic between the clipboard and the destination. Its famous use is the multiply-by-1 trick that snaps text-numbers back to real ones. But it generalises:
- Copy a cell containing
1000, select a column, Paste Special → Divide: the whole column converts to thousands, in place, no helper column. - Copy
1.05, Paste Special → Multiply: every selected price rises 5%.
In-place arithmetic is quick and irreversible in bulk — the original numbers are gone (Ctrl+Z willing). For anything with consequences, the honest version is a helper column and a paste-values at the end, where the workings stay visible until you choose otherwise.
The supporting cast
Worth knowing they exist: Formats (paint one cell’s look onto a range — the Format Painter’s honest twin), Column widths (make the new report grid match the old one), Skip blanks (paste a patchy column over a full one without blanking the gaps), and Paste Link (paste cells that reference the source — useful, but the moment you’re linking between workbooks, consider whether a query would rot less).
The habit
Paste Special is a stage-two skill on the path for the same reason as the rest of the cleaning kit: it’s about controlling exactly what enters your sheet. The habit that sticks — when you copy anything, spend half a second asking what do I actually want to arrive: the answer, the recipe, or the outfit? Ordinary paste brings all three. Paste Special is the packing list.
Values, Transpose, Multiply. Three checkboxes; a decade of small rescues.