MicrosoftВ® Office ExcelВ® 2007: Data Analysis and Business Modeling (Bpg -- Other)

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 column. How can I make the list appear in a row instead of a column?

• I’ve downloaded U.S. T-Bill interest rates from a Web site into Excel. The data displays 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 displayed as .05?

With the Paste Special command in Microsoft Office Excel 2007, you can easily manipulate worksheet data. In this chapter, I’ll show how you can use the Paste Special command to perform the following types of operations:

• Paste only the values in cells (not the formulas) to a different part of a worksheet.

• Transpose data in columns to rows and vice versa.

• Transform 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 Paste Special Value worksheet in the file Pastespecial.xlsx, 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 on the next page. 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, press Ctrl+C, and then move to the upper-left corner of the range where you want to copy the data (cell E13 in this example). Next, right-click the upper-left corner cell in the target range, click Paste Special, and then fill in the Paste Special dialog box as indicated in Figure 13-2 on the next page. 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 worksheet.

  Figure 13-1: 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 column. How can I make the list appear in a row instead of a column?

• To realign data from a row to a column (or vice versa), copy the data and then use the Paste Special command with Transpose selected. 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 vice versa. For an example, look at the Paste Special Transpose worksheet in the file Pastespecial.xlsx, 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 or a column of data into a row.

• Suppose that you want to list the players’ names in one row (starting in cell E13). Simply select the range E5:E9, and then press Ctrl+C to copy the data. Right-click cell E13, click Paste Special, and check Transpose in the Paste Special dialog box. After clicking OK, Excel transposes the players’ names into one 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, press Ctrl+C. Now move to the upper-left corner of the range where you want to put the transposed information (E17). Right-click and choose 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. T-Bill interest rates from a Web site into Excel. The data displays 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 displayed as .05?

• The Paste Special Divide Before worksheet in the file Pastespecial.xlsx (see Figure 13-4 on the next page) contains the annual rate of interest paid by three-month U.S. T-Bills for each month between January 1970 and February 1987. In January 1970, the annual rate on a three-month T-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: Data for using the Divide option in the Paste Special dialog box to divide a data range by a constant.

• The Operations area 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 divisor (100). You can enter it anywhere in the worksheet. I chose F5. With F5 selected, press Ctrl+C. You will see the “moving ants” surrounding cell F5. (See Figure 13-4.) Next, select the range of numbers you want to modify. To select all the data in column D, click in cell D10 and press Ctrl+Shift and then the Down Arrow key. This shortcut is a useful trick for selecting a “tall” cell range. (To select a “wide” set of data listed in one row, move to the first data point and then press Ctrl+Shift and the Right Arrow key.) Next, right-click and choose Paste Special, and then select Divide in the Paste Special dialog box, 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