Thursday, April 25, 2013

How to Use Absolute Cell Referencing

Creating an Absolute Reference
1. Determine if a formula should be absolute or relative. When designing a formula, look for hard-coded numbers. A sample formula for the discount could be 'discount = cost of merchandise * 20%'. The 20% is a hard-coded number and has the potential to change. Instead, create a cell titled Discount Amount. The new formula would be 'discount = cost of merchandise * discount amount'. That way, if the discount ever increased, you would need to change only the amount in the Discount Amount cell, and all the formulas would automatically update.
2. Create the formula. Once you know a formula will have an absolute reference, you need to create the formula in Excel. Create a cell that holds the data that has the potential to change; in this example it will be A4. This is the 20% discount in our example. Identify the first cell that will contain the cost of the merchandise; for example, cell D4 is the first cell on the purchase order that contains merchandise cost. To figure out the discount amount, in cell D5, type =D4*A4 and press the Enter key.
3. Make the cell absolute. To make the cell A4 absolute in this formula (meaning Excel will use cell A4 absolutely every time), simply highlight A4 in the formula and press your F4 key. This will place a $ before the cell row and column name. You could also enter a $ in front of the cell row and column name in the formula. The new formula be =D4*$A$4.
4. Copy and paste the formula to the other cells. When you paste the formula into other cells, the relative reference, D4, will automatically update to the corresponding row (or column) number, and the absolute reference will stay the same. For example, the next row would contain the formula =D5*$A$4.

Blogger news