How to Copy Exact Formula in Excel (without Changing Reference)
Anyone know the shortcut for copying the formula in a cell to another cell without the reference changing (and without using the $ to anchor it)? I've seen someone do it but I forgot what the keys were.
How to copy a fomula in excel
This can be done simply by going to the paste special menu and selecting formulas.
Shortcuts: Copy something first and then select Alt + E + S+ F.
How to copy an exact forumla in excel
@jackofalltrades" recommends:
Highlight the desired cell/cells with formulas you want to copy.
Ctrl+H (find/replace) find all "=" equal signs and replace with "]" or any uncommon symbol.
Now all your formulas are busted, but this is good because we don't want excel to think it's a formula.
Copy the busted text and paste wherever you'd like and then highlight those copied cells and Ctrl+H again, this time find "]" and replace with "=".
Now you have copied the exact formulas.
Copy the exact formula with the Factset Plug-In
With the factset excel plug in this become much easier:
Shortcut: Ctrl + Alt + , (to copy) then Ctrl + Alt + . (to paste)
Looking to become an excel wizard?
Sign up for our financial modeling training course to learn all this and more.
are you talking about paste special? there is a feature that allows for that i think
none of the specials do it.
Cut (Control+X) and paste (Control+V) it. Otherwise, you can enter the cell (F2), highlight the entire formula (from start to finish), copy (Control+C) and paste it into another cell.
sure, for a single cell. and for multiple cells at once...?
Yes I know you can do that...but there's a shortcut for that
ctrl+' copies formulas exactly, but only in the cell directly below...
go to cell, hit right click, paste special, formulas
what is this click?
Don't listed to the BJ expert below (Marcus)
on multiple cells at once? nope,
Copy the cell then Alt+F+C+N
All there is to it.
You guys are bunch of n00bs.
lol
I hope the OP isn't my intern.
"Copy the cell then Alt+F+C+N"
Don't do this! This will close the current workbook and not save it.
I just learned an important lesson of not blindly doing things I see on the Internet.
I guess some people will find this funny, but now I have some to speed a while recreating what I just lost :-(
that's totally your own fault, and hilarious
You do realize people can lose important data if they do this. Believe it or not, there is actual work going on in the world that can save lives while you sit there being a troll you irrelevant, non-contributing zero. TO ANY ONE WHO READS THIS POST; DO NOT FOLLOW THIS ADVICE!
It's funny because Marcus has contributed top-tier advice here since forever and you've done like one thing
Asshole. That destroyed three hours of work.
Halberstram's advice will destroy your workbook.
You kill the thread
THIS CLOSES YOUR WORK DON'T FALL FOR IT
Nice Marcus, good call.
[Bookmark this!]
and boom goes the dynamite.
Nice trick, man! Very creative!
hey jackofalltrades, i just joined here to say thanks... your replace trick worked for me too saving me a lot of time. what i don't understand, though, is why on earth microsoft makes us jump through loops to do basic stuff with excel 2013 that we used to have no problems doing using prior versions of excel. i liked excel '13 the moment i bought it and started using it, but then i bumped into this issue and it slowed me down immensely because every time i wanted to paste a formula from one book into another - or when copying a sheet to another workbook - all the equations in the cells would get instantly linked back to the book of origin. it gets even more annoying when you realize that there apparently is no way of doing this with paste special as it used to be the case with prior excel versions. again, thanks for sharing your solution.
I still use it to this day... and I'm 2 career paths removed from IBD. Happy to help :)
Genius
Thanks much. This trick still works, and even works on Google Sheets.
Brilliant trick mate, thanks for sharing. Gotta LOL at all the other "answers" that totally missed the point and wouldn't help at all.
At this point, it makes more sense to enter the cell and highlight its contents and then repaste it... Unless it's multiple cells.
it's multiple cells...but thanks
MUAHAHAHA I have found an extremely satisfying way of doing this, which seems to specifically grind excel's gears in the process.
Select the range you want to copy Ctrl+x Open a new workbook Paste into the workbook Save the workbook Close the workbook Go back to your original workbook Ctrl+z Open the new workbook Select the range Ctrl+X Go to where you want the cells pasted Ctrl+V If you need to copy the range into multiple locations,close the new workbook without saving changes, i
This might seem long winded, but the only change in the formulae, is that it now specifies the sheet the cell is on. This means no possible mistakes from substitution.
Fuck you excel, eat a whole bag of bratwursts
Outstanding! Just saved me about 4 hours.
Haha, haven't been on a while, but glad my trick is being put to good use...
Hahaha, strong first post. What a way to ease into a holiday.
you guys are retarded...it literally takes less than a second to go into a cell and highlight (shift home)/copy a formula. Why the fuck do you have to find and replace stuff?
The paste special formula only works if every cell is anchored, which is obviously not what the OP was looking for.
Factset Add-in macro
Copy exact formula alt + y2 + m + c + e Paste exact formula alt + y2 + m + c + p
Did I miss something here? Did someone forget one of the greatest, most handy shortcuts ever?
Highlight the range of cells you wish to copy the formulas of and press ctrl + alt + , then go to the first cell in the sequence of cells you wish to paste into and press ctrl + alt + .
Doesn't copy the formatting, just the exact formula(s) of the cell(s) you initially highlighted. This has always been one of my favorite time-saving shortcuts.
This is actually the answer to the question, but this might require an add-in (factset?) because it worked on my computer at a prior firm, but doesn't at my new firm.
Install Macabacus >> copy formula >> ctrl + alt + e >> Pastes EXACT formula (cell references identical to original formula).
If you really want to get fancy >> ctrl + alt + d >> Pastes DUPLICATE formula (cell references "maintain formulaic relativity"). Said more simply, linked cell references maintain their original locale per the copied formula, but formulaic references are dependent on where you choose to paste the formula (okay, I probably just increased confusion levels there). You must try this, saves me light years when checking my work / messing with complex assumptions.
And now for the fanciest of fancy - the grande finale. This magic trick has saved me an ungodly amount of time. All who witness will bow down, begging that you (a modern day Houdini) reveal the secret behind these few magic keystrokes that you so effortlessly pulled from your sleeve.
With a formula already copied to your clipboard, navigate to your desired destination cell, and hit >> alt + h + v + s + l.
Boom.
You have now just PASTED LINKS to the copied cell in your selected destination cell. This works within the same worksheet / separate worksheets / separate workbooks, for one / two / a whole range of cells. The possibilities are endless. Very helpful when searching through client files - find the number you need, copy it, and navigate back to your model to paste a link wherever you see fit.
Happy modeling friends.
macabacus is god's gift to banking, i swear.
"Alt + E + S + (whatever)" is where its at. best shortcut by far. along with the "Ctrl +" hotkey to add rows/columns.
...not for multiple cells at once...
f2, then ctrl+shift+left left left left left til you get all that sucker. Then ctrl+c that thing. Then go to the cell of your choice and hit f2, ctrl+v
it's not the quickest way, but it's fast enough and gives you a few seconds to think about what you're doing with your life.
please let me know if you have any questions. regards.
...not for multiple cells at once, which was the whole point of the OP.
1.Put Excel in formula view mode. The easiest way to do this is to press Ctrl+
(that character is a "backwards apostrophe," and is usually on the same key that has the ~ (tilde). 2.Select the range to copy. 3.Press Ctrl+C 4.Start Windows Notepad 5.Press Ctrl+V to past the copied data into Notepad 6.In Notepad, press Ctrl+A followed by Ctrl+C to copy the text 7.Activate Excel and activate the upper left cell where you want to paste the formulas. And, make sure that the sheet you are copying to is in formula view mode. 8.Press Ctrl+V to paste. 9.Press Ctrl+to toggle out of formula view mode.Nice. That works too. I never use formula view, but that's a great use for it. Maybe that's why they designed it lol.
Bunch of poverty analysts... I pray that none of my analysts are asking this question.
FactSet = CNTRL + ALT + , then CNTRL + ALT + .
If you don't have FactSet, close more deals so that you can afford better add-ins than CrapitalIQ
It may be a shock, but some of us use Excel for things that aren't finance related, and don't have any of your pretty boy plug ins. :-)
Factset sucks
Aut doloremque nam veniam ut eveniet voluptas laboriosam. Velit aut suscipit dolores eum et est. Nobis magni et doloremque neque distinctio. Asperiores a facilis et voluptas et rem.
Sit voluptas occaecati aspernatur cumque nobis omnis non. Aut modi amet hic earum omnis laborum fugit. Dolores nemo adipisci alias quia similique voluptatem rerum sed. Voluptatum porro quis enim fugit ut et sunt. Quo aut et ullam minus necessitatibus sunt.
Enim pariatur ad quo repellendus sequi est quo aut. Blanditiis necessitatibus consequatur nisi explicabo dolorum id. Minima assumenda voluptatem quo quod qui delectus.
Et sed tempora perspiciatis non. Eum occaecati vero accusantium non voluptatem aut. Cupiditate enim eos iusto animi eos. Sit minima sapiente magni unde mollitia est est nihil. Ratione voluptatem aspernatur voluptatem deleniti.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...
Maiores qui libero quos deleniti tenetur. Corporis officiis vero mollitia facere voluptas eum. Facilis aperiam unde sit eligendi quia sunt id iste. Sint ea doloremque dolore et beatae consequatur. Aspernatur consequuntur molestiae reiciendis et unde voluptas dolor. Voluptatibus eum ut voluptatem et optio.
Aut qui est aperiam. Mollitia quia suscipit voluptates sunt eligendi. Qui qui minus ut animi repudiandae vitae.
Iure consectetur alias aut et placeat velit id nostrum. Iste accusamus nobis sit placeat nostrum. Qui natus at quia quaerat doloremque. Omnis ad eius est consequatur. Iste neque quam magnam repudiandae nihil asperiores.
Magni aut mollitia et quasi veniam dignissimos. Est vitae error esse assumenda sed maxime rerum excepturi. Sint dolores veritatis perferendis cumque aut fuga. Optio aliquid culpa dolor aut adipisci.