Google Sheets allows you to create multiple worksheets in a single workbook. But there is a downside to this feature which is the inability to delete several worksheets at once.
This can become quite frustrating if you have to delete each sheet manually. It could waste your time and also put you behind your work schedule.
Unlike Microsoft Excel, there is no inbuilt or easy way to automatically select multiple sheets and delete them at once.
In this guide, we will learn and discuss the methods used to delete multiple sheets in your workbook at one, in Google Sheets.
Move the Sheets You Need to a New Workbook
In case you have a workbook filled with the important worksheets and the irrelevant worksheets, you can simply move the needed worksheets into a new or existing workbook and delete the old workbook with the unwanted worksheets.
Using the sample worksheet below, we have a workbook containing a worksheet with some important information on the sales analysis in a particular store. Other sheets are also present in the workbook.
To move this sheet into a new document, you need to follow the steps below.
Step 1: Click on the Sales Analysis tab.
Step 2: Select the “Copy to” option.
Step 3: Click on New Spreadsheet or Existing Spreadsheet, based on your choice. If you selected the “Existing Spreadsheet” option, you have to select and specify the spreadsheet you want your worksheet copied to.
These steps would copy the selected worksheet into a new or existing spreadsheet document. After that, you can delete the old workbook with unwanted worksheets.
Note that the copied worksheet should not have any formula or function referring to a cell or column in the deleted worksheets.
Also, if you have multiple important sheets, you would have to repeat the steps for each worksheet.
It’s not the best method but it does serve its purpose of deleting multiple worksheets at once.
Delete All Sheets Except the Specified Sheets.
To use this method, you would need the help of a code. The code would delete the unspecified worksheets in the workbook.
In the sample worksheet below, I have a workbook with multiple sheets but I want to keep the Sales Analysis worksheet and the Stock Count Sheet while the other sheets are deleted.
The app script code used is stated below.
function DeleteAllSheets() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheets = ss.getSheets(); for (i = 0; i < sheets.length; i++) { switch(sheets[i].getSheetName()) { case “Sales Analysis”: case “Stock Count”: break; default: ss.deleteSheet(sheets[i]);}}}
Note: In the parts in the code where “Sales Analysis” and “Stock Count” are stated, replace them with the names of your specified worksheets.
The Apps script code operates by scanning the names of the worksheets in your workbook. Once a specified sheet title is detected, it gets skipped. If otherwise, the sheet gets deleted.
The code immediately deletes every unspecified worksheet in the workbook. If you know you might need the unspecified sheets later, ensure you copy the sheets for future purposes.
To input this code in your Google Sheets workbook, you need to follow the steps below.
Step 1: Go to the toolbar and select Extensions.
Step 2: From the provided list, select Apps Script.
Step 3: You get redirected to the Apps Script website. On the left side of the page, select Code g.s.
Step 4: There is some random code pasted there. Delete it and paste the apps script code above.
Step 5: Click on the Save icon at the top of the page.
Step 6: Select the Run icon at the top of the page.
Step 7: Grant the required permissions to allow the code to take effect on your worksheet.
The code would be inputted into your workbook and all unspecified sheets would be deleted.
Delete the First 10 Sheets or First N Sheets.
With the use of a macro code, you can easily delete a selected number of worksheets in your spreadsheet document.
The macro code stated below would delete the first ten worksheets from the left side of the tab.
function DeleteFirstNSheet() { var spreadsheet = SpreadsheetApp.getActive(); for (i = 0; i < 10; i++) { spreadsheet.deleteActiveSheet();} };
The code operates by using the variable (i) and scans through the selected number of sheets from the left side. The variable moves in a circle deleting the sheets that fall in the specified number.
The variable stops at the 10th sheet from the left.
Running the code above works the same way as the code used to specify sheets.
Using an Add-on
If you don’t fancy any of the methods stated above, you may prefer this method. This method involves the use of an add-on.
Add-ons are added features and tools that are imported or installed from the Google Workspace Marketplace. Here, we would use an add-on called Bulk Sheet Manager.
This is an add-on that helps to select multiple sheets to delete and delete them in a single go.
To install this add-on, you need to follow these steps.
Step 1: Click on Extensions on the toolbar.
Step 2: Click on Add-Ons.
Step 3: Click on Get Add-ons.
Step 4: You would be redirected to the Google Workspace Marketplace website. Various apps, innovations and tools are sold here. Some are paid for while some are for free.
Step 5: In the search box, enter Bulk Sheet Manager.
Step 6: Click on the Bulk Sheet Manager, and install it.
Step 7: You have to grant permission for the Bulk Sheet Manager to access your Google sheet.
You would be redirected to confirm your google account to be linked with the Bulk Sheet Manager. After the permissions are granted and the account has been verified, the Bulk Sheet Manager becomes installed as an add-on in your Google sheet.
Step 8: After it’s been successfully installed, go to Extensions on the toolbar.
Step 9: From the list of options here, Open the Bulk Sheet Manager.
Step 10: Select the sheets you want deleted and click on Delete.
These are the steps to take to successfully use an add-on to delete sheets in your workbook.
The Bulk Sheet Manager add-on not only deletes multiple sheets, but is also used to protect and hide worksheets.
Final Thoughts
Here, we’ve covered the known ways used to delete multiple sheets in a single Google Sheets document.
Although there is no inbuilt feature for deleting specific sheets, with these workaround methods, you can delete many sheets in a single workbook.
Do you have a favorite method or do you know any other workaround methods to deleting multiple sheets in Google Sheets? Share them with us in the comment section.
I hope this guide comes in handy when needed. Thanks for reading.
