Convert a Formula to Values

This is a very simple, but I think very practical little tip.   You need to convert a formula to values in cell or a range of cells to values.  I don’t know about you, but I do this all time.  I use formulas to manipulate text and numbers to save time then I just want the result of the formula.  You may have seen other tips and shortcuts out there and they are great too.

But there are only so many shortcuts I can remember and some rely on keyboards with the right menu button which I might not have on the day.  Some people have their own macro assigned to their Quick Access Toolbar (QAT) to do it.  But if you do this you can’t Undo, because the result has come from a macro.

Copy PasteValue Tip
1) Copy 2) Paste Value Simple.

So I have found the best solution of all is simply to add two tools to my QAT, one for Copy and one for Paste Values.  Note the Copy tool is under Popular Commands but you need to change the dropdown showing popular commands to All Commands to find the more obscure Paste Values tool.  Once done highlight your range 1) hit the Copy tool 2) hit the Paste Values tool.  3) Escape to leave the ‘marching ants’ range selection.  That’s it.  Saves me heaps of time – three keystrokes is surely the shortest it can be and because you are using native Excel and not a macro, you can Undo.

By the way,  I export my QAT Customizations to my Dropbox folder both because it’s a good backup of all my Office customizations, plus I can access these customization setting easily if I have to work on another PC for some reason.  Choose the Import/Export button at bottom of the Customization of QAT dialog, then Export all customizations to a folder on your cloud folder like Dropbox.  Then on the new PC go to the same place except import all customizations.  If you are working on someone else’s PC however, you don’t want to erase their customizations if they have any.  So best to export their customizations first as a temporary file, then import back in after you have finished your session so their Excel setup is the same as before you touched it.   If you are really sure they have no customizations, instead you could reset customizations using the button above Import/Export erasing your QAT customizations after you have finished your work.