According to study (which was conducted in a totally unscientific way with zero level of confidence…, i.e. my gut feeling), 100% of Excel users use Copy and Paste every time they work with Excel. 🙂  However, 99.99999% of them never used Clipboard.  Agree?

But indeed, Clipboard could be helpful in some occasions:

For example:

The top of mind thing should be something like selecting the cells and then Copy and Paste:

However it doesn’t work as the selections are not continuous and not on the same row/column.

For situation like this, you may need to do the copy and paste one by one… manually.

Seriously?  Sort of.

Nevertheless, the Clipboard may help a little bit… just a little bit.  See below screen cast for illustration (Pls observe what’s happening in Clipboard):

(Note: Pls make sure the clipboard is empty; or you may Clear All first)

Oh… What a magical feature….. (it just saved us from eight key strokes of Paste + selecting the cell destination eight times).  😛

Hey, don’t  under estimate this subtle thing.

Why not doing it in three batches?

You may wonder… why didn’t I select (1, 2, 3) together, and then (4,5,6), then (7,8) followed by Paste All?

I did… but to my surprise, the Clipboard doesn’t work that way.  Take a look at the following screen cast.  Pay attention to what is being put in the Clipboard: “1 x 2 x 3”, not “1 2 3”.

What’s more surprising?!

A regular paste action (e.g. CTRL+V) gives different result from clicking the item on the Clipboard:

That’s why I have to put the 8 numbers on the Clipboard one by one.

What the Clipboard pastes?

I am quite curious about what would be pasted exactly by clicking an item on the Clipboard.  So I did a little test:

Finding: By clicking an item on Clipboard, only Format and Value will be pasted!

All a sudden, I realize why my favorite trick of converting a pivot table into a static range with format of pivot table works!

Two practical and useful tips for using Clipboard

1) Copy and Paste Pivot Table with format but not pivot cache

Here’s the situation:

I want to copy the result from a Pivot Table, but I don’t want the pivot cache connected to it… So I copy the range, then Paste it by selecting “Values and Source Formatting“:

Unfortunately… the source formatting is not pasted somehow (when the whole pivot table is copied).   😦

No worry.  The Clipboard comes to rescue.

Instead of using “regular” paste, open the Clipboard, select the item to Paste…

There you go. 🙂

Nevertheless, the Clipboard does not care about Column Width.  If you are going to paste the “Disconnected” Pivot Table to different columns, you may need to adjust the columns afterward.

2) Copy and paste visible cells only

You may probably know, if you wish you copy only visible cells, you need to first select visible cells only.  Here’s my blogpost about the trick.  Nevertheless, I observed an interesting behaviour of Clipboard while testing different scenarios of COPY: When I selected a range with hidden cells, only visible cells are being put on the Clipboard.

And guess what?  I can paste only visible cells by selecting the item on Clipboard.

Isn’t it nice?  I find it a good alternative to selecting visible cells before doing the copy and paste.  🙂

Other Tip:

Clipboard works across Word, Power Point, Excel…  When you are doing the Copy and Paste across applications, the Clipboard could save you lots of time from switching windows back and forth.

How do you use Clipboard (if you have ever used it)?  Please share with us in comments.