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 Morrell is a professional trainer, consultant and course creator who lives on the glorious Gold Coast in Queensland, Australia.
He helps people of all levels unleash and leverage the power contained within Microsoft Office by delivering training, troubleshooting services and taking on client projects. He loves to simplify tricky concepts and provide helpful, proven, actionable advice that can be implemented for quick results.
Purely for amusement he sometimes talks about himself in the third person.
SHARE
