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

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.

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.

• 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.

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

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

• 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.

• 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 RangeFor Each FormulaCell In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)FormulaCell.Formula = Application.ConvertFormula(FormulaCell.Formula, xlA1, xlA1, True)NextEnd Sub

• After that, paste the code onto the blank module. Keep the cursor inside the code.

• 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

• How to Write X Bar in Excel
• How to Insert Tick Mark in Excel
• How to Insert Symbol in Excel Header
• How to Put Equal Sign in Excel without Formula
• How to Insert Symbol in Excel Footer
  
• How to Put Sign in Excel Without Formula

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