How to Insert Dollar Sign in Excel Formula (3 Handy Methods)

Get FREE Advanced Excel Exercises with Solutions!

This article illustrates how to insert the dollar sign ($) in the Excel formula. The dollar sign is used to change the cell references from relative to absolute or mixed references. For example, assume a formula contains the cell reference A1. This is a relative reference. Now if you copy the formula down using the Fill Handle icon, the cell reference will change to A2, A3, A4, and so on. On the other hand, if you copy the formula to the right, the cell reference will change to B1, C1, D1, and so on.

But, you can insert the dollar sign in the cell reference to stop this if required. Imagine you want to multiply a range of cells by a fixed number which is in cell A2. Then you need to change the reference to $A$2. This is an absolute reference. Two more possible references can be $A2 or A$2. These are mixed references. The first one makes the column fixed and the second one makes the row fixed.

Now, follow the article to learn how to do that easily in Excel.


How to Insert Dollar Sign in Excel Formula: 3 Handy Methods

Assume you have the following dataset. It contains the sales from two different stores and their total.

Insert Dollar Sign ($) in Excel Formula

The FORMULATEXT function shows that the Total column contains formulas with the SUM function.

Now follow the methods below to insert the dollar sign in those formulas.


1. Insert Dollar Sign ($) in Excel Formula with Keyboard Shortcut

Follow the steps below to insert the dollar sign in the Excel formula using a keyboard shortcut.

📌 Steps

  • First, put the cursor beside a cell reference in the formula or select that cell reference in the formula bar.

Insert Dollar Sign ($) in Excel Formula with Keyboard Shortcut

  • Alternatively, you can double-click on the cell containing the formula to insert the dollar sign there.

  • Then, press F4 on your keyboard. It will insert the dollar sign in the formula making the cell reference an absolute reference.

Insert Dollar Sign ($) in Excel Formula with Keyboard Shortcut

  • Next, press F4 It will change the reference to a mixed reference making the row fixed but keeping the column relative.

Insert Dollar Sign ($) in Excel Formula with Keyboard Shortcut

  • Press F4 again after that. Now, the column will be fixed but the row will become relative.

Insert Dollar Sign ($) in Excel Formula with Keyboard Shortcut

  • You can select the entire reference before pressing the F4 key to change the entire reference at once.

Thus, you can switch between relative, absolute, and mixed references by repeatedly pressing the F4 key in Excel.

Read More: How to Insert Rupee Symbol in Excel


2. Use the Find and Replace Tool

You can also insert the dollar sign in the Excel formula using the Find and Replace feature. Follow the steps below to do that.

📌 Steps

  • First, notice that the formula text shows no dollar signs in the formulas.

  • Now, press CTRL+H to open the Find and Replace window. Then find for (B and replace it with ($B$ by pressing Replace All as shown in the picture below.

  • Next, you will see the confirmation if it was done properly.

  • Notice how the formula text changes.

  • After that, find for :C and replace them with :$C$ as shown below.

Use the Find and Replace Tool

  • More dollar signs will be added to the remaining part of the formulas.

Read More: How to Insert Sign in Excel Formula


3. Use a VBA Code to Insert Dollar Sign ($) in Formula

You can use Excel VBA to insert the dollar sign to all formulas in the active worksheet.

📌 Steps

  • First, press ALT+F11 to open the VBA window. Then, select Insert >> Module as shown below.

  • Next, copy and paste the following code.
Sub InsertDollarSignsInExcelFormulas()
Dim FormulaCell As Range
For Each FormulaCell In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
FormulaCell.Formula = Application.ConvertFormula(FormulaCell.Formula, xlA1, xlA1, True)
Next
End Sub
  • After that, paste the code onto the blank module. Keep the cursor inside the code.

Use a VBA Code to Insert Dollar Sign ($) in Formula

  • Now press F5 to run the code. After that, you will get the desired results.

Things to Remember

  • If your computer has the fn key, then you need to press fn+F4 for the shortcut.
  • The VBA code changes the cell references to absolute references only by inserting the dollar sign.

Download Practice Workbook

You can download the practice workbook from the download button below.


Conclusion

Now you know how to insert the dollar sign in the Excel formula in 3 different ways. Which method seems more convenient for you to use? Do you have any further queries or suggestions for us? Please let us know in the comment section below. You can also visit our website to explore more solutions to the problems Excel users face every day.


Related Articles


<< Go Back to Insert Symbol in Excel | Excel Symbols | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

2 Comments
  1. Thanks for the examples. I had totally forgot about the F4.. lol. Thanks

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo