The Clipboard can be useful occasionally…

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:

Excel Tips - Clipboard

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

Excel Tips - Clipboard

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):

Excel Tips - Clipboard1

(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”.

Excel Tips - Clipboard2

What’s more surprising?!

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

Excel Tip - Clipboard.PNG

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:

Excel Tip - Clipboard2.PNG

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“:

Excel Tip - Clipboard4.png

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

Excel Tip - Clipboard3.PNG

No worry.  The Clipboard comes to rescue.

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

Excel Tip - Clipboard5.PNG

There you go. 🙂

Excel Tip - Clipboard6.PNG

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.

Excel Tip - Clipboard 9

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

Excel Tip - Clipboard 10

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.


About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged , . Bookmark the permalink.

2 Responses to The Clipboard can be useful occasionally…

  1. GraH says:

    Hi Fung (I checked how to call you first)
    Nice overview of clipboard usage “to the rescue”. I’ve been using clipboard to keep multiple copies and paste them later. Like for nesting functions whenever I need to. But I never noticed the other types of behaviour you describe. Really handy ones, I admit.
    You just earned your spot on my Excel Heroes list, I share at work 😉
    Greets from XL in G.


    • MF says:

      Hi GraH,
      Appreciate your kind words. It’s my pleasure to be on your list. 😃
      Like your idea of using clipboard for nested function.


Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.