Paste Special: Seven Negotiated Prices Went Into a Filtered Column, All Seven Landed in the File, and One of Them Landed on the Right Row

The quote came back on the Tuesday with seven cable lines renegotiated. The buyer opened the price file, filtered column C to Cable, and got seven rows — one for each line on the quote, in the same order. Copy the seven new prices out of the quote, click the first visible cell in the price column, Ctrl+V.

Seven cells copied. Seven cells pasted. No error, no warning, no blanks, and no message from Excel of any kind.

The count was right. The order was right. The values were right — all seven of them are in the file, to the penny. The only thing wrong is which row each one is on, and that is not a thing Excel will ever tell you.

Excel pastes into contiguous cells. A filter changes what is on screen; it does not change what is next to what. There is no "paste into visible cells only" option, there never has been, and Alt+; — Go To Special ▸ Visible cells only — is a copy tool that has no effect on the destination of a paste. So seven values clicked into cell F3 went into F3, F4, F5, F6, F7, F8 and F9, and four of those rows were hidden at the time.

What this covers. The Paste Special dialog, its operations, Skip Blanks, Transpose, Paste Link and paste options behave the same in Excel 2016, 2019, 2021, Microsoft 365 and Excel for Mac; Excel for the web has a reduced Paste Special and no Operation section at all, which matters in section 6. SUM, SUMIFS, COUNTIF, COUNTIFS, COUNTA, SUMPRODUCT, INDEX/MATCH, XLOOKUP, IFERROR, ROUND, MIN, MAX, UNIQUE, FILTER, TRANSPOSE and LET in the checking sections work in every version unless noted. A price list is only the example: a headcount file, a rate card, a stock count, a budget upload — anything where somebody pastes a column of numbers next to a column of keys is the same job, and every trap below applies unchanged.

1) Seven Values, Seven Rows, One Match

The seven cable products are on rows 3, 5, 6, 9, 12, 16 and 19. Filtered, they stack up on screen looking like a block. They are not a block.

Nine of twenty rows are wrong, in three tidy groups of three: three cable products carrying another cable's price, three cable products never repriced at all, and three products from three other categories overwritten while hidden.

The three hidden ones are the expensive ones, and they are the ones nobody could have seen. At the moment the paste happened, rows 4, 7 and 8 were not on the screen. By the time the filter came off, the old prices were gone — a paste of values leaves nothing behind to compare against.

=SUMPRODUCT(E2:E21,F2:F21)   →  78,728.75   what the file now says it holds
                                 84,143.00   what the update should have produced
                                 81,412.30   what it held before anybody touched it

That is the shape of the damage worth remembering: an uplift of between 7.26% and 10.38% on seven products moved the file down 3.30%. The direction was wrong, not just the amount.

🎯 Scenario: Before pasting a column of numbers into a filtered list, clear the filter and look at the destination. If the rows you want are not contiguous with the filter off, a paste cannot reach them — and the fix is not a better paste, it is section 3's lookup.

2) Every Check Anyone Ran Passed

This is not a story about nobody checking. Three checks were run, and all three passed.

"Did all seven new prices go in?" Yes. Every one of them:

=COUNTIF($F$2:$F$21,2.11)    → 1        present
=COUNTIF($F$2:$F$21,2.88)    → 1        present
=COUNTIF($F$2:$F$21,5.74)    → 1        present
=COUNTIF($F$2:$F$21,8.42)    → 1        present
=COUNTIF($F$2:$F$21,1.55)    → 1        present
=COUNTIF($F$2:$F$21,2.27)    → 1        present
=COUNTIF($F$2:$F$21,3.79)    → 1        present

Seven of seven. This check cannot fail, because the values really are in the column — that is what a paste does. A check that asks whether the numbers arrived and not where they arrived is not a check of anything.

"Do the cable prices add up to the quote?" Near enough:

sum of the seven visible prices after the paste   → 27.02
sum of the seven prices on the quote              → 26.76
                                                     0.26 apart, 0.97%

