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

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.


Download Practice Workbook

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


5 Examples with Grouping and Consolidation Tools in Excel

Data consolidation 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.

grouping and consolidation tools in excel dataset

We wanna 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.

grouping and consolidation tools in excel consolidating data from multiple rows

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

grouping and consolidation tools in excel consolidating data from multiple rows

  • Select cells in the B4:C14 range.

grouping and consolidation tools in excel consolidating data from multiple rows

  • 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.

grouping and consolidation tools in excel consolidating data from multiple rows

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

grouping and consolidation tools in excel consolidating data from multiple rows

Read More: How to Consolidate Data from Multiple Rows in Excel (4 Quick Methods)


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 are divided into 4 Quarters.

grouping and consolidation tools in excel consolidating data from multiple worksheets

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

grouping and consolidation tools in excel consolidating data from multiple worksheets

We wanna 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.

grouping and consolidation tools in excel consolidating data from multiple worksheets

  • 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.

grouping and consolidation tools in excel consolidating data from multiple worksheets

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

grouping and consolidation tools in excel consolidating data from multiple worksheets

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

grouping and consolidation tools in excel consolidating data from multiple worksheets

  • 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.

grouping and consolidation tools in excel consolidating data from multiple worksheets

  • 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.

grouping and consolidation tools in excel consolidating data from multiple worksheets

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

grouping and consolidation tools in excel consolidating data from multiple worksheets

Read More: How to Consolidate Two Sheets into One in Excel (3 Useful Methods) 


Similar Readings


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.

grouping and consolidation tools in excel consolidating data using subtotal command

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

grouping and consolidation tools in excel consolidating data using subtotal command

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

grouping and consolidation tools in excel consolidating data using subtotal command

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

grouping and consolidation tools in excel consolidating data using subtotal command

  • 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.

consolidating data using subtotal command

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

grouping and consolidation tools in excel consolidating data using subtotal command

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

consolidating data using subtotal command


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.

grouping and consolidation tools in excel consolidating data using manual grouping

  • 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.
=SUM(C6:C8)

consolidating data using manual grouping

  • Similarly, do the same for row 16.

grouping and consolidation tools in excel consolidating data using manual grouping

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

Using Manual Grouping in Excel

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

Using Manual Grouping in Excel

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

Using Manual Grouping in Excel

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

Using Manual Grouping in Excel


5. Inserting Pivot Table

It is simple to summarize particular data by filtering when data is grouped using Excel’s PivotTable. 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.

Inserting Pivot Table

  • 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.

Inserting Pivot Table

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

Inserting Pivot Table

  • 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.

Inserting Pivot Table

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

Inserting Pivot Table

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

Inserting Pivot Table

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

Inserting Pivot Table

Read More: How to Consolidate Multiple Worksheets into One PivotTable (2 Methods)


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. Please visit our website Exceldemy to explore more.


Related Articles

Shahriar

Shahriar

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

2 Comments
  1. Nice writing. Can I ask a ques? We are a small merchandise startup. In recent time, we,re maintaining some sheets. In one of them, we are storing the monthly sales of different products ( as example: different designs of t’s). But i wanna group them. Like, product with sales greater than 2000$ is small,>2000 but 5000 is key product. So, we can concentrate on the best seller. Help me in this.thanks.

  2. Hello LINCHEN NUMBY,
    Thanks for your comment. Here, we’re very eager to help this kind of new startup.
    For ease of understanding, you may download the workbook to go along with the approach.
    From your comment above, we’ve made an imaginary dataset for your company. Let’s have a look at this first.

    Then, construct a new column named Group under Column E.
    After that, select cell E5 and enter the following formula.
    =IF(D5>5000,"Key Product",IF(D5>=2000,"Large",IF(D5<2000,"Small")))
    Following this, press ENTER.

    Now, bring the cursor to the right-bottom corner of cell E5; instantly, it’ll look like a plus (+) sign. Basically, it’s the Fill Handle tool.
    Currently, double-click on it to get results in the following cells also.

    Finally, the results are here.

    Alternatively, you can use the following formula instead of the previous one.
    =IFS(D5>5000,"Key Product",D5>2000,"Large",D5<2000,"Small")

    So, that’s all from me on this problem. Feel free to contact us for other inquiries. Follow our website Exceldemy to explore more about Excel.

Leave a reply

ExcelDemy
Logo