When you copy cells in Excel, the user can choose how the copy inserts. I often encounter questions as how copying only the result value, not to change the column width, to transfer only the formatting etc. And all these problems are solved by a single choice – Paste special. Check out the article on the most common methods of insertion. If this guide has helped you, become a fan on Facebook and recommend this site to your friends, it can be useful for them too.
Firstly, we copy a cell or range of cells by a command, for example CTRL + C. Most often we put the copy into the cell by Ctrl + V, but it puts everything into destination – formulas / value, formatting, data validation, and other properties of the original source. So if you want to only insert something, you must select the correct option of the Paste spacial option.
- Home tab > Clipboard group > Paste > Paste special
- or right mouse menu
- version 2010 offers us using different icons of paste options, but that option Paste special is better …
Description of the most interesting options in Paste special is listed in a this list:
- All … inserts all of the copied region (except for column widths)
- Values … only insert the result value without formatting and formulas
- Formatting … inserts only properties of the format (format numbers, colors, borders, conditional formatting …)
- Comments … inserts only comments from the copied cell
- Validation … inserts only the validation conditions of a Data validation (loceated on Data tab)
- Column width … inserts everything including the width of columns of copied cell
- Transpose … swap rows and columns copied from the range (rotated by 90 degrees)
- Paste link … creates a formula in a target cell with an absolute reference to the source