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.


Download Practice Workbook

Download the following Excel file for your practice.


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.

Remove Formulas From Entire Workbook by Grouping Sheets

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

Remove Formulas From Entire Workbook by Grouping Sheets

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

Here is the result,

Removing Formulas From Entire Workbook by Grouping Sheets (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.

Remove Formulas From Entire Workbook by Grouping Sheets (Ungroup)

Read More: How to Remove Formula in Excel and Keep Values (5 Ways)


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.

Remove Formulas From Entire Workbook with VBA codes

  • 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,

Removing Formulas From Entire Workbook with VBA codes

Read More: VBA to Remove Formulas in Excel Keeping Values and Formatting


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.


Related Articles

Hafiz Islam
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo