How to Consolidate data in Multiple Worksheets in Excel

This post will guide you how to summarize and report results from multiple worksheets or workbooks in Excel. How do I consolidate data and merge multiple worksheets into one worksheet by using the built-in Excel Consolidate feature. How to create a summary worksheet from multiple worksheets in Excel.

Consolidate Data in Multiple Worksheets

Assuming that you have the sales data for four quarters (1-Q, 2-Q, 3-Q, 4-Q) in the different worksheets. And you want to combine this sales data from different worksheets into a single worksheet so that you can get one summary report with sales totals of all the products.  You can use the built-in Consolidate feature to consolidate your data.

You can see the below screenshot, there are four worksheets to be consolidated that have a similar data structure.

consolidate data from multiple sheets1

consolidate data from multiple sheets2

consolidate data from multiple sheets3

consolidate data from multiple sheets4

To consolidate your sales data into a single summary worksheet, just do the following steps:

#1 create one new worksheet named as summary in your workbook.

consolidate data from multiple sheets5

#2 go to DATA tab, click Consolidate command under Data Tools group.  And the Consolidate dialog will open.

consolidate data from multiple sheets6

#3 select one of the summary functions you want to use to consolidate your sales data, such as: Count, Sum, etc. Let’s select sum in this case from the Function list box. Clicking the Collapse Dialog icon to select the range on the 1-Q worksheet. Then Click Add button to add the selected range into the All references list box.

consolidate data from multiple sheets7

#4 repeat the step 3 for the rest 3 worksheets to add range reference into All references list box. If your sales data contain labels for top row and left column, and you want to copy those labels into summary worksheet, just check Top row and Left column check box under Use labels in. Select the Create Links to source data check box if you want the summary report to be updated automatically when the source data changes.

consolidate data from multiple sheets8

#5 click Ok button to finish consolidating operation. All of the Sales data from 4 worksheets have been consolidated into a single summary worksheet.

consolidate data from multiple sheets9

Leave a Reply