Excel Absolute Reference Copy

[MRennie]

Older versions of Excel used to have a feature that you could toggle to allow you to copy and paste using Absolute References. I can't seem to find this feature in the current version of Excel. It seems that the only option now is to use the dollar sign on cell references in formulas.

Does anyone know what I'm referring to and  if the old feature still exists?

Thanks,

Mike

[#2]

Did you try to press F4 when you highlight the references. F4 should toggle between absolute and relative references. @MRennie

[#3]

Thanks @Oken . 

Unfortunately that simple adds or removes the '$' from the formulas. What I want is to be able to copy a bunch of cell contents and paste them somewhere else without it changing the formulas, regardless of whether I've set the '$' or not. This is how it used to work.

Thanks,

@MRennie 

[#4 · Accepted solution]

@MRennie 

The workaround to copy/paste formulas is like https://www.xelplus.com/copy-excel-formulas-without-changing-cell-references/, it was the same on previous versions of Excel if only you didn't use some third-party add-ins.

[#5]

Thanks @Sergei Baklan 

This will work. I guess they no longer have the built in feature.

[#6]

@MRennie  I don't recall that being the case in the past but I don't remember what I ate yesterday.  That said, here are a couple things that might help?

A) if you want the exact same formula pointing at the exact same cells then why not just 'paste link' which basically pastes =[previous cell ref] which will give you the same value at that new location as the previous location without excel needing to repeat the calculation.

B) there is a difference between copying a cell and cut and paste a cell.  SO, if you copy it will do that relative position but if you cut and paste it won't. That said you, you can only cut & paste 1x at a time (i.e. you can not cut, paste, paste, paste, ....). But what you can do is (for example copying range A1:M100 to multiple locations)

1. copy cells A1:M100 down to A101
2. cut A1:M100
3. paste in new location
4. copy A101:M200 back to A1
5. repeat steps 2-4 as needed

I know it isn't pretty but might help with what you need.

[edit] didn't see the new reply until after I posted.  The solution in that link is probably easier than this one.

[#7]

Hi @mtarler,

Thanks for your suggestion.