Last updated Monday, Aug. 31, 2020, at 10:27 a.m. This article is based on legacy software. After creating part of an Excel worksheet, you may discover that the information needs to be changed, moved, or repeated. Excel allows you to edit cell contents in a variety of ways that can make creating your document easier. Functions and formulas can be copied or moved, lists can be automatically continued, and formulas can be applied to different data. This document will cover various editing
techniques you can use in Excel 2007/2008. Often, your first approach at organization will not be the same as your final ideas. For this reason, you may want to reorganize information. You may also want to duplicate an established formula for use in another cell. The Drag and Drop, Cut and Paste, and Copy and Paste options will help you do this without having to recreate the entire worksheet. Drag and Drop
allows you to move the information from a single cell or a range of cells. Drag and Drop is great for moving short distances but challenging for moving to cells not displayed on the current screen. Cut and Paste is the better method when moving information over long distances. Formulas using
relative cell references are automatically updated when the cells they are referring to are moved using the Drag and Drop method. NOTE: Be sure to check the cell references; one accidental absolute reference can alter the end result of the calculation.
To Undo Drag and Drop:
Moving Information: Cut and PasteFormulas using relative cell references are automatically updated when the cells they are referring to are moved using the Cut and Paste method. Be sure to check the cell references after pasting; if even one accidental absolute reference is contained in your formulas, the results of your calculation could be altered.
Copying FormulasCopying formulas works differently than moving formulas. When copying formulas, you must be aware that some types of cell references will update to reflect information for the new location, while some will continue to use the original information. When copying formulas, correct cell references are especially important to the result of the formula. Formulas that are copied to a new location can use either relative or absolute cell references. If you want to copy the formula and look at cells with similar information one column over, a relative formula is the best choice. However, if you want to copy the formula and refer to the same cell (perhaps the wage rate of a student employee), you should be working with an absolute formula, not a relative formula. NOTE: Absolute references are automatically updated for column and row additions and deletions.
Copy and PasteCopy and Paste works well for duplicating formulas, values, and labels without reentering them. The process for copying information in Excel is similar to copying in Word or other Windows applications. For another option to copy cell information, refer to Using the Fill Command below.
Using the Fill CommandThe Fill command allows you to repeat or continue information in contiguous cells without copying the information manually. With this option, if the first cell contains a formula, the formula will be repeated in the additional cells, and if the first cell contains text, the text will be repeated in the additional cells. If Excel recognizes a pattern of information, the additional cells will contain the next item in the pattern (e.g., if the selected cells are numbered from one to five, the next cell would contain six). For more information, refer to Using the Fill Command. Was this article helpful? Yes No View / Print PDFWhich one automatically changes reference to match the location of the cell?Relative references
When copied across multiple cells, they change based on the relative position of rows and columns. For example, if you copy the formula =A1+B1 from row 1 to row 2, the formula will become =A2+B2.
When a formula is copied to a new location absolute cell reference will change?There are two types of cell references: relative and absolute. Relative and absolute references behave differently when copied and filled to other cells. Relative references change when a formula is copied to another cell. Absolute references, on the other hand, remain constant no matter where they are copied.
Does a relative cell reference change when it is copied to a new location?By default, a cell reference is relative. For example, when you refer to cell A2 from cell C2, you are actually referring to a cell that is two columns to the left (C minus A), and in the same row (2). A formula that contains a relative cell reference changes as you copy it from one cell to another.
What happens to cell references when you copy a formula to a new cell?Select the formula in the cell using the mouse, and press Ctrl + C to copy it. Select the destination cell, and press Ctl+V. This will paste the formula exactly, without changing the cell references, because the formula was copied as text.
|