Copying and Moving

COPYING

One of the most common reasons for copying in a spreadsheet is to copy a formula. If your application is as small as the PRACTICE.XLS file, it is not difficult to type in a formula several times. But image that you have numerous rows or columns in which the same formula appears. You would want to be able to copy a formula and not have to manually type it in. One way to copy is to use the AutoFill handle.

Exercise 7

You are going to add three rows at the bottom for showing the maximum value, minimum value, and average value for each color (in each column). In cell A11 type the heading Largest Number. Place the cursor on cell C11 and type in this function: =MAX(C4:C9) . Rather than have to type this formula in each cell across the row, you can copy it. The active cell is still C11, where the formula is located. If you look at the cell, you will see a small dot in the lower right corner; this dot is called the AutoFill handle. Put your mouse cursor on the AutoFill handle and when it becomes a simple plus sign, click on and drag the handle to the right through cell H11. When you release the handle, information from the "source" cell is copied into the other cells.

The cells in row 11 will display as values, but if you click on these cells you will see that the =MAX function was copied to the right. Check out the formulas in D11, E11, F11, G11, and H11. You will notice that Excel has adjusted the formula as it was copied. That is, references to the column change as the column changes.

In cell A12 type the heading Smallest Number. Then in cell C12 type this formula =MIN(C4:C9). The smallest value in that column will display. Again, copy the formula across, by dragging on the AutoFill handle in cell C12.

In cell A13 type the heading Average. Then in cell C13 type this formula =AVERAGE(C4:C9). The average for that column displays. Again, copy the formula across, by dragging on the AutoFill handle in cell C13. (These numbers may display decimal values, which you will fix soon.)

Change some of the numbers in your spreadsheet and see if the totals recalculate and new maximum, minimum, and average values appear. Save this copy of the PRACTICE.XLS file.

Check your work.

The AutoFill feature is so easy to use that it is quite popular. However, it only works for adjacent values. If you had information you wanted copied to another section of the spreadsheet, you would have to highlight the cells to be copied (or a single cell), click on the

"Copy" button on the Standard toolbar, then move your current cell to another location and click on the

"Paste" button.

MOVING

To move information in a cell or a range of cells, highlight the cell or cells. Then click and drag on the border of the cell or cells and drag the cells to its new location. Release the mouse. This takes some practice in order to click and drag on the border.

If you move a formula, there is no change in the formula. That is, if it referenced cell B4, it would continue to reference B4.

Exercise 8: Highlight the block A10 through H13 and move this block to cell A18. Click on one of the formula cells and look at the cell ranges in the formula. Have the formulas changed? Use the "Undo" button on the toolbar to move the range back.

Learning Excel - Index