FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

The Excel Addict - Help with Excel 2013, 2010, 2007, 2003
May 5, 2016
 
Greetings from The Excel Addict
Hi fellow Excel Addict,

Fort Mcmurray Fire 20160505As you have probably seen in the news this week, the forest fires in Fort McMurray, Alberta, Canada are almost apocalyptic. Fort Mac is an oil town in western Canada and over the past couple of decades, tens of thousands of people from my province of Newfoundland, on Canada's east coast, have gone there to work in the oil industry. There are so many Newfoundlanders in Fort McMurray that everyone here knows or is related to someone there. I personally know dozens of people who live there.

This week I am hoping that you will keep the people who are affected by this devastating tragedy in your thoughts and prayers.

Thankfully, it appears that no one has died in these fires...which is almost miraculous when you see the pictures of people trying to evacuate the area, stuck in gridlocked traffic, surrounded by fire shooting hundreds of feet into the air and being rained down upon by burning embers.

With more than 80,000 people evacuated from their homes and jobs, many of whom will have nothing to return to, we can only pray for the rain and cooler weather that will allow these people to begin the long process of putting their lives back together.

Please understand, I'm not asking or expecting you to do this but if you feel so inclined, the Canadian Red Cross (http://www.redcross.ca) is accepting financial donations to help out the thousands of people who have lost their homes and belongings this week.

Now back to Excel...

In today's 'Excel in Minutes' tutorial, I'm going to show you a way to 'Copy Multiple Ranges of Data and Paste Them As One Single Range' using a little-known feature that has been available in Excel for years but somehow I had forgotten all about it. I think you may find many uses for this tip.

Also, if you missed my 'Excel in Seconds' newsletter on Tuesday, I showed you some
'Shortcuts To Quickly Select Entire Rows Or Columns'. You can read all about that here.

Wishing you another great day of Excelling,
Francis Hayes (The Excel Addict)
Email:  fhayes[AT]TheExcelAddict.com


Francis Hayes (TheExcelAddict.com)



If you missed my last newsletter, y
ou can click here to view it online.
 
 
TheExcelAddict.com Quote of the Day

"It's not how far you fall, but how high you bounce that counts."

-- Zig Ziglar --

If you have a favourite quote, send it to me and I may post it in my newsletter.

Today's Microsoft Excel Tip

Copy Multiple Ranges of Data and Paste Them As One Single Range

Clipboard Group in Microsoft Excel 2007 2010 2013 2016 365When you click Copy or press CTRL+C in Excel, that data is temporarily stored in
an area of your computer's memory called the clipboard.

The usual way we use the clipboard is that we Copy something and then Paste it somewhere else. Since the copied item is temporarily stored on the clipboard, we can even Paste it multiple times in multiple places, until we either Copy something else or hit the ESC key.

You may have thought that when you copy subsequent items to the clipboard, the previous copied item is replaced or that when you hit the ESC key, the clipboard is cleared. That's not what actually happens.

What you may not know, or if you're like me and forgot all about it for a few years, is that in Microsoft Office, you can copy and store multiple items on this clipboard. Those items can subsequently be pasted somewhere else, either individually or all as a group. In fact, the clipboard can hold data from up to 24 separate copy operations.


Show The Clipboard Pane in Microsoft Excel 2007 2010 2013 2016 365So "Where is this 'secret' Clipboard?", you ask.

I am using Excel 2013, so I'm not sure if earlier versions will differ.

To view the Clipboard and any items that are stored on it, you must first open it it by clicking its dialog launcher—that tiny arrow on bottom right corner of Clipboard group on the Home tab (or by quickly pressing CTRL+C twice1). The clipboard pane will appear to the left of your worksheet.

As you will see, when you copy an item, it gets added to the top of the Clipboard list and all previous items are moved down. Once there are 24 items on the clipboard, the oldest item gets dropped. When you click the Paste command on the Ribbon or press the CTRL+V keyboard shortcut, only the last item that was placed on the clipboard gets pasted.

Pressing
the Esc key doesn't actually clear items off the clipboard. It merely inactivates (greys out) the Paste command on the Ribbon and the CTRL+V keyboard shortcut. Even when Paste and CTRL+V won't work, you can still paste any item from the Clipboard list by clicking on it.

In reality, you can choose to either paste all of the items stored on the clipboard as a group or choose any individual item to paste, then choose another and another and so on. This can be done in the same worksheet, in another worksheet or even in a different workbook. This gives you capabilities that you may have thought were impossible in Excel.

An important point to remember hear is that pasting from the Clipboard pane doesn't paste formulas. All data pasted using the Clipboard pane is pasted as fixed values. 

