In the consolidate dialogue box, select the check the box Create links to source. Then it completes the process. Now, it has created the SUM function. There is a plus (+) icon on the right-hand side of the spreadsheet, click on the plus (+) icon to see the breakup. Effective Excel combine tool: the software allows you to merge multiple Excel files into a single file in just 3 steps. Please read the step-by-step guide below to find out how you can do that. Create one spreadsheet: in addition to creating one Excel file, this program can pick multiple spreadsheets from multiple Excel files and merge them.
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 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 it 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.
[adinserter block=”2″]
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.
Consolidate Multiple Worksheets using the Pivot Table Wizard
First press Alt+D, then press P.
Excel displays the The Pivot Table Wizard dialog box.
A 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!
How to modify the pivot table row and column labels?
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.
Get our data from Pivot table using drill down technique
How To Use Consolidate Tool In Excel
Consolidate Tool In Excel
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:
How To Use Consolidate Excel
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.
Conlusion
If you are in a hurry, we strongly recommend our free Excel add-in. It’s not complex to consolidate multiple worksheets into a Pivot table and prepare data to create dashboard templates. We have to use the same shapes for data sets. Using the drill-down method, our life will be easier.
Check our tutorial on YouTube!Download the sample workbook!
Consolidate Tool In Excel 2016
Additional resources: