Absolute Cell References use in Formulas

 Oct 22, 2015

Definition:

In Excel, an absolute cell reference, like other cell references, identifies the location a cell or group of cells and are used in such things as formulas, functions, and charts.

An absolute cell reference consists of the column letter and row number used in a regular cell reference but both letter and number are preceded by dollar signs ( $ ).

One of the main uses for absolute cell references is in a formula when you want a cell reference stay fixed on a specific cell. As a result, if the formula is copied and pasted to other cells, the absolute cell references in the formula or function do not change.

By contrast, most cell references in a spreadsheet are relative cell references, which change when copied and pasted to other cells.

An easy way to add the dollar signs to a cell reference while creating a formula:

  1. Click once on a cell to enter that cell reference into a formula'
  2. Press the F4 key on the keyboard – dollar signs will be added before both letter and number in the reference.

Absolute Cell References

$C$1

Absolute Column and Absolute Row

C$1

Any Column and Absolute Row

$C1

Absolute Column and Any Row

C1

Any Column and Any Row

F4

Keyboard Shortcut

Front End Results

Super

LSL

Work Cover

Name

Wage

9.50%

2%

3.75%

John

$      1,500.00

$           142.50

$          30.00

$             56.25

Mary

$      1,750.00

$           166.25

$          35.00

$             65.63

Ted

$         925.00

$           87.88

$          18.50

$             34.69

Frank

$      1,200.00

$           114.00

$          24.00

$             45.00

Dean

$      1,115.00

$           105.93

$          22.30

$             41.81

Marilyn

$      1,067.25

$           101.39

$          21.35

$             40.02

Sammy

$         897.52

$           85.26

$          17.95

$             33.66

Rita

$         400.00

$           38.00

$            8.00

$             15.00

Lauren

$      1,475.50

$           140.17

$          29.51

$             55.33

Show Formulas – Back End Result

Super

LSL

Work Cover

Name

Wage

0.095

0.02

0.0375

John

1500

=$B13*C$12

=$B13*D$12

=$B13*E$12

Mary

1750

=$B14*C$12

=$B14*D$12

=$B14*E$12

Ted

925

=$B15*C$12

=$B15*D$12

=$B15*E$12

Frank

1200

=$B16*C$12

=$B16*D$12

=$B16*E$12

Dean

1115

=$B17*C$12

=$B17*D$12

=$B17*E$12

Marilyn

1067.25

=$B18*C$12

=$B18*D$12

=$B18*E$12

Sammy

897.52

=$B19*C$12

=$B19*D$12

=$B19*E$12

Rita

400

=$B20*C$12

=$B20*D$12

=$B20*E$12

Lauren

1475.5

=$B21*C$12

=$B21*D$12

=$B21*E$12

 

 

How do your Excel skills stack up?   

Test Now  

Tags

Excel microsoft excel Desktop Applications office applications applications excel formulas absolute cell cell references

How do
your Excel skills
stack up?

Grade your skills now

About the Author:

Alice Antonsen  

Alice is one of New Horizons’ most experienced Desktop Applications trainers. She is qualified with a Diploma of Business (Computing) and a Diploma of Information Technology. Since joining New Horizons in 2006, Alice has achieved the prestigious status of a certified Microsoft Office Master and for the past 4 years, has been placed in the Top 25 Desktop Applications trainers for New Horizons Worldwide. Throughout her career, she has gained a wealth of knowledge and experience in providing training to individuals and groups of varying skill levels. Alice delivers each training session with great care and consideration ensuring each one is tailored to the learning needs of her students.

Read full bio

Next up:

Previously

top