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.

Enter a cell and press F4 on the keyboard to insert $-signs manually.
Enter a cell and press F4 on the keyboard to insert $-signs manually.
  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”).

Copy and Replace can only help a bit when converting relative to absolute references in Excel.
Copy and Replace can only help a bit when converting relative to absolute references in Excel.
  • 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.

Insert a new module in VBA.
  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.
Paste the VBA code, select your cells and press start in the VBA editor. The macro will now insert §-signs.
Paste the VBA code, select your cells and press start in the VBA editor. The macro will now insert §-signs.

That’s it!

Image by Gerd Altmann from Pixabay

By Henrik Schiffner

Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.

Leave a comment

Your email address will not be published. Required fields are marked *