Register for your free TechRepublic membership or if you are already a member, sign in using your preferred method below.

We recently updated our Terms and Conditions for TechRepublic Premium. By clicking continue, you agree to these updated terms.

Invalid email/username and password combination supplied.

An email has been sent to you with instructions on how to reset your password.

By registering, you agree to the Terms of Use and acknowledge the data practices outlined in the Privacy Policy.

You will also receive a complimentary subscription to TechRepublic's News and Special Offers newsletter and the Top Story of the Day newsletter. You may unsubscribe from these newsletters at any time.

Username must be unique. Password must be a minimum of 6 characters and have any 3 of the 4 items: a number (0 through 9), a special character (such as !, $, #, %), an uppercase character (A through Z) or a lowercase (a through z) character (no spaces).

Six tips for using Excel sheet groups in easy (but unusual) ways

Your email has been sent

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.

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. More about Software Microsoft Office: Get a lifetime license and second laptop screen for one price How to use the spill range in Excel How to create a fun Fly In effect in PowerPoint Google Workspace vs. Microsoft 365: A side-by-side analysis

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:

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.

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:

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.

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.

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.

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.

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.

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.

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.

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.

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.

Six tips for using Excel sheet groups in easy (but unusual) ways

Your email has been sent

Your message has been sent

TechRepublic Premium content helps you solve your toughest IT issues and jump-start your career or next project.

These 11 cloud-to-cloud solutions back up your organization’s data so you’ll be covered in the event of deletions, malware or outages. Compare the best online cloud backup services now.

You can use a mobile device to speak with another person directly through the Teams app. Lance Whitney shows you how to use this handy feature.

A phishing technique called Browser in the Browser (BITB) has emerged, and it’s already aiming at government entities, including Ukraine. Find out how to protect against this new threat.

With so many project management software options to choose from, it can seem daunting to find the right one for your projects or company. We’ve narrowed them down to these nine.

Start-ups, DARPA and Accenture Ventures announce research partnerships, new hardware and strategic investments.

Procuring software packages for an organization is a complicated process that involves more than just technological knowledge. There are financial and support aspects to consider, proof of concepts to evaluate and vendor negotiations to handle. Navigating through the details of an RFP alone can be challenging, so use TechRepublic Premium’s Software Procurement Policy to establish ...

Recruiting a Security Analyst with the right combination of technical expertise and experience will require a comprehensive screening process. This hiring kit from TechRepublic Premium includes a job description, sample interview questions and a basic want ad that you can customize for your business to find, interview, recruit and hire the best candidates for a ...

Recruiting a DevOps engineer with the right combination of technical expertise and experience will require a comprehensive screening process. This hiring kit from TechRepublic Premium includes a job description, sample interview questions and a basic want ad that you can customize for your business to find, interview, recruit and hire the best candidates for a ...

Video game writing jobs are in demand. Recruiting a video game/quest writer with the right combination of technical expertise and experience will require a comprehensive screening process. This hiring kit from TechRepublic Premium includes a job description, sample interview questions and a basic want ad that you can customize for your business to find, interview, ...