# How to Use Grouping and Consolidation Tools in Excel?

Get FREE Advanced Excel Exercises with Solutions!

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

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

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

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shahriar Abrar Rafid

Shahriar Abrar Rafid, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked with the ExcelDemy project for more than 1 year. He has written over 100+ articles for ExcelDemy. He is a professional visual content developer adept at crafting scripts, meticulously editing Excel files, and delivering insightful video tutorials for YouTube channels. His work and learning interests vary from Microsoft Office Suites and Excel to Data Analysis, VBA, and Video recording and... Read Full Bio

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.

Advanced Excel Exercises with Solutions PDF