There are many different ways you can leverage the clipboard's capabilities but, for this tutorial, let's look at an example of something which you probably have always wished you could do.

Say you have several lists of similar data and you want combine them into one list. Maybe the lists are in different worksheets or even in different workbooks. You have a couple of options. You can go back and forth copying and pasting each list individually, which most Excel uses do, or you can copy all the lists to the Clipboard first and then paste them all at their final destination in one step.

Combine Multiple Small Lists into One Big List

PLEASE CLICK HERE TO DOWNLOAD THIS PRACTICE FILE

This practice workbook is made up of worksheets containing the roster for three baseball teams in the ABC Baseball League. You have been asked to create a new workbook with the information from the three teams as one combined listing. The usual way to do this is to copy the data from the first sheet, switch to the combined sheet and paste, then go to the next sheet, copy the data and so on. For three sheets, that would be three separate Copy and Paste operations. Obviously, if there were more sheets, that would be a lot of work.

Combine Data From Multiple Sheets in Microsoft Excel 2007 2010 2013 2016 365

Rather than do this the 'usual' way, let me show you this 'secret' Office Clipboard trick.

1) First, open a new workbook where you will be creating the combined list;

2) Open the practice workbook. Since you are copying this data to a blank worksheet, if the lists have column headings, you will want include the column headings (Row 11) from the first sheet along with its data, and for all other sheets you will copy just the data;

3) If the clipboard is not currently visible, click the dialog launcher on bottom right corner of Clipboard group on the Home tab. If there are any items currently on the clipboard, click the Clear All button;

4) Now, go to the Blue sheet tab, select cells B11 to G36 (i.e. includes the column headings), then click Copy (or CTRL+C). You should see a portion of the data appear on the clipboard list;

First Item Copied To Clipboard in Microsoft Excel 2007 2010 2013 2016 365

5) Switch to the Red sheet tab and select cells B12 to G36 (i.e. no column headings)
, then click Copy (or CTRL+C). The data will appear at the top of the clipboard list and the previously copied item will move down;

Second Item Copied To Clipboard in Microsoft Excel 2007 2010 2013 2016 365
6) On the Green sheet tab, select cells B12 to G36, then click Copy (or CTRL+C). This data will now appear at the top of the clipboard list and the two previous items will move down;

Second Item Copied To Clipboard in Microsoft Excel 2007 2010 2013 2016 365

5) Finally, switch to the blank workbook where you want the combined data to go, select a cell and click the Paste All* button on the Clipboard pane. All of the data from the three Copy operations will be pasted as a group into the worksheet in the same order that they were copied to the clipboard.

* Note that if you didn't click the Clear All button before you started or if you have copied something else to the clipboard, you can select and Delete individual items in the list before clicking the Paste All button.


Paste All Items From Clipboard in Microsoft Excel 2007 2010 2013 2016 365
If you want, to have some separation between the items pasted from the clipboard, include a blank row from below each list as you copy them.

Copy Blank Row To Clipboard To Separate Lists in Microsoft Excel 2007 2010 2013 2016 365

Are you now beginning to see new applications for using the Office Clipboard in your Excel work?

If you haven't opened the practice workbook and tried this out, please give it a try. Sometimes the act of doing something will open you mind to new possibilities.


YOUR FEEDBACK:
New Uses for the Office Clipboard?
Let me know what ways you will be using the capabilities of the Office Clipboard to make your job easier or to do things you could never do before.

Using the Office Clipboard in a different version of Excel?
If you are using a different version of Excel than me (2013), please let me know which version you have and if the Clipboard works the same way for you or if there are any differences.

Notes:

1 If pressing CTRL+C twice doesn't display the Clipboard pane, use the dialog launcher to open it, then click the Options... button at the bottom and select the option 'Show Office Clipboard When Ctrl+C Pressed Twice''.


Other Clipboard Options
In addition to the Paste All and Clear All commands in the Clipboard Pane...
• You can select any item in the list and click Paste.
• You can delete unwanted items (e.g. before pressing Paste All).
• You CANNOT select multiple items.
• You CANNOT rearrange the items.

Clipboard Paste Options in Microsoft Excel 2007 2010 2013 2016 365
At the bottom of the Clipboard pane, there is an Options button with additional settings you can customize...
• Show Office Clipboard Automatically
Show Office Clipboard When Ctrl+C Pressed twice
Collect Without Showing Office Clipboard (i.e. don't show the Clipboard)
Show Office Clipboard Icon on Taskbar (see image below)
Show Status Near Taskbar When Copying (see image below)

Office Clipboard Options in Microsoft Excel 2007 2010 2013 2016 365



Thanks for supporting this newsletter and website

FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose.
"Spreadsheets Tips From An Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.

8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630