• Quick note - the problem with Youtube videos not embedding on the forum appears to have been fixed, thanks to ZiprHead. If you do still see problems let me know.

Quick Excel Help - Absolute Cell References

madurobob

Philosopher
madurobob
Joined
Jul 20, 2007
Messages
7,401
Location
Blue Heaven
I have a few hundred cells with formulas in them referencing other tabs in the same worksheet. All of them use "relative" cell references rather than "absolute". I need to be able to copy/paste these several times without the formulas changing, so I need the formulas to have absolute cell references.

I know I can edit each cell manually, adding the "$", but it will take forever. I'm pretty sure there is a keystroke combination that will let me toggle between relative and absolute references. Anyone know what it is? Something to do with F4 key?
 
Use the F4 key on your keyboard to toggle through the 4 possible choices of absolute and relative
references. When you have the formula you want, press enter
 
Wudang said:
Select the range first then F2 to go into edit mode then F4 to cycle through
Click to expand...

Hmmmm... not quite. I can select the range. Hitting F2, though, only opens the first cell in the range to edit, then F4 toggles though the cell reference possibilities for that cell only. So, to use the F4 key I still have to open/edit each cell individually.

(I'm using Excel 2002 - maybe different in other versions?)

I could have sworn there was a combination of keystrokes that would do a mass change, but I can't find it. It may have been a macro someone wrote that I saved on an old laptop... or I may simply be wishful remembering!

I can use find/replace to cut down on the volume of editing... but its still a daunting task... 1442 cells to update.
 
I don't know of a quick way of making a mass change from relative to absolute, but there is a way around it.

Copy the tab that contains all the formulas.
Using this copied tab, instead of copying the formulas you need, cut them.
Paste to the new location.

When you cut and paste formulas in Excel instead of copy and paste, the formula references do not change. You will need to make copies of the tabs for however many copies of the formulas you need. Once you're done, just delete the extra copied tabs.
 
madurobob said:
I could have sworn there was a combination of keystrokes that would do a mass change, but I can't find it.
Click to expand...
Ctrl+Enter might be your forgotten keystrokes however this won't work for the problem at hand.

ETA: here is a utility.
 
Last edited:
Robert Oz said:
I don't know of a quick way of making a mass change from relative to absolute, but there is a way around it.

Copy the tab that contains all the formulas.
Using this copied tab, instead of copying the formulas you need, cut them.
Paste to the new location.

When you cut and paste formulas in Excel instead of copy and paste, the formula references do not change. You will need to make copies of the tabs for however many copies of the formulas you need. Once you're done, just delete the extra copied tabs.
Click to expand...
I hadn't thought of that! That will work, and work quickly.

varwoche said:
Ctrl+Enter might be your forgotten keystrokes however this won't work for the problem at hand.

ETA: here is a utility.
Click to expand...

Wudang said:
Apologies - you're right. This macro appears correct from a quick scan. This PC doesn't have excel on it
http://www.ozgrid.com/VBA/formula-ref-change.htm
Click to expand...

Great minds... this macro looks perfect. Will give it a shot after lunch, thanks.
 
Depending on what you're doing, you could also do a simple "Find and Replace" using CTRL+H.

The work I do is fairly spreadsheet intensive, and when I'm faced with tons of cells I need changing, I can go the "Find and Replace" route. Assuming, of course, that there is a general amount of uniformity between the cells you're editing.
 
You must log in or register to reply here.

Back
Top Bottom