# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  > [SOLVED] Changing all formula references to absolute in a range of cells

## warthog34

I know how to use F4 to toggle between relative and absolute references in a formula when you click on the single reference.  But it would be helpful if I could select a whole range of cells containing formulas and make all the references within the selected cells absolute.

Is there a way?  When I search I only get info on the basics of relative vs. absolute and nothing more advanced like this.

----------


## JBeaucaire

Here's a macro to accomplish that. Be sure to highlight the range first or it will do the WHOLE sheet and there's not undo.




```
Please Login or Register  to view this content.
```


(Source)

----------


## Rich Z

Thank you for that macro. That is exactly what I came to this forum to find....

----------


## ozsupra

Problem is, as soon as you have numerous (number varies) cell references in the formula it stops working and replaces the formula with "#VALUE!". So user beware and *always save* before using this macro.

----------


## JBeaucaire

> Problem is, as soon as you have numerous (number varies) cell references in the formula it stops working and replaces the formula with "#VALUE!". So user beware and *always save* before using this macro.



Sage advice for anyone trying any macro for the first time, eh?

----------


## MegMagruder

THANK YOU!!!! This code saved me tons of work.... I needed to change over 2,400 cells!

----------


## shannonu

Thank you for this macro!  It is really helpful!  Is there a way I can chenge it to Make all the references in the whole workbook absolute?  I have a lot of sheets to go through.

----------


## arlu1201

shannonu,

Welcome to the Forum, unfortunately:

_Your post does not comply with Rule 2 of our Forum_ RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

----------


## BUSHEAD

Just used this today... Brilliant!

----------


## karenmccabe

I'm a self-taught Excel user, can someone explain how to use this macro in a range of cells?  I don't understand how to enter it.  Thanks  :Smilie:

----------


## JBeaucaire

*How/Where to install the macro:*

1. Open up your workbook
2. Get into VB Editor* (Press Alt+F11)*
3. Insert a new module* (Insert > Module)*
4. Copy and Paste in your code _(given above)_
5. Get out of VBA* (Press Alt+Q)*
6. Save as a macro-enabled workbook

The macro is installed and ready to use. Press *Alt-F8*  and select it from the macro list.


Karen, if you're not familiar with VBA, be very careful with this.  Make sure you run it on a COPY of your data, there's no UNDO from what a macro does.

As per forum rules you should ask further questions regarding the specifics of what you're trying to do in a thread of your own.

----------

