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

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.

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Durjoy Paul

Hi there! I'm Durjoy. I have completed my graduation from the Bangladesh University of Engineering and Technology. I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF