Insert $-Signs to Different Existing Excel Formulas: Simple and Fast!

Insert $-signs into Excel formulas right when entering the formula is quite easy: Either type the dollar sign manually or press F4 on the keyboard to insert a dollar sign. But what about existing formulas – that are different? For example, how to insert $-signs into a large range of cells at once? With just a few clicks? Here is how to convert relative references to absolute references in Excel!

In a hurry?

Scroll down to number 3 and copy the VBA macro into your workbook. It’s probably the best solution for inserting dollar signs.

Method 1: Insert $-signs manually

As usual, I have to mention this method because often it’s the fastest. In this case, though, it might take longer, depending on your size of cell range and the formulas themselves.

1. Enter the cell containing a formula by pressing F2 on the keyboard.
2. Navigate to the cell reference you want to change.
3. Press F4 on the keyboard to add (or remove) dollar signs.

Method 2: Use Find and Replace to convert cell references to absolute references

The Find and Replace function in Excel is very powerful. With some tricks, you can not only find plain values, but even edit formulas. For our task here, though, it can only help a bit.

Here are a few recommendations and examples. First, open the replace function (press Ctrl + H on the keyboard or click on Find & Replace (on the Home ribbon) and then “Replace”).

• Let’s assume, your formula is =A1.
  • Find what: =A
  • Replace with: =$A$
• If your formula is =SUM(C6:C10) (like in the screenshot):
  • Find what: (C
  • Replace with: ($C$
  • Again, Find what: :C
  • Replace with: :$C$

Probably this method requires some trial and error. And it’s – admittedly – quite inconvenient. That said, let’s continue with method 3 below.

Method 3: Use a VBA macro to insert $-signs

You probably look for this code (please find the instructions of how to use it below the code):

Sub InsertDollarSigns()
     Dim cellRange As Range
     For Each cellRange In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
         cellRange.Formula = Application.ConvertFormula(cellRange.Formula, xlA1, xlA1, True)
     Next
 End Sub

Here is how to use the VBA-Code. If you need more help of how to use VBA macros, please refer to this article.

1. Copy the VBA code from above.
2. Open the VBA editor by pressing Alt + F11 on the keyboard.
3. Insert a new module: Right-click on the active workbook name on the left.
4. Go to “Insert” and click on “Module”.
5. Paste the code by pressing Ctrl + V on the keyboard.
6. Make sure that the correct cells are selected in your Excel sheet.
7. Click on start in the VBA editor.

That’s it!

Method 4: Use an Excel add-in

Because this is a common task, you can save a lot of time here if you use the right tools. That’s why we have included a feature to switch the reference type (absolute, relative and mixed – that means all types of dollar signs) into our Excel add-in Professor Excel Tools.

It’s very simple: Just select the cell(s) and press the “Change Reference” button – or the small arrow next to it to see further options.

Image by Gerd Altmann from Pixabay