Twenty-six pence. Three of the seven visible cells hold a quoted price that belongs to a different row, three hold last year's price, and the errors happen to point in both directions, so the total lands within 1% of right. A column total is a weak test precisely because it is indifferent to order, and order is the only thing that went wrong.

"Is anything blank or broken?" No. =COUNTBLANK(F2:F21) returns 0 and =COUNTIF(F2:F21,"<>") counts twenty numbers. Nothing errored because nothing was asked to do anything difficult.

Here is the check that fails, and it is the only one:

=SUMPRODUCT($E$2:$E$21,$F$2:$F$21)         → 78,728.75
=SUMPRODUCT($E$2:$E$21,$F$2:$F$21)/81412.3-1
                                           → -3.30%     after an uplift

A repricing that raises seven prices and touches nothing else cannot lower the value of the file. One cell holding the before-value, one cell holding the after-value, and the sign of the difference would have stopped this on the Tuesday.

It surfaced eleven days later, when a customer quotation for a 400×300 wall enclosure printed at £2.88.

🎯 Scenario: Park =SUMPRODUCT(units,price) in a cell before you paste, then paste over it into the data and read the two side by side. Any bulk edit has an expected effect on that number — up, down, or by a known amount — and a bulk edit whose effect you cannot predict is one you should not be making with the clipboard.

3) The Fix Is Not a Better Paste

There is no keystroke that makes a paste respect a filter. What there is instead:

Match on the key, do not align on position. Put the quote on its own sheet — code in one column, new price in the next — and let a lookup put each price where it belongs:

=XLOOKUP($A2,Quote!$A$2:$A$8,Quote!$B$2:$B$8,$F2)
=IFERROR(INDEX(Quote!$B$2:$B$8,MATCH($A2,Quote!$A$2:$A$8,0)),$F2)

Both say the same thing: if this product is on the quote, take the quoted price; otherwise keep the price it has. Fill down all twenty rows, filter or no filter, and every row gets its own answer. The fourth argument of XLOOKUP and the IFERROR wrapper on INDEX/MATCH are doing the real work — they are what turns "not on the quote" from an error into leave it alone.

Then, if you want static values in the end, paste those results over themselves as values once, with the filter off, having checked the total first.

Or sort, do not filter. Sorting physically moves rows, so a sorted block really is contiguous and a paste really does line up. Sort by Category, paste the seven, sort back by Code. This works, and it is how a lot of people do it, but it depends on a second sort that restores the original order — which needs a column that holds the original order. If the only thing you can sort back on is Code and the file was in Code order, fine. If it was not, add a sequence column before you sort, or you will have traded a paste problem for a sort problem.

Count before you trust. =COUNTIF($C$2:$C$21,"Cable") returns 7 and =ROWS(Quote!$A$2:$A$8) returns 7, and those two agreeing is necessary but not sufficient — it was true in this file, on the day, and the paste still went wrong. The count tells you the quote covers the right number of products. Only a key match tells you it reached the right ones.

🎯 Scenario: Any time you are about to paste a column of values against a column of keys, ask whether you could write the lookup instead. If the keys exist, the lookup takes twenty seconds longer and cannot land on the wrong row; if there are no keys, that is the finding — a list you cannot join to is a list nobody can check.

4) What a Plain Paste Actually Carries

Ctrl+V is not "put these numbers there". It replaces the destination cell with a copy of the source cell, and a cell is more than its value:

• its value or formula, with relative references translated (section 5);
• its number format — a General cell that lands on a currency cell becomes currency, or the reverse;
• its font, fill, borders and alignment;
• its conditional formatting rules, which are added to whatever rules the destination already had (section 10);
• its data validation, which replaces the destination's — including replacing a dropdown with nothing at all (section 9);
• its comments and notes;
• its merged state, which will merge or unmerge the destination to match.

