How to Consolidate Data by Category in Excel (2 Suitable Examples)

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.

Download Practice Workbook

Download the Practice Workbook for consolidation of data by category in different worksheets.

Download the following Practice Workbooks for consolidation of data by category in different workbooks.

What Is Data Consolidation?

Consolidation can be defined as the merged data from several worksheets or workbooks. If you have several workbooks or worksheets and you need to merge them into a single sheet. 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.

2 Suitable Examples to Consolidate Data by Category in Excel

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 the process 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 consolidate option. To do this, follow the steps carefully.

Steps

• At 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 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 that means Sales in Jan worksheet or manually write the following.

• After that, click on Add.

• It will include this reference in the All reference section.

• Next, to add the next dataset, again go to the Reference section.
• Then, select the second worksheet or write down the following manually.

• After that, click on Add.

• It will include this reference in the All reference section.

• Next, to add the next dataset, again go to the Reference section.
• Then, select the third worksheet or write down the following manually.

• After that, click on Add.

• It will include this reference in the All reference section.

• Then, go to the Use Labels in section, and check both 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.

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

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 from different workbooks. In that case, the rows and columns aren’t

arranged in the same order on all the workbooks. We can 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

• At 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 containing the data that you want to consolidate.
• Then, go to the Data tab in the ribbon.
• After that, from the Data Tools group, select 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 that means Sales in Jan workbook or manually write the following.

• After that, click on Add.

• It will include this reference in the All reference section.

• Next, to add the next workbook, again go to the Reference section.
• Then, select the second workbook or write down the following manually.

• After that, click on Add.

• It will include this reference in the All reference section.

• Then, go to the Use Labels in section, and check both 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 in the worksheet.
• Then we have found the following output. See the screenshot.

Read More: How to Automate Consolidation in Excel (with Easy Steps)

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.

Conclusion

Finally, we have shown two different examples 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 really interesting. If you have any questions, feel free to ask in the comment box. Finally, don’t forget to visit our Exceldemy page.

Related Articles

• How to Consolidate Multiple Worksheets into One PivotTable (2 Methods)
• Consolidate Data from Multiple Workbooks in a Single Worksheet
• How to Consolidate Data from Multiple Columns in Excel (7 Easy Ways)
• Consolidate Function for Text Data in Excel (with 3 Examples)
• How to Remove Consolidation in Excel (2 Handy Methods)
• Consolidate Data in Excel from Multiple Workbooks (2 Methods)
• How to Consolidate Data from Multiple Rows in Excel (4 Quick Methods)