Once we have finished adding the references, click “OK”. In the same manner, we now will add a reference from all other sheets like North, South, East, and West. The reference is shown in the “Reference” box like this.Ĭlick “Add,” and the reference will be added in the “All Reference “box. We only have to select sales data and not heading and rows. We will then go to our first sheet that contains the sales data for North. Now, we will go to the reference tab to add a reference to our data from different worksheets. Since we want a sum of sales, we will select “Sum” in the dropdown. The first thing we look at is the “Function” dropdown which shows many arithmetic functions like sum, count, max, average, etc. Once we click on Consolidate, the below window will appear: As we can see above, cell B3 is selected, and now we will move up to the Data tab in Excel Ribbon and will go to Consolidate. Where we want the “Consolidate” function to insert the data from other sheets. This is our template in the “consolidated file” sheet, and now we will start consolidating data from the worksheets. Here, you can see the different worksheet names and the last consolidated file we have to work on. This example will show the consolidated sales for all the regions. There is a fifth sheet named consolidated file. Now we would like to consolidate the data into one place rather than in a sheet within the same workbook. Let’s say we have a worksheet in which we have sales data with four worksheets marked as per their region as North, South, East, and West. Example #1 – Consolidate Data in the Same Workbook Let’s understand how to consolidate data in multiple worksheets with a few examples. Excel functions, formula, charts, formatting creating excel dashboard & others How to Consolidate Data in Multiple Worksheets?

How to combine excel workbooks