Site icon Professor Excel

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

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

That’s it!

Do you want to boost your productivity in Excel?

Get the Professor Excel ribbon!

Add more than 120 great features to Excel!

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.

With our Excel add-in Professor Excel Tools you can change reference types with just a click (insert or remove dollar signs).

This function is included in our Excel Add-In ‘Professor Excel Tools’

(No sign-up, download starts directly)

More than 35,000 users can’t be wrong.

Image by Gerd Altmann from Pixabay

Exit mobile version