The Paste Special Command
Overview
- How can I move the results of calculations (not the formulas) to a different part of a worksheet?
- I have a list of names in a single column. How can I make the list appear in one row instead of one column?
- I’ve downloaded U.S. Treasury bill interest rates from a Web site into
Excel. The data lists a 5 when the interest rate is 5 percent, 8 when the interest rate is 8 percent, and so on. How can I easily divide my results by 100 so that a 5 percent interest rate, for example, is listed as .05?
The Paste Special command in Excel enables you to easily perform certain manipulations of spreadsheet data. In this post, I’ll show how you can use the Paste Special command to perform the following types of operations:
- Pasting only the values in cells (not the formulas) to a different part of a spreadsheet.
- Transposing data in columns to rows and vice versa.
- Transforming a range of numbers by adding, subtracting, dividing, or multiplying each number in the range by a given constant.
How can I move the results of calculations (not the formulas) to a different part of a worksheet?
In the worksheet named Paste Special Value in the file PasteSpecial.xls, the cell range E4:H9 contains the names, games, total points, and points per game for five 10-11-year-old basketball players from Bloomington, Indiana. In the cell range H5:H9, I’ve used the data in cells F5:G9 to compute each child’s points per game, as shown in the following figure. Suppose we want to copy this data and the calculated points per game-but not the formulas that perform the calculations-to a different cell range (E13:H18, for example). All you do is select the range E4:H9, choose Edit, Copy, and then move to the upper left corner of the range where you want to copy the data (cell E13 in this example). Next, select Edit, Paste Special, and then fill in the Paste Special dialog box as indicated in Figure 13-2. After clicking OK, the range E13:H18 contains the data but not the formulas from the cell range E4:H9. You can check this by going to cell H16. You will see a value (7) but not the formula that was used to compute Gregory’s average points per game. Note that if you use the Paste Special command, select Values, and then paste the data into the same range from which you copied the data, your formulas will disappear from the spreadsheet.
I have a list of names in a single column. How can I make the list appear in one row instead of one column?
To realign data from a row to a column (or vice versa), the key is to use Edit, Copy and then Paste Special, Transpose. Essentially, the Transpose option in the Paste Special dialog box ‘flips’ selected cells around so that the first row of the copied range becomes the first column of the range you paste data into, and so on. Look at the worksheet named Paste Special Transpose in the file PasteSpecial.xls, shown inthe following figure.
Suppose that you want to list the players’ names in a single row (starting in cell E13). Simply select the range E5:E9, and then choose Edit, Copy. Move to cell E13, and then choose Edit, Paste Special and check Transpose in the Paste Special dialog box. After clicking OK, the players’ names are transposed into a single row.
Suppose you want to transpose the spreadsheet content in E4:H9 to a range beginning in cell E17. Begin by selecting the range E4:H9. Next, choose Edit, Copy and then move to the upper left corner of the range where you want to put the transposed information (E17). Choose Edit, Paste Special, check Transpose, and then click OK. You’ll see that the content of E4:H9 is transposed (turned on its side), as shown in the following figure. Note that in F20:J20, Excel was smart enough to adjust the points-per-game formula so that the average for each player is now computed from data in the same column instead of the same row.
| Note |
When you select Paste Special and click Paste Link instead of OK, the transposed cells are linked to the original cells, and changes you make to the original data are reflected in the copy. By changing the value in cell F5 to 7, the value in cell F18 becomes 7 as well, and cell F20 would display Dan’s average as 4 points per game. |
I’ve downloaded U.S. Treasury bill interest rates from a Web site into Excel. The data lists a 5 when the interest rate is 5 percent, 8 when the interest rate is 8 percent, and so on. How can I easily divide my results by 100 so that a 5 percent interest rate, for example, is listed as .05?
The worksheet Paste Special Divide in the file PasteSpecial.xls (see in the following figure) contains the annual rate of interest paid by three-month U.S. Treasury bills for each month between January 1970 and February 1987. In January 1970, the annual rate on a three-month Treasury bill was 8.01 percent. Suppose we want to earn annual interest on $1 invested at the current T-bill rate. The formula to calculate the rate is (1 + (annual rate)/100). It would be easier to compute earned interest if our column of annual interest rates were divided by 100.
The Operations portion of the Paste Special dialog box lets you add, subtract,-multiply, or divide each number in a range by a given number, providing an easy way to divide each interest rate by 100. Here we want to divide each number in column D. To begin, I entered our given number (100). You can enter it anywhere in the spreadsheet. I chose F5. With F5 selected, choose Edit, Copy. Next select the range of numbers you want to modify. To select all the data in column D, move the cursor to cell D10 and press Ctrl+Shift and then the down arrow. This shortcut is a useful trick for selecting a ‘tall’ cell range. (By the way, to select a ‘wide’ set of data listed in a single row, move to the first data point and then press Ctrl+Shift and the right arrow.) Next, select Edit, Paste Special, and then select Divide, as shown in the following figure.
After you click OK, Excel divides each selected number in column D by 100. The results are shown in the following figure. If we had selected Add, D10 would have displayed 108.01; if we had selected Subtract, D10 would have displayed -91.99; and if we had selected Multiply, D10 would have displayed 801.
