# Relative, Absolute or Mixed references

Relative, absolute or mixed cell referencing are the major ways to reference cells in excel.

Most often, you will need to reference a cell or cells during calculations.

Relative and absolute references behave differently when copied and filled to other cells. Relative references change when a formula is copied to another cell.

On the other hand, absolute references remain constant no matter where they are copied.

**RELATIVE REFERENCES**

By default, all cell reference is relative when copied across multiple cells or rows. For example, if you copy the formula =A1+B1 from row 1 to row 2, the formula will become =A2+B2.

Relative references work best whenever you need to repeat the same formula across multiple rows or columns.

In the example below, you simply want to populate the total column with the total quarterly sales of Dangote Group of Company products.

Click on cell F6, using the auto sum shortcut, press

- Alt +equal sign (=): auto-sum

The sum formula is filled in for you, press Enter on your keyboard, the formula will be calculated for B6 through E6 and the result displayed in F6.

Locate the auto fill handle and copy drag the formulas down to column. The formulas will be copied to the selected cells with relative references and the values will be calculated in each cell.

You can simply use the shortcut below to easily view how the formulas are reference relatively down the column for each cell, depending on its rows.

- Control (Ctrl)+Tilde (~): show formulas in cells

**ABSOLUTE REFERENCES**

There may be times you want your reference to remain constant and not change. Absolute references do not change when copied or filled, you use absolute reference to keep a row or column constant.

An absolute cell reference is designated in a formula by adding a dollar sign ($) before the column or row. If it precedes the column or row (but not both) it’s called a mixed cell referencing.

Most times, mixed cell referencing is hardly used, but you can watch this video for quick understanding of mixed cell reference and how to use it.

When writing a formula in Microsoft Excel, best is to use the F4 key on your keyboard to switch between relative, absolute or mixed cell references, as shown in the video below.

In the excel worksheet above, you want to calculate the percentage for each Dangote product by dividing the total for each product in absolute to the cell in red.

Remember the cell in color red will be absolute, fixed or constant, it will not move or shift.

Enter the formula to calculate the desired value in G6=F6/F9, after selecting the red cell(F9) to be absolute, press F4 on your keyboard once to make the cell (G6=F6/$F$9) absolute.

If you press: F4 once, twice and thrice respectively.

On your keyboard, press “Enter” key, locate the auto fill handle and copy the formula by holding and dragging the auto fill handle down the column just like you did in relative reference.

You can double click the filled cells to check their formulas for accuracy, but the shortcut:

- Control (Ctrl)+Tilde (~): show formulas in cells

Shows all formulas in cells, super-fast and easy, so you can see how the formulas are related at a single glance.

As you can see in the above worksheet, how the formulas are in reference to each other.

Be sure to include the dollar sign ($) whenever you are making an absolute reference across multiple cells.

You can watch the video below for visual explanation and understand how mixed reference work in excel.

Now that you know what relative, absolute or mixed reference is all about, kindly share to educate someone. Thanks, sharing is caring.

Download file for practice.