How to Consolidate Data by Category in Excel?

Get FREE Advanced Excel Exercises with Solutions!

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.

How to Consolidate Data by Category in Excel

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.

How to Consolidate Data by Category in Excel

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.

How to Consolidate Data by Category in Excel

  • 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

How to Consolidate Data by Category in Excel

  • After that, click on Add.

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

How to Consolidate Data by Category in Excel

  • 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

How to Consolidate Data by Category in Excel

  • After that, click on Add.

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

How to Consolidate Data by Category in Excel

  • 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

How to Consolidate Data by Category in Excel

  • After that, click on Add.

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

How to Consolidate Data by Category in Excel

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

How to Consolidate Data by Category in Excel

  • Finally, if we do some formatting in the worksheet, then we have found the following output. See the screenshot.

How to Consolidate Data by Category in Excel


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.

How to Consolidate Data by Category in Excel

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

How to Consolidate Data by Category in Excel

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

How to Consolidate Data by Category in Excel

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

How to Consolidate Data by Category in Excel

  • Finally, we do some formatting on the worksheet.
  • Then we found the following output. See the screenshot.

How to Consolidate Data by Category 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.


<< Go Back To Consolidation in Excel | Merge Sheets in Excel | Merge in Excel | Learn Excel

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.
Durjoy Paul
Durjoy Paul

Durjoy Kumar, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, is a dedicated contributor to the ExcelDemy project. His substantial contributions include crafting numerous articles and demonstrating expertise in Excel and VBA. Durjoy adeptly automates Excel challenges using VBA macros, offering valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, SPSS, C, C++, C#, JavaScript, Python Web Scraping, Data Entry... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo