How to Use Paste Special in Excel (7 Tricks Worth Knowing)

A normal paste in Excel copies everything: values, formulas, formatting, borders, the lot. Often that's not what you want. Paste Special lets you choose exactly which part to paste, and it has a few clever tricks most people never find.

How to open Paste Special

1. Copy some cells with Ctrl + C.
2. Click where you want to paste.
3. Press Ctrl + Alt + V, or right-click and choose Paste Special…, or go to Home > Paste (the arrow under the button) > Paste Special…

On a Mac, press Control + Command + V.

The right-click menu also shows Paste Options icons for the most common choices. Hover over each one to preview the result before you click.

1. Paste values only

The most useful option. It pastes the results of formulas as plain numbers or text, with no formulas.

Use it when:

• You're sending a file and don't want people to see (or break) your formulas.
• You've used a helper formula like TRIM or PROPER and want to replace the original data.
• You want to freeze numbers that would otherwise keep changing.

Shortcuts: In Microsoft 365, Ctrl + Shift + V pastes values directly. In any version, press Ctrl + Alt + V, then V, then Enter.

2. Paste formatting only

Copies the look (colours, fonts, borders, number formats) without changing any values.

Choose Formats in Paste Special, or use the Format Painter (the paintbrush on the Home tab). Double-click Format Painter to apply the formatting to several places.

3. Paste column widths

Copied a table to a new sheet and all the columns are squashed? Copy the original columns, then in Paste Special choose Column widths. Only the widths change.

4. Paste formulas only

Choose Formulas to paste the formulas without their formatting, which is useful when copying calculations into a sheet that's already styled.

5. Transpose

Tick Transpose at the bottom of Paste Special to flip rows into columns and vice versa. See our full guide to transposing data.

6. Do maths as you paste

This is the hidden gem. You can add, subtract, multiply or divide a whole range by a number without writing a single formula.

Example: increase every price by 10%.

1. Type 1.1 in an empty cell and copy it.
2. Select all the prices.
3. Open Paste Special.
4. Under Operation, choose Multiply.
5. Click OK.

Every price is now 10% higher. Delete the 1.1 cell afterwards.

The same trick fixes numbers stored as text: copy an empty cell, select the text numbers, choose Paste Special > Add. Excel adds zero to each, which converts them into real numbers.

7. Skip blanks

Tick Skip blanks to stop empty cells in the copied range from overwriting data where you paste. Handy when merging an updated list with gaps into an existing one.

Bonus: Paste as a linked picture

In the right-click Paste Options, the Linked Picture icon pastes a live image of the cells. It updates whenever the original changes, and it's great for building dashboards or showing a table from another sheet.

Troubleshooting

Paste Special is greyed out. You used Cut (Ctrl + X) rather than Copy. Paste Special only works with Copy.

Paste Special opens a different box with "HTML" or "Unicode Text". You copied from outside Excel, such as a web page or Word. Choose Text or Unicode Text to paste without formatting.

Ctrl + Shift + V doesn't work. Your version of Excel doesn't have it yet. Use Ctrl + Alt + V, V, Enter instead.

Wrapping up

Learn Ctrl + Alt + V. Paste Values to strip formulas, Formats to copy a look, Column widths to fix squashed tables, and Multiply when you need to change a whole range at once.