How To Delete Multiple Sheets In Google Sheets (All Ways)

Knowing how to delete multiple sheets in Google Sheets is useful if you want to get rid of numbers of sheets in bulk to save time.

Meaning, you can delete a bulk number of sheets without spending so much time doing it manually.

In Google Sheets, there are four convenient ways to delete multiple sheets:

  • Moving the sheet to keep in a new worksheet
  • Delete all sheets except the ones specified (using macro/script)
  • Delete the first 10 sheets (first N sheets)
  • Using a Google Sheets Add-on

The rules for deleting multiple sheets in Google Sheets are as follows:

  • The ways provided above are just workarounds, instead of deleting multiple sheets one by one.
  • There’s currently no Google Sheets feature yet that deletes multiple sheets in one click.

Let’s take an example.

Rod has been trying to optimize and speed up his worksheet by removing irrelevant sheet tabs in his Google sheet file.

How to Delete Multiple Sheets in Google Sheets

Luckily, Rod only needs the sheet tab named 05/03 Record. Other tabs are of no use and irrelevant. Hence, he can delete them to speed up and optimize his file.

Since the tab that he needs has no reference to other tabs, instead of manually deleting the other tabs 1 by 1, Rod exported the tab named 05/03 Record to a new spreadsheet.

Afterwhich, he deleted the old spreadsheet by moving it to trash.

Rod no longer had to spend so much time deleting each irrelevant tab.

Pretty clever, right?

Let’s have another example.

Like Rod, Jules has been trying to clean up and optimize his Google sheet file by deleting sheets that he thinks of no use.

How to Delete Multiple Sheets in Google Sheets

However, unlike Rod, Jules needs to keep three relevant tabs and delete the other ones. So, following Rod’s method could take as much time.

Jules used a macro instead in deleting multiple sheet tabs.

How to Delete Multiple Sheets in Google Sheets

He simply ran the script he made and viola! Irrelevant sheets have been deleted in a few seconds:

How to Delete Multiple Sheets in Google Sheets

Watch out for a more advanced tutorial and examples on how you can delete multiple sheets in the coming weeks. Be sure to subscribe to be notified. 

Awesome! Let’s begin getting to know more about the process and steps in deleting multiple sheets in Google Sheets.

The Anatomy of the Methods Used in Deleting Multiple Sheets

Let’s discuss each method and understand how each of them works:

  • Moving a Sheet to a New Worksheet – a simple workaround to save time to get rid of multiple sheet tabs. The sheet to be kept is exported to a new or an existing spreadsheet.
  • Delete all sheets except the ones specified (using macro/script) – a method that involves Google App Script. A one-block code that tells Google Sheets, thru script, which sheet tabs to delete by providing the list of tabs to keep.
  • Delete the first 10 sheets (first N sheets) – another method that involves Google App Script. A one-block code that tells Google Sheets the number of sheets to delete from left to right.
  • Using a Google Sheets Add-on – a method that allows users to install an add-on that manages Google Sheet tabs in bulk.

A Real Example of Deleting Multiple Sheets

Take a look at the file example below to see how to delete multiple sheets in Google Sheets:

How to Delete Multiple Sheets in Google Sheets

In the file above, notice that Rod had to keep only one sheet tab and delete all the other ones.

Instead of spending time manually deleting each Sample tabs, Rod did the workaround of exporting the only sheet that he needs. He used the feature Copy to > New Spreadsheet.

How to Delete Multiple Sheets in Google Sheets

This should be one of the easiest methods especially if the sheet you need to keep has no reference to another sheet.

On the other note, if the sheet you need to keep has a reference to a formula to another sheet, then, you should also export that other sheet and keep them together. Otherwise, you’ll get a #Ref! error to your sheet.

Rod here used the option New Spreadsheet. He could also use the option Existing Spreadsheet if he wanted to put his 05/03 Record sheet tab to his existing file, instead of creating a new spreadsheet.

Smart, right?

Now, let’s check Jules’ method!

In his file, Jules used a script to be able to delete sheets in bulk. See below:

How to Delete Multiple Sheets in Google Sheets

For now, we will only focus on the part of the script that should be modified to be able to delete certain sheet tab names.

Nonetheless, be sure to Subscribe to be able to be notified once we release a separate article regarding basic scripting.

Let’s get back to the topic.

Take note of the string enclosed in quotation marks in line 6-8:

How to Delete Multiple Sheets in Google Sheets

This is the part of the script that lets you identify which tabs to keep. Meaning, tab names that are not listed in this area will be deleted once the script runs.

Please also note that you can key in as many sheet tab names in that script. All you have to do is follow the format:

How to Delete Multiple Sheets in Google Sheets

Let’s try the script above to the file below:

Then run the script:

How to Delete Multiple Sheets in Google Sheets

See the file below after running the script:

How to Delete Multiple Sheets in Google Sheets

Since we put the sheet tab names “Important Tab 1”, “Important Tab 2”, “Important Tab 3”, “Test 1”, and, “Test 2”, in the script, other tabs were deleted.

The third method is almost the same as the second one. Only this time, we will not be identifying the names of the sheets to delete. Instead, the process will delete a specified number of sheets.