Paste Special exists to take a subset. The dialog is Ctrl+Alt+V, and the options worth knowing by their accelerator letters:

So Ctrl+Alt+V, V, Enter is paste-values, and it is worth learning as one gesture because it is the one you want four times out of five.

The other route is the paste options button that appears at the bottom-right of the pasted range. Press Ctrl once after a paste and it opens; the same choices are there as icons. It disappears the moment you type anything, which is why it is a worse habit than the dialog.

5) Copy Translates References, Cut Does Not

This is the other half of why pasted formulas produce wrong numbers, and it is a rule with no exceptions:

Copy a formula and Excel rewrites every relative reference by the offset between source and destination. =E2*F2 copied from G2 to G5 becomes =E5*F5. That is the whole point of relative references and it is right 95% of the time.

Cut a formula and Excel rewrites nothing inside it. =E2*F2 cut from G2 to G5 is still =E2*F2. What Cut does change is every formula elsewhere that pointed at G2 — those follow the cell to G5, because Cut is a move and a move takes the cell's identity with it.

Two consequences people meet constantly:

• A copied formula that lands one row down reads one row down. If the block you copied was aligned to its data and the destination is not, the formula is now reading the wrong row and returning a perfectly plausible number. Anchor with $ — =$E2*F$1 — for exactly the parts that must not move.
• Cut-and-paste cannot break a reference to the cell you moved, and can absolutely break a reference to the cell you moved onto. Anything that pointed at the destination now points at a cell that has been overwritten, and Excel does not warn.

in G2:  =SUMPRODUCT($E$2:$E$21,$F$2:$F$21)
copy to G5:  =SUMPRODUCT($E$2:$E$21,$F$2:$F$21)    absolute, unchanged
in G2:  =SUMPRODUCT(E2:E21,F2:F21)
copy to G5:  =SUMPRODUCT(E5:E24,F5:F24)            three rows off the data

The second one returns a number. It returns 69,301.30 instead of 81,412.30 on this sheet, because rows 22, 23 and 24 are empty and rows 2, 3 and 4 — 12,111.00 of stock between them — have been left behind. Nothing is red.

🎯 Scenario: Before copying a formula anywhere, press F2 on it and read the colours. Every coloured box that must not move needs a $ on the part that must not move — and if you cannot say which parts those are, copy it once, check the result, and only then fill.

6) Operation ▸ Multiply, and the Rate That Got Applied Twice

The bottom half of the Paste Special dialog is the part most people have never used, and it is the most destructive thing in it. Copy a cell containing a number, select a range, choose Multiply, and every cell in the range is replaced by itself times that number.

It is genuinely useful. Converting a price column to euros at 1.1745 is one copy, one select, Ctrl+Alt+V, M, Enter. Converting units to thousands is the same gesture with Divide.

It is also completely invisible afterwards. There is no record on the sheet that it happened, no formula, no note, no format change. Which produces the most common bulk-edit failure there is: it gets run twice.

48.50 × 1.1745             → 56.96       the intended euro price
48.50 × 1.1745 × 1.1745    → 66.90       what two runs produce
                             1.37945025  the rate that was actually applied
                                 17.45%  overstatement, on every row equally

Applied to the whole file, 81,412.30 converts once to 95,618.75 and twice to 112,304.22 — 16,685.47 apart. Every line is internally consistent, every ratio between products is exactly right, and the file is wrong by a fixed percentage that no sanity check on relative values can see.

Three more things Operation does that are worth knowing before you use it:

• It rewrites formulas rather than replacing them. A cell holding =E2*F2 under a Multiply by 1.1745 becomes =E2*F2*1.1745, and Excel parenthesises where it has to so the arithmetic still binds correctly. The cell keeps recalculating, with a magic number now buried in it.
• It cannot touch text. A price that arrived from a CSV as text — "48.50" rather than 48.5 — is not a number, and it comes out of the paste exactly as it went in. A column that is 90% numbers and 10% text-numbers converts 90% of the way and looks converted, which is section 13's first check.
• Excel for the web has no Operation section, so a file that carries this as a documented monthly step cannot have that step done in the browser.

