Assume two workbooks named Book1.xls and Book2.xls. In Book1.xls, there is a sheet named Jan and in the Book2.xls, there is a worksheet named Feb. In both the worksheets – Jan and Feb, the following exist
1. Same number of columns on both worksheets; and
2. Same order of columns on both worksheets; and
3. Same spellings of headings on both worksheets
As you can observe in zipped file, all three aspects mentioned above are in place. One may want to perform any of the following tasks:
1. Create one pivot table from both worksheets (which are in different workbooks); and/or
2. Consolidate data from both worksheets (which are in different workbooks) one below the other
To curtail the workbook size, I have deleted rows of data. In each workbook, there were 65,000 rows of data on each Jan worksheet and Feb worksheet.
While one simple way would be to copy and paste data from Jan and Feb worksheet in a third worksheet and then create a pivot table, the following shortcomings exist with this method
1. Copying and pasting data from multiple workbooks into one is a manual process; and
2. Since the pivot table will be based on the manually created sheet (by copying and pasting), changes in the two original workbooks will not update the pivot table on refreshing unless the same changes are carried out in the consolidated sheet (created by copying and pasting above) as well. So this leads to duplication of effort.
Both problems outlined above can be resolved by using MS Query. The technique mentioned below will work as long as the three conditions mentioned above are satisfied. Furthermore, since MS Query will only recognize named ranged with rows up to 65,536, the number of rows of data in each of the worksheets (Jan and Feb) should not exceed 65,536. The combined rows in both worksheets could be any number.
Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem
Solution for MS Excel 2010 and higher versions
If you are using the Power Query add-in, then a few simple steps (no formulas at all) can solve this problem. The result will be dynamic and refreshable (just as in a Pivot Table). In order for the Power Query solution to work, all files will have to be saved as CSV files in the same folder (Since a CSV files can have only one worksheet, the Jan and Feb worksheets in the other workbook will have to become two workbooks – Jan.csv and Feb.csv). In Power Query, there is a feature to append data from multiple CSV files lying in a specific folder into one running range. Once this is done, the running range can either be transferred to an MS Excel worksheet (if the number or rows are up to 1 million) from where a Pivot Table can be created OR the running range can be loaded to the Data Model (Excel 2013) [the Data Model will be able to accommodate more than 1 million rows depending upon the hardware and software configuration of the machine] from where a Pivot Table can be created.
You may watch a short video here:
Solution for all versions of MS Excel
The steps for creating a pivot table from multiple worksheets (both in the same file) are:
1. Both workbooks – Book1.xls and Book2.xls are saved on the desktop.
2. Open Book1.xls, select the data on the Jan sheet (including the first row as the header row – on the Jan sheet, it will be A1:S4. Ensure that the header row has some distinctive formatting such as Bold or some colour) and press Ctrl+F3 > New. In the Name box, type Dummy and click on OK > Close.
3. To cross check that the name assigned above has indeed been assigned correctly, select the data range once again and in the Name box (left of the formula bar), Dummy should appear.
4. Select A1:S4 of the Jan sheet again and press Ctrl+T to convert this range into a Table. Ensure that the “My Table has headers” box is checked. Save and close Book1.xks
5. Repeat steps 2 – 4 for the Feb sheet on Book2.xls as well. In step 3, just change the name to Dummy1. Save and close Book2.xls
6. Open a new workbook and go to Data > From Other Sources > From Microsoft Query
7. Under Databases, select Excel files > OK
8. In the Directories dialog box, navigate to the folder on the desktop where the Book1.xls file is saved. So for me, it is saved under C:\Users\Ashish\Desktop\ and double click on the folder where Book1.xls file is saved
9. In the left hand side window, select the Book1.xls file and click on OK
10. With Dummy selected, click on the > symbol to bring over all columns of this named range to the right hand side box
11. Click on Next three times
12. Select the option of Return Data to Microsoft Excel
13. At this stage, if you wish to get data in a Table form then select Table. If you wish to create a pivot table, select the second option button – Pivot Table. Select any cell where you would like to the result to appear, say cell A1.
14. Click on OK. A counter will run at the bottom left hand side with the title of Reading Data
15. If you had selected pivot table in step 13 above, then the pivot table grid/layout will appear
16. You may now drag fields to create a pivot table
So the Table or pivot table which you have created so far is only from Jan sheet of Book1.xls. We have to also include data from Feb sheet of Book2.xls.
17. If you had created a Table in step 13 above, then click on any cell in the Table and under Table Tools (yellow button in the MS Excel title bar) > Options, click on the small drop down arrow under Refresh and select Connection Properties. If you had created a Pivot Table in step 13 above, then select any cell in the pivot table and under Pivot Table Tools (red button in the MS Excel title bar) > Options, click on the small drop down arrow under Refresh and select Connection Properties.
18. In the Command text box, you see something like this
SELECT dummy.Invoice_number, dummy.Date, dummy.`Article Number`, dummy.`Article Name`, dummy.`Tax Type`, dummy.Brand, dummy.Category, dummy.`Sports Code`, dummy.`Product Type`, dummy.MRP, dummy.`Customer #`, dummy.`Customer Name`, dummy.Region, dummy.`Area Code`, dummy.Quantity, dummy.`Line Discount`, dummy.`Sales Value`, dummy.`Before Tax`, dummy.`VAT Amount` FROM `C:\Users\Ashish\Desktop\Book1.xls`.dummy dummy
Replace this lengthy statement with the following shorter one
SELECT * from `C:\Users\Ashish\Desktop\Book1.xls`.dummy
SELECT * from `C:\Users\Ashish\Desktop\Book2.xls`.dummy1
19. Click on OK and the Table/Pivot Table should update
20. Save the file as Consolidated.xls and close it.
Update Table/Pivot Table for addition/editing in existing named ranges
To see the effect of a dynamic Table/pivot table, edit data in Jan and/or Feb sheets of Book1.xls and/or Book2.xls. Save and close the file(s). Open Consolidated.xls and refresh the Table/Pivot Table (Right click and Refresh). At the bottom right had side the counter will run again and (step 14 above) and once it has read all data, the Table/pivot table will update.
Furthermore, since the two named ranges (Dummy and Dummy1) have been converted into Tables, even if you add data by rows (with no row being left entirely blank), when you right click to Refresh the pivot Table, data of new rows will appear.
Update Table/Pivot Table for addition of new worksheets
Assume you now want to add data from a worksheet titled March in Book3.xls. To include this sheet in the pivot Table, follow the under mentioned steps:
1. Open Book3.xls
2. Follow steps 2 – 4 mentioned above for the March sheet. In step 3, just change the name to Dummy3
3. Save and close the workbook
4. Open Consolidated.xls
5. Select any cell in the pivot table and under Pivot Table Tools (red button in the MS Excel title bar) > Options, click on the small drop down arrow under Refresh and select Connection Properties. If you selected Table (instead of Pivot Table) in step 13 above, click on any cell in the Table and under Table Tools (yellow button in the MS Excel title bar) > Options, click on the small drop down arrow under Refresh and select Connection Properties.
6. Click on the Definition tab and under Command Text, add the following at the end of the SQL query:
SELECT * from `C:\Users\Ashish\Desktop\Book3.xls`.dummy3
7. When you click on OK, the counter will run again and the pivot should reflect data from March sheet.