Reading Time: minutes remaining

Paste Special: The Hidden Secrets Within

Jason Morrell

by Jason Morrell 
April 12, 2020

In this post you'll discover different ways that the Paste Special tool in Excel can be used to give you more control over what happens to your data.

1. What is Paste Special?

With an ordinary cut / copy / paste operation, not only are the original cell contents pasted to the destination cell(s), but so is all the formatting, such as borders, shading and column widths.

The Paste Special feature allows the values, formulas or formats of the original cell(s) to be pasted individually. Therefore, the user has far greater control over what is pasted.

To use the Paste Special feature:

  • Copy the source cell(s).
  • Click the large Paste icon on the left side of the Home ribbon, or right-click the destination cell.
  • The Paste Special gallery appears offering the most common Paste options. Once you have used this feature a few times, you can use these icons directly. Hover each to display a screen tip that tells you the function of the icon.
  • When you are new to this feature, it is easier to use the text-based descriptions. To display these, hover over the Paste Special … text on the menu, then when the submenu appears, click Paste Special at the bottom of the list.
The Paste Special icons and dialog box

2. Choosing which component(s) to paste

The Paste section of the dialog box lists each component of the original cell(s). Only one option may be selected. All is the default and will paste the contents (formulas or values), formatting (borders, shading, fonts etc.), number formats, comments, validation and column widths. This is what a normal Paste operation does.

Choosing which component to paste
  • Select a component to apply to the destination cell by clicking an option button in the Paste section of the dialog box. If more than one option is required, then Paste Special must be used twice, each time selecting one option. For example, the first Paste Special might paste the Formulas and number formats while the second Paste Special might paste the Column widths.

3. How to create a link between the copied and pasted data

If All or All except borders is selected from the Paste section of the Paste Special dialog box, then the Paste Link button becomes available. If this button is clicked instead of OK then a link is created between the original data and the pasted data. This means that if the original data changes, the change is reflected in the pasted data.

4. Using Paste Special to consolidate data

The second section of the dialog box titled Operation allows the contents of the source cell to be combined with the existing contents of the destination cell.

For example, if the source cell contains the value 5 and the destination cell currently contains 10 and Add is chosen from the Paste Special dialog box, the destination cell will then have the value 15.

Using paste special to consolidate data

5. Transposing data using Paste Special

If a table is transposed, the column headings become the row headings and the row headings become the column headings. The data shifts around automatically.

In the Paste Special dialog, check/tick the box labelled Transpose.

Transpose data from  horizontal to vertical

Transposing data using paste special

Transpose data from vertical to horizontal

Transposing data using paste special

Transpose the entire table (rows become columns and vice versa)

Transpose the entire table (rows become columns and vice versa)

6. Key Takeaways

  • Paste Special lets you paste just one aspect or component of the item that you copied, such as formulas, values, formatting, comments, validation, column widths.
  • The Operations section of the Paste Special dialog box lets you consolidate data.
  • You can use Paste Special to transpose data, i.e. swap it from columns to rows or vice versa.

I hope you found plenty of value in this post. I'd love to hear your biggest takeaway in the comments below together with any questions you may have.

Have a fantastic day.

Jason Morrell blog signature
About Jason Morrell

About the author

Jason Morrell

Jason Morrell is a professional trainer, consultant and course creator who lives on the glorious Gold Coast in Queensland, Australia.

He helps people of all levels unleash and leverage the power contained within Microsoft Office by delivering training, troubleshooting services and taking on client projects. He loves to simplify tricky concepts and provide helpful, proven, actionable advice that can be implemented for quick results.

Purely for amusement he sometimes talks about himself in the third person.


more from

Office Mastery logo
Read More
How to Consolidate Data in Excel: 2 Easy Ways to use Excel’s Consolidation Tool
Read More
Excel Screen: A Beginners Tour


{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}