Sometimes, we collect data in different worksheets. To merge them into a single worksheet, we can use the consolidation process. In the consolidation process, we can merge data by category. In Microsoft Excel, we have options to consolidate data by category. This article will mainly focus on how to consolidate data by category in Excel. I hope you find this article very informative and gain lots of knowledge regarding consolidation.
What Is Data Consolidation?
Consolidation can be defined as the merged data from several worksheets or workbooks. If you need to combine multiple worksheets into one workbook, then the consolidation will come under consideration. Again, the consolidation by category process can be defined as when you have data in several worksheets but the rows and columns aren’t arranged in the same order on all the worksheets. Then, consolidation by category can become a handy process. It will merge all the data from several worksheets in the same order of rows and columns.
How to Consolidate Data by Category in Excel: 2 Suitable Examples
To consolidate data by category in Excel, we consider two different examples by which you can easily understand the whole problem. Both examples try to explain how to consolidate data by category in Excel. Here, we consider two examples based on data in several worksheets and workbooks.
1. Consolidate Data by Category from Different Worksheets
Our first example is based on the process of consolidating data by category in different worksheets. Here, we have several sales data from several months. But the rows and columns aren’t arranged in the same order on all the worksheets. In our first worksheet, we take a dataset that includes the sales in January.
Then, in the second worksheet, we have the dataset that includes the sales in February. But the most important thing to mention is that column C and column D interchange with one another. That means the unit column goes to column D and the amount column goes to column C.
After that, in the third worksheet, when we consider sales in March, we include a new column as the total. It calculates the product of amount and unit.
Now, we need to consolidate these data by category using the consolidate option. To do this, follow the steps carefully.
Steps
- First, go to a new worksheet and select the cell where you want to consolidate data by category.
- Then, go to the Data tab in the ribbon.
- After that, from the Data Tools group, select the Consolidate option.
- Then, the Consolidate dialog box will appear.
- From the Function section, select Sum from the drop-down option. You can select other functions for your purpose.
- Then, in the Reference section, select the first dataset which means the Sales in Jan worksheet or manually write the following.
'Sales in Jan'!$B$4:$D$12
- After that, click on Add.
- It will include this reference in the All references section.
- Next, to add the next dataset, go to the Reference section.
- Then, select the second worksheet or write down the following manually.
'Sales in Feb'!$B$4:$D$12
- After that, click on Add.
- It will include this reference in the All references section.
- Next, to add the next dataset, go to the Reference section.
- Then, select the third worksheet or write down the following manually.
'Sales in Mar'!$B$4:$E$12
- After that, click on Add.
- It will include this reference in the All references section.
- Then, go to the Use Labels in section, and check both the Top row and Left column options.
- Finally, click on OK.
- There we have the consolidation data by category.
- Finally, if we do some formatting in the worksheet, then we have found the following output. See the screenshot.
2. Consolidate Data by Category from Different Workbooks
Our next example is based on the consolidated data by category in different workbooks. Here, we can consolidate data in Excel from multiple workbooks. In that case, the rows and columns aren’t
arranged in the same order on all the workbooks. We can find a dataset in the first workbook that implies the sales in January data.
In the next workbook, we take a dataset that implies the sales in March data. Here, we include a new column and the position of rows and columns are interchanged compared to the previous workbook.
To consolidate data by category in a different workbook, you need to follow the following steps carefully.
Steps
- First, go to a new workbook.
- Then, select the cell where you want to consolidate data by category.
- Before applying the Consolidate command, you need to open all the workbooks to consolidate information in Excel.
- Then, go to the Data tab in the ribbon.
- After that, from the Data Tools group, select the Consolidate option.
- Then, the Consolidate dialog box will appear.
- From the Function section, select Sum from the drop-down option.
- You can select other functions for your purpose.
- Then, in the Reference section, select the first dataset which means Sales in Jan workbook or manually write the following.
'[Sales in January.xlsx]Sales in Jan'!$B$4:$D$12]
- After that, click on Add.
- It will include this reference in the All references section.
- Next, to add the next workbook, go to the Reference section.
- Then, select the second workbook or write down the following manually.
'[Sales in March.xlsx]Sales in March'!$B$4:$E$12
- After that, click on Add.
- It will include this reference in the All references section.
- Then, go to the Use Labels in section, and check both the Top row and Left column options.
- Finally, click on OK.
- There we have the consolidation data by category in a different workbook.
- Finally, we do some formatting on the worksheet.
- Then we found the following output. See the screenshot.
Read More: How to Consolidate Data from Multiple Columns in Excel
Things to Remember
- While doing consolidation by category, you need to make sure that your datasets have some labels. Here, the rows and columns aren’t arranged in the same order on all the worksheets, they have still similar labels.
- Always check or uncheck label options, whether you need them or not.
Download Practice Workbooks
Conclusion
Finally, we have shown two different examples of how to consolidate data by category in Excel. Both of the examples provide a clear view of the topic. It also gives a complete solution regarding the issue. I hope you find this article fascinating. If you have any questions, feel free to ask in the comment box.
Related Articles
- How to Do Consolidation in Excel?
- How to Consolidate Data from Multiple Ranges in Excel
- How to Consolidate Data from Multiple Rows in Excel
- How to Consolidate Rows and Sum Data in Excel
- How to Remove Consolidation in Excel
- How to Automate Consolidation in Excel
- How to Build a Static Consolidation in Excel
- How to Create a Linked Consolidation in Excel
