Paste vs Paste Values in Excel

Paste vs Paste Values comes down to one question: do you want the formula, or only its answer? A normal paste copies the formula and its formatting, and the references move to fit the new cell. Paste Values copies only the result, as a fixed number or text. This guide is for anyone who has pasted a formula and seen a 0, a #REF! or a strange number appear, and wants to know which paste to use.

Paste vs Paste Values at a glance#

Normal paste brings the whole cell with it; Paste Values brings only what the cell shows, without the formula behind it.

What a normal paste copies#

A normal paste copies everything about the cell, and it treats a formula as a set of directions rather than a fixed address. =B2*C2 in D2 really means "the cell two to the left times the cell one to the left". Paste it into D3 and it becomes =B3*C3, which is exactly what you want when you copy a calculation down a column.

The same rule causes trouble when you paste somewhere the directions no longer make sense. Paste that D2 formula into F2 and it becomes =D2*E2: two columns to the left of F2 is D2, and one to the left is E2, which is empty. The result is 0. Our guide on how to copy a formula without changing its references explains the ways around this, and Fill Down and Fill Right covers the faster way to copy into neighbouring cells.

What Paste Values copies#

Paste Values pastes only the stored result of each cell, so the pasted cells hold plain numbers or text. There is no formula left to shift, break or recalculate. The values stay the same even if you later change, sort or delete the original data.

Two details catch people out:

• Formats are not pasted. Excel stores a date as a serial number, and Paste Values pastes that number. If the destination is formatted as General, 1 January 2025 shows as 45658. Format the cells as a date again, or use Values & Number Formatting instead.
• The full value is pasted, not the rounded one. If a cell shows 49.67 but holds =149/3, Paste Values pastes 49.666666… . Totals built on those pasted values can then be a cent or two away from what you expect. Round in the formula first, as in the exercise on this page.

How to paste values on Windows and Mac#

You paste values from the Paste Special box or from the Paste menu on the ribbon. Copy the cells first with Ctrl+C (Windows) or ⌘C (Mac), select the destination, then use one of these:

The Paste Special box has more choices worth knowing:

• Values & Number Formatting keeps dates, currency and percentages looking right.
• Formats pastes only the look (colours, borders, number formats), not the contents.
• Transpose flips rows into columns.
• Skip blanks stops empty source cells from overwriting existing values.

Recent Microsoft 365 builds on Windows also accept Ctrl+Shift+V for paste values. If it does nothing in your version, use Ctrl+Alt+V. For the full set of options, see our guide to Paste Special for values, formats and more.

The same copy, side by side#

Here is the same copy done both ways on one small order list. Column D holds =B2*C2, filled down.

You copy D2:D5 and paste into F2:F5.

With a normal paste#

F2 now holds =D2*E2. Every reference moved two columns right, so the formula multiplies the total by an empty cell. Column F shows 0, 0, 0, 0, and the cells still carry the currency format from column D.

With Paste Values#

F2 holds the number 98, F3 holds 298, F4 119.7 and F5 160. There is no formula in the formula bar. If you later change the desk lamp's price in B2, D2 updates and F2 does not. That is the point: F is now a snapshot of the totals.

When to use a normal paste#

Use a normal paste when you want the calculation itself, not just today's answer. It is the right choice when:

• You copy a formula down or across a table, so each row calculates from its own inputs.
• You copy a whole block, such as a summary layout, and want formulas and formatting together.
• The results must update when someone changes the inputs.
• You copy a lookup like VLOOKUP or XLOOKUP to more rows. Lock the table range with $ first; our comparison of VLOOKUP vs XLOOKUP shows which ranges to lock.

When to use Paste Values#

Use Paste Values when you need the result to stop depending on its formula. Typical cases:

• Freezing a snapshot, such as month-end totals that must not change when next month's data arrives.
• Removing helper formulas. After you clean names with TRIM or PROPER in a helper column, paste the clean column as values over the original, then delete the helper.
• Before sorting results that depend on row position. Formulas that use ROW or point at neighbouring cells can change when rows move; values never do. Our comparison of the Sort button vs the SORT function explains when sorting changes your data.
• Sending a file to someone who shouldn't see your formulas or links to other workbooks.
• Reducing recalculation in a slow workbook with thousands of lookups, such as the HLOOKUPs in our comparison of VLOOKUP vs HLOOKUP.

Common mistakes with Paste and Paste Values#

Most paste problems come from a few habits:

1. Pasting a formula far from its inputs. Relative references shift, so the copy reads the wrong cells. Check the formula bar after you paste.
2. Pasting values over your only copy of the formulas. Once saved, they are gone. Keep the formula version on another sheet if you might need it again.
3. Forgetting that Paste Values drops formats. Dates turn into numbers like 45658. Use Values & Number Formatting, or reformat the cells.
4. Expecting pasted values to be rounded. They keep every decimal Excel stores. Use ROUND in the formula before you freeze it.
5. Pasting over a filtered list. A paste into filtered rows can land in hidden rows too. Clear the filter, or paste into a new column first.
6. Cutting when you meant to copy. Cut (Ctrl+X or ⌘X) moves the cell and keeps its references; it is not the same as either paste.

Our recommendation#

Use a normal paste while you are building and copying calculations, and switch to Paste Values only at the moment you want results to stop changing. A good habit is to ask "should this cell update if the data changes?" If yes, paste normally. If no, paste values, and use Values & Number Formatting when the cells hold dates or currency.

In short: paste formulas while you work, paste values when you're done.

Practise it#

The exercise on this page builds a rounded total, so the value you would paste matches what you see. Then open the copy, cut and paste lesson to watch copied references move in a live sheet, see how cut keeps them the same, and predict what a pasted formula will add up before you press Enter.