We use the Dollar sign to make absolute/mixed cell references in Excel. And also after using it, we often may have any need to remove the Dollar sign. In this article, I will show you how to remove the dollar sign in Excel formula by simple methods and transparent illustrations.
Download Practice Workbook
You can download the practice workbook from here:
Introduction to Relative, Mixed and Absolute Cell References
We use cell references in Excel to automate the calculation. For example “=A4 * B7” will do the multiplication of cell A4 and cell B7 and here cell A4 is the cell of Column A and Row 4 similarly B7 is the cell of Column B and Row 7.
There are 3 types of cell references: Relative, Mixed and Absolute.
Relative Cell Reference: In Excel, all cell references are relative references by default. After using relative cell references, when you will copy and paste the formula into other cells, the references will change based on the relative positions of rows and columns.
Absolute Cell Reference: In Excel, we use it when we have to fix the position of a cell or range of cells in any formula. So, the reference of the fixed cell will not change by doing copy and paste to other cells.
Making absolute references is very easy. Just put a Dollar ($) sign before the row and column index in the formula. For example, if you want to fix the value of Cell B2, then you have to write $B$2 so it will make an absolute cell reference.
Mixed Cell Reference: We use this when we have to lock either only the row or the column index.
- To lock the row, put the dollar ($) sign before the row number. So, if you copy and paste the formula to other cells it will change only Column numbers relative to that cell.
- Similarly, if you want to lock the column number then put a dollar ($) sign before the column number. As an outcome, if you copy and paste the formula to other cells it will change only Row numbers relative to that cell.
2 Simple Methods to Remove Dollar Sign in Excel Formula
Now, if you want to remove the dollar signs in formulas in your Excel worksheet, we are here to help you with 2 quick methods. Before starting, have a look at the following screenshot of a dataset that has formulas with the dollar signs in them.
1. Use F4 Key to Remove Dollar Sign in Excel Formula
To remove the Dollar ($) sign from any Excel Formula, you can use the F4 key. This shortcut works in Excel by default.
- At first, go to the cell which you want to edit. And double click on it to open the edit mode.
- Then, press the F4 key on the keyboard once.
- As a result, you will see that the Dollar sign before the Column name is removed.
- Then, press the F4 key again.
- And you will see that the Dollar sign before the Row number is removed but the Dollar sign before the Column name returns again.
- At last, press the F4 key again
- And, you will see both Dollar signs will go away.
- Now, you can do the similar steps for the other cells to remove the Dollar sign from them
2. Remove Dollar Sign in Excel Formula Manually
For this, just go to the cell and double-click on the cell to open the edit option. Alternatively, click on the cell and go to the Formula bar. Then remove the Dollar sign by using backspaces on the keyboard.
In this article, you have learned how to remove the Dollar sign in Excel formula. You should try these methods yourself. I hope you found this article helpful. If you have any queries or recommendations, please share them in the comments section below. Don’t forget to check our website ExcelDemy for various Excel-related problems and solutions. Keep learning new methods and keep growing!