how to delete sheets in excel

Sheet groups can streamline your Excel tasks in all kinds of ways. Here are some inventive possibilities for using groups to interact with multiple sheets more efficiently.

Excel allows you to select multiple sheets, which you can then edit as a group. When sheets are grouped in this way, everything you do to the active sheet is also done to the other sheets in the group. This grouping behavior can save a lot of formatting and editing time if your workbook splits related data across similarly structured sheets. You can also use grouped sheets to do things you might not realize. In this article, I'll show you a few helpful grouping tricks that you might not have considered.

I'll be using Excel 2016 on a Windows 10 64-bit system, but you can apply these tips to earlier versions without any additional instruction. There's no downloadable demonstration file.

A word on related but separate

Groups rely on what I call the related but separate construct. Each sheet is similar in purpose and more important, in structure, but data is divided across separate sheets by some defining criteria, such as dates, personnel, and departments. You might see orders by month, sales by region, or classes by teacher. The aggregate is all orders, all sales, and all classes, but the structure separates the data.

At its best, this arrangement makes reporting simple, but it complicates the aggregate grouping and analytical processes that Excel is so good at. I prefer storing data in a more traditional database-type record structure. It's easier to pull data together from a common source for reporting than to retrieve data from multiple sheets for analyses and manipulation. I am in the minority, however; you will find many Excel workbooks use this related but separate model. It isn't wrong by any means; but consider the way you want to use all the data before accepting this model as the best way to store it. How you store data is just as important as how you present the information that results from analyzing that data.

Create the sheet group

You can select two or more sheets to create a group and act on all the sheets at once. What you do on the active sheet will occur on all the sheets in the group. If you already know how to create a group, feel free to skip to the tips that follow. Otherwise, to create a sheet group, do the following:

Activate any sheet you want to include in the group.
Hold down [Shift] and click the first and last sheet tabs to create a contiguous group (Figure A). You'll notice that the tabs change color when grouped. Use [Ctrl] to click individual tabs to create a group of noncontiguous sheets.

Figure A

The white tabs indicate a group.

That's it! If you want to group all the sheets in the workbook, you can do so quickly using a menu option: Right-click any tab and choose Select All Sheets.

While you're working in group mode, Excel displays the workbook's name with [Group] in the title bar. You must remember to ungroup the sheets once you finish acting on multiple sheets. It's easy to forget that you're working in group mode and make changes to all the sheets without meaning to. On the other hand, if you use group mode frequently and to your advantage, you'll quickly learn to ungroup when you're finished.

To ungroup sheets, right-click on any sheet tab in the group and choose Ungroup Sheets. Or click any tab not in the group. If you want to ungroup and stay at the current sheet, hold down the [Shift] key and click the active tab. Now, let's move on to a few clever ways to use groups that might not have occurred to you.

1: Copy an existing formula or value to other sheets

To enter a formula or value into all the sheets in the group, you enter it just once--on the active sheet. If you want to copy an existing formula or value to another sheet in the same workbook, you can always copy and paste. Or you can create a group:

Select the sheet that contains the formula or value you want to copy--the source sheet.
Hold down [Shift] or [Ctrl] and click the tabs you want to include in the group--the target sheets.
With a sheet group now active, select the cell that contains the formula or value.
Press [F2].
Press [Enter].

Pressing [F2] activates the contents of the cell (edit mode). Pressing [Enter] then pastes the contents of the active cell in the active sheet into the corresponding cell in all the other sheets in the sheet group.

SEE: Master sorting basics in Excel

2: Control the number and position of inserted sheets

By default, new Excel workbooks have three sheets. You can add or delete sheets. You can even change the default number of sheets for new workbooks. What you can't easily do is add multiple sheets on the fly, exactly where you want them. Grouped sheets can help.

Create a group with the same number of sheets you want to add where you want to add the new sheets. With the group active, add a new sheet by right-clicking any tab in the group and choosing Insert. Excel will add the same number of sheets as in the group between the grouped sheets. Figure B shows the results of creating a group from Sheet3, Sheet4, and Sheet5 (with Sheet5 being the last sheet in the workbook) and inserting a sheet.

Figure B

Add multiple new sheets exactly where you want them.

Give it a try. It won't take you long to get the hang of it. This route isn't easier than clicking the New Sheet icon a few times, but it is easier than moving the newly inserted sheets after adding them. Use a group to add them exactly where you want them.

3: Delete a group

Deleting several sheets at once is just as easy, and they don't have to be in a contiguous selection. Using the [Ctrl] key lets you add noncontiguous sheets to a group. Then, you can delete all the sheets in the group with one task: Right-click a tab and choose Delete.

SEE: How to use Excel's Data Validation feature to prevent data entry mistakes

4: Move grouped sheets

If sheets aren't in a meaningful order, you can move them. You can also move multiple sheets by grouping them and moving the group. First, create the group. Then, right-click the group, choose Move or Copy, select a position, and click OK. If you click the Create A Copy option, you can copy, instead of delete, the group of sheets to a new location.

5: Print quickly

To quickly print several sheets at once, create a group. With the group active, proceed with your printing task as you normally would. This tip can take a bit of practice to get things right, but it's worth the extra effort.

6: See more tabs

This isn't strictly a group tip, but if you can't see the sheet tabs, it can be difficult to create a group. You can use the arrows to the left of the sheet area but that moves tabs, it doesn't display more tabs. A better solution is to decrease the size of the horizontal scroll bar immediately to the right. Simply hover over the left edge of the bar and drag it to the right to reduce its width and display more sheet tabs.

Not rocket science

None of these tips are rocket science, but they let you use groups in ways you might not consider on your own. Once you work through a few, you'll probably think of others. Feel free to share your favorite grouping tips in the Comments section below.

Send me your question about Office

I answer readers' questions when I can, but there's no guarantee. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.