You need to paste data in Excel without bringing formulas, formatting, or column layout. The standard paste command copies everything, which can break your workbook. Paste Special gives you precise control over what gets pasted. This article explains how to use its three most common operations: Values Only, Format Only, and Transpose.
Key Takeaways: Paste Special Operations
- Paste Special > Values: Pastes only the results of formulas, removing the formulas and links from the source.
- Paste Special > Formats: Copies only cell formatting like colors, borders, and number styles to another range.
- Paste Special > Transpose: Switches rows to columns or columns to rows during the paste operation.
What Paste Special Does
Paste Special is a feature in Excel that lets you choose specific elements from the copied cells to paste. Unlike the standard Ctrl+V command, it separates content like formulas, values, formatting, and column widths. You must copy cells first before the Paste Special dialog becomes available. This tool is essential for cleaning data, applying consistent styling, and restructuring tables without manual rework.
Steps to Use Paste Special
The methods below use keyboard shortcuts and the right-click menu. The Paste Special dialog offers the same options regardless of how you open it.
Paste Values Only
Use this to convert formulas to static numbers or to break links to other sheets.
- Copy the source cells
Select the cell or range with formulas. Press Ctrl+C to copy. You will see a moving border around the selection. - Select the destination
Click the top-left cell where you want the values to appear. - Open Paste Special
Right-click the destination cell and choose Paste Special from the menu. Alternatively, press Alt+E, then S to open the dialog. - Choose the Values option
In the Paste Special dialog, select the ‘Values’ radio button. Click OK. The pasted data will be static numbers or text.
Paste Format Only
This copies cell appearance like fill color, font, and borders without changing the cell content.
- Copy a cell with the desired format
Select a cell that has the formatting you want to duplicate. Press Ctrl+C. - Select the target cells
Highlight the cell or range you want to reformat. - Apply the format paste
Right-click the selection, choose Paste Special, and then select the ‘Formats’ radio button. Click OK. The target cells will update to match the source formatting.
Paste with Transpose
Transpose rearranges data by switching the orientation of rows and columns.
- Copy the original table
Select the range of data you want to rearrange. Press Ctrl+C. - Choose a new starting cell
Select a single cell where the top-left corner of the transposed data should go. Ensure there is enough empty space for the rearranged data. - Open Paste Special and transpose
Right-click the destination cell and select Paste Special. In the dialog, check the ‘Transpose’ box at the bottom. Click OK. Your rows will become columns and columns will become rows.
Common Mistakes and Limitations
Avoid these errors to use Paste Special effectively.
Paste Special Options Are Grayed Out
The Paste Special menu is unavailable if you have not copied anything first. Always use Ctrl+C on a cell or range before right-clicking the destination. The command may also be disabled if you are editing a cell directly in the formula bar.
Transpose Does Not Work on Merged Cells
Excel cannot transpose a range that contains merged cells. You will get an error message. Unmerge all cells in the source range before copying. Use the Merge & Center button on the Home tab to unmerge.
Pasting Values Overwrites Formulas Permanently
When you paste values over existing formulas, the formulas are replaced with static results. This action cannot be undone with the Undo command if you perform other operations after pasting. To be safe, paste values into a new, empty range first.
Paste Special Operation Comparison
| Item | Paste Values | Paste Formats | Paste Transpose |
|---|---|---|---|
| Primary Use | Remove formulas, create static data | Copy cell styling like colors and fonts | Switch rows to columns or columns to rows |
| Keyboard Shortcut | Alt+E, S, V, Enter | Alt+E, S, T, Enter | Alt+E, S, E (check Transpose), Enter |
| Changes Source Links | Breaks all cell references and links | Does not affect cell content or links | Maintains formulas but adjusts references |
| Best For | Finalizing reports, sharing data | Standardizing the look of multiple sheets | Reformatting data for charts or analysis |
You can now paste only values, formats, or transpose data in Excel. Try using the keyboard shortcuts Alt+E, S to open the Paste Special dialog faster. For advanced control, use Paste Special to perform operations like Add or Multiply on a range of values.