Skip to content
AnIntent

How to Paste Values Only in Excel (Paste Special and Skip Blanks)

Paste formula results as plain values in Excel on Mac or Windows, choose which values option keeps number formatting, and find Skip Blanks in Paste Special.

AnIntent Editorial · · 4 min read

Person working on a laptop with a spreadsheet outdoors
Photo by Gorilla ROI Data Connector on Unsplash
In this article
  1. Why a normal paste brings the formulas with it
  2. Paste values only from the Paste menu on Mac
  3. Paste values only from the Paste menu on Windows
  4. Which values option keeps your formatting
  5. Use the Paste Special dialog for Skip Blanks
  6. Make Paste Values one step on a Mac
Share this article

To paste values only in Excel, copy the cells, then on the Home tab open the Paste menu and pick Paste Values. You get the results of your formulas, not the formulas. The same menu, and the Paste Special dialog behind it, also lets you paste only formats or skip blank cells.

Why a normal paste brings the formulas with it

Microsoft's Paste options page says a normal copy and paste carries everything: data, formatting, formulas, validation and comments. The Mac page says the same about Command+C and Command+V. To leave something out, you have to pick a specific paste option. Any attribute you don't select is left behind.

Paste values only from the Paste menu on Mac

  1. Select the cells that hold the data you want to copy.
  2. On the Home tab, select Copy.
  3. Select the first cell where the data should go.
  4. On the Home tab, select the arrow next to Paste.
  5. Choose Paste Values.

The options you see depend on what kind of data you copied, according to Microsoft's Mac page, so your list may not match every option named here.

Paste values only from the Paste menu on Windows

Microsoft's Paste options page uses the clipboard icon as the way in:

  1. Copy the cells you want.
  2. Select the first cell where the data should go.
  3. On the Home tab, select the clipboard (Paste) icon.
  4. Choose Paste Values.

The same page lists Values & Source Formatting in that menu, and lists All, Values, All using Source theme and All except borders among the Paste Special choices.

Which values option keeps your formatting

There are three values options, and choosing the wrong one is the most common reason pasted numbers look wrong. This is how Microsoft's Mac page describes them:

Paste menu option What it pastes
Paste Values Only the values as displayed in the cells
Values & Number Formatting Only values and number formatting
Values & Source Formatting Only values and number colour and font size formatting

If you pasted values and your currency symbols, percentages or dates now look like raw numbers, undo and use Values & Number Formatting instead. It keeps the number format and still drops the formulas.

Microsoft's two pages describe Paste Values differently. The Mac page says "only the values as displayed in the cells". The Paste options page says "formula results, without formatting". Neither page explains how hidden decimals or number formats are handled. If exact precision matters, compare a few pasted cells with the originals before you delete the source. If you need a number turned into fixed text in a particular format, a formula like the one in our guide to the TEXT function gives you control over that.

Use the Paste Special dialog for Skip Blanks

The Paste menu doesn't show the Skip Blanks option. On a Mac, you need the full dialog:

  1. Select the cells and choose Home > Copy.
  2. Select the first destination cell.
  3. Choose Home, then the arrow next to Paste, then Paste Special.
  4. Under Paste, pick Values (or another option).
  5. Under Operation, leave it on None unless you want an operation.
  6. Tick Skip Blanks if you need it, then confirm.

The Mac page lists these Paste choices in the dialog: All, Formulas, Values, Formats, Comments, Validation, All using Source theme, and All except borders. The Operation choices are None, Add, Subtract, Multiply and Divide.

Skip Blanks stops blank cells in the copied range from replacing values or attributes already in the paste area. It's useful when you're pasting a partial update over a full column and don't want the gaps to wipe existing data.

Make Paste Values one step on a Mac

Microsoft says that in Excel for Mac 16.33 or higher you can add "paste formatting", "paste formulas" and "paste values" to the Quick Access Toolbar or assign them custom key combinations. That's the fastest way to make paste values a one-step action on a Mac.

If you paste formulas rather than values, the Paste options page warns that cell references may need fixing afterwards. Pasting values avoids that problem, since there are no references left to break. For flipping rows and columns while you paste, see how to transpose in Excel for Mac.

Common questions

Why did my numbers lose their currency or percentage formatting after Paste Values?

Microsoft's general Paste options page describes Paste Values as formula results without formatting. Use Values & Number Formatting instead to keep the number format while still dropping the formulas.

How do I paste values only in Excel on Windows?

Microsoft's Paste options page says to select Home, select the clipboard (Paste) icon, and pick the option you want. Choose Paste Values.

What does Skip Blanks do in Paste Special?

According to Microsoft's Mac page, it stops blank cells in the copied range from replacing values or attributes already in the paste area.

Can I make a keyboard shortcut for Paste Values on a Mac?

Yes. Microsoft says Excel for Mac 16.33 or higher lets you add paste values to the Quick Access Toolbar or assign it a custom key combination.

Why doesn't my Paste menu show every option?

Microsoft's Mac page says the options shown depend on the type of data in the cells you copied.

macbook pro on brown wooden table
Explainer

Windows 11 Reset This PC: Cloud Download vs Local Reinstall

What Cloud download and Local reinstall do in Windows 11 Reset this PC, which one to pick, and which option actually decides what gets deleted.

· 5 min

More in Software