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

Get FREE Advanced Excel Exercises with Solutions!

Many of us know the techniques to remove formulas from a single worksheet. However, when we need to remove formulas from the 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 the right place. In this article, you will learn 2 easy ways to remove formulas from multiple worksheets.


How to Remove Formulas from Entire Excel Workbook: 2 Easy Methods

Let’s introduce our sample dataset first. From Sheet 1 to Sheet 5, let’s apply the AVERAGE function 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 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


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


Download Practice Workbook

Download the following Excel file for your practice.


Conclusion

In this tutorial, I have discussed 2 easy ways to remove formulas from the entire workbook. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


<< Go Back to Remove Formulas in Excel | Excel Formulas | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Hafizul Islam
Hafizul Islam

Hafizul Islam is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo