How to Remove Formulas from Entire Excel Workbook (2 Easy Ways)

Many of us know the techniques to remove formulas from a single worksheet. However, when we need to remove formulas from entire workbook, the application of the techniques for the single sheet will be tiresome. If you are looking for a method to accomplish this task for multiple sheets at one time, you are in a right place. In this article, you will learn 2 easy ways to remove formulas from multiple worksheets.

Table of Contents hide

Download Practice Workbook

2 Easy Ways to Remove Formulas from Entire Workbook

1. Remove Formulas from Entire Workbook by Grouping Sheets

2. Remove Formulas from Entire Workbook with VBA codes

Conclusion

Download Practice Workbook

Download the following Excel file for your practice.

Removing Formulas from Workbook.xlsm

2 Easy Ways to Remove Formulas from Entire Workbook

Let’s introduce our sample dataset first. From Sheet 1 to Sheet 5, there is an AVERAGE formula in all sheets. Our goal is to remove this formula from the entire workbook.

1. Remove Formulas from Entire Workbook by Grouping Sheets

To apply this method, first, we need to group all 5 sheets. Then, when we will apply formula removing methods in one sheet, the formula will be erased from the entire workbook. Follow the steps below.

Steps:

First of all, click the Name tab of the 1st sheet. Then press & hold the SHIFT key and simultaneously click on the Name tab of the last sheet. Now, these sheets are grouped together.

Next, go to any of the grouped sheets > select and copy the cells that you need to remove formulas from.

Now, Right-click on the copied cells. Then go to Paste options and select Values(V).

Here is the result,

It will erase formulas from all the grouped 5 worksheets. After removing the formulas, you can ungroup all the 5 sheets by Right-clicking and then selecting Ungroup.

2. Remove Formulas from Entire Workbook with VBA codes

Another magic way to remove formulas from the entire workbook is to apply macro. Just follow the steps below.

Steps:

To start, press Alt+F11 or go to the Developer tab. Click on the Visual Basic button. A window will pop up. Under this window, click on the Insert tab. Then select the Module option. A module window will pop up.

Copy the following code and paste it into the module window.

Sub RemoveFormulasFromExcel()
    Dim Xws As Worksheet
    For Each Xws in Worksheets
        Xws.UsedRange.Value =Xws.UsedRange.Value
    Next
End Sub

Finally, here is the result,

Conclusion

In this tutorial, I have discussed 2 easy ways to remove formulas from the entire workbook. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.