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
- 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 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
- 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
- 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
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:
- 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:
- 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
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
Learn more about Paste Special and other hidden secrets in the Office programs
E-Book Tutorials and Online Classes!
For more tips, see Linda's Computer Stop at: http://personal-computer-tutor.com