Hello Excellers and welcome back to another blog post of #ExcelTips in my 2019 #FormulaFriday series. Today let’s look at 4 ways to convert cells with formulas to values. So you are probably thinking when would I make use of this then?. Well, how about if your worksheet contains a lot of volatile formulas or lots of array formulas. These will degrade the performance of your workbook?. You workbook maybe the full and final presentation. It needs no further amending. Or you simply just do not want anyone to see your formulas!. So, whatever your reason, I will share with you my top 4 Excel Tips for converting formulas to values in Excel.
I have a sample of data. So, we will use the same data for all the methods I am about to share with you. It is a simple set of sales values with a sales+ tax calculation.
Method 1. Paste Special.
Yes, that is correct, the good old Paste Special method. Follow the steps below.
- Select the range of cells that contain the formulas you want to convert to values. In this example, it is D2:D9.
- Right-Click the mouse to copy the values.
- Right- Click again to access the Paste Special options and select Values
- Or, Home Tab | Clipboard | Paste Special | Values.
Method 2. Right-Click Drag and Drop.
If you want to avoid using the Paste Special method then this one may be for you. It’s a neat and quick way to convert formulas to values. We will use the same data set as in Method 1.
- Select the range of cells with your formulas.
- Right- Click on the edge of your selection of cells.
- Hold the Right – Click on your cells. Next, move your selection to the right and bring it straight back to its original position and drop it.
- A menu will appear, you need to select Copy Here As Values Only.
Method 3. Using A Keyboard Shortcut.
If you are a fan of Excel keyboard shortcuts then this is the method for you. Using keyboard shortcuts to convert your Excel formulas to values.
- Select the range of cells that you want to convert to values.
- Hit Ctrl+C to copy those cells.
- Press Alt+E S V
- This is will replace all formulas with their values.
Method 4. Using VBA (Macro).
So, my final method uses some simple Excel VBA code. With just a few lines of code, we can write our own Excel macro. How cool is that?
This macro will convert ALL formulas in an Excel worksheet to values on our Excel worksheet.
- Open the Visual Basic Editor. You can do this by either selecting the Developer Tab | Code | Visual Basic. Alternatively, you can hit ALT+F11 as a shortcut to open the Visual Basic Editor window.
- Decide where you want to save your Excel code. If we want to use this code only in this workbook then we need to insert a new module into this workbook. If we want to use this code in ANY workbook then we can create a module in the Personal Macro Workbook. I invite you to read my blog posts relating to the Personal Macro Workbook below. Saving it in this specific workbook makes your code available to any Excel workbook you use.
- We select all of the cells in all of our worksheets in our workbook. (Worksheets. Select coding will select all worksheets in the current workbook).
- Excel uses the PasteSpecial Paste:=xlPasteValues command to paste the value only back into the cells of the worksheets.
- Test your Macro!.
That is all there is to this piece of code. Save this in your Personal Macro Workbook. If you do this it will be to hand if you need to convert ALL formulas in ALL worksheets in your current workbook. A quick rough and ready Excel VBA Macro!
What Next? Want More Tips?
So, if you want more top tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Do You Need Help With An Excel Problem?.
I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST.
