How to Save the Data or Workbook Without Formulas in Excel?

Right-click on the selected sheets and select Move or Copy from the menu that pops up.

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.

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, click the sheets 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.

Right-click on the selected sheets and select Move or Copy from the menu that pops up.

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

Steps to move selected sheets to a new workbook.

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.

Sample output after moving selected sheets to a new workbook.

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).

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.

From the Paste Special menu, select Values and click OK.

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).

Click the Quick Access Toolbar button and select More Commands.

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.

Steps to add the Paste As Values command in the Quick Access Toolbar.

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.

Once the “paste as values” command is selected, click the Add >> button. Then, click OK.

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).

Example of what happens to the Quick Access Toolbar once the Paste as Values command is added.

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.

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).

Select all cells within one of the sheets 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.

Press CTRL + C to copy the cells. Then, click the Paste as Values command from the Quick Access Toolbar.

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.

Go to the Insert menu and click Module.

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
Copy the code and paste it onto the new module added.

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.

The prompt that will appear once you run the code.

Click Yes to continue.

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

The prompt that appears 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:

The prompt that appears when you save a .xlsx file with macro.

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.

Author: Jessica

I am an Excel and Access VBA Developer with years of experience in developing customized solutions to automate complex business processes. I am highly skilled in creating user-friendly interfaces that enable clients to easily interact with and analyze their data.

Leave a Reply

Your email address will not be published. Required fields are marked *