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.
- 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 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.
- 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.
Dim Xws As Worksheet
For Each Xws in Worksheets
Finally, here is the result,
Download Practice Workbook
Download the following Excel file for your practice.
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.
- How to Remove Formula When Filtered in Excel
- How to Remove Hidden Formulas in Excel
- How to Remove Automatic Formula in Excel