Since leaving the classroom, he's been a tech writer, writing how-to articles and tutorials for MakeUseOf, MakeTechEasier, and Cloud wards.net. If you want to create, for example, standard tables or calculations on many worksheets, grouping can save you a lot of time.
These works best if the worksheets already have identical data structures. All the worksheets have identical structures showing sales of coffee drinks.
To perform the same tasks for a particular set of worksheets, follow the steps below. Press and hold down the Ctrl key, and click the worksheet tabs you want to group.
Now, suppose you want to add the same formula to cell B7 on both the “Paris” and “London” worksheets. Press and hold the Ctrl key, and click the worksheet tabs you want to ungroup.
When you ’ve made all the changes, toucan ungroup the worksheets. By default, a workbook comes with one worksheet, although toucan add more.
After you make your edits, toucan ungroup the worksheets to modify them individually. Hold down the “Control” key while clicking specific worksheet tabs to group them.
Alternatively, right-click any grouped tab and select “Ungroup Sheets.” This forum is for general questions and feedback related to Microsoft Excel (all versions) as they pertain to the IT Pro community.
If you have more basic usage questions with Microsoft Excel, toucan also visit the forums at Microsoft Answers: http://answers.microsoft.com/en-us/office/forum/ excel 22 I am trying to organize an Excel file that has numerous tabs /sheets.
I'd like to group them together so that they show as ONE tab, like a drop down menu or something. You may need to build a User Form or look at a custom Ribbon to assist your navigation.
Our workbook contains 3 similar worksheets (North, Mid and South) and a blank fourth worksheet. To group worksheets, hold down CTRL and click the sheet tabs of the sheets you want to group.
Now toucan edit multiple worksheets at the same time. For example, on the North sheet, change the value of cell B2 to $1000 and delete row 4.
Excel allows you to select multiple sheets, which toucan then edit as a group. This grouping behavior can save a lot of formatting and editing time if your workbook splits related data across similarly structured sheets.
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 toucan apply these tips to earlier versions without any additional instruction.
Groups rely on what I call the related but separate construct. 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. I am in the minority, however; you will find many Excel workbooks use this related but separate model.
If you already know how to create a group, feel free to skip to the tips that follow. Hold down and click the first and last sheet tabs to create a contiguous group (Figure A).
Use to click individual tabs to create a group of noncontinuous 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. If you want to ungroup and stay at the current sheet, hold down the key and click the active tab.
Hold down or and click the tabs you want to include in the group --the target sheets. Pressing activates the contents of the cell (edit mode).
Toucan even change the default number of sheets for new workbooks. Using the key lets you add noncontinuous sheets to a group.
Then, right-click the group, choose Move or Copy, select a position, and click OK. This tip can take a bit of practice to get things right, but it's worth the extra effort.
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. Feel free to share your favorite grouping tips in the Comments section below.
Be your company's Microsoft insider by reading these Windows and Office tips, tricks, and cheat sheets. 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 has two ways to synchronize data, both between separate spreadsheets (by linking files) and between worksheets in the same sheet.
This is useful for building summary spreadsheets and for ensuring that changes in one worksheet are reflected (and replicated) in others. Open the workbook with the data you expect to be shared in Excel.
Select the cell in the source workbook you want the data to come from, and enter “Ctrl+C” to copy it. Switch to the target workbook, and select the cell you want the data to appear in.
Hold down the “Ctrl” key while clicking on multiple worksheet tabs. Is there a way that I can combine worksheets into expandable and collapse-able tab.
Guest CAN I Groups UNDER A “Material” IN EXCEL WORKBOOK....HAVE MANY TABS AND WISH TO HAVE THEM IN Separate CATEGORIES...THANKS Guest Hi Jr, Add 'Master' sheets and name them Master1, Master2 ...
In each of the other Master sheet code modules paste similar code, changing the sheet names listed in the statement lines: Note that this line needs to be changed in each of the two subs.
Registered User Hi Normanize you still here, five years later? I, too, am plagued with workbooks with vast numbers of tabs and scrolling through them is not efficient.
I stumbled on your code solution here, tried to use it, but have no luck making it work. I'm a neophyte with VBA, so maybe I'm doing something wrong, or maybe there's a step to activate the code that I'm missing.
Forum Expert Hi and welcome to the board Your post does not comply with Rule 2 of our Forum RULES. If you feel it's particularly relevant, provide a link to the other thread.