Click the worksheet that contains the data you want to consolidate, select the data, and then click the Expand Dialog button on the right to return to the Consolidate dialog. If a worksheet containing data that you need to consolidate is in another workbook, click Browse to locate that workbook.
Click the cell in the worksheet where you want to place the consolidated data. Click the Data tab on the ribbon. Click the Consolidate Data button in the Data Tools group. The Consolidate dialog box appears. Consolidate You can use Excel's Consolidate feature to consolidate your worksheets (located in one workbook or multiple workbooks) into one worksheet. Below you can find the workbooks of three districts. Open a blank workbook. On the Data tab, in the Data Tools group, click Consolidate. Choose the Sum function to sum the data. Click in the Reference box, select the range A1:E4 in the district1 workbook, and click Add. Repeat step 4 for the district2 and district3 workbook.
In this tutorial we will show you how to consolidate multiple worksheets into a Pivot table using Excel.
If the data is arranged properly, then you can do that.
Most of the time when you create a Pivot table in Excel 2013 or Excel 2016, you’ll use a data list, or an Excel table.
There might be some different worksheets (or workbooks) that you have in your collection with data arranged differently, but you’ll still want to create a pivot table.
If the data is arranged properly, then you can do that. In this tutorial, we will use the consolidating sample file. You can download from here.
The workbook contains four worksheets, and all those worksheets are data collections that you can use to combine into a Pivot Table.
On the Team1 worksheet, you can see that there is a rectangular region of data; it starts here in cell B3, and runs down the cell F7.
It’s very important that your data be in a perfect rectangle.
Here comes a non-regtangular data sample. If we were to have Product in cell B2, and Year in cell C2, the data values would not be perfectly rectangular.
In this case the Pivot table consolidation technique will not work.
Further information: We need to make sure that there are no blank cells anywhere in our data table.
This data summarizes yearly sales for 4 years (from 2013 to 2016); the years are along the top and the row labels are along the side.
We will use four worksheets. Team1, Team2, Team3 and Team4 tabs contain similar data.
OK, go to the Team1 worksheet and now we can open the tool named the Pivot Table Wizard.
Our goal to consolidate multiple worksheets into a pivot table.
Bad news: The Pivot Table Wizard is no available on the ribbon. We have to use a smart keyboard shortcut to display it. The key combination what we need to use this tool the following.
First press Alt+D, then press P.
Excel displays the The Pivot Table Wizard dialog box.
A short summary of data tables before we consolidate the worksheets: Sames ranges, same shapes and same labels are required to combine datasets into a pivot table.
Now check the Multiple consolidation ranges option.
We can create a pivot table report that uses ranges from one or more worksheets, and that has no no page fields or up to four page fields.
Page fields are fields that are used to filter the contents of a Pivot Table.
We will create the page fields option, then click Next.
Now you can identify the ranges that you want to consolidate.
To do that, click the collapse dialog button at the right side of the Range box.
It is important to make sure that the insertion point is flashing inside the Range box.
Choose the collapse dialog button, and select the first set of cells.
Those are on the Team1 worksheet. Now we will select from B2 to F7. Expand the dialog box, and then click Add.
Doing so adds that range to the All Ranges pane.
Now we have to do the same thing for the other three worksheets.
Click the collapse dialog box button. Click the Team2 sheet tab, select cells B3 to F7, expand the dialog box, and click Add.
Finally repeat these steps for Team 3 and Team4 worksheet.
Datasets are the same size, and the exact same shape.
We have identified the consolidation ranges. The next task is to specify how many page fields we want.
In this example, select 0.
Click Next, and we can select where to create the new pivot table.
We are done, click Finish!
Doing so creates a pivot table with the values that are all consolidated into a pivot table from our four worksheets.
If you look over in the Pivot Table Fields pane at the right corner of the main window, you’ll see that we have three fields.
They are called Row, Column, and Value.
Value is ok, further explanation is not necessarry.
But Row and Column aren’t terribly descriptive. We will modify the name of those fields.
To do that, click a value in the Row Labels area, and then on the Analyze contextual tab of the ribbon, which is already selected.
Now we will modify the value in the Active Field box.
It currently says Row, and clicking in the box selects it.
These are the products so we will type in Product, and press Enter. Doing so changes the name of the field in the pivot table fields pane.
Do the same thing for Column! Click one of the Column Labels, and again, on the Analyze tab. We can edit the value in the Active Field box.
Type Year that, and press Enter.
Enter, and the value changes.
Click the Sheet1 sheet tab to go back to the pivot table. You can create an Excel table based on this data by using a drill down technique.
The first thing you need to do is remove all field headers from the Rows and Columns area, so that you’re left with just the Grand Total in the Value area, so it should be a single cell.
Here is the result:
Now, to create the Excel table, double-click that cell; in this case, it’s cell A4. Doing so creates an Excel table that contains a list of all of your data.
So, we have Product A for the year 2013, the value; Product A for the year 2014 with a value, and so on.
If you are in hurry we strongly recommend our free excel add-in. It’s not a complex task to consolidate multiple worksheets into a Pivot table and prepare data to createdashboard templates. We have to use same shapes for data sets. Using the drill down method our life will be easier.
Check our tutorial on YouTube!Download the sample workbook!
As the scale and complexity of your data sources grow, you might find merging data with Excel is harder to do. For large and varied data sets, Excel becomes too complicated, cumbersome and slow to use. That’s where Trifacta comes in. Trifacta is specifically designed to make this preparation process easier and more intuitive.
For example, imagine being able to save the specific merge functions you use to consolidate data in Excel, customized to each unique data source, and then reuse and share them with your colleagues effortlessly.
With Trifacta, data preparation is accessible, intuitive, and scalable across the organization. By providing a connected application for users to explore, structure, and produce dashboard-ready datasets, Trifacta helps users deliver faster, more accurate analysis.
Trifacta was designed from the ground up to help reduce data cleansing and data preparation time. At Trifacta, we live and breathe data in order to provide easy-to-use, intelligent, visual data analysis that improves data understanding for any project or organization. Sign up for Free Trifacta Wrangler today.