Paste Special in Excel

When you copy cell data, there are many ways it can be pasted. Some examples include:

  • Transpose Data: Copy columns to rows or vice versa.
  • Paste Values: Paste the results or values from a formula instead of copying and pasting the formulas themselves.
  • Paste Formats: Copy formatting from a cell or cell range and paste—or apply it elsewhere.

Transpose Data

Transposing data changes columns of copied data to rows, and vice versa.

  1. Select a cell range.
  2. Click Copy.
    Transpose Data
  3. Click the cell where you want to paste the data.
  4. Click the Paste list arrow.

    To preview a paste option before you commit to it, click the Paste list arrow and hold the mouse over the paste option.

  5. Select Transpose.
    Transpose Data

The selected data is transposed from columns to rows.

Paste Values

You can paste only the values as displayed in the cells, instead of the formulas that are used to calculate those values.

  1. With a cell containing a value you want to copy selected, click Copy.
  2. Click the cell where you want to paste the data.
  3. Click the Paste list arrow.
  4. Select Values.
    Paste Values

Excel pastes the values of the copied cell into the selected cell.

Whenever you paste data, the Paste Options Smart Tag appears next to the selected cell. You can also paste values by clicking this tag and selecting a paste option from the list.

Paste Formats

You can also copy and paste cell formatting, applying formats such as a cell’s font, font size, and color to other cells.

  1. Select the cell that has the format you want to copy.
  2. Click the Copy button.
  3. Click the cell where you want to paste the format.
  4. Click the Paste list arrow.
  5. Select Formatting.
    Paste Formats

Excel applies the formatting of the copied cell to the selected cell.

Advanced Paste Special Commands

If the paste commands you’re looking for aren’t available in the Paste menu, you can open the Paste Special dialog box to view advanced paste special commands.

  1. Copy a cell value.
  2. Click the cell where you want to paste the value.
  3. Click the Paste list arrow.
  4. Select Paste Special.

    5. The Paste Special dialog box displays, containing numerous paste options.

  5. Select a paste option.
  6. Click OK.
    Advanced Paste Special Commands

Paste Special Options

The table below describes Excel's available Paste Special options.

Paste Special Options
Paste Paste Pastes all cell contents and formatting.
Formulas Pastes only the formulas as entered in the formula bar.
Formulas & Number Formatting Pastes both the formulas as entered in the formula bar and the copied cell’s number formatting.
Keep Source Formatting Pastes all cell contents and formatting, including the theme, if one was applied to the source data.
No Borders Pastes all cell contents and formatting applied to the copied cell except borders.
Keep Source Column Width Pastes the width of the source cell’s column to the destination cell’s column.
Transpose Changes columns of copied data to rows, and vice versa.
Paste Values Values Pastes only the values as displayed in the cells.
Values & Number Formatting Pastes both the copied cell’s values and number formatting.
Values & Source Formatting Pastes cell values and formatting, including the theme, if one was applied to the source data.
Other Paste Values Formatting Pastes only a cell’s formatting, the same as the Format Painter..
Paste Link Links the pasted data to the source data by pasting a formula reference to the source data.
Picture Pastes a picture from your Clipboard.
Linked Picture Pastes a picture from a website as a link that you can click to view.
Excel Cheat Sheet

FREE Quick Reference

Click to Download

Free to distribute with our compliments; we hope you will consider our paid training.