The alternative that survives review is a helper column: =ROUND($F2*$H$1,2) with the rate in H1, filled down, one visible rate that can be read, changed and audited. Then paste-values over it once, deliberately, if you must have static numbers.

🎯 Scenario: If you ever run an Operation paste, type what you did in the cell next to the range before you do anything else — "×1.1745 applied 14/09, whole column". It is the only evidence that will exist, and the next person to open the file is going to want to know whether to run it.

7) Skip Blanks Does the Opposite of What Its Name Suggests

Skip blanks is about blanks in the source, not the destination. Tick it, and any empty cell in what you copied leaves the corresponding destination cell alone instead of clearing it.

That is genuinely the right behaviour when you have a sparse column of corrections — twenty rows, four of them holding a new price, sixteen empty — and you want the four to land and the sixteen to leave the existing prices alone. Without Skip Blanks, that paste wipes sixteen prices to empty. With it, four cells change.

Two limits to know:

• A blank is not a zero and not an empty string. A source cell holding =IF(A2="","",B2) returns an empty string, not a blank, and Skip Blanks will happily paste it — overwriting the destination with something that looks empty, counts as text to COUNTA, and breaks SUM on nothing at all.
• It skips, it does not align. Skip Blanks still pastes positionally. On a filtered range it makes exactly the same mistake as section 1, just in fewer cells.

8) Transpose Is a Snapshot; TRANSPOSE Is a Link

Two things with one name. They are not variants of each other.

Paste Special ▸ Transpose (Ctrl+Alt+V, E) writes a rotated copy, once. A 1×7 row becomes a 7×1 column of independent cells that will never hear from the original again. And because it is a copy, relative references rotate with it: a formula that read the cell above it now reads the cell to its left, which is almost never what was meant. Transposing formulas is the reliable way to produce a grid of numbers that are all individually wrong.

=TRANSPOSE(A1:G1) is a formula. It spills a live rotation that updates whenever the source does, and it holds no copy of anything. In Microsoft 365 you type it in one cell and it spills; in Excel 2019 and earlier it is a legacy array formula that has to be committed with Ctrl+Shift+Enter across a pre-selected range of exactly the right shape.

Which you want depends on one question: should this change when the source changes? If yes, the function. If no — and "no" is right for a report you are about to send — the paste, done last, after the numbers are final.

=TRANSPOSE($A$1:$F$1)        live, spills six rows, updates with the header
Ctrl+Alt+V, E                static, six rows, never updates again

Transposing data with a filter on is also worth naming, because it is the one case where Excel does stop you: you cannot paste into a filtered range with Transpose ticked, and you cannot transpose a multiple selection either. Excel refuses rather than guessing. It is the only place in this article where it does.

9) The Dropdown That Is Not There Any More

Data validation is a property of a cell, and a paste replaces the destination cell. So pasting a plain value into a cell that had a dropdown deletes the dropdown — silently, with no prompt, and with no visual difference until somebody clicks the cell and finds no arrow.

Worse, and this is the one that matters: pasted values are not validated. A cell restricted to a list of four categories will accept Cabel all day if that text arrives by paste. Validation checks typing. It does not check the clipboard.

Both halves of that show up in this file. Column C is restricted to the four categories; one paste from a supplier's spreadsheet is all it takes for a fifth to appear, and =COUNTA(UNIQUE($C$2:$C$21)) returning 5 against an expected 4 is how you find out.

The repairs:

