Paste Special in Excel: how it works

With Paste Special you paste only what you need: the values without formulas, just the formatting, or rows and columns swapped. Press Ctrl + Alt + V after copying.

Try the first exercises for freeIn the lesson Paste Special, inserting and deleting

What does Paste Special do?

When you paste normally, Excel takes everything with it: the formula, the formatting, the borders and the colours. With Paste Special, you choose yourself what comes along. The best-known choices are Values, which only pastes the results, Formats, which only copies the colours and number formats, and Transpose, which swaps rows and columns.

Pasting values is especially handy if you want to replace a formula with its result, for example before you send a list on, or if you want to delete the data the formula was calculating with.

When do you use Paste Special?

You want to send on the results of formulas, without the formulas and references behind them.
You've cleaned up a column with TRIM and want to delete the old column.
You only want to copy the formatting of one table to another table.
You want a list that's in a row to go in a column: Transpose.

Pasting values, step by step

This is how to go about it:

Select the cells with the formulas and copy them with Ctrl + C, or Cmd + C on a Mac.
Click the cell where they should go. That can also be the same place, to replace the formulas with their results.
Choose Home > Paste and click the Values icon, or press Ctrl + Alt + V, or Ctrl + Cmd + V on a Mac, and choose Values.
Click a pasted cell: the formula bar now shows the number itself, without a formula.

Transposing

Choose Transpose when you paste, and what was in rows ends up in columns, and the other way round.

Where it often goes wrong

Pasted values no longer recalculate. If the source changes, they stay as they were.
If you just paste with Ctrl + V, you bring everything along: formulas, formatting and the borders and colours of the source.

Want to try it yourself?
Practise this in the lesson Paste Special, inserting and deleting. The first exercises are free.

Try the first exercises for free

Frequently asked questions

How do I paste without changing the formatting?

Choose Paste > Values, or Paste > Formulas if you want to keep the formulas. The formatting of the cells you paste into then stays as it is.

Can I calculate with Paste Special?

Yes. In the Paste Special window you can choose Multiply, for example: copy a cell containing 1.2 and multiply a whole column of amounts by it in one go.