While you work within an Excel workbook, you might unintentionally add too many worksheets to your workbook.
While having multiple worksheets is handy in many situations, having too many can be frustrating, especially if you dont know how to delete multiple worksheets.
Not many Excel users know how to delete worksheets more than one at a time. Until now!
This article will show you how to delete multiple sheets in Excel, so you dont spend too long clicking “delete sheet” each time.
Working with multiple sheets is common in Excel. But sometimes you may need to delete several sheets at once. Deleting sheets one by one can be tedious and time-consuming. Fortunately Excel provides some quick ways to delete multiple sheets.
In this comprehensive guide, we will explore the quickest methods to delete multiple worksheets in Excel
Why Delete Multiple Sheets in Excel?
Here are some common scenarios where you may need to delete multiple Excel sheets
- Removing old, redundant sheets to declutter the workbook
- Deleting temporary sheets used for calculations
- Getting rid of sheets containing erroneous data
- Archiving only the required sheets before sharing the workbook
- Keeping the file size and loading time low by removing unneeded sheets
Deleting single sheets is easy. But when dealing with a workbook containing 50+ sheets, deleting each sheet individually can take forever. That’s where excel’s built-in options to delete multiple sheets come handy.
Method 1 – Delete Multiple Sheets Using Ribbon
The Ribbon interface provides a direct option to delete multiple selected sheets. Here are the steps to use it:
-
Open the Excel workbook containing the sheets you want to remove.
-
Select the sheets to delete. To select adjacent sheets, click the first sheet, hold down the Shift key, and then click the last sheet. For non-adjacent sheets, press and hold Ctrl, and click each sheet you want to delete.
-
Go to the Home tab > Cells group > Delete drop-down > Delete Sheet.
-
A confirmation dialogue box appears. Click Delete.
This removes all the selected sheets in one go.
Shortcut: Press Alt > H > D > S to delete selected sheets without navigating the ribbon.
When to Use?
Use this method when:
- You have a small number of sheets to delete
- The sheets are next to each other
- You prefer the ribbon interface over right-clicking
Method 2 – Delete Sheets Using Context Menu
The context menu provides a quick way to delete sheets without navigating through the ribbon. Follow these steps:
-
Select the sheets you want to delete. Use Shift/Ctrl as mentioned earlier.
-
Right-click on any of the selected sheets.
-
Click Delete from the context menu.
This will instantly delete the chosen sheets without asking for confirmation.
When to Use?
Use this method when:
- You have to delete only a few sheets
- The sheets to delete are identifiable by name or position
- You are more comfortable with right-clicking than using the ribbon
Method 3 – Delete Sheets Using VBA Macro
For bulk deletion of sheets based on criteria, VBA macros are the best method. Follow these steps:
-
Press Alt + F11 to open the VBA editor. Or go to Developer > Visual Basic.
-
Insert a new module from Insert > Module.
-
Type the following macro:
Sub DeleteSheets() 'Turn off alerts Application.DisplayAlerts = False 'Delete sheets Sheets("Sheet1").Delete Sheets("Sheet2").Delete 'Turn alerts back on Application.DisplayAlerts = True End Sub- Run the macro by pressing F5.
This will delete Sheet1 and Sheet2 without any confirmation prompts.
When to Use?
Use VBA macros when:
- You have a large number of sheets to delete
- The sheets have a pattern in their names or position
- You want to automate repeated deletion tasks
How to Delete Sheets Based on Criteria
Apart from deleting a fixed set of sheets, you can also delete sheets based on dynamic criteria. Here are two useful examples:
Delete All Sheets Except Active Sheet
To retain only the active sheet and delete everything else, use this VBA code:
Sub KeepOnlyActiveSheet() Dim sh As Worksheet Application.DisplayAlerts = False For Each sh In Worksheets If sh.Name <> ActiveSheet.Name Then sh.Delete End If Next sh Application.DisplayAlerts = True End SubDelete Sheets Containing Specific Text
To delete all sheets with a particular text string (e.g. “Sales”) in their names, use this macro:
Sub DeleteSalesSheets() Dim sh As Worksheet Application.DisplayAlerts = False For Each sh In Sheets If sh.Name Like "*Sales*" Then sh.Delete End If Next sh Application.DisplayAlerts = True End SubModify the text within Name Like as needed.
Troubleshooting Errors
You may encounter some errors when trying to delete sheets in Excel:
-
“Cannot shift objects off sheet” error – Undelete any charts/shapes in the sheet before deleting it.
-
Delete option grayed out – The sheet is protected. Unprotect it first.
-
Delete doesn’t work – The file is shared/stored online. Save a local copy and then delete sheets.
-
Sheets reappear after deletion – The file may be corrupted. Create a new workbook and transfer data.
Best Practices
Follow these tips for smoothly deleting multiple Excel sheets:
-
Check sheet protection status – Unprotect any protected sheets first.
-
Save a copy before deletion – In case you need to revert, keep a backup copy.
-
Double-check sheets before deletion– Avoid accidentally deleting important data.
-
Use macros for bulk deletion – It’s faster than manually selecting sheets.
-
Close file after deleting sheets – Saving with many deleted sheets slows Excel.
FAQs
Here are some common questions about deleting multiple sheets in Excel:
-
Can I recover deleted sheets in Excel?
No, once deleted, sheets cannot be recovered in Excel. The only way is to restore from a previous backup.
-
Can I delete sheets in a protected workbook?
Yes, unprotect the workbook first before deleting sheets.
-
Is there a shortcut key to delete selected sheets?
Yes, you can press Alt + H + D + S to quickly delete selected sheets.
-
What is the alternative to deleting sheets?
Instead of deleting, you can hide sheets using right-click > Hide. Hidden sheets can be unhidden later if needed.
Conclusion
Deleting multiple Excel sheets swiftly is possible using in-built options like the Ribbon, Context Menu, and VBA Macros. Select sheets efficiently, choose the right method as per the scenario, and avoid accidental permanent deletion of sheets by keeping backups.
With the techniques discussed in this guide, you should be able to delete any number of sheets in Excel hassle-free. Excel offers the flexibility to remove sheets based on specific criteria or patterns using VBA as well.
So next time you need to get rid of multiple worksheets, don’t slog sheet-by-sheet. Use these quick methods to delete sheets in bulk and boost your Excel productivity.
Deleting a Single Sheet in Excel Workbook
Before we look at how to delete multiple sheets, lets quickly look at deleting worksheets one at a time. To do this, select your active worksheet, right-click, and select delete sheet.
You will know you have selected your active sheet as the color will change and differ from your other tabs. Once you are happy you have chosen the right tab, you can select the delete option and hit the delete button.
Delete multiple sheets in Excel.
Now we have quickly covered how to delete a single sheet in Excel, lets look at how to delete multiple sheets.
To do this, you must select multiple sheets, right-click on any of the selected sheets in Excel, and press delete. But how do you select multiple sheets in the first place?
Start by clicking on the first sheet tab you want to delete. From here, press the shift key, and click the last tab you want to delete. You will notice that if you use this keyboard shortcut, you will have highlighted all the tabs in between the two tabs you have clicked, making it quick and easy to delete multiple sheets.
However, if the sheets you want to delete are not in an easy-to-select order, you should use a different technique.
This time, click on the first sheet tab you want to delete as before. However, we will do something a little different this time in the deleting process. To quickly delete sheets not next to each other in your workbook, hold down the CTRL key and click on all the sheet names you want to delete. Your selected worksheet tabs will now be highlighted, and you can delete them by following the same process.
Deleting worksheets in Excel is an excellent skill to learn, so you can delete a worksheet when needed and keep your workbook tidy.
With any of these options, you will see a warning dialogue box appear before you click delete, so you can double-check you have chosen the right sheet name in the worksheet application.
Be sure to save your Excel worksheet before you press delete, just in case you make any mistakes!
We hate SPAM. We will never sell your information, for any reason.