• Paste Special ▸ Validation (N) copies the rule from a known-good cell back over a range, without touching values or formats. Keep one pristine row of the table somewhere off to the side and this is a five-second fix.
• Circle Invalid Data (Data ▸ Data Validation ▸ Circle Invalid Data) draws a red ring around every cell that currently violates its own rule, including everything that arrived by paste. It is the only thing in Excel that audits validation retrospectively.
• A count check beats both: =COUNTA(UNIQUE($C$2:$C$21)) for a category column, =SUMPRODUCT(--(COUNTIF(ValidList,$C$2:$C$21)=0)) for a count of rows whose category is not on the approved list. That second one returns 0 on a clean file and the number of bad rows on a dirty one.

🎯 Scenario: On any sheet where other people paste into validated cells, put =SUMPRODUCT(--(COUNTIF(ValidList,C2:C21)=0)) in a header cell with a label. Validation cannot stop a paste, but that cell counts what got through, and it costs one cell.

10) Conditional Formatting Fragments, and Column Widths Travel

Conditional formatting spreads by paste and never consolidates. Copy a cell carrying a rule, paste it into four places, and Manage Rules now lists the same rule four times with four different Applies To ranges — or one rule with an Applies To reading =$F$3,$F$7,$F$12:$F$14. Nothing is broken yet. It is just that the next person who edits "the rule" edits one of four copies, and three cells keep the old behaviour.

The tidy-up is manual: Home ▸ Conditional Formatting ▸ Manage Rules, set the scope selector to This Worksheet, and read every row. Delete the duplicates, widen one Applies To to cover the lot. Doing this once a quarter on a heavily-pasted sheet is not optional maintenance — a workbook with two hundred fragmented rules is measurably slower to recalculate and repaint.

To avoid creating them: paste Values (V), or Values and number formats (U), or All except borders — none of which bring rules along. A plain Ctrl+V does.

Column widths are the one thing a normal paste does not carry, which is why a pasted table looks wrong even when it is right. Ctrl+Alt+V, W pastes widths and nothing else — no values, no formats. It is the second half of "copy this table somewhere else and have it look the same", and almost nobody knows it is there.

11) Paste Link, Pictures, and Everything Arriving From Outside Excel

Paste Link (the button at the bottom-left of the dialog) writes formulas instead of values: paste-linking A1:F21 produces a grid of =$A$1, =$B$1 and so on, or ='[Book1.xlsx]Prices'!$A$1 across workbooks. Two things follow from those being absolute references. It is live, so the copy updates. And it is positional, so inserting a row in the source shifts what each linked cell points at only if Excel can track the edit — across an open workbook it adjusts, from a closed one it does not, which is how a link that was right in March quietly refers to the wrong row by June. Link to a named range or a table column instead of a raw address, and the problem goes away.

Paste as Picture and Linked Picture (the camera icons in the Paste menu) are for dashboards: a linked picture is a live image of a range that can be resized and placed anywhere, including on another sheet, and it repaints when the source changes. It is the cleanest way to put a small table next to a chart without fighting row heights. It is also invisible to every formula on the sheet — nothing can read it, sum it, or audit it.

Pasting from a browser, a PDF or an email is where numbers arrive as text. The paste options button offers Match Destination Formatting and Text Only; neither of them converts anything. A column that arrived this way will sum to zero, left-align, and refuse every lookup. The three fixes, in order of how much you should prefer them: paste into Power Query and let it type the columns; use Data ▸ Text to Columns with the right decimal setting; or =VALUE(TRIM(A2)) in a helper column, then paste-values back. =ISTEXT($F2) filled down the column tells you which rows need it.

12) The Shapes Excel Refuses

Four messages, all of which mean the same thing — the destination is not the shape the clipboard expects:

• "The copy and paste areas are not the same size." You copied a block and selected a destination that is neither one cell nor an exact multiple of the source. Click one cell instead of a range and Excel extends from there.
• "This command cannot be used on multiple selections." A Ctrl-clicked, non-contiguous copy. Excel will copy multiple areas only if they share the same rows or the same columns; anything else, it refuses to guess the order.
• "We can't do that to a merged cell." Merged cells make the sheet non-rectangular, and almost every paste into or across them either fails or unmerges something. This is the least surprising of the four and the most avoidable: Center Across Selection does the same job to the eye and leaves the grid intact.
• Pasting a range into an Excel Table that is narrower than the paste extends the table, taking structured references and the format with it. Pasting over a calculated column replaces the formula in the whole column, not just the cell you were on, because a table column holds one formula by design.

