SubscribeQuickly Change Formulas Using Find / Replace
So you have built that spreadsheet report your boss wanted. And you were all eager to use it in your presentation. But in the last minute, your boss asked you to change average sales to total sales figures (or measure growth wrt to 2006 instead of 2007). You also want to grab an espresso before rushing to the meeting. Now what?
Simple, skip the coffee. 
Of course, I am kidding, who would skip coffee for a bunch of formulas ? So, we use Find / Replace to do the dirty work while we let the roasted beans restore sanity. Just press Ctrl+` (it is the key next to 1), this will enable formula view. Now press Ctrl+H and change the spreadsheet formulas or input range en masse.
What are you still waiting for? Go get that espresso!
Do you know that you can use find / replace to change spreadsheet formatting too ?
This post is part of our spreadcheats series – Learn Excel in a month.
Excel Formulas Too Confusing ? You should get Excel Formula Help E-Book
|
Trackbacks & Pingbacks
- Pingback by Use Mouse to Edit Formula Ranges - Excel Tip | Learn Excel | Pointy Haired Dilbert - Chandoo.org on May 5, 2009 @ 10:32 am
Comments
RSS feed for comments on this post. TrackBack URI
Leave a comment
If you have a question, please ask in the forums
At Pointy Haired Dilbert, I have one goal, "to make you awesome in excel and charting". PHD is started in 2007 and today has 300+ articles and tutorials on using excel, making better charts. 
Chandoo,
this is a really cool stuff what I use quite often. In addtion this method also could be a good choice to switch the reference type of the formulas from relative to absolute or vice versa. (just simply replace the $ in the same way).
Andras
This could be the most useful tips I’ve seen in a while. I use this all the time and can instantly change 400 formulas with a few clicks. Like so many other functions in Excel, I don’t know what I would do without this one.
Keep ‘em coming!
@Andras: you are right, we can use find / replace to change references, reference types etc. Now, only if they had regex in find/ replace, we could so much more
@Tony Rose: Thank you. This is very useful and powerful feature. I even use it for cleaning up data. While formulas are good, they are not the solution for every problem. Often when I need more powerful cleanup / changing, I copy paste the stuff to text editors like notepad++ and then use their find/replace to do the dirty task.