Overview
-
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 chapter, 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.
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 Figure 13-1. 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.
Figure 13-1: An
example of using the Paste Special command to paste only values.
Figure 13-2: The
Paste Special dialog box with Values selected. Selecting Values pastes only
values and not any formulas.
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 in Figure 13-3.
Figure 13-3: Use
the Transpose option in the Paste Special dialog box to transpose a row of data
into a column of data or a column into a row.
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 Figure 13-3. 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 Figure
13-4) 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.
Figure 13-4: Use
the Divide option in the Paste Special dialog box to divide a data range by a
constant.
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 Figure 13-5.
Figure 13-5: You
can apply an option in the Operation area of the Paste Special dialog box to a
range of cells.
After you click OK, Excel divides each selected number in column D
by 100. The results are shown in Figure 13-6. 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.
Figure 13-6:
Results of using the Divide option in the Paste Special dialog box
No comments:
Post a Comment