How to Use Paste Special in Excel & Google Sheets

Written by

Editorial Team

Reviewed by

Laura Tsitlidze

Last updated on November 30, 2023

This tutorial demonstrates how to use Paste Special in Excel and Google Sheets.

paste special start

Open Paste Special Dialog Box

PC Shortcut:Ctrl+Alt+V
Mac Shortcut:++V

See All Excel Shortcuts

  1. First, copy the cell(s) you want to paste.
  2. Then in the Ribbon, go to Home > Clipboard > Paste Special (or use the shortcut CTRL + ALT + V).

paste special 2

This opens the Paste Special dialog box.

paste special 1

Paste Special allows you to paste more than just the contents of a cell – it allows you to paste just the formatting of the cell, or just the values of the cell. It also allows you to transpose data and paste data while skipping blanks among other features.

Paste Formulas

  1. First, select the cell that contains the formula you wish to copy.

paste special excel copy

  1. Then, select the cells where you wish to paste the formula, and in the Paste Special dialog box, choose Paste > Formulas and then click OK.

paste special excel formulas

  1. Alternatively, in the Ribbon, click the drop-down arrow under Paste, and then click the Paste Formulas icon.

paste special 5

  1. Your formula is copied down to the selected cells without any formatting.

Paste Values

To paste just the values, without any formulas, choose Paste > Values from the Paste Special dialog box or choose the Paste Values icon from the drop down.

paste special excel values

Paste Formats

  1. To paste the format of selected cells, first, copy the cells whose formats you wish to use.

paste special excel copy format

  1. Then, select the cells where you wish to paste the format, and choose Formats in the Paste Special dialog box or click the Paste Formats icon from the Paste drop down in the Ribbon.

paste special excel format

Paste Comments and Notes

  1. Copy the cell that contains the comment or note that you want to paste.

paste special 10

  1. Then in the Ribbon, go to Home > Clipboard > Paste Special and choose Comment and Notes.

paste special 11

  1. Click OK to paste the note only.

paste special 12

Paste Data Validation

  1. Copy the cell that contains the data validation rule(s) that you wish to apply to other cells.

paste special excel validation

  1. Highlight the cells where you wish to apply the validation, and then in the Paste Special Dialog box, choose Validation.

paste special excel validation paste

  1. The data validation is now applied to all selected cells.

paste special excel validation pasted

Operations

Add Values

Add Values increases all the numbers in a range of cells by a set amount.

  1. Copy the cell that contains the value that you wish to increase the range of cells by.

paste special excel value

  1. Highlight the cells where you wish to increase the value and then, in the Paste Special dialog box, choose Values; from Operation, choose Add.

paste special values add

  1. Click OK. The numbers in each cell increase by the pasted amount.

paste special values added

Subtract Values

You can apply the same concept to subtract a set value from each cell of data.

paste special values subtract

Multiply and Divide Values

You can also multiply or divide the data in each cell by a set value.

  1. In the Paste Special dialog box, go to Values, then Multiply or Divide.

paste special excel multiply divide

  1. Because this example uses Multiply, this command multiplies each value in the paste range by the number copied – in this instance, 10.

paste special excel multiplied

Paste Without Borders

  1. Select the cells you wish to copy and press CTRL + C.
  2. Then, select the cell where you wish to paste the data and, in the Paste Special dialog box, choose All except borders.

paste special 22

The data is pasted with all values, formulas, and formatting other than borders.

paste special excel no borders

Paste Column Widths

  1. Copy the cells in the column that has the desired width (or copy the entire column).

paste special excel copy width

  1. Then, select the columns where you wish to copy the column width to, and then in the Paste Special dialog box, choose Column Width.

paste special excel paste width

  1. Click OK to paste the column width.

paste special excel values

Paste Without Blanks

If you want to replace data but don’t want to overwrite some existing data, you can paste data while skipping blanks.

Consider the following data:

paste special excel blank lines

Highlight the data you wish to copy and click Copy. Then select the first destination cell, and in the Paste Special dialog box, choose Values and tick the Skip blanks checkbox.

paste special excel paste blanklines

The data is pasted into the selected destination, but it doesn’t replace data that was already there. In this example, only the values in C4, C6, C8, C10, C13, and C15 are replaced.

paste special excel pasted blanklines

Paste and Transpose Data

Transpose allows you to copy data from a row and paste it into a column, or vice versa.

  1. First, select the row or column of data you wish to copy.
  2. In the Ribbon, go to Home > Clipboard > Copy.

paste special copy transpose

  1. Select the destination cell and in the Paste Special dialog box, choose Values and tick the Transpose checkbox.

paste special paste transpose

The selected cells are copied from a row into a column.

paste special pasted transpose

Paste Special From Other Sources

AutoMacro | Excel Automation Made Easy | Free Trial

Paste Special From a Web Browser

  1. In the web browser, highlight the text you wish to paste into Excel. Right-click and choose Copy.

paste special web copy

  1. In Excel, click the first cell where you want to paste the data. In the Ribbon, go to Home > Clipboard > Paste > Paste Special.

