All of us use the Paste Special dialog. However, it is underused and/or misused quite often.

This happens because we have never taken the effort to understand what really is Paste Special. This article will help you understand and use Paste Special more clearly.

There are two types of Copy Paste (CP) at a broad level. CP within the same application and CP across applications. For examples copying cells from Excel and pasting them into PowerPoint is CP across documents.

Both of these work differently and we must understand them separately.

Let us take Excel as an example to explore Paste Special.

CP within the same application

What can you copy from Excel? Cells, Charts, Shapes, SmartArt and other objects.

Let us focus on Cells. What can a cell contain? Well, the list is longer than you think.

1. Content (Formulas or values)
2. Comments
3. Formatting (manually done or conditional formatting)
1. Formatting is also of two types – number formatting and other formatting (color, background, bold, borders, etc)
4. Validations
5. Width of the cell ( it is actually width of the COLUMN – because individual cells in a column can hot have different sizes)

Remember, we are talking about copy pasting WITHIN excel.

When you copy cells, EVERYTHING in it is copied – all the above items.
When you paste it somewhere else, EVERYTHING is pasted.
This is the default.

So what is special about Paste Special?

The word ‘special’ may sound as though Paste Special means paste something MORE than what would get pasted otherwise. But here is the paradox…

Paste Special = Paste something less than pasting everything!

Therefore, Paste Special asks you what you want to paste.

For example, if you choose Paste Special Values – it will paste values ONLY.

This “ONLY” is the key to understanding Paste Special.

Paste Values means Paste ONLY Values and DO NOT paste comments, formatting and validations.

Once you understand that Paste Special is a combination of DOING something and NOT doing something, you can use it effectively.

Common problem: Paste Special Values disturbs dates

I am sure you have faced this before.

You had data like this, with formulas in column C.

You copied it and chose Past Special – Values. Now what happens?

The date formatting goes away and you see numbers instead of dates. Why did this happen?

Now you know! It pasted values ONLY and DID NOT PASTE formatting.
Dates look like dates because they have been FORMATTED as dates. And we told Excel NOT to paste formatting. So dates show the underlying numbers.

Most users get frustrated with this outcome and manually reformat the dates. But that is not required.

The solution

Microsoft realized that Paste Special Values leads to this side effect and therefore, they provided a COMBINATION of Pasting Values and Number Formatting.

That is what you need to use in the Paste Special Dialog.

What if you have already pasted as Values and missed the chance to choose Values and Number formatting?

No problem. You can still change the paste setting as an after-thought. Notice the Paste Options SmartTag (that icon which we always ignored). Click on it and choose Values and Number Formatting.

 

Key learning

Paste Special within the same application means – Paste something lesser than All.

By the way, there is no COPY SPECIAL in Office. Copy always copies EVERYTHING.

Why? Because to get the ability to PASTE exactly what you want, you need to Copy EVERYTHING!