Back to Excel Formulas menu

Examples of Using Absolute Reference


Among the most useful usages of absolute referencing is dealing with percentages.
The percentage can relate to tax rate, interest rate, discount, bonus etc.

The percentage should be written in a cell outside the table of data. When later relating to that cell in a formula, use absolute referencing.

For example, let’s calculate the tax of the cameras in the following table:

A caemras table in Excel for calculating tax rate

If we assume a tax rate of 8%, the steps that should be taken are as follows:

1. Write the tax in a cell outside the table (let’s assume you wrote it in cell G5).
2. Click in cell D5, and write the following formula:
=C5*G8 (multiplying the camera’s price by the tax rate)
3. Before hitting the Enter key, press the F4 button. The formula will look like that:
=C5*$G$8
The meaning of it is that when the formula will be copied to all the other cells, it will multiply all the different values (the cameras prices) with cell G8 only.
4. After you have pressed the F4 key, you can now press Enter. The calculated tax will now appear in that cell.
5. Click cell D5 again, and drag it downwards with the fill handle for all other cameras.

A formula that will give you immediately the cameras final price (including tax) will be:
=C5*(1+$G$8)