Paste Special in Excel: Values, Formats and More

Paste special values in Excel pastes only the results of your cells, not the formulas behind them. Copy, then press Ctrl+Alt+V (Mac: Control+⌘+V), choose Values and press Enter. This guide is for anyone who cleans data, sends reports or takes Excel tests, and it covers the other Paste Special options that save time too: formats, column widths, transpose and the maths operations.

What is Paste Special in Excel?#

Paste Special is a dialog box that lets you choose which part of the copied cells to paste. A normal paste (Ctrl+V) brings everything: the value or formula, the number format, fonts, fills, borders and data validation.

Sometimes you only want part of that. You might want the numbers without the formulas, or the formatting without the numbers. Paste Special gives you that choice.

There are three ways to open it after copying:

How to paste values only#

Paste values is the Paste Special option you'll use most. It replaces formulas with their results.

1. Select the cells and copy them with Ctrl+C (Mac: ⌘C).
2. Click the first cell where the values should go.
3. Press Ctrl+Alt+V (Mac: Control+⌘+V) to open Paste Special.
4. Press V (or click Values), then press Enter (Mac: Return or click OK).

Windows has a few faster routes:

• Alt, H, V, V: Home tab > Paste > Values, pressed one key after another.
• Ctrl, then V after pasting: paste normally, press Ctrl to open the Paste Options button, then press V for Values.
• Ctrl+Shift+V: in recent Microsoft 365 versions on Windows, this pastes values when you copy cells inside Excel. It isn't in one-time-purchase versions such as Excel 2021, so learn Ctrl+Alt+V too.

To convert formulas to values in place, copy the range and paste values on top of the same range without moving the selection.

When to paste values instead of a normal paste#

Paste values when the numbers must stop depending on other cells. The most common cases:

• After cleaning data with a helper column. You write =PROPER(TRIM(A2)) in column B, fill it down, then paste the results as values over column A and delete column B. The exercise on this page is the first step of that workflow.
• Before deleting the source. If B2 says =A2*1.2 and you delete column A, B2 becomes a #REF! error. Paste values first.
• When sending a report. Values can't break when someone moves a sheet, and they don't reveal how a number was worked out.
• When a formula should stop changing. For example, a time stamp or random sample you want to keep.

Here is a cleaning example:

Fill B2 down with Ctrl+D (Mac: ⌘D), as the guide to Fill Down and Fill Right shows. Then copy B2:B6, click A2 and paste values. The clean names replace the messy ones, and column B can go.

Paste Special options explained#

The Paste Special box has more than a dozen options. These are the ones worth knowing:

Copying a formula with Formulas keeps relative references moving, just like a normal paste. If you need the references to stay fixed, read how to copy a formula without changing its references and the guide to cell references.

Use Paste Special to multiply, divide, add or subtract#

The Operation section applies one number to a whole range of values in a single step, with no helper column.

Example: prices in B2:B20 must rise by 5%.

1. Type 1.05 in an empty cell and copy it.
2. Select B2:B20.
3. Open Paste Special, choose Values and Multiply, then click OK.

Every price is now 5% higher, and you can delete the 1.05. Other uses:

• Divide by 1000 to show figures in thousands.
• Multiply by -1 to flip the sign of refunds or credits.
• Multiply by 1 to turn numbers stored as text into real numbers, so SUM counts them.

Choose Values together with the operation if the target cells contain numbers, or Excel may also paste the format of the cell you copied.

Transpose, Skip blanks and Paste Link#

Three checkboxes and buttons at the bottom of the box are useful too:

• Transpose flips the copied range: rows become columns and columns become rows. Copy A1:D2, tick Transpose, and paste it into a 2-column, 4-row block.
• Skip blanks leaves the destination unchanged where the copied cell is empty, so blanks don't wipe out existing data.
• Paste Link pastes formulas that point back to the copied cells, such as =Sheet1!B2, so the destination updates when the source changes.

Common Paste Special mistakes#

These are the mistakes that cost people the most time:

1. Pasting values over formulas you still need. The formulas are gone once you save and close. Undo straight away with Ctrl+Z (Mac: ⌘Z) if you notice, or keep a copy of the sheet.
2. Losing number formats. Values alone can turn 12.5% into 0.125 and dates into numbers like 45658. Use Values and number formats, or reapply the format.
3. Pasting into a filtered list. Excel copies only the visible rows from a filtered list, but pasting into a filtered range can overwrite hidden rows. Clear the filter or paste somewhere else first.
4. Pasting values into a range of a different size. The pasted block overwrites whatever is next to it. Check the destination is empty.
5. Forgetting Values with an operation. Multiply with All selected can paste the source cell's format onto every target cell.

If you rearrange a sheet after pasting, our guide to inserting and deleting rows and columns shows how to do it without breaking formulas. For bulk text fixes before you paste values, Find and Replace in Excel is often faster than a formula. And if you're unsure which paste to use, see Paste vs Paste Values.

Practise it#

Paste Special builds on the basics of copying. Practise copy, cut and paste in a live sheet and watch how formula references move when you paste. Then try the exercise on this page: clean the names with TRIM and PROPER, which is the step you take before pasting the results as values.