← All Kits · Excel Kit

Paste Special in Excel: Values, Formats, Transpose

Michael Nocito · Updated August 2026 · Every result on this page was run in Excel before it was published

Paste Special is a menu of "paste only part of it", and four of its options do most of an analyst's daily work. This page shows each one running: freezing a formula into a number, flipping rows and columns, fixing a column of numbers that Excel is treating as text, and copying a format without dragging the contents along with it.

What you do: Ctrl+C, then Ctrl+Alt+V, then one letter. V is values, T is formats, E is transpose, W is column widths. That is the whole keyboard route, and it is the fastest thing you will learn this week.

The short version. A normal paste replaces everything about a cell. Paste Special lets you choose which part.

Values: freeze a formula into a number

Before the example: column B holds =A2*1.2 down five rows. If you paste those cells back onto themselves as values, what happens to B2 when A2 changes afterwards?

Nothing, and that is the point. Here is the run. B2 held =A2*1.2 and displayed 144. After selecting B2:B6, copying, and pasting back with Values, B2 held the number 144 and no formula at all. Changing A2 from 120 to 999 left B2 sitting at 144.

Three moments where you want this. Sending a workbook to somebody who does not have the source data, so the formulas would return errors. Freezing a month that is now closed, so a later correction upstream cannot quietly restate it. And breaking a link before deleting the helper columns a formula depends on.

Say out loud what you lose when you do this. The answer is the audit trail: nothing in the cell now says where 144 came from. Keep the formula version on a hidden sheet, or write the calculation into a note beside the frozen figure.

The keyboard sequence is worth burning in: Ctrl+C, Ctrl+Alt+V, V, Enter. Four keystrokes, and it is the single most used Paste Special option in analyst work.

Multiply by 1: fix numbers stored as text

First: five cells that look like the numbers 120, 340, 75, 900 and 45, imported as text. What does =SUM over them return?

0. =COUNT over the same five returns 0 as well. Excel is not being difficult, it genuinely has five pieces of text there, and text has no total.

Paste Special fixes it in one move, using an option most people never open: the operations at the bottom of the dialog.

1. Type 1 into any spare cell and copy it.
2. Select the text numbers and set their format back to General, on the Home tab.
3. Ctrl+Alt+V, choose Values, choose Multiply, then Enter.

Multiplying by 1 changes no value and forces Excel to evaluate each entry as a number, so the text becomes numeric in place. It works on a whole column at once, which is why it beats retyping and beats a helper column of VALUE() formulas you then have to paste back anyway.

The format step matters. If the cells are still formatted as Text, the result goes back in as text and the sum stays at 0. Set General first, then paste. More on why exports do this in cleaning messy data and CSV imports that eat leading zeros.

Transpose: flip rows and columns

Before the description: a report arrives with twelve months across the top and four products down the side, and every tool you own wants months down the side. What do you do?

Copy the block, click one empty cell, then Ctrl+Alt+V and E. Excel writes the same data rotated, so rows become columns and columns become rows.

Two rules keep it safe. Paste into empty space rather than over the original, because Excel refuses to transpose onto a range that overlaps what you copied. And check your formulas afterwards, because relative references rotate with the block and rarely mean what they used to. The reference mechanics are in absolute vs relative references.

If the source keeps changing, the formula =TRANSPOSE(range) does the same rotation and stays linked, which is usually better for a report you rebuild monthly. Paste Special transpose is for a one-off.

Formats and column widths: copy the look, not the contents

Paste Special ▸ Formats carries the fill, font, borders, number format and conditional formatting rules to the destination and leaves the destination's values alone. It is the same job as the Format Painter, and it is faster when the source and destination are far apart, because the clipboard waits and the paintbrush does not.

Paste Special ▸ Column Widths is the small one nobody knows. Build a table on one sheet, get the widths right, then copy any cell from it and paste widths onto the new sheet. The column layout arrives without a single value moving.

Both of these are on the exam as separate options, and both are on the right-click paste icons if you would rather point at them than remember a letter.

The paste that deletes your drop-down list

First: a column has a Yes/No drop-down on it, set up with Data Validation. Somebody copies a cell from elsewhere and pastes it into that column. What happens to the drop-down?

It is gone. Not on the one cell, on exactly the cells that were pasted over, and with no warning at all. In the run for this page, the validation rule on F2 disappeared after a plain Ctrl+V, while F3 directly below it still had its rule.

This follows from the rule at the top of the page: a normal paste replaces everything about the destination cell. Validation and conditional formatting are part of everything.

What to do about it, in order of how much it costs you:

1. Paste with Values instead of a plain paste, whenever the destination has rules on it. Values leaves validation and formatting alone.
2. Tell the people using the sheet, in the sheet. A cell saying "paste values only into this column" is worth more than a rule they will break once.
3. Protect the sheet if the workbook goes to people who did not build it. Validation is a guide rail, not a lock, and it was never designed to survive a paste.

Picture the last shared workbook you built with drop-downs in it. If somebody pasted a block of data into that column last month, the rule is not there any more, and nothing in the file will tell you. Checking takes one click on a cell and a look at Data ▸ Data Validation. The full setup is in Excel data validation.

Edge cases worth knowing

Paste Special only works from Excel's own clipboard. Copy from a browser or a text file and most options grey out, because Excel has text rather than cells. Paste it into the sheet first, then copy from there.

Skip Blanks is the one that saves an update. Tick it and empty cells in the source will not overwrite what is in the destination. It is how you overlay a partial update onto a full column without punching holes in it.

Paste Link is different from paste. It writes a reference back to the source rather than a copy, so the destination follows the source forever. Useful for a summary sheet, dangerous when the source workbook moves.

The paste icons and the dialog are the same thing. The icons under the Paste button on the Home tab are the common options with pictures on them. Anything unusual, including the operations and Skip Blanks, is only in the dialog.

Undo works, and it is not enough. Ctrl+Z restores a wiped validation rule if you notice immediately. Nobody notices immediately, which is why the values habit matters more than the fix.

Why the layers exist

A cell is not one thing. Excel stores its value, the formula that produced the value if there is one, its number format, its cell format, any conditional formatting, any validation rule, and its comment, all separately.

Ctrl+V was designed to move a whole cell from one place to another, so it replaces every layer. That is right when you are duplicating something, and wrong every time you only wanted the number.

Paste Special is the list of layers with checkboxes on it. Once you see the dialog as a list of what a cell is made of, the options stop being a menu to memorise: values, formulas, formats, widths, validation, and the arithmetic that combines the source with what is already there.

How to apply this to your own work

1. Learn one sequence today: Ctrl+C, Ctrl+Alt+V, V, Enter. It pays for itself the first time you send a workbook out.
2. Any time a column of numbers refuses to total, try the multiply-by-1 paste before you touch anything else. It takes fifteen seconds and rules out the most common cause.
3. In any workbook other people type into, paste values by default. Treat a plain paste over a validated column as a mistake you are avoiding, not a risk you are accepting.
4. When you freeze formulas into values, keep the formula version somewhere. A frozen number with no working shown is a number nobody can defend later.
5. Do not go hunting through old workbooks for wiped validation rules. Check the ones people are actively typing into, and rebuild those.

If you have paper nearby, write the four layers of a cell down the page: value, formula, format, rule. Beside each one, write which paste brings it. That page is the dialog, and you will not need to read the dialog again.

Cheat sheet

The one habit to keep

Paste values by default into any sheet somebody else uses. A plain paste is the only everyday action in Excel that can delete a rule, a format and a formula at once while looking like it worked.

What is in the workbook you share most: drop-downs, conditional formatting, both? And when did you last check they are still there?