Shahriar Abrar Rafid
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.
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.
- 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.
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.
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 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.
- 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.
Read More: How to Consolidate Two Sheets into One in Excel (3 Useful Methods)
Similar Readings
- Remove Consolidation in Excel (2 Handy Methods)
- How to Automate Consolidation in Excel (with Easy Steps)
- [Fixed]: Consolidation Reference Is Not Valid in Excel (with Quick Fix)
- How to Consolidate Data from Multiple Ranges in Excel (2 Easy Ways)
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 as 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 on 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 grouped format. There are ( – ) signs which mean 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 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.
- 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.
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
- Create a Linked Consolidation in Excel (2 Useful Methods)
- How to Build a Static Consolidation in Excel (2 Suitable Examples)
- Data Validation and Consolidation in Excel (2 Examples)
- How to Consolidate Data from Multiple Columns in Excel (7 Easy Ways)
- Consolidate Function for Text Data in Excel (with 3 Examples)
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.
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.