Paste Special

This is a tip for absolute beginners to Excel. Most people who are familiar with the program should be well aware of this feature.

You can copy and paste contents between programs and throughout Excel. If you know the basics of Windows, you should have no problems with the standard copying and pasting. Sometimes, you don’t want to paste things directly into the spreadsheet. For example, copying information from your web browser to Excel will copy formatting and perhaps images that you might not want.

Sometimes, pasting content from another program into Excel can be tricky. If you want pure text, you can paste plain text. After copying, either click the arrow below Paste then select Paste Special while the destination cell is selected, or right-click on the destination cell and click Paste Special. A dialog box will appear.

Either double-click Text from the list or select Text then OK.
Unfortunately, pasting as text does not keep tables intact. If you want an unformatted table from a web page, I know how it’s done. Copy the table then paste it normally into Excel. Select the table again, copy it, select a new destination, then open the Paste Special Dialog box.






Since you are copying from elsewhere in Excel, you are given different options for pasting. If you paste values, the contents for each cell will be pasted, but the formatting will not. This is the options to use if you want to strip out the formatting from data in Excel or if you want to copy unformatted data into a section of Excel that has already been formatted. Similarly, pasting formulas will copy formulas without the formatting.

Another valuable option is to paste formats. If you add data to a spreadsheet, you probably do not want to have to remember formatting options that should be applied to each cell. Excel can format some of this as you type, but complicated formatting on a spreadsheet expanding in multiple directions can cause problems. To simplify the formatting, you can copy formatting from one cell (or multiple cells) and paste the formatting to your new data.

Pasting formats is so valuable that Microsoft has provided users with a shortcut. This shortcut is the Format Painter. With the Format Painter, select the cell(s) with the format that you would like to copy. Click the Format Painter button. Select the cell(s) where you want to paste the format. At that point, the format painter will deselect. If you would like to paste the format to multiple cell ranges, double click the Format Painter, select the cell where you want the formats pasted, and click the Format Painter again when you are done. In my Sightings spreadsheet (http://cid-2e6efb80915ed5bd.office.live.com/view.aspx/Public%20Notes/Sightings.xlsx), I use the Format Painter for each date. I select the column for a previous date, click on the Format Painter, and select the new date.