Hi fellow Excel Addict,
As 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)
If you missed my last newsletter, you can click here to view it online.
If you have a favourite quote, send it to me and I may post it in my newsletter.
Copy Multiple Ranges of Data and Paste Them As One Single Range
When 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.
So "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
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.
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;
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;
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;
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.
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.
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.
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.
|"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