Home › Excel basics › Paste Special
Paste Special in Excel: Shortcuts and Practical Uses
A normal paste copies everything: values, formulas, formatting and comments. Paste Special lets you choose exactly what to paste, and can even do a calculation or flip rows into columns while it pastes.
Excel CSE · 4 min readIn this guide
How to open Paste Special
First copy the cells with Ctrl+C (Mac: Cmd+C), select where you want to paste, then:
| Windows | Mac | |
|---|---|---|
| Keyboard shortcut | Ctrl+Alt+V | Cmd+Ctrl+V |
| Ribbon | Home › Paste (the arrow under the button) › Paste Special… | |
| Right-click | Right-click the target cell › Paste Special… | |
The dialog that opens has two parts: Paste (what to paste) and Operation (an optional calculation), plus the checkboxes Skip blanks and Transpose.
The paste options
| Option | What it pastes |
|---|---|
| All | Everything, like a normal paste. |
| Formulas | Only the formulas (or values), without formatting. |
| Values | Only the results, as fixed values. Formulas are gone. |
| Formats | Only the formatting: fonts, colours, borders, number formats. |
| Comments and Notes | Only the comments and notes attached to the cells. |
| Validation | Only the data validation rules, such as drop-down lists. |
| All except borders | Everything except the borders. |
| Column widths | Only the widths of the copied columns. |
| Formulas and number formats | Formulas plus number formats such as currency or dates. |
| Values and number formats | Results plus number formats, without other formatting. |
Paste values only (the one you'll use most)
Pasting values turns formulas into fixed numbers. That's useful when you want to send a sheet without your formulas, freeze the results of a calculation, or clean up data before deleting the source columns.
- Copy the cells with formulas.
- Select the destination (or the same cells, to replace the formulas in place).
- Press Ctrl+Alt+V, then V, then Enter.
On a Mac, press Cmd+Ctrl+V, choose Values and click OK. You can also use the Paste Options button that appears after a normal paste and choose the 123 icon.
Do maths while you paste
The Operation section applies a calculation to the cells you paste onto, using the copied value. Classic example: raise all prices by 10%.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Price | After | |
| 2 | Apple | $0.35 | $0.39 | 1.1 |
| 3 | Banana | $0.25 | $0.28 | |
| 4 | Mango | $1.20 | $1.32 |
- Type 1.1 in an empty cell (D2) and copy it.
- Select the prices in B2:B4.
- Open Paste Special, choose Values under Paste and Multiply under Operation, and click OK.
Every price is multiplied by 1.1 in place, with no helper column or formula needed. Column C shows the results here for comparison. The same trick works with Add, Subtract and Divide, for example to divide a column of amounts by 1,000.
Convert numbers stored as text into real numbers: copy an empty cell, select the "text numbers", and Paste Special with Add. Adding zero forces Excel to turn them into numbers. This often fixes VLOOKUP #N/A errors and SUM totals that ignore some cells.
Transpose: turn rows into columns
Check Transpose in the dialog to flip the copied range: rows become columns and columns become rows. Handy when data arrives horizontally and you need it vertically. Make sure the destination doesn't overlap the copied cells. For a version that stays linked to the original, see the TRANSPOSE function.
Transpose creates a static copy. If you need the result to update when the source changes, use the =TRANSPOSE(A1:D3) function instead.
More useful tricks
- Column widths: copy a nicely formatted table, select the new table's columns and paste Column widths to match the layout instantly.
- Skip blanks: pastes only the copied cells that have content, so empty cells don't overwrite existing values in the destination.
- Paste Link: the button at the bottom of the dialog pastes formulas like
=Sheet1!B2that keep pointing to the source. - Formats: works like the Format Painter, but for large or non-adjacent ranges.
Frequently asked questions
What is the shortcut for paste values in Excel?
On Windows, press Ctrl+Alt+V, then V, then Enter. On a Mac, press Cmd+Ctrl+V and choose Values.
Why is Paste Special greyed out?
You haven't copied anything in Excel yet, or the copy was cancelled (for example by pressing Esc or typing in a cell). Copy the cells again with Ctrl+C and try once more.
How do I paste values without changing the formatting?
Use Paste Special › Values. It pastes only the results and keeps the destination's existing formatting.
Can I undo Paste Special?
Yes, press Ctrl+Z (Mac: Cmd+Z) straight after pasting.