How to Use Grouping and Consolidation Tools in Excel?

This article shows you how to use grouping and consolidation tools in Excel. The Excel grouping tool groups two or more rows or columns. The user can choose to minimize or maximize the grouped data. On the other hand, when we consolidate data, we are assembling it so that updates may be made quickly. Here, we will take you through 5 easy and convenient methods on how to use grouping and consolidation tools in Excel.

How to Use Grouping and Consolidation Tools in Excel: 5 Easy Examples

Consolidation in Excel is integrating a procedure into your software that unifies the various components of the required information into a single entity. You can merge data from various spreadsheets into a single worksheet using the built-in consolidation feature in Excel. You can use these 5 methods to consolidate and group your data.

1. Consolidating Data from Multiple Rows

Suppose, we have a Sales Report of some Sales Rep containing their Sales amount. Some of the names have been repeated in different rows.

We want to consolidate those repeated Sales Reps accounts into a single row. Follow our steps carefully.

Steps:

• At first, select cell E4. Go to the Data tab and then, select the Consolidate command.

• Next, a Consolidate dialog box appears. Click on the Reference box.

• Select cells in the B4:C14 range.

• Now, in our Consolidation wizard, we can see that our selected cell reference is present in the Reference box. Also, it appears in the All references box automatically. Check the boxes before Top Row and Left Column.

• Finally, we can see the consolidated report in our existing worksheet named Multiple Rows.

2. Consolidating Data from Multiple Worksheets

Suppose we have Sales Reports of some Sales Reps for 2 consecutive years in two different worksheets. In Sales Report 2020, their Sales amount is divided into 4 Quarters.

Similarly, Sales Report 2021 has the Sales amount of those Sales Reps. But, here, the names of Sales Reps are in a different order.

We want to consolidate those two tables into a single worksheet named Consolidate. Follow the steps cautiously.

Steps:

• Open the worksheet named Consolidate and select cell B4. Then go to the Data tab and select the Consolidate command.

• At this point, the Consolidate dialog box opens. Click on the Reference box. Go to the worksheet Year 2020 and select cells in the B4:F9 range. Finally, click on the Add button.

• After that, we can see that our selected cell references are here in the All references box.

• Similarly, select the cell range from the Year 2021 worksheet and click on the Add button.

• Later, in the Consolidate dialog box, we can see that our cell references from two different sheets are visible in the All references box. Check the 3 boxes down and click on OK.

• Finally, our consolidated report is visible in the Consolidate worksheet. Also, the ( + ) sign is available on the left side of the spreadsheet. We can click on those signs to see the yearly sales individually.

• Click on 2 in the left top corner to expand the whole dataset.

3. Using Subtotal Command to Group Data in Excel

This command returns a subtotal in a list or database. Using the Subtotal command in the Outline group on the Data tab of the Excel desktop application usually makes it simpler to construct a list with subtotals. You can edit the subtotal list after it has been formed by changing the SUBTOTAL function.

However, we have the Sales amount of some Sales Reps. Some rows have the name of Sales Reps repetitively. We want to group those repeated names. So follow us seriously.

Steps:

• Select the cells in the B4:C14 range. Then, go to the Data tab and select Sort A to Z in the Sort & Filter group.

• At this point, we can see that our dataset is arranged in alphabetical order.

• Then, we’ve already selected our dataset in the previous action, so go to the Data tab, and select the Outline group > Subtotal command.

• After that, the Subtotal dialog box opens. Select the options in the image below and click on OK.

• Consequently, we can see that our dataset is grouped having ( – ) signs on the left of the spreadsheet. That means all rows and columns are expanded now.

• After that, click on button 2 in the left top corner to see just the Totals of the individuals.

• Also, you can click on button 1 to see just the Grand Total of all Sales Reps.

4. Using Manual Grouping in Excel

In Excel, grouping cells means combining one or more cells in a spreadsheet. Grouping cells can improve data reading accuracy and perhaps automate some outline additions. Excel makes data categorization into groups easier for us.

Steps:

• Select and right-click on row 9. Then, choose the Insert command.

• As a result, a new row will be created. Here, write down Harry Total in cell B9 and paste the formula below in cell C9.

• Similarly, do the same for row 16.

• Next, select the cells in the B6:C8 range. Also, go to the Data tab and select Outline group > Group tool.

• Then, the Group wizard opens. Select Rows and click on OK.

• Similarly, group the rows of Tom’s account. At this moment, our dataset comes in a grouped format. There are ( – ) signs which means this dataset is expanded completely.

• Click on button 1 to collapse this expansion and see your dataset in compressed format.

5. Inserting Pivot Table

It is simple to summarize particular data by filtering when data is grouped using Excel’s Pivot Table. Data grouping is really easy to do using Microsoft Excel. So, without further hesitation, let’s look at the steps involved in grouping data by pivot table.

Steps:

• First, select cells in the B4:D14 range. Then, go to the Insert tab and select PivotTable.

• At this moment, PivotTable from table or range wizard opens. Select Existing Worksheet and click on the Location box to select cell F4. Now, click on OK.

• Now, we have the presence of a pivot table in our worksheet.

• After that, concentrate on the PivotTable Fields on the right side of the spreadsheet. Drag those fields into the areas like in the image below.

• The Rows area now has Sales Rep and Months fields. However, the Values area has the Sum of Sales field.

• Finally, we have our pivot table with grouped data. Monthly sales are grouped under individual names of Sales Reps.

• We can make our table more compressed by clicking on the ( – ) sign.

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.

Conclusion

Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.

<< Go Back To Consolidation in Excel | Merge Sheets in Excel | Merge in Excel | Learn Excel