Copy and move formulasAfter you create a formula, you can copy it and paste it into another cell. When you do, Excel changes the formula to work in the new cells. For instance, suppose you have a worksheet in which cell C7 contains the formula =SUM(C2:C6). If you copy cell C7 and paste the copied formula into cell D7, Excel enters =SUM(D2:D6). Excel knows to change the cells used in the formula because the formula uses a relative reference—a reference that can change if the formula is copied to another cell. Relative references are written with just the cell row and column—for example, C14. Show Relative references are useful when you summarize rows of data and want to use the same formula for each row. As an example, suppose you have a worksheet with two columns of data, labeled Sale Price and Rate, and you want to calculate a sales representative’s commission by multiplying the two values in a row. To calculate the commission for the first sale, you would enter the formula =A2*B2 in cell C2. Use formulas to calculate values such as commissions Selecting cell C2 and dragging the fill handle down through cell C7 copies the formula from cell C2 into each of the other cells. Because you created the formula by using relative references, Excel updates each cell’s formula to reflect its position relative to the starting cell (in this case, cell C2). The formula in cell C7, for example, is =A7*B7. Copying formulas to other cells to summarize additional data When you enter a formula in a cell of an Excel table column, Excel automatically copies the formula to the rest of the column and updates any relative references in the formula. If you want a cell reference to remain constant when you copy a formula to another cell, use an absolute reference by inserting a dollar sign ($) before the column letter and row number or a mixed reference by inserting a dollar sign before either the column letter or row number. One quick way to change a cell reference from relative to absolute is to select the cell reference in the formula bar and then press F4. Pressing F4 cycles a cell reference through the four possible types of references:
To copy a formula without changing its cell references
To move a formula without changing its cell references
To copy a formula and change its cell references
To create relative and absolute cell references
by Karyn Stille Excel uses two types of cell references to create formulas. Each has its own purpose. Read on to determine which type of cell reference to use for your formula. Relative Cell References This is the most widely used type of cell reference in formulas. Relative cell references are basic cell references that adjust and change when copied or when using AutoFill. Example: =SUM(B5:B8), as shown below, changes to =SUM(C5:C8) when copied across to the next cell. Absolute Cell References Situations arise in which the cell reference must remain the same when copied or when using AutoFill. Dollar signs are used to hold a column and/or row reference constant. Example: In the example below, when calculating commissions for sales staff, you would not want cell B10 to change when copying the formula down. You want both the column and the row to remain the same to refer to that exact cell. By using $B$10 in the formula, neither changes when copied. A more complicated example: Let's pretend that you need to calculate the prices of items in stock with two different price discounts. Take a look at the worksheet below. Examine the formula in cell E4. By making the first cell reference $C4, you keep the column from changing when copied across, but allow the row to change when copying down to accommodate the prices of the different items going down. By making the last cell reference A$12, you keep the row number from changing when copied down, but allow the column to change and reflect discount B when copied across. Confused? Check out the graphics below and the cell results. Copied Across Copied Down Now, you might be thinking, why not just use 10% and 15% in the actual formulas? Wouldn't that be easier? Yes, if you are sure the discount percentages will never change - which is highly unlikely. It's more likely that eventually those percentages will need to be adjusted. By referencing the cells containing 10% and 15% and not the actual numbers, when the percentage changes all you need to do is change the percentage one time in cell A12 and/or B12 instead of rebuilding all of your formulas. Excel would automatically update the discount prices to reflect your discount percentage change. Summary of absolute cell reference uses:
There is a shortcut for placing absolute cell references in your formulas! When you are typing your formula, after you type a cell reference - press the F4 key. Excel automatically makes the cell reference absolute! By continuing to press F4, Excel will cycle through all of the absolute reference possibilities. For example, in the first absolute cell reference formula in this tutorial, =B4*$B$10, I could have typed, =B4*B10, then pressed the F4 key to change B10 to $B$10. Continuing to press F4 would have resulted in B$10, then $B10, and finally B10. Pressing F4 changes only the cell reference directly to the left of your insertion point. I hope this tutorial has made these cell reference types "absolutely" clear! Microsoft Office
Tutorials How do you copy a formula in Excel and change cell references?Copy and paste a formula to another cell or worksheet in Excel.... Select the cell with the formula you want to copy.. Press. + C.. Click the cell where you want to paste the formula. ... . To quickly paste the formula with its formatting, press + V. ... . Clicking the arrow gives you a list of options.. How do you automatically update cell references in Excel?When you are typing your formula, after you type a cell reference - press the F4 key. Excel automatically makes the cell reference absolute! By continuing to press F4, Excel will cycle through all of the absolute reference possibilities.
How do you copy a formula and keep a cell reference?To copy the formula entered using absolute references and preserve the cell references, select the cell containing the formula and copy it (Ctrl + C) and click the destination cell into which you want to paste the formula.
How do you reference a formula that references another cell?Use cell references in a formula. Click the cell in which you want to enter the formula.. In the formula bar. , type = (equal sign).. Do one of the following, select the cell that contains the value you want or type its cell reference. ... . Press Enter.. |