|

Excel: Clipboard Tricks

By Bill Jelen
December 1, 2021

There are two obscure clipboard tricks available in Excel. The rarely used Office Clipboard task pane can keep a history of multiple ranges that are copied to the clipboard and then paste them using a single Paste All. Starting in Windows 10 and continuing in Windows 11, the Windows Clipboard can be configured to allow you to copy from one device and paste in another.

 

PASTE ALL WITH CLIPBOARD PANE

 

The Clipboard group on the Home tab of the ribbon offers icons for Paste, Cut, Copy, and Format Painter. Click the dialog launcher in the lower-right corner of the Clipboard group to open the Office Clipboard task pane. Unlike most Excel task panes, the Clipboard pane appears on the left side of the screen. If you drag the Clipboard title bar off the right side of the screen, you can dock the Clipboard pane on the right.

 

Once the Office Clipboard is visible, Excel will start collecting each item that’s copied or cut from Excel. New copied items are added to the top of
the list.

 

 

In Figure 1, the pane lists four separate Copy events. By clicking on the Paste All button, the rectangular data was arranged into a single column. Here are the events that lead to Figure 1:

 

  • A1:A6 was copied. The Clipboard pane showed Andy through Flo.
  • B1:B6 was copied. The newer copy of Gary through Lou is shown above Andy in the Clipboard pane.
  • C1:C6 was copied. The list of Mike through Robert appears above Gary in the Clipboard pane.
  • D1:D6 was copied. The list of Sam through Zeke is added to the top of the Clipboard pane.
  • Select A9 and click the Paste All button in the Clipboard pane. Excel pastes all 24 names in a vertical list. The paste happens starting from the oldest item to the newest, so the pasted data correctly shows Andy through Zeke in sequence.

 

If you need to paste or clear a single item, hover over the item to reveal a drop-down menu with Paste or Delete. To clear all clipboard history, use the Clear All button near the top of the Clipboard pane.

 

At the bottom of the Clipboard pane is an Options button, where you can change these settings:

 

  • Show Office Clipboard Automatically will make sure that the Clipboard pane appears every time Excel is opened.
  • Show Office Clipboard When Ctrl+C Pressed Twice is an optional setting that gives you a faster way to open the Office Clipboard. Note that this setting isn’t turned on by default, so you’ll need to use the dialog launcher to display the Office Clipboard the first time.
  • Collect Without Showing Office Clipboard allows Excel to keep the clipboard history without the Clipboard pane taking up any screen space.
  • Show Office Clipboard Icon on Taskbar will display a small clipboard icon in the Windows taskbar. Right-click this icon to open the Clipboard pane, to stop collecting, or to change options. The last option controls if the taskbar icon will display a status near the icon.

 

SYNC ACROSS DEVICES

 

Windows 10 (and now Windows 11) offers a new clipboard that you can display using the Win+V keyboard shortcut. The amazing features of this clipboard are turned off by default. To enable them, go to Start, Settings, System, Clipboard. In the Clipboard settings, turn on Clipboard History and Sync Across Devices, as shown in Figure 2.

 

 

In order for the Sync Across Devices to work, you need to have multiple computers that are signed in with the same Windows account. You can also have a mobile phone where the Edge browser is signed into the same account. You should enable Sync Across Devices on each computer and phone.

 

With Sync Across Devices, you can copy some cells from one computer and then immediately move to the other computer. Press Win+V, and the range copied from the first computer is available on the second computer.

 

You can even see history of previous things copied to the clipboard. The Windows Clipboard isn’t limited to only Excel ranges. It also shows images, paragraphs from Word, and more.

 

The one disappointment here is that the Windows Clipboard doesn’t offer a Paste All button. If you have four separate items on the clipboard and want to paste them all, you would have to press Win+V four times, each time starting in a new cell and selecting a different item to paste.

 

It looks like the Office Clipboard in Excel might be getting ready to support Sync Across Devices, which would give us the best of both worlds: syncing across devices and the Paste All button. But the Paste All button currently isn’t reliable when the clipboard contains a mix of items from multiple devices.

 

Bill Jelen is the host of MrExcel.com and the author of 64 books about Excel. He helped create IMA’s Excel courses on data analytics and the IMA Excel 365: Tips in Ten series of microlearning courses. Send questions for future articles to IMA@MrExcel.com.
0 No Comments
You may also like