Absolute values in Excel

Posted on 2006-04-12
Last Modified: 2012-05-05
I need to make a list of values in Excel, and then have them be absolute.

For instance, a few cells would be:


What is the easiest way to set this up?  Once you put the dollar sign in, you can't easily fill down the list.
P.S. - Don't ask me why they have to be absolute, my boss wants to move stuff around and likes it that way.
Question by:BOTA-X
    LVL 80

    Expert Comment

    Hi BOTA-X,
    You can do it with a formula and a macro. The formula might be:
    ="=" & CELL("address",B1)               when copied down, you get =$B$1,      =$B$2,    etc.

    The macro would then be run after you select the cells containing those "formulas". It converts text that looks like a formula into a real formula.

    Sub TextToFormulas()
    Selection.Formula = Selection.Value
    End Sub

    To install a sub in a regular module sheet:
    1) ALT + F11 to open the VBA Editor
    2) Use the Insert...Module menu item to create a blank module sheet
    3) Paste the suggested code in this module sheet
    4) ALT + F11 to return to the spreadsheet

    To run a sub or macro:
    5) ALT + F8 to open the macro window
    6) Select the macro
    7) Click the "Run" button

    Optional steps to assign a shortcut key to your macro:
    8) Repeat steps 5 & 6, then press the "Options" button
    9) Enter the character you want to use (Shift + character will have fewer conflicts with existing shortcuts)
    10) Enter some descriptive text telling what the macro does in the "Description" field
    11) Click the "OK" button

    If the above procedure doesn't work, then you need to change your macro security setting. To do so, open the Tools...Macro...Security menu item. Choose Medium, then click OK.


    LVL 8

    Author Comment

    Seems to be working as intended!

    Is there a way to make it affect EVERY selected cell?  
    LVL 80

    Accepted Solution

    The macro works on every cell in the selection. It will wipe out a real formula--so be careful.

    To get around that possibility, try the following revision to the original macro:

    Sub TextToFormulas()
    Dim cel As Range, rg As Range
    Set rg = Intersect(Selection, ActiveSheet.UsedRange)

    Application.ScreenUpdating = False
    On Error Resume Next
    For Each cel In rg.Cells
        If Left(cel, 1) = "=" Then cel.Formula = cel.Value
    On Error GoTo 0
    Application.ScreenUpdating = True
    End Sub

    LVL 80

    Expert Comment

    Thanks for the grade!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Suggested Solutions

    Title # Comments Views Activity
    Office 2016 vs o365 4 56
    Missing Categories and colors in Outlook & 3 38
    excell formula 4 17
    Windows 10 Modified 2 13
    Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
    In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
    This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
    This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    6 Experts available now in Live!

    Get 1:1 Help Now