Magazine article Business Credit

What's So Special about "Paste Special"?

Magazine article Business Credit

What's So Special about "Paste Special"?

Article excerpt

I spend a tot 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 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 dick 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 dick 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 dick 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 cells where there is no new data. …

Search by... Author
Show... All Results Primary Sources Peer-reviewed

Oops!

An unknown error has occurred. Please click the button below to reload the page. If the problem persists, please try again in a little while.