Skip to main content

How to quickly add $ into Excel formulas?

Author: Sun Last Modified: 2025-07-01

In Excel, cell references in formulas are relative by default, such as "A1". This means when you copy or drag a formula to another location, the references will update automatically to reflect their new positions. For example, if you enter the formula =A1+B1 in cell C1 and then copy it to cell H12, the formula will automatically adjust to =F12+G12. This behavior is helpful in many situations but can cause errors when you always need to refer to specific cells, regardless of where the formula is placed. To prevent this automatic adjustment, you need to change the cell references to absolute references by adding the dollar sign ($), like $A$1. Absolute references remain constant when copied or dragged, making them essential for calculations using fixed values or ranges.
A screenshot showing relative cell references in an Excel formula

This guide will introduce several practical methods to quickly add $ signs to Excel formulas, making it easier to control how your formulas reference cells. Choosing a suitable approach will depend on your worksheet structure, the number of formulas you need to convert, your Excel version, and personal workflow preferences. Below is a summary of methods covered in this guide:

Add $ to formula with shortcut keys

Add $ to formula with Kutools for Excel good idea3

Use VBA Macro to batch convert cell references to absolute


arrow blue right bubble Add $ to formula with shortcut keys

You can easily toggle between different types of cell references—relative, absolute, and mixed—using shortcut keys. To change a cell reference, simply select the target cell, click into the formula bar, and place the cursor inside or next to the cell reference you wish to modify. Then, press the F4 key to cycle through the available reference types. Each press of F4 updates the reference in this order: relative (A1), absolute ($A$1), mixed (column fixed) (A$1), and mixed (row fixed) ($A1).
A screenshot showing the F4 key being used to change relative references to absolute in Excel

This method is suitable for quickly updating individual references within a formula. However, it requires manually selecting each cell reference, which can be time-consuming if you have many formulas or complex ones containing multiple cell references. Also, make sure you are editing the formula directly in the formula bar or in-cell. If the keyboard shortcut does not work, confirm that your keyboard's Function Lock (Fn) key is set correctly, as some keyboards may require using Fn + F4.


arrow blue right bubble Add $ to formula with Kutools for Excel

The F4 shortcut can only address one cell reference at a time, making it inefficient when you need to update several cells or a whole range of formulas. If you're handling multiple formulas or need to standardize reference types across a worksheet, Kutools for Excel provides an efficient solution with its Convert Refers utility.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

After free installing Kutools for Excel, you can follow these steps:

1. Select all the formula cells that you wish to add $ signs (make references absolute). Then go to Kutools > More > Convert Refers. See screenshot:
A screenshot showing Kutools for Excel's Convert Refers utility to add $ to multiple cell references

2. In the dialog that appears, choose the type of reference you need. For making all cell references absolute, select the “To absolute” option.
A screenshot of the Convert Refers options in Kutools for Excel for changing cell references to absolute

3. Click OK, and all the selected formula cell references will be updated as specified.
If your formulas reference data from external sheets or workbooks, verify the changes to ensure that the linked data is still correct, as some scenarios may require keeping certain references relative. Kutools also allows you to easily revert changes and convert references to relative or mixed types as needed.

This approach is beneficial for processing large quantities of formulas or when you need batch operations. Kutools also provides undo functionality, making it easy to reverse actions in case of mistakes. 


arrow blue right bubble Use VBA Macro to batch convert cell references to absolute

If you frequently need to convert all cell references within formulas to absolute references ($A$1 format) across a range or the entire worksheet, using VBA macros is an efficient and flexible solution. This method is especially useful for advanced Excel users or when working with large datasets, as it automates the conversion process and can handle complex formula structures in one operation. If you have formulas linking to external sheets or workbooks, make sure absolute references are appropriate for your scenario—sometimes, mixed references are preferred to support formula flexibility.

1. On the Excel ribbon, go to Developer Tools > Visual Basic. In the Microsoft Visual Basic for Applications window, click Insert > Module, then paste the following macro into the new module:

Sub ConvertToAbsoluteReferences()
    Dim rng As Range
    Dim cell As Range
    Dim formulaStr As String
    Dim i As Integer
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select the range to convert formulas to absolute references", xTitleId, rng.Address, Type:=8)
    
    Application.ScreenUpdating = False
    
    For Each cell In rng
        If cell.HasFormula Then
            formulaStr = cell.Formula
            For i = 1 To 10 ' Protects from infinite looping
                formulaStr = Replace(formulaStr, "([A-Za-z]+)([0-9]+)", "$$1$$2") ' Basic pattern replacement
            Next i
            cell.Formula = Application.ConvertFormula(formulaStr, xlA1, xlA1, xlAbsolute)
        End If
    Next
    
    Application.ScreenUpdating = True
End Sub

2. And then, click Run to run the code. A dialog box will appear prompting you to select the range you wish to process. After confirmation, all valid cell references in formulas within the range will be automatically converted to the absolute reference style (with $ signs).

If you encounter errors, check that your selected range contains formulas. This macro converts references in standard Excel formulas; special array formulas or non-standard functions may not be handled as expected, so review the affected cells after execution. Always save a backup of your workbook before running macros, especially on important or sensitive data.

Using VBA allows for complete batch processing, making it suitable for power users, IT staff, or those working in environments where installations are restricted. However, running macros may be disabled on some systems due to security settings, in which case you will need to enable macros in your Excel trust settings.


Summary suggestion: When deciding how to convert cell references to absolute in Excel, consider the number of formulas you need to modify and your familiarity with Excel features. For single formulas or ad-hoc edits, using shortcut keys is quick and convenient. For batch operations, Kutools for Excel and VBA macros save a significant amount of manual work. Always double-check the results after conversion, especially in complex worksheets or when using automated tools. Should you encounter any errors or unexpected outcomes, restore from your backup or use the Undo feature whenever possible.

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!