Paste Special: The Hidden Secrets Within

AUTHOR:  JASON MORRELL 

Jason Morrell

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.

Please feel free to post any questions you have into the comments below. And before you leave this page, I want to give you one quick little action item! 

ACTION ITEM: Comment below with ONE thing you picked up from this post and share how it has helped, or will help, you. It's good to share your successes because it encourages others and provides an instant boost.

Enjoy the rest of your day!

Jason Morrell blog signature
About Jason Morrell

About the author

Jason Morrell


Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!

SHARE

Check the RELATED articles below

Read More
10 Steps to a Perfect Print Page Setup in Excel With Auto-Repeat Headings and Controlled Page Breaks
Read More
Excel Auditing Checklist: 2 Auto Features and 4 Tools to Quickly Identify and Rectify Errors BEFORE You Publish
Read More
Supercharge Your Cut and Paste: Popular Methods, Shortcuts and 2 Smarter Approaches
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
Office Mastery
>