13) Four Checks

One cell each. Put them on any sheet that receives pasted data.

=SUMPRODUCT($E$2:$E$21,$F$2:$F$21)
    The value of the file, before and after any bulk edit. It is the only
    number that noticed. 78,728.75 here against 84,143.00 expected.

=SUMPRODUCT(--ISTEXT($F$2:$F$21))
    How many cells in a numeric column are not numbers. 0 on a clean file;
    anything else is a paste from outside Excel that has not been converted,
    and those cells are invisible to SUM and to Operation ▸ Multiply alike.

=SUMPRODUCT(--(COUNTIF(ValidList,$C$2:$C$21)=0))
    Rows whose category is not on the approved list. Data validation does not
    check pasted values; this counts what got past it.

=COUNTIFS($C$2:$C$21,"Enclosure",$F$2:$F$21,"<10")
   +COUNTIFS($C$2:$C$21,"Tooling",$F$2:$F$21,"<10")
    Enclosures and tools priced under 10.00. There are none in a sane file and
    two in this one — the 400×300 enclosure at 2.88 and the ratchet crimp tool
    at 1.55. A floor per category catches the rows a column total cannot.

The pattern behind all four: check a property of the whole column that should be stable, not the individual values you just pasted. You already believe the values. The clipboard is not where they go wrong.

14) Twelve Traps

1. A paste ignores filters completely. Values go into contiguous cells, hidden or not. There is no visible-cells-only paste, and Alt+; only affects copying.
2. All the right values in all the wrong rows passes every count check. COUNTIF finds them, COUNTBLANK finds nothing, and a column total lands within 1%. Only a key-matched comparison or a weighted total disagrees.
3. Paste Values destroys the formula and the audit trail in one keystroke. Ctrl+Z is the entire recovery plan, and only until the file is closed.
4. Copy translates relative references; Cut does not. The same formula in the same place behaves differently depending on which one you used to get it there.
5. Operation ▸ Multiply leaves no trace. Nothing on the sheet says it ran, so it gets run twice — 1.1745 twice is 1.37945025, which is 17.45% over on every row equally.
6. Operation cannot touch text. A number stored as text comes out of a conversion unconverted, so a mostly-numeric column converts mostly.
7. Skip Blanks is about the source. It stops empty source cells clearing the destination; it does nothing about blanks already there, and an empty string from an IF is not a blank.
8. Paste Special ▸ Transpose rotates references too. A formula reading the cell above ends up reading the cell to its left. Use =TRANSPOSE() when it should stay live, the paste when it should freeze.
9. A paste deletes the destination's data validation, and pasted values are never validated. A dropdown restricted to four categories will hold a fifth without a murmur.
10. Conditional formatting multiplies with every paste. Four copies of one rule with four Applies To ranges, and editing "the rule" edits one of them.
11. Column widths do not travel with a normal paste — Ctrl+Alt+V, W is the only thing that moves them, and it moves nothing else.
12. Paste Link writes absolute references. From a closed workbook they do not follow an inserted row, so a link that was correct in March points one row off in June. Link to a name or a table column instead.

The paste in this article did exactly what it was asked to do. Seven values were on the clipboard, a cell was selected, and seven cells got seven values in the order they were given. Excel had no way to know that the person doing it believed the filter was part of the instruction.

That is the whole category, and it is not really about the clipboard. It is about position being used as a join. Every time a column of numbers is lined up against a column of keys by eye, the alignment is an assumption that nothing on the sheet is holding — no formula depends on it, no check tests it, and the first thing to disturb it does so silently. XLOOKUP costs twenty seconds more and states the join out loud, where a test can reach it.