Here is a very simple & fun way to replace formulas with values.
(Earlier, we talked about how to do this with keyboard)
Follow these steps
- Select all the cells that contain formulas
- Right click on the selection border (your mouse pointer changes to 4 sided arrow)
- Right click + Drag the selection a few cells away and drop them back at original place (ie wiggle)
- A menu appears. Select Copy here as values only.
- You are done
See the demo aside.
More fun & productive ways to use mouse in Excel
You can do a lot of powerful & useful things in Excel with mouse. Check out our Mouse shortcuts section.
- 5 areas where mouse kicks keyboard’s butt
- Do you know these double click tricks
- Use mouse to edit formula ranges quickly
- Insert multiple columns at once with mouse
Say thanks to John if you like this tip
This is one of the most fun mouse shortcuts I have ever learned. Thanks to John for teaching me this wiggle technique.
If you too enjoyed it, please say thanks to John.
17 Responses to “Replace formulas with values with a simple wiggle”
I had seen this in an excel book may be Learn2007high by Bill Jelen.
Thank you. Awesome tip. I "paste values" all the time. What an excellent new method.
I copy & paste special to change my formulas into values - this is so much easier.
And I was just playing around with the other functions - easy to format or create hyperlinks.
Thanks so much - I'm sure this tip will become a well used one for me.
This doesn't seem to work in Excel 2010 on Mac. Is this Windows-only?
Very nice trick!
I'm loving the quick tips!
Being very pedantic I know, but if the range is already highlighted, I would personally favour the ctrl + c, then ctrl + alt + v + v combo.
I try to avoid the mouse as much as possible....
Adam, for selecting the range you can do ctrl + a and just drag and drop back the selection. Its much easier than what you have said.
I was just offering a keyboard only solution. By the time I've moved from the keyboard to the mouse & back again - I could have used the keyboard shortcuts about three times!
I try to avoid the mouse as much as possible...
Chandoo: Does not seem to work for me. I am using Win 7 Enterprise and Office 2013. All I get it quick analysis icon at right bottom corner of my range. I am using RANDBETWEEN formula to test it.
Thanks. It is very good
That is such an awesome tip. Thanks again Chandoo
Amazing tip Chandoo
Thanks a lot !!
Its a nice quick tip, thank you!
Doesn't work with me in excel 2007
Great simple trick, this is definitely quicker that they way I've been doing it for ages...and i thought my way was fast!
Really useful tip - thanks!
No. It did not work for me. I use Excel 2016 Professional Plus edition.
When I wiggle and come back and release, the mouse button, nothing happens. Any File>Options> Settings calibration required?