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:
- Click once on a cell to enter that cell reference into a formula'
- 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 NowNext up:
- What is new in Office 2016
- Selecting Layers in a Visio Flowchart Diagram
- On the Checkout Option in SharePoint
- What they don’t tell you about becoming a manager
- What’s new in Microsoft Dynamics CRM 2015?
- Power Map in Excel 2013
- Creating a simple Website and User Interface with MVC (Part 3)
- Microsoft Word: Make an Exclusion Dictionary
- The Power of the Paragraph
- What’s new in Skype for Business
Previously
- Speak well under pressure
- The awesomeness that is AngularJS – Part 2
- Create Scenarios in Excel using cell names
- Creating a simple Website and User Interface with MVC – Part 2
- Save time by using your Document Stencil
- Courage? …I’m feeling quietly confident
- Welcome to the new Edge
- Creating a simple Website and User Interface with MVC – Part 1
- Non-Breaking Spaces & Non-Breaking Hyphens in Word
- Deleting an undelete-able site collection