**In this post youâ€™ll discover a technique that is essential to master if you want to succeed in Excel. You will hit a ceiling if you do not get your head around this concept and use it extensively.**

**It's one tool that you absolutely cannot ignore. Itâ€™s the concept of absolute references. **

You may have seen cell references in other peopleâ€™s formulas that have dollar signs and you may have a vague notion of what they do, but this post deconstructs them and shows you how to use them properly and to their full potential.

First, some background.

First, some background ...

## 1. How to use AutoFill to copy a formula

When a formula is used in a table column, you only need to write one formula in the first cell then copy it down.

1. Write the formula in the top cell. Press **Enter** then reselect the cell.

2. Locate the **AutoFill Handle** (small solid square) at the bottom right of the cell.

3. Hover the mouse pointer over the **AutoFill Handle**. The pointer will change to a solid cross.

4. Click and hold the left mouse button.

5. Drag over the cells you want to copy the formula to.

6. Release the mouse button. The formula is copied to each cell.

In the example pictured below, the original cell **C1**, contains a formula to add the values in **A1** and **B1**.

**C1** is then AutoFilled down to cells **C2** to **C5**.

Whenever you AutoFill, you should get into the habit of doing 2 extra mouse clicks.

1. Click the **AutoFill Options** icon (pictured above).

2. Choose **Fill Without Formatting**.

Doing this lets you keep the formulas you just generated but restores the original formatting back to what it was before you AutoFilled. This saves you time later having to do multiple formatting fixes and eliminate the problems of missing bottom table borders and other inconsistent formatting.

## 2. Relative Reference

When a formula is copied using AutoFill, the cell references in the formula change automatically.

This is called **Relative Cell Addressing**, because the cells referred to in each formula are relative to the cell references in the original formula.

However, in this next example, the original formula in cell **D2** multiplies the value in **C2** by 110% (100% plus **A2** which contains the sales tax figure of 10%).

As **D2** is AutoFilled down, the subsequent formulas refer to **C3**, **C4**, **C5** and **C6** (correct) and **A3**, **A4, A5** and **A6** (major problem because theyâ€™re blank). This is where the application of an **Absolute Reference** is required.

## 3. Absolute Reference

An **Absolute Reference** is a cell reference that is prefixed by a dollar sign ($). Every cell reference comprises a column letter and a row number, so a dollar sign may be placed in front of either component, or both or neither, to make it absolute (to fix it in place). The following table shows each combination of absolute cell referencing.

**A1**

Column is relative.

Row is relative.

When a formula containing reference to cell A1 is copied to other columns and rows, the whole cell reference will change.

When copying across columns, reference to cell A1 will change to B1, C1, D1 etc.

When copying down rows, reference to A1 will change to A2, A3, A4 etc.**A$1**

Column is relative.

Row is absolute.

When a formula containing reference to cell A1 is copied to other columns and rows, the**row**component will remain fixed, but the column component of the reference will change.

When copying across columns, reference to cell A1 will change to B1, C1, D1 etc.

When copying down rows, reference to A1 will remain unchanged.**$A1**

Column is absolute.

Row is relative.

When a formula containing reference to cell A1 is copied to other columns and rows, the**column**component will remain fixed, but the**row**component of the reference will change.

When copying across columns, reference to cell A1 will remain unchanged.

When copying down rows, reference to A1 will change to A2, A3, A4 etc.**$A$1**

Column is absolute.

Row is absolute.

When a formula containing reference to cell A1 is copied to other columns and rows, the reference will not change.

When copying across columns, reference to cell A1 will remain unchanged.

When copying down rows, reference to A1 will remain unchanged.

A **Partial Absolute Reference** (also known as **Mixed ****Addressing**)** **is where just the row or just the column is fixed (i.e. A$1 or $A1).

## 4. F4 â€“ The Magic Key (on a Windows PC)

Press **F4** to toggle the current cell reference between absolute, relative and mixed addressing.

1. While creating or editing a formula, place the cursor next to or inside a cell reference.

2. Press **F4** to cycle through the absolute addressing combinations, until the correct one is displayed.

To fix our example with the incorrect pricing hereâ€™s what to do:

1. Edit cell **D2**.

2. Place the cursor in the reference to **A2**.

3. Press **F4** once to convert **A2** into **$A$2**.

4. Press **Enter** then reselect cell **D2**.

5. AutoFill **D2** down to **D6**.

6. Every subsequent formula in column D will now refer to the fixed reference **$A$2**.

Note: Fixing just the row number (i.e. A$2) would work just as well.

## 5. PARTIAL ABSOLUTE Reference

In this example the commission figures in the lower table are calculated by multiplying the sales figures by the commission rates in the upper table

The commission rates are different for each salesperson. When we autofill cell **C11** down, we want cell **C12** to refer to **B5** and cell **C13** to refer to B6. Therefore, we need to leave the row number relative (no $).

However, as we autofill cell **C11** across the table. Cells **C11**, **D11** and **E11** all need to refer to cell **B4** for the commission rate. Therefore, we make the column B component absolute, hence $B4.

The single formula in** C11: =C4 * $B4** now works for the entire table. That's the power of partial absolute references.

## 6. Summary

- The AutoFill feature allows you to create a formula once and then copy it across a row or down a column. It copies every element of the original cell. This includes the formula, formatting, data validation etc. The AutoFill Smart Tag appears immediately, and this can be used to include or exclude the elements that you need.
- By default, Excel uses
**relative cell addressing**. **Absolute cell referencing**is a method of fixing all or part of a cell reference.

â–**$A$1**fixes the entire cell.

â–**$A1**fixes the column only.

â–**A$1**fixes the row only.

â–**A1**is a relative reference.- The
**F4 key**toggles between the 4 absolute/relative combinations.

I hope you found plenty of value in this post. I'd love to hear your biggest takeaway in the comments below together with any questions you may have.

Have a fantastic day.

About the author

Jason Morrell

Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!

SHARE