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.
- 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.
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.
- 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,
Read More: VBA to Remove Formulas in Excel Keeping Values and Formatting
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.