In my Excel classes i do a brief review of using copy and paste, but given the amount of important material to cover, there is never enough time to cover Excel’s paste special feature in any detail. The purpose of this blog post is to explain the importance of Excel’s paste special feature in detail.

To follow along with this exercise, download the workbook here

The first important concept about paste special is that it only works with copied cells, not cut cells.

To copy a selection either press the Ctrl+c keys on the keyboard, or right click the mouse over the selection to display a context-sensitive shortcut menu where you can left click the copy command.

For this example, I will copy the formulas from cell K4 through K13 on Sheet1.

Now select another cell inside or outside the selection and choose the paste special command by either right clicking over a cell or using the paste button on the home tab of the ribbon.

I’ve right clicked inside cell L4 and left clicked the Paste Special . . . command to show the paste special dialog.

One of the most common uses of the paste special feature is to convert a formula to a value. Excel formulas are dynamic. When the value in a cell referenced by a formula changes, the formula automatically recalculates to display a new result.  At some point you may want the result to stop updating and become static. To convert the formulas copied in K4:K13 into values in L4:L13, click the values button then click the OK button.

Notice the result strips the formatting of both the cell and the number. To verify the values are now static, in cell J4 change the $12.50 to $22 and note how gross pay in cell K4 changes but the gross pay in cell L4 does not. If you want to keep the number format with the value, paste special offers another option labeled Values and Number Formats.

Another great use for paste special is to copy/paste formatting from one cell to others. The format painter can also accomplish this task. Switch to the Sheet2 worksheet. I’d like to use the formatting in column K (Gross Pay) in column J (Rate). I’ll copy any cell in the range K4:K13. I’ll select from cell J4 to J13 and right click inside the selection, then left click the paste special command. In the paste special dialog I’ll chose the format option and click the OK button. The result is that only the formatting is copied, not the formula or value.

One of the amazing features of paste special is the ability to perform math. Let’s imagine that annually you increase some value by a fixed amount, like a salary or a price. Instead of manually updating values the paste special multiply command provides this important feature. I’ll start by selecting the Sheet3 worksheet tab. Let’s imagine we’ll generously double everyone’s hourly rate. In any empty cell type the number 2 (so that we can double the rate, 1.1 would mean increase the value 10%). Then copy the number 2, select all the rates in column J, right click inside the selection, and left click the paste special command. In the paste special dialog, choose the Multiply option and click the OK button. Note how the rates in column J have doubled and the gross pay formulas in column K have increased.

The paste special command offers an option called transpose. The transpose checkbox feature transposes rows to columns and columns to rows. On the Sheet4 tab, select and copy cells A3:K13.

Right click over cell A15 and left click the paste special command. Check the transpose box and click OK to view the result.

Another important feature of the paste special command is the ability to link information between files and programs. Imagine a colleague maintains some data that you require for your analysis. Instead of routinely opening and copying their data to your model (static), you can dynamically link to others data so that when numbers change your models update! On the sheet labeled HRData (for this exercise imagine the HRData sheet is in another workbook), copy the values from B2:B11 (the rate). On Sheet5, right click inside cell J4 and left click the paste special command. In the paste special dialog choose the link option and click the OK button. To understand how link works, on the HR data sheet, change the  value in cell B2 from $12.50 to $100. On the worksheet labeled Sheet5, look at the value in cell J4 has changed. If you paste link data between workbooks, Excel creates a link to a specific workbook name in a specific location. If the workbook filename changes, or the workbook location changes, you can use the link manager to adjust the reference. The edit links command can be found by customizing the ribbon.

The last topic to mention about Excel’s paste special feature. If you copy data from a web page and paste special into Excel (Word, or PowerPoint), paste special shows the following dialog

If you choose to paste as HTML Excel attempts to retain the formatting of the text/images, if you choose paste as text Excel removes any HTML formatting.

To practice your paste special skills, download a practice workbook here. You must enable macros (in Excel 2007 select the Office Button, then Excel Options (lower right), then on the left choose Trust Center, then to the right click the Trust Center Settings … button, on the left select Macro Settings and to the right choose Enable All Macros.

You are encouraged to add comments about other ways you use Excel’s paste special feature! You’ll learn more by visiting our Excel VBA Class NYC Blog here or our Excel Training NYC blog on WordPress.com here. To learn more about Excel Training in NYC click here.