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

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.

Download Practice Workbook

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

3 Ways to Insert Dollar Sign ($) in Excel Formula

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 Add Currency Symbol in Excel (6 Ways)

Similar Readings

• Insert Less Than or Equal to Symbol in Excel (5 Quick Methods)
• How to Insert Tick Mark in Excel (7 Useful Ways)
• Type Delta Symbol in Excel (8 Effective Ways)
• How to Put 0 in Excel in Front of Numbers (5 Handy Methods)
• Excel Formula Symbols Cheat Sheet (13 Cool Tips)

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

• Now, notice how the formula text changes.

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

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

Read More: How to Insert Rupee Symbol in Excel (7 Quick Methods)

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. Follow the steps below to be able to do that.

📌 Steps

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

• Next, copy the following code using the copy button in the upper right corner.

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.

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

Read More: How to Insert Symbol in Excel (6 Simple Techniques)

Things to Remember

• If your PC 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.

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 ExcelDemy blog to explore more solutions to the problems Excel users face every day.

Related Articles

• How to Put Sign in Excel Without Formula (5 Ways)
• Insert Symbol in Excel Header (4 Ideal Methods)
• How to Insert Symbol in Excel Footer (3 Effective Ways)
• Add Symbol Before a Number in Excel (3 Ways)
• How to Type Math Symbols in Excel (3 Easy Methods)
• Put Equal Sign in Excel without Formula (4 Easy Ways)
• How to insert square root symbol in Excel (7 easy ways)