paste special excel paste special

  1. To paste the web data as text, choose Text, and then click OK.

paste special excel paste text

This pastes the copied content into Excel as text. If a cell contains a number value, Excel automatically recognizes it as a numerical cell.

paste special excel pasted

Paste From Microsoft Word

When you paste data from Word into Excel, you have a few options in Paste Special.

  1. Copy the information that you need from Word to the Clipboard.
  2. Then in the Excel Ribbon, go to Home > Clipboard > Paste > Paste Special.

paste special word paste special

  • If you paste as a Microsoft Word Document Object, you can double-click on the object to edit it within Word.
  • If you paste as a Picture (Enhanced Metafile), then the data comes in as an image. If you resize the image, the text and graphics in the image are also resized.
  • If you paste as HTML, Unicode Text, or Text, then the data is pasted into Excel as normal text.
  • If you paste as a Hyperlink, you get a link that opens the Word file that the data was pasted from.
  • If you use Paste link instead of Paste, you can’t edit the data in Excel. However, if you edit the table in Word, then the Excel sheet gets updated as well.

Paste Special in Google Sheets

Google Sheets has most of the same options as Excel.

Paste Values

Copy the range of cells, and then select the destination cell. In the Menu, go to Edit > Paste special > Values only.

paste special gs values

AutoMacro | Excel Automation Made Easy | Free Trial

Paste Format

Copy the cells whose format you wish to obtain and then highlight the destination cells. In the Menu, go to Edit > Paste specials > Format only.

paste special gs format

Paste Formula

Copy the cell that contains the formula you wish to paste. Select the destination cells and then, in the Menu, go to Edit > Paste special > Formula only.

paste special gs formula

Paste Conditional Formatting

  1. Select the cells that contain the conditional formatting rules(s) you wish to copy, and then in the Menu, go to Edit > Copy.

paste special gs conditional formatting

  1. Highlight the cells where you wish the conditional formatting to be copied to, and then in the Menu, go to Edit > Paste special > Conditional formatting only.

paste special gs conditional formatting paste

The conditional formatting rules that were applied to the copied cells are pasted to the destination cells. It changes that range’s formatting, but not its values or formulas.

paste special gs conditional formatting pasted

Paste Data Validation

  1. Select a cell that contains data validation, and then click Copy.

paste special gs data validation

  1. Select the cells where you wish to paste the data validation rule(s), and then in the Menu, go to Edit > Paste Special > Data validation only.

paste special gs data validation paste

Data validation rules are copied over to the selected cells.

paste special gs data validation-pasted

AutoMacro | Excel Automation Made Easy | Free Trial

Paste Transposed

Once you have copied the cells that you wish to transpose, select the destination cell. In the Menu, go to Edit > Paste special > Transposed.

paste special gs transpose

Your copied cells are transposed to the selected destination cells.

paste special gs transposed

Paste Column Width

Copy the cells that contain the desired column width and then select a cell in the column that you wish to adjust.

In the Menu, go to Edit > Paste special > Column width only.

paste special gs column width

Paste All Except Borders

Copy the data you wish to paste without the column width and then select the destination cell.

In the Menu, go to Edit > Paste special > All except borders.

paste special gs borders

The data is pasted exactly as it is, just excluding any borders.

paste special gs no borders

Read more of our tutorials on using Paste Special in Excel and Google Sheets.

Paste Special
Calculationyes
Add Values to Cells and Columns
Change the Signs of Values
Divide Cells and Columns
Multiply Cells and Columns
Subtract Cells and Columns
Commentsyes
Copy Comments
Conditional Formattingyes
Apply Conditional Formatting Multiple Sheets
Apply Conditional Formatting to Multiple Rows
Copy Conditional Formatting
Copy & Pasteyes
Copy and Paste a Range or Object as a Picture
Copy and Paste as Text or Value
Copy and Paste Cells
Copy and Paste With Skip Blanks
Copy and Paste Without Borders
Copy and Paste Without Changing the Format
Copy Number Not Formula
Excel Cannot Paste the Data Error
Paste & Match Destination Formatting
Paste Horizontal Data Vertically
Transpose Rows to Columns
Databaseyes
Clean Up Data
Duplicatesyes
Show Only Duplicates
Format Cellsyes
Copy Cell Color
Copy Column Widths
Copy Row Height
Linksyes
Cross-Reference
Number Formatsyes
Move Decimal Places
Organize Sheetsyes
Pull Data From Multiple Sheets and Consolidate
Save Asyes
Create a Word Document
Save a Table as an Image
Shortcutsyes
Excel Keyboard Shortcut For Paste Special
Sort Datayes
Sort IP Addresses
Tablesyes
Rotate Data Tables (Row to Column)
Text to Columnsyes
Split a Text Cell to Two or More Rows
VBAyes
Cut, Copy, & Paste from a Macro
Excel VBA Ranges and Cells
VBA Value Paste & PasteSpecial

AI Formula Generator

Try for Free

See all How-To Articles