Excel Functions Tutorials
- Excel Tips
- Excel vs Access
- Excel Rows vs Columns
- Apple Numbers vs Excel
- 3D Reference in Excel
- Absolute Reference in Excel
- Mixed References in Excel
- Excel Reference to Another Sheet
- Array Formulas in Excel
- Arrays in Excel VBA
- Auto Numbering in Excel
- AutoFit in Excel
- AutoCorrect in Excel
- AutoSave in Excel
- AutoRecover in Excel
- Bullet Points in Excel
- Break Links in Excel
- Barcode in Excel
- Change Case in Excel
- CAGR Formula in Excel
- Calculate Age in Excel
- Calculate Percentage in Excel Formula
- Cell Reference in Excel
- Checklist in Excel
- Circular Reference in Excel
- Column Sort in Excel
- Column Lock in Excel
- Move Columns in Excel
- Custom List in Excel
- Consolidate in Excel
- Combine Cells in Excel
- Compare Two Columns in Excel
- Compare and Match Columns in Excel
- Compound Interest Formula in Excel
- Convert Columns to Rows in Excel
- Convert Date to Text in Excel
- Convert Numbers to Text in Excel
- Convert Text to Numbers in Excel
- Convert Excel to CSV
- Count Characters in Excel
- Count Rows in Excel
- Count Unique Values in Excel
- Countif not Blank in Excel
- Create Templates in Excel
- Family Tree in Excel Template
- Custom Number Format in Excel
- Delete Row Shortcut in Excel
- Divide in Excel Formula
- Drop Down List in Excel
- Dynamic Tables in Excel
- Dashboard in Excel
- KPI Dashboard in Excel
- Date to Text in Excel
- Date Format in Excel
- Database in Excel
- Delta Symbol in Excel
- $ Symbol in Excel
- Excel Column to Number
- Edit Drop-Down List in Excel
- Equations in Excel
- Exponents in Excel
- Excel Extensions
- Excel Translate
- Excel Not Responding
- Excel Find and Replace
- Find and Select in Excel
- Excel Subtraction Formula
- Excel Formula for Grade
- Excel as Calculator
- Excel Formula Not Working (Updating)
- Excel Table Styles & Formats
- Excel vs Google Sheets
- External Links in Excel
- Excel Alternate Row Color
- Excel Worksheet Tab
- Extract Number from String Excel
- Evaluate Formula in Excel
- Find Duplicates in Excel
- Finding Links in Excel
- Filter Shortcut in Excel
- Formatting in Excel
- Format Numbers to Millions & Thousands in Excel
- Format Phone Numbers in Excel
- Formula Errors in Excel
- Fractions in Excel
- Frequency Distribution in Excel
- Group in Excel
- Group Worksheets in Excel
- Group Columns in Excel
- Hide Formula in Excel
- Hiding a Column in Excel
- Highlight Every Other Row in Excel
- Highlight Duplicates in Excel
- How to Create a Formula in Excel?
- How to Create an Excel Spreadsheet?
- How to Add Text in Excel Formula?
- How to Create Dashboard in Excel?
- How to Copy Sheet in Excel?
- How to Delete Pivot Table?
- How to Calculate Percentage Increase in Excel?
- How to Multiply in Excel Formula?
- How to Unhide Columns in Excel?
- Insert Date in Excel
- Insert Calendar in Excel
- Import Data into Excel
- Insert Comment in Excel
- Insert Hyperlinks in Excel
- Insert Multiple Rows in Excel
- Insert Row Shortcut in Excel
- Insert New Worksheet in Excel
- Insert (Embed) an Object in Excel
- Insert Image in Excel Cell
- Insert Page Break in Excel
- Line Breaks in Excel
- Linear Interpolation in Excel
- Leading Zeros in Excel
- Last Day of the Month in Excel
- Logical Operators in Excel
- Lookup Table in Excel
- Mortgage Calculator in Excel
- Moving Average in Excel
- Not Equal to in Excel
- Numbering in Excel
- Name Manager in Excel
- Page Numbers in Excel
- Page Break in Excel
- Personal Budget Template in Excel
- Project Management Template in Excel
- Percentage Difference in Excel (Increase / Decrease)
- Pivot Table Calculated Field & Formula
- Pivot Table Sort
- Pivot Table From Multiple Sheets
- Print Comments in Excel
- Print Excel Gridlines
- Print in Excel
- Print Preview in Excel
- Print Area in Excel
- Print Titles in Excel
- Print Labels From Excel
- Project Timeline in Excel
- Protect Sheet in Excel
- Ratio in Excel Formula
- Random Numbers in Excel
- Randomize List in Excel
- Refresh Pivot Table in Excel
- Relative References in Excel
- Remove Blank Rows in Excel
- Remove Duplicates in Excel
- Remove Duplicates from Excel Column
- Remove Hyperlinks in Excel
- Remove Space in Excel
- Remove Leading Spaces in Excel
- Remove Watermark in Excel
- Row Limit in Excel
- Rows and Columns in Excel
- Rows to Columns in Excel
- Row Header in Excel
- Search in Excel
- Search For Text in Excel
- Share an Excel Workbook
- Shortcut to Merge Cells in Excel
- Show Formula in Excel
- Split Cells in Excel
- Separate Text in Excel
- Strikethrough in Excel
- Strikethrough Text in Excel
- Sum by Color in Excel
- Subscript in Excel
- Superscript in Excel
- Themes in Excel
- Timesheet Calculator in Excel
- Top 20 Keyboard Shortcuts in Excel
- Unmerge Cells in Excel
- Uppercase in Excel
- Word Count in Excel
- Word Cloud in Excel
- Watermark in Excel
- Weighted Average in Excel
- Wildcard in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tools (98+)
- VBA (162+)
- Power Bi (35+)
Absolute reference in excel is one of the types of cell reference in excel where the cells being referred to doesn’t change as they did in the relative reference, to make a formula for absolute referencing we use the $ symbol by pressing f4, $ symbols means lock and it locks the cell reference for all of the formulas so same cell is being referred to all the formulas.
Absolute Reference Excel (Table of Contents)
- Absolute Reference Styles in Excel
- What is Absolute Cell References in Excel?
- How to do Absolute Reference in Excel?
Absolute Cell Reference in Excel
In my personal opinion, your understating level of references in excel will decide how quickly you can understand the working style of Excel software. If you are quick enough to understand the reference styles in excel probably you will go long way in excel.
However, in Excel, it is not just referenced, we have absolute, relative and mixed references (but what-is-this-stuff)
Excel worksheets made up of rows and columns, which contains cells. Each cell represents the particular column and particular row. For example, cell A1 refers first column (A) and first row (1). Similarly, C15 refers third column (C) and fifteenth row (15).
The real advantage of Excel is in the usage of these cell references in other cells when creating formulas.
You can use three types of cell references in Excel: relative, absolute, and mixed. Using the right type of references could save you tons of work time and make you feel like a hero.
- Relative Reference
- Absolute Reference
- Mixed Reference
In this article, I will cover the Absolute Reference in Excel.
When we work with formulas, we usually work with cell references and making relative reference, absolute references and mixed references are very pivotal.
- =A1 means a relative reference. Both the row and column changes when we copy the formula cell.
- =$A1$ means an absolute reference. Both the row and column are locked when we copy the formula cell and remains constant.
- =$A1 means the only column is locked and row keeps changing in that particular column.
- =A$1 means only row is locked and column keeps changing in that particular row.
The Absolute Reference Shortcut key to insert those important dollar signs is F4.
Absolute Cell Reference
Relative Reference keeps changing along with moving cells but Absolute Reference in Excel does not change at all. However, the tuning you need to do here is you need to lock the cell completely.
In Absolute Cell Reference, a dollar sign ($) means that a particular column or cell is locked. You can only lock either a column or cell. If the dollar symbol is in form of an alphabetic means column is locked and if it is in front of the serial number that means row is locked.
Using Absolute Reference in Excel
Below are some of the practical Absolute Reference examples..
Absolute Reference in Excel – Example #1
Assume you have a data, which includes hotel costing for your project and you want to convert the entire US dollar amount to INR at 72.5 US dollars. Look at the data below.
In cell C2, we have our conversion rate value. We need to multiply the conversion value to all the USD amounts from B5:B7. Here the C2 value is constant for all the cells from B5:B7. Therefore, we can use an absolute reference in excel.
Follow the below steps to apply the formula.
Step 1: Type equal (=) sign in cell C5 and apply the formula =B5*C2. As soon as you give reference to the cell C2 type F4 once.
This would do the conversion for the first USD value.
Step 2: Now drag and drop the formula to the remaining cells.
Look at the dollar symbol for the cell C2 ($C2$) that means the cell C2 is absolutely referred. If you copy paste the cell C5 to the below cell it will not change. Only B5 will change to B6, not the C2.
However, in Relative References, all the cells keep changing but in Absolute Reference in excel whichever the cells locked with the dollar, the symbol will not change.
Absolute Reference in Excel – Example #2
Now let us look at the absolute cell reference example of absolute along with mixed references. Below is the sales data across months for 5 salespeople in the organization. They sold multiple times in a month.
Now we need to calculate the consolidated summary sales for all the five sales managers in the organization.
Apply the below SUMIFS formula to get the consolidated all the five people.
The result will be:
Closely look at the formula here.
- First thing is our SUMRANGE, we have selected from $C$2:$C$17. A dollar symbol in front of both the column and row means it is an Absolute Reference.
- The second part is Criteria Range1, we have selected from $A$2:$A$17. This is also Absolute Reference
- The third part is the criteria, we have selected $E2. That means the only column is locked when you copy the formula cell only thing that changes is the row reference, not the column reference. Irrespective of how many columns you move to the right, it always stays the same. However, when you move downwards the row numbers keeps changing.
- The fourth part is Criteria Range2, we have selected from $B$2:$B$17. This is also an Absolute Reference in Excel.
- The final part is Criteria, here the cell reference is F$1. This type of reference means row is locked because the dollar symbol is in front of the numeric number. When you copy the formula cell only thing that changes is the column reference, not the row reference. Irrespective of how many rows you move down it always stays the same. However, when you move to the right side the column numbers keep changing.
Play With Reference from Relative to Absolute or Mixed Reference.
We can change from one type of reference to another. The shortcut key that can do the job for us is F4.
Assume you had given a reference to cell D15 pressing the F4 key would do the following changes for you.
- If you press F4 only once, the cell reference changes from D15 to $D$15 (becomes ‘absolute reference’ from ‘relative reference’).
- If you press F4 twice, the cell reference changes from D15 to D$15 (changes to mixed reference where the row is locked).
- If you press F4 thrice, the cell reference changes from D15 to $D15 (changes to mixed reference where the column is locked).
- If you press F4 for the 4th time, the cell reference becomes D15 again.
When copying formulas in Excel, absolute addressing is dynamic. Sometimes you do not want cell addressing to change but rather absolute addressing. All you need to do is make the cell absolute by pressing the F4 key once.
A dollar sign is all about! If you copy this cell from place to place, the formula will not move with it. So if you type =$A1$+A2 into cell A3, then copy and paste that formula into cell B3, one cell automatically change but the other does not change, =$A1$+B2.
In Absolute reference in Excel, each referred cell will not be changing along with the cells you move either left, right, downward and upward.
If you give an absolute cell reference to cell $C$10 and move to one cell down it won’t changes to C11, if you move one cell upward it won’t change to C9, if you move one cell to the right it won’t change to D10, if you move on cell to the left then it won’t change to B10
This has been a guide to Absolute Reference in Excel. Here we discuss its uses and how to use Absolute Cell Reference along with excel example and downloadable excel templates. You may also look at these useful tools in excel –