Why Paste Special exists
A regular paste (Ctrl+V) copies everything — values, formulas, formats, validation rules, comments. Usually that is what you want. Sometimes it is not. Paste Special lets you choose exactly what to paste: just the value the formula produced, just the formatting without the content, just the formula without the format, or the validation rules without anything else.
How to access it
After copying (Ctrl+C), press Alt+E S to open Paste Special (works in all Excel versions). Or right-click the destination cell → Paste Special. Or use the Ctrl+Alt+V shortcut. The dialog shows twelve paste options and four operation options.
Paste Values only
The most frequently used Paste Special option. Shortcut: Alt+E S V Enter. Pastes the result of the formula rather than the formula itself. Essential when: you want to break a formula link and keep the current value, you are copying calculated results to a different workbook that does not have the source data, or you want to freeze a snapshot of values that currently update dynamically.
Paste Formats
Shortcut: Alt+E S T Enter. Pastes only the formatting — colours, fonts, borders, number formats, conditional formatting — without changing the cell content. Useful for applying a consistent style from one table to another without overwriting data. Equivalent to Format Painter but usable on non-adjacent ranges.
Paste Formulas
Shortcut: Alt+E S F Enter. Pastes the formula without the source formatting. Useful when you want a formula from a formatted source range applied to a destination range with different formatting — the formula arrives clean, without bringing its original cell colours or borders.
Paste Validation
Shortcut: Alt+E S N Enter. Pastes only the data validation rules — dropdown lists, number restrictions, error messages — without affecting the cell content or formatting. Useful when you have carefully configured validation in one cell and want to apply the same rules across a range without rebuilding them.
Transpose while pasting
Transpose flips rows and columns. Copy a horizontal row of data; paste transposed to get a vertical column. Copy a vertical list; paste transposed to get a horizontal row. In the Paste Special dialog, check the Transpose checkbox at the bottom right before clicking OK. This is faster and cleaner than manually rearranging data.
Arithmetic paste operations
The Operations section of Paste Special allows arithmetic on paste. Copy a cell containing a number, then paste with Add to add that value to all destination cells, Subtract to subtract it, Multiply to multiply all cells by it, or Divide. Example use: you have a column of prices in USD and want to convert them to INR. Put the exchange rate in one cell, copy it, select the price column, Paste Special → Multiply. All prices are converted without any formula column needed.
Paste as Link
Shortcut: Alt+E S L Enter. Creates a formula in the destination that references the source cell — whenever the source changes, the destination updates automatically. Different from a regular copy because it maintains a live connection. Useful for building summary dashboards that pull data from detailed data sheets.
`