You just finished setting up the formulas in your workbook and now you’re wondering if there’s a way to save the file without the formulas so you can share it with someone who doesn’t need to know how the calculations are performed. There sure is.
IMPORTANT:
Before trying any of the methods described below, please secure a copy of the file first. We want to preserve the formulas in the original workbook; otherwise, you might end up re-adding all the formulas one by one in case you need them again especially after accidentally deleting the sheet.
Table of Contents
Save the Data or Workbook Without Formulas in Excel by Copying and Pasting the Cells as Values
1. If you have multiple sheets in your workbook and you only want to share some of them, copy these sheet(s) first onto a new workbook. (Skip this step if you intend to share all sheets inside the workbook).
To do this, activate all the sheet(s) you want to share.

While pressing the CTRL key, select these sheets.
Right-click on one of the selected sheets. A pop-up menu will appear. Select Move or Copy.

When the Move selected sheets menu appears, select (new book) from the dropdown list.

Tick the Create a copy checkbox, and click OK.
You should now be redirected to a new workbook containing only the worksheets that you have selected.

2. Now, it’s time to select all cells within the sheet(s) and copy and paste them as values.
If you did Step 1 and have selected multiple sheets, leave these sheets selected or activated. Doing so will tell Excel to apply the same changes to these selected sheets.
Select all the cells within the sheet(s) by clicking the triangle located at the top left corner (near cell A1).

Once the cells are all selected, press CTRL + C to copy them.
You should now see moving broken lines surrounding the selected cells.
When that happens, press CTRL + ALT + V. This will open the Paste Special menu.

Tick the Values option, then click OK.
And that’s it! You have successfully removed all the formulas from the selected sheet(s) and only retained the resulting values.
Don’t forget to press ESC to deactivate the copy mode.
Also, if you have selected multiple sheet(s), remember to select one of the sheets to disengage multiple sheet selections.
You can now proceed with saving this file and sending it to the intended recipients.
Save the Data or Workbook Without Formulas in Excel Using the Quick Access Toolbar
If you need to regularly remove the formulas from your current workbook (and other workbooks as well), you can make use of the Quick Access Toolbar (QAT) so you can paste the cells as values with just one click.
To set this up:
1. Click the Customize Quick Access Toolbar button.
You’ll see this at the top left of the menu bar (near the workbook’s name).

From the menu that shows up, select More Commands.
2. The Excel Options menu will appear.
Select All Commands from the “Choose commands from”dropdown list.

From the list of items, look for Values [Paste Values].
If you can’t find it, you might be using the older version of Office. If so, try looking instead for Paste Values.
3. Once the “paste as values” command is selected, click the Add >> button.

When you see the command added to the list of QAT, click OK.
You should now see the “paste as values” icon in the QAT (as shown below).

Note that it is currently disabled. It will only be activated once you copy cells inside the workbook.
Using this new command from the QAT method, we can now quickly remove the formulas from the data within the workbook. To do this:
1. Select all the sheets you want to remove the formulas from.

2. With these sheets activated, select all cells within one of the sheets.
You can do so by clicking the triangle on the top left corner (just before cell A1).

3. Once all cells are selected, press CTRL + C to copy them.

Then, click the “paste as values” command that we have added in the QAT.
And that’s it! You have successfully removed all the formulas from all the selected sheets.
Save the Data or Workbook Without Formulas in Excel Using Macros
If you have a whole lot of sheets in your workbook and you want to remove the formulas from all of them, then the best method for you would most likely be by using macros.
1. With your workbook open, press ALT + F11. This will open the VBA Editor.

2. Copy the following code and paste it onto the new module added.
Option Explicit
Sub RemoveFormulasInWorkbook()
Dim ws As Worksheet, answer As Integer
answer = MsgBox(prompt:="You are about to remove all the formulas in this workbook." & vbNewLine & "Continue?", Buttons:=vbExclamation + vbYesNo)
If answer <> vbYes Then Exit Sub
For Each ws In ThisWorkbook.Worksheets
ws.Cells.Copy
ws.Range("A1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Next
MsgBox prompt:="All formulas inside this workbook are removed.", Buttons:=vbInformation
End Sub

3. Once you’ve added the code, it’s time to run it.
Before running the code, please remember to secure a copy of the workbook first. Once the macro runs, all changes cannot be undone.
To run the macro, place the mouse cursor anywhere inside the code and press F5.
4. A prompt will appear to confirm this process.

Click Yes to continue.
5. Another prompt will appear to inform you once the macro is done running.

Click OK. And that’s it!
All formulas will be removed from all the worksheets inside the workbook. Only the values will be retained.
6. When you save the file, you will be seeing the following prompt if it’s in .xlsx format:

If you want to save the macro, click No and save the file in .xlsm or .xlsb format.
If not, just click Yes.
Related Tutorial: How to Save an Excel Table as an Image
Conclusion
Excel formulas are such a great help when setting up our templates and reports.
There are instances, however, when we need to hide these calculations so that they remain confidential, and the intended reader can’t just see how the numbers were processed.
I hope you find the suggested methods above helpful.