Take a look at the script below:

How to Delete Multiple Sheets in Google Sheets

You can easily notice that this is a shorter script than the first one. This is a very straightforward script that tells Google Sheet to delete a certain number of tabs.

In what order?

Simply, from left to right.

In the sample script above, the number of sheet tabs to be deleted is 10.

How to Delete Multiple Sheets in Google Sheets

So, if you’re just planning to delete 5 sheets from your file, you can change the part of the script below to 5:

Let’s try to run the script in the file below and see the magic happens:

How to Delete Multiple Sheets in Google Sheets

See the result after:

How to Delete Multiple Sheets in Google Sheets

The script deleted the first 5 tabs from the left namely Tab 1, Tab 2, Tab 3, Tab 4, and Tab 5.

Is there a way to delete multiple sheets in Google Sheet that doesn’t require coding?

Yes, there is!

Luckily, there’s an add-on available in Google Sheet that lets you manage sheets in bulk. It is called Bulk Sheet Manager.

How to Delete Multiple Sheets in Google Sheets

Using buttons, you can conveniently delete multiple sheets.

Voila!

Pretty amazing, right?

You may make a copy of the spreadsheet using the link I have attached below.

How to Delete Multiple Sheets in Google Sheets

Moving a Sheet to a New Worksheet

  1. Activate the sheet that you want to keep by clicking on it. For this guide, I will be selecting the tab named 05/03 Record.

 

  1. Right-click on the tab, then click Copy to.

How to Delete Multiple Sheets in Google Sheets

 

  1. The option to copy your sheet to a new spreadsheet or an existing one should appear. Click which applied to your scenario. In this guide, I’ll be using New Spreadsheet.

How to Delete Multiple Sheets in Google Sheets

 

  1. A prompt will appear telling that your sheet has been successfully copied to a new spreadsheet. Click Open Spreadsheet to view your file, then click Ok.

How to Delete Multiple Sheets in Google Sheets

 

  1. Your new untitled spreadsheet will open in a new tab.

How to Delete Multiple Sheets in Google Sheets

 

  1. You can now go ahead and delete your old file by moving it to the Trash. Click the File menu > Move to trash.

How to Delete Multiple Sheets in Google Sheets

 

Delete all sheets except the ones specified (using macro/script)

  1. On your Google Sheet file, click the Tools option in the menu bar, then click Script Editor.

How to Delete Multiple Sheets in Google Sheets

 

  1. This will open the Apps Script editor in a new tab. Make sure Code.gs file is selected.

How to Delete Multiple Sheets in Google Sheets

 

  1. Copy-paste the below code into the script editor. If there’s already a code in there, delete it.
function DeleteAllSheets() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheets = ss.getSheets();
for (i = 0; i < sheets.length; i++) {
switch(sheets[i].getSheetName()) {
case "Important Tab 1":
case "Important Tab 2":
break;
default:
ss.deleteSheet(sheets[i]);}}
}

 

  1. Click on the Save icon.

How to Delete Multiple Sheets in Google Sheets

 

  1. Click on the Run button.

How to Delete Multiple Sheets in Google Sheets

 

  1. The script will delete all the worksheets except the ones we have specified in the code.

 

Delete the first 10 sheets (first N sheets)

  1. On your Google Sheet file, click the Tools option in the menu bar, then click Script Editor.

 

  1. This will open the Apps Script editor in a new tab. Make sure Code.gs file is selected.

How to Delete Multiple Sheets in Google Sheets

 

  1. Copy-paste the below code into the script editor. If there’s already a code in there, delete it.

function DeleteFirstNSheet2() {
var spreadsheet = SpreadsheetApp.getActive();
for (i = 0; i < 4; i++) {
spreadsheet.deleteActiveSheet();}
}

 

  1.  Click on the Save icon.

How to Delete Multiple Sheets in Google Sheets

 

  1. Click on the Run button.

How to Delete Multiple Sheets in Google Sheets

 

  1. The script will delete 4 worksheets starting from left to right.

How to Delete Multiple Sheets in Google Sheets

 

Using a Google Sheets Add-on

  1. On your Google Sheet file, click on the Add-on option in the menu bar. Then, click Get add-ons.

How to Delete Multiple Sheets in Google Sheets

 

  1. The Google Workspace Market dialog box will appear. Type in the search bar ‘Bulk Sheet Manager’, then hit the Enter key.

 

  1. Click on the ‘Bulk Sheet Manager’ add-on and install it. The add-on may ask for your permissions.

 

  1. Once installed, click the Add-ons option > Bulk Sheet Manager > Manage Sheets.

 

  1. In the Bulk Sheet Manager user interface, check the sheet tabs that you want to delete.

 

  1. Finally, click on the Delete button.

How to Delete Multiple Sheets in Google Sheets

 

  1. Those checked tabs should now be deleted from your Google Sheet file.

 
That’s pretty much it. You can now delete multiple sheets in Google Sheets together with the other numerous Google Sheets formulas to create even more powerful formulas that can make your life much easier.

 

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published.

You May Also Like