«««To work with absolute cell referencing in Excel

7:59 pm May 11, 20176108

when you move a formula in Excel or copy, then change automatically the cell references, so the addresses of cells , which uses the formula. Usually, this works just as you would expect. But whenever the structure of the table from the series falls, there are problems.

problems with unique fields in the table

one example: A store of spare parts for motors would like to start a special sales campaign. To do this, there is a fixed discount rate of 15%.

uses the existing list with the normal prices of the shop to give customers a list of discounted prices, and builds up a discount.

of commissioned employees begins in cell D5 and there enters a formula, which is based on the original price and the discount. The result is also correct.

this is why the copied cell references are wrong now

he wants to enter the formula into the underlying cells of course not by hand but copied it by pulling down the fill-square. But the result is disaster: all additional cells are wrong.

Excel Rabattpreisliste mit falschen Bezügen
discount price list with errors due to incorrect reference

the reason is a reference to the discount rate in cell A2: Excel assumes that no longer A2 should be used in row A6, but the cell including and further continues this logic.

absolute cell address as a solution

the remedy is very simple: in the formula, not A2 to the discount cell referencing, but writes $A$ 2 . The formula for the first discounted price is then

=C5*(1-$A$2)

this formula is copied down, is the result.

the effect of the dollar sign is that Excel makes an absolute address it will be modified during the copy.

you can work with only one dollar sign: Excel would for example the cell reference A change$ 2 copy in column (A), but let the line (2) alone.

want to know more about the topic? Here we explain the difference between relative and absolute cell references .

alternative: work with name

a similar effect as the full absolute cell reference with dollar signs for column and row for a name using the menu item setting define name in the register formulae .

If you give the name “Discount rate” in cell A2, you can write the formula:

=C5*(1-Rabattsatz)

so they have then no issues related to the cell. Because Excel uses these names when you copy formulas intact. A nice side effect: The formula is thus significantly easier to understand.

last updated on the 19.12.2016 / affiliate links / images from the Amazon product advertising API