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.

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.

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

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

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

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