How to create sections in Excel sheet

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.

Ill be using Excel 2016 on a Windows 10 64-bit system, but you can apply these tips to earlier versions without any additional instruction. Theres 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. Its 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 isnt 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

• Learn advanced Excel skills for less than $35
• How to use VBA to repurpose a built-in command in Word and Excel
• How to create a fun Fly In effect in PowerPoint
• Google Workspace vs. Microsoft 365: A side-by-side analysis

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:

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

Figure A

Thats 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 youre working in group mode, Excel displays the workbooks name with [Group] in the title bar. You must remember to ungroup the sheets once you finish acting on multiple sheets. Its easy to forget that youre 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, youll quickly learn to ungroup when youre 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, lets 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 onceon 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:

1. Select the sheet that contains the formula or value you want to copythe source sheet.
2. Hold down [Shift] or [Ctrl] and click the tabs you want to include in the groupthe target sheets.
3. With a sheet group now active, select the cell that contains the formula or value.
4. Press [F2].
5. 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 cant 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

Give it a try. It wont take you long to get the hang of it. This route isnt 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 dont 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 Excels Data Validation feature to prevent data entry mistakes

4: Move grouped sheets

If sheets arent 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 its worth the extra effort.

6: See more tabs

This isnt strictly a group tip, but if you cant 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 doesnt 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, youll 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 theres no guarantee. Dont 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 whats wrong probably wont get a response, but Can you tell me why this formula isnt returning the expected results? might. Please mention the app and version that youre using. Im 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 .

Also read

• Configure Outlooks Calendar view to suit your work routine
• How to update and alphabetize a validation control list programmatically in Excel
• How to permanently change simple formatting defaults in Word
• Office Q&A: Word page numbering and Excels Find feature