Convert Formula to Values

If you like this post please share it!
  •  
  •  
  •  
  •  
  •  
  • 1
  •  
  •  

print
This is a very simple, but I think very practical little tip.   You need to convert a 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 for a another PC say to my client’s site.  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, best to export their customizations first as a temporary file, then import this in before you finish so their Excel setup is the same before you changed it.   If you are really sure they have no customizations you could reset customizations using the button above Import/Export erasing your QAT customizations.

 

Tagged with: , , ,