My Newest Book
Excel 2003 Study Guide

get it at Amazon.

FROM WILEY: Download sample Excel files to accompany the book.
Instructors may download tests, slides, outlines, etc. HERE

Linda's Ebooks

Linda's CD

The Newbie Club

Online classes
at Eclectic Academy

FrontLook
Add-ins
& Screen Capture

WinBackup
SpeedUpMyPC
WinTasks5Pro

What's So Special About "Paste Special"?

by Linda Johnson

reprinted with permission from TechTrax

I spend a lot of time answering questions about Microsoft Office programs. I see people using long, convoluted methods to achieve effects that can be done easily by using Paste Special. People use outrageous formulas in Excel, which could be eliminated with Paste Special. I see people fiddling around with multiple graphics in PowerPoint, when Paste Special would allow them to convert the graphic into a format that would more easily let them to do what they want. And I receive endless questions from people wondering why they paste something into a Word document from a Web Page and it looks screwy. Paste Special is also the answer to this dilemma.

In this article, I'll share some Paste Special secrets. Hopefully, they will make your life easier. I'll be using Excel and Word, as these programs offer some of the best Paste Special features. Once you understand what Paste Special can do, you can experiment with this feature in other Office programs.

Controlling Column Widths in Excel
Ever notice that when you copy data from one worksheet to another, the column widths don't copy correctly? Try this method.

Copy the data and paste it into another spreadsheet. Leave the data highlighted. Go to Edit/Paste Special and put a tick mark in the radio button that says Column Widths.

Bingo! The column widths are corrected.

Excel Auto Calculations
To setup your spreadsheet for more examples, add the numbers 10 through 100 in cells A1:A10 in increments of 10. Shortcut: Note that you can type 10 in cell A1 and 20 in cell A2. Select both cells and use the Fill Handle to drag down to cell A10. Since Excel recognized the series of 10, 20, etc., you should now have 10 through 100 in cells A1 through A10. Remember that you only need to enter enough numbers for Excel to see the pattern, then select them all and drag with the Fill Handle and Excel will continue the series, ad infinitum.

In cell B1, type 100 and hit enter. Now return to cell B1 and copy it. Then highlight cells A1:A10 and go to the Edit menu and choose Paste Special.
In the center section of the Paste Special box where it says Operation, put a tick mark in the radio button beside Multiply and click OK.

Excel's Paste Special dialog box

Excel will multiply all the numbers in A1:A10 by 100 (which you copied from cell B1). Try doing the same thing, but choosing Add, Subtract, or Divide. You will see that this is a very quick way to perform the same, simple mathematics on multiple cells.

Removing an Excel Formula
Here's one to use when you want to keep a value in a cell but remove the formula that produced this value.

Add this formula to cell C1: =A1+B1.
Copy it and leave it selected. Go to the Edit menu and choose Paste Special.
This time, put a tick mark in the radio button in the Paste section that says Values and click OK.

Since you copied it right over the top of itself in the same cell and pasting values only pastes the value and not the formula, this is a quick way to remove a formula from a cell without removing the result of the formula.

Changing the Data Layout in Excel
This one is handy to know when you inherit a spreadsheet that someone else made and you want to change the layout of the data quickly.

Highlight cells A1:A10 again and copy them.
Now click into cell D1 and go to the Edit menu and choose Paste Special.
This time, put a check in the box at the bottom that says Transpose and click OK.

You'll see that Excel will pasted your values across the columns, instead of down the rows.

Skip Blanks in an Excel Data Series
This is a great one to use when you want to copy new data over old, but don't want to replace existing data in a cells where there is no new data.

In cells C5:C9, enter the numbers 10, 20, <blank>, 40, <blank> (<blank> meaning do not put anything in cells C7 and C9).
Now, in cells D5:D9, enter 50, 60, 70, 80, and 90.
Highlight cells C5:C9 and copy them. Click in cell D5 and go to the Edit menu and choose Paste Special. This time, put a check in the box that says Skip blanks and click OK.

You will see that cells D5:D9 now show 10, 20, 70, 40, 90, because Excel did not paste blank values over existing data.

Linking Data in Excel

Add another simple formula to your spreadsheet (again, two simple numbers and a sum to add them up will do).
Now copy the cell with the formula in it and go to another sheet in the workbook. Click on any blank cell. Go to Edit/Paste Special and click at the button where it says Paste Link.

You will see your number is in the cell and the formula bar shows that it relates to another sheet.

Go back to that sheet and change the SUM formula to an AVERAGE formula.
Return to the sheet where you pasted it and you will see it is updated there also.

Pasting a link means the destination cell will always be updated when you change the original cell. You can also do this between workbooks.

Pasting Web Pages into Word
First, copy some text from a Web Page and paste it into Word and see if you have problems. If you go to a website (like mine, Linda's Computer Stop), you will see my text is white on a dark background. If you copy white text and paste it into Word, you won't see anything! So, let's look at that Paste Special box:

Word's Paste Special dialog box

You see that by default, Word wants to copy this text in HTML Format, which would include the white font formatting.
Try Paste Special/Unformatted Text. You will get just the text, in whatever default font you have set in Word.
Also, sometimes when you copy text from an Email into Word, all of the margins are messed up. Try Paste Special/Unformatted Text for that one too.

Now try copying a picture from a Web Page or from an Email (right click on the graphic and choose Copy). Because graphics come in many formats, you may want to convert yours into a format that is smaller in file size or more compatible with your computer. Often graphics embedded in Emails only allow you to save them as bmp (bitmap) files, which are HUGE. People think they have to go into a graphics program to convert this file to something more manageable. Not true. Try Word's Paste Special:

Word's Paste Special options for graphics

Note that you have various choices for different graphic formats, such as gif, jpeg, and png, which are all smaller and more manageable than bmp files.
Try pasting your graphic in different formats and see the different choices you then have for making changes to your graphic. Also, notice the drastic difference in your file size when you make different choices.

Also, notice that Word's Paste Special box has the same Paste Link feature mentioned in the Excel section above. Though it's not always available (depends on what you are pasting and where you are pasting it from), when it's not grayed out you can use it to automatically update whatever you pasted when the original is changed.

So, what's the moral of this story?
"When copy/paste just doesn't do it for you, always check out Paste Special. You may very well find that the choice you are looking for is just one click away!"

Learn more about Paste Special and other hidden secrets in the Office programs in Linda's E-Book Tutorials and Online Classes!