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+)
$ Symbol in excel is used to lock a specific cell or rows or columns in a worksheet, the shortcut to lock down a reference in excel is by pressing ALT + F4, this feature is used while working in formulas when we do not want the reference to be changed when we copy or drag down the formula to other cell references, this feature will keep the reference same for all the further calculations.
$ Symbol in Excel Formula
One of the common question among all the excel beginners is “what does dollar ($) symbol doing inside the formula”??? This is the most trending question in excel forums. I was not different from you either, I had the same doubt. Ok, in this article we will explain to you the importance of “dollar ($)” symbol in excel formula.
Cell References in Excel
Before I explain to you about the $ symbol let me brief you about cell references in excel. For example, I have a value of 100, 200, and 300 in the first 3 cells.
Now in cell C1, I will give a link to cell A1.
So, now the C1 cell is dependent on cell A1. Whatever happens in cell A1 will directly have an impact on cell C1. Now if I copy and paste the cell C1 to C2, as a new learner we think we will get the value of 100 only but that is not the case here.
Look what we got here, instead of 100 we got 200.
If you look at the formula bar it says A2, the reason why we got A2 because when I copied the cell C1 it was not valued rather it was a formula, since cell C1 had a reference to the cell A1 and we moved down the cell by 1 it changed from cell A1 to A2, so we got the value of cell A2.
Similarity when we copy the cell reference cell based on how many cells we move down and how many cells we move either to left or right cell reference changes accordingly.
This is a brief understanding of cell references. Now we will see what $ symbol does with excel formulas.
How to Use $ Symbol in Excel formula? (with Examples)
Let’s see some simple examples of the $ symbol in excel formula.
For an example look at the below data set.
In the above data, we have Tax Rate in cell A2 as 5%. In the column “B” we have cost price. To arrive at the Tax Amount we need to multiply the Cost amount with Tax percentage. Apply the formula in cell C2 as B2 * A2 to get tax amount.
Since we have given a reference to cells I will copy and paste the formula down to the below cell.
This time we got 0 as a result. Now press F2 on the cell to debug the formula.
Now Tax percentage cell has been changed from A2 to A3. So change the tax amount reference from A3 to A2 again.
Now if I copy and paste this cell again we will get again the tax percentage cell will change from A2 to A3. This will be the case with the remaining cells as well.
So, how do we deal with it???
Can we do this for all the cells?
What if there are a thousand cells??
These are the questions everyone will get who don’t know about the $ symbol in excel formula.
Now, the ($) dollar symbol in excel formula plays a vital role here. In a cell, C2 opens the formula.
Now give the cell reference to the A2 cell.
After giving the cell reference to cell A2 press F4 key and see the magic.
Wow!!! After all these days now you got to know the technique of inserting a Dollar ($) symbol in excel formula for cell reference. I am sure you must have tried entering the $ symbol manually!!!
Press enter to have a value of Tax Amount.
Now copy and paste the formula to the below cell and see the magic.
Wow!!! Now it is taking the same cell reference even though we copy and paste the above cell to below cell.
The reason why it is taking the same cell reference because since we have pressed the F4 key it has inserted a ($) dollar symbol in excel made the cell as an Absolute Reference cell.
It doesn’t matter where you are pasting in the worksheet it will still take the reference of the cell A2 only.
Now copy and paste to all the other cells to have an accurate tax amount.
Now we have seen how to make the cell absolute reference by pressing the F4 key which inserted a ($) dollar symbol in excel. Once the absolute reference sets it will still take the same cell reference.
We can two absolute references i.e. absolute row reference and absolute column reference.
For an example look at the below data.
In column A we have unit’s data and in row 2 we have price data. In the empty boxes, we need to arrive at the sales amount by multiplying Units * Price.
Let’s apply the formula.
To arrive at the first sales price I have applied the formula as B2 to A3. Now I will copy and paste the formula to the below cell.
Since we have not locked the cell by using F4 key Price cell B2 moved down by one cell and taken the reference of B3 cell.
While applying the formula we need to decide on two things.
First, we need to decide while moving to the left Column reference of Units should be absolute but while moving down row reference should change.
The second thing is while moving from top to bottom row reference of Price should be absolute but while moving to the right column reference should change.
To make these arrangements lets open the formula in B3 cell and select B2 cell i.e. Price.
Now press the F4 key to make it an absolute reference.
This is absolute reference now, but for Price row while moving from top to bottom row reference of Price should be absolute but while moving to the right column reference should change. In order to make the row cell reference absolute press F4 key one more time.
As you can see from two it has changed to one dollar i.e. in front of the row number.
Now put multiply sign and select A3 cell.
With this column we need to make the column cell reference absolute, so press the F4 key thrice to make the ($) dollar symbol come in front of a column header in excel.
Press Enter key to have a result. Copy and paste the formula to other cells to have sales value.
Like this, by using a ($) dollar symbol in excel formula we can create absolute cell reference, absolute row reference, an absolute column reference.
This has been a guide to Why Use $ in Excel. Here we discuss Examples of the $ symbol in excel formula using row & column references along with downloadable excel template. You may also look at these useful functions in excel –