The Excel Clipboard is an extremely useful tool that’s very well hidden.
Most of us are familiar with being able to paste the last item copied as many times as we like until we press ESC, or copy something else. But few of us know that you can actually access the last 24 items copied and use them again and again.
And these copied items are collated from your operating system which means you can access a raft of items from different programs all from within Excel.
Tip: This clipboard is actually available in all of your Office programs.
Opening the Excel Clipboard
So where is this elusive clipboard you ask? All you need to do is click on the arrow in the Clipboard group on the home tab:
This will open the Clipboard pane to the left of the worksheet.
You can move, size and close it in the top right of the Clipboard pane:
Pasting from the Clipboard
To paste an item from the clipboard simply click on it, or hover your mouse over the item to reveal the down arrow where you can select ‘Paste’:
Deleting Items from the Clipboard
You can clear the entire Clipboard by clicking the ‘Clear All’ button* at the top, or click on the down arrow beside an item to delete items one by one:
Note: *Clear All will clear all Clipboards since the Clipboard is actually shared amongst all of your Office programs.
Clipboard Settings
You can access the settings via the Options button at the bottom of the Clipboard:
- Automatically displays the Office Clipboard when copying items.
- Automatically displays the Office Clipboard when you press CTRL+C twice.
- Automatically copies items to the Office Clipboard without displaying the Clipboard task pane.
- Displays the Office Clipboard icon
in the status area of the system taskbar when the Office Clipboard is active. This option is turned on by default.
- Displays the collected item message when copying items to the Office Clipboard. This option is turned on by default but only displays when the Clipboard pane is open.
Excel Clipboard Tips
- Copying Formulas: Copying a cell containing a formula will copy the result as a value. If you want to copy the actual formula you must edit the cell (F2), then select the formula, or select it from the Formula bar and press CTRL+C to copy (see image below). You can press ESC to close the cell editing and the formula will still be available on the Clipboard.
Tip: You can copy parts of formulas you might want to reuse in other formulas and then paste them into your new formula as required.
- Copying Formatted Text: If you want the formatting to remain when pasting make sure you copy the cell, as opposed to editing the cell and copying the text as per tip 1 above. In the image below you can see I’ve selected cell H6 and copied that to the Clipboard:
This will retain the red font formatting applied to ‘special’ when pasting from the Clipboard.
- Copying Values Only: If you don’t want to retain the formatting then edit the cell (F2) and select the text you want to copy and press CTRL+C to copy it to the Clipboard.
For a definitive guide on the Office Clipboard for all Office programs click here.
How to used it with VBA ?
Thanks.
ES
Hi,
There is a possibility. In MsForms 2 Library, there is a DataObject that can be used to store text with different ID’s. You have to set a Reference to Microsoft Forms 2.0 Object Library (no need to insert a form, you will just use that library). Chip Pearson website has a great resource for that:
Dim DataObj As New MSForms.DataObject
Dim S1 As String
Dim S2 As String
S1 = "text string one"
S2 = "text string two"
With DataObj
.SetText S1, "FormatId1"
.PutInClipboard
.SetText S2, "FormatId2"
.PutInClipboard
S1 = vbNullString
S2 = vbNullString
.GetFromClipboard
S1 = .GetText("FormatId1")
S2 = .GetText("FormatId2")
End With
Debug.Print S1
Debug.Print S2
But I think that it’s less powerful than a vba Dictionary object, that can hold any data type, not only text:
Dim MyDictionary as Variant
Set MyDictionary=CreateObject(“Scripting.Dictionary”)
MyDictionary.Add Key:=”ID1″, Item:=”Text string 1″
MyDictionary.Add Key:=”ID2″, Item:=Sheets(“Sheet1”).Range(“A1:H1”)
Msgbox MyDictionary(“ID1”)
Msgbox MyDictionary(“ID2″).Cells(1,”C”)
As you can see, it can hold in memory even ranges A1:H1 in this example, then you can extract a single cell (C1) from the dictionary range. That gives you a lot of powerful ways to store data and extract the necessary informations.
More, you can check if an item is in the dictionary:
Msgbox MyDictionary.Exists(“ID3”)
Cheers,
Catalin
What a tip, so simple & yet fantastic !! I am sure many Excel users (even 20+ year veterans like me) are unaware of this. Thanks Mynda ;-))
Cheers, Paranam. I love those simple but effective tips too 🙂