How to Group Tabs Under a Master Tab in Excel (with Quick Steps)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes we need to work with several worksheets. To maintain them effectively, we can group those tabs and work under a master tab in Excel. A master tab is mainly a worksheet where you can utilize all other sheets effectively. In Microsoft Excel, you can easily group tabs under a master tab. This article will specifically focus on how to group tabs under a master tab in Excel. I hope you find it really informative and gain lots of knowledge regarding the issue.

Table of Contents hide

Download Practice Workbook

Step-by-Step Procedure to Group Tabs Under a Master Tab in Excel

Step 1: Create Multiple Tabs

Step 2: Create Table from Datasets Present in Each Tab

Step 3: Utilize Power Query to Group Tabs Under a Master Tab

Conclusion

Download Practice Workbook

Download the practice workbook.

Group Tabs Under a Master Tab.xlsx

Step-by-Step Procedure to Group Tabs Under a Master Tab in Excel

To group tabs under a master tab in Excel, we will show a step-by-step procedure through which you can easily do the work. We use the power query to solve this problem. After that, when you alter any data in the worksheet, it will automatically change it in the master sheet. That means it will provide you with a compact solution. Follow the steps carefully.

Step 1: Create Multiple Tabs

At first, we need to create some tabs or sheets. After that, we have to group them under a master tab. To show the process, we take a dataset that contains several countries’ sales data. We need to create individual country sales data in a single worksheet and then combined them in the master sheet.

At first, we have to create a dataset for the united states and its sales data.

Then, we want to calculate the total sales of the United States.
To do that, select cell F9.

After that, write down the following formula to calculate the total using the SUM function.

=SUM(F5:F8)

Then, press Enter to apply the formula.

Next, go to a new tab where we want to create sales data for Canada.
In this sales data section, we include the date, product, category, and amount for Canada just like in the previous tab.

Then, we want to calculate the total sales of Canada.
To do that, select cell F9.

After that, write down the following formula to calculate the total using the SUM function.

=SUM(F5:F8)

Then, press Enter to apply the formula.

Next, go to another worksheet where we want to create the sales data for Spain.
In this sales data section, we include the date, product, category, and amount for Spain just like in the other tabs.

Then, we want to calculate the total sales for Spain.
To do that, select cell F9.

After that, write down the following formula to calculate the total using the SUM function.

=SUM(F5:F8)

Then, press Enter to apply the formula.

Step 2: Create Table from Datasets Present in Each Tab

After creating several worksheets, we need to convert them into tables for further purposes. As we use power query, so, we need to have all the data in table format.

At first, select the range of cells B4 to F9.

Then, go to the Insert tab in the ribbon.
From the Tables group, select the Table option.

A Create Table dialog box will appear.
As we select the dataset first, so, there is no need to select the dataset. It will automatically appear in that section.
Check on My table has headers.
Finally, click on OK.

As a result, it will create a table with the sales data of the United States.

After that, we need to do the same for the sales data of Canada and Spain also.
In terms of the sales data table of Canada. We will get the following table.

After that, the sales data table of Spain can appear in the following way. See the screenshot.

Step 3: Utilize Power Query to Group Tabs Under a Master Tab

In this step, we will utilize the Power Query to create the master tab. Then, if we change the other tabs, it will automatically change in the master tab. Follow the steps carefully.

Steps

Before going to the Power Query, you need to set your table name.
At first, select the range of cells B5 to F9.
Then in the Name Box, change the name and set it as Table1.
After that, press Enter.

Then, do the same for the other two tables and set the table named Table2 and Table3
Now, select the range of cells B4 to F9 in the United States tab.

Then, go to the Data tab in the ribbon.
After that, from the Get & Transform Data group, select From Table/Range.

As a result of this, we found table 1 in the power query interface. See the screenshot.

Then, go to the Home tab in the power query.
From the Combine group, select Append Queries.

After that, the Append dialog box will appear.
Next, select the Two Tables
Then, from the Table to append section, select Table1(current).
Here, you may have a question about why we append the same table again. The reason behind this is mainly because we need to append first and after that load the other tables using Advanced Editor.
Finally, click on OK.

As a result of this, a duplicate table of table1 will appear below table1.

Then, go to the Home tab in the Power Query.
From the Query group, select Advanced Editor.

The Advanced Editor dialog box will appear.
Here, you will find one source. You need to edit this and include the other two sources for other tables.

Then, include the Souce2 and Source3 for Table2 and Table3 respectively. See the following screenshot.
Finally, click on Done.

It will include all the tables from your source and show them in one table.

Then, go to the Home tab in the Power Query.
After that, select the Close & Load drop-down option.
From there, select the Close & Load To This is useful to load the resultant table in your preferred worksheet.

Then, the Import Data dialog box will appear.
After that, check on the Existing Worksheet option and select your preferred cell point from where the resultant table will start.
Finally, click on OK.

As a result, we will get our desired solution in the Master Tab. See the screenshot.

The main benefit to do this is that if you alter any data in the other tab, it will update it in the master tab automatically.
For example, if there is a condition where the orange sales amount increase from $2564 to $3210.
First, change it in the United States worksheet.

Now, go to the master tab.
Then, right-click on any cell in the master tab.
From the Context Menu, select Refresh.
The main reason for clicking the Refresh option is that it will reload the Power Query and update the resultant table.

As a result, we will get an updated solution. See the following screenshot.
Without changing data, you can also increase the table row and it will automatically update in the master tab. That means whatever you do in the other tabs, this will get updated in the master tab.

Conclusion

To group tabs under a master tab in Excel, we have shown a step-by-step procedure by using the Power Query. The Power Query set all the table as a group in the master tab and provide you a complete solution where you can alter the data and it will automatically update in the master tab. I hope you find this article very interesting and informative. If you have any questions, feel free to ask in the comment box, and don’t forget to visit our Exceldemy page.