Paste Special in Excel.

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.

Excel Auto CalculationsTo 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 numbrs 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 besideMultiply 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 FormulaHere'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. 

Changing the Data Layout in ExcelThis 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 SeriesThis 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, , 40, ( 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 WordFirst, copy some text from a Web Page and paste it into Word and see if you have problems. If you go to a website, 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 is changed.