Create a Pivot Table from multiple worksheets in the same workbook

{65 Comments}

Assume a workbook with two worksheets - Jan and Feb.  In both the worksheets, 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 this workbook, 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; and/or
2. Consolidate data from both worksheets (one below the other)

To curtail the workbook size, I have deleted rows of data.  In the actual workbook, there were 65,000 rows of data on each worksheet.

While one simple way would be to copy and paste data from both worksheets into one and then create a pivot table, the following shortcomings exist with this method

1. Copying and pasting data from multiple worksheets 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 sheets 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 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 Power Query, there is a feature to append data from multiple worksheets 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.

Solution for all versions of MS Excel

The steps for creating a pivot table from multiple worksheets (both in the same file) are:

1. Suppose the workbook with the two worksheets (Jan and Feb) is named Monthly data.xls and is saved on the desktop.
2. 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.
5. Repeat steps 2 - 4 for the Feb sheet as well.  In step 3, just change the name to Dummy1
6. Save the workbook as Consolidated.xls (save as Excel workbook if the combined rows in Dummy and Dummy1 exceed 65,536 rows) and close it
7. Open a new workbook (Ctrl+N) and go to Data > From Other Sources > From Microsoft Query
8. Under Databases, select Excel files > OK
9. In the Directories dialog box, navigate to the folder on the desktop where the Monthly Data.xls file is saved.  So for me, it is saved under C:\Users\Ashish\Desktop\ and double click on the folder where Monthly Data.xls file is saved
10. In the left hand side window, select the Monthly Data file and click on OK
11. With Dummy selected, click on the > symbol to bring over all columns of this named range to the right hand side box
12. Select Dummy1 and click on the > symbol to bring over all columns of this named range to the right hand side box
13. Click on Next and on the message box which appears, click on OK
14. Click on the SQL button and delete all content from the SQL Statement box
15. Type this SQL statement in the box there

Select * from dummy
union all
Select * from dummy1

16. Click on OK and on the message box which appears, click on OK
17. Under File, select the last option - Return Data to Microsoft Excel
18. At this stage, if you wish to get data from Dummy and Dummy1, one below the other, then select Table.  If you directly want 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.
19. Click on OK.  A counter will run at the bottom left hand side with the title of Reading Data
20. If you had selected pivot table in step 18 above, then the pivot table grid/layout will appear.
21. You may now drag fields to create a pivot table
22. Close the workbook

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 Monthly data.xls.  Save and close the file.  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 19 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 Table/Pivot Table, data of new rows will appear.

Update Table/Pivot Table for addition of new worksheets

Assume you now add a new worksheet, titled March in Monthly data.xls.  To include this sheet in the pivot Table, follow the under mentioned steps:

1. Open Monthly data.xls

2. Follow steps 2 - 4 mentioned above for the March sheet.  In step 3, just change the name to Dummy2

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 18 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:

union all
Select * from dummy2

7. When you click on OK, the counter will run again and the pivot should reflect data from March sheet.

Update Table/Pivot Table when files are mailed to someone else

If you now mail both files - Monthly Data.xls and Consolidated.xls to someone else, that other person will not be able to refresh the pivot table because the path specified in step 9 above would be different for that other person.  To reestablish the path, follow the under mentioned steps:

1. Open Consolidated.xls

2. 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 18 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.

3. Click on the Definition tab and under Connection String, edit the following:

a. From DBQ= till the next ;, change the file path to the new path where the mail recipient has saved the file.  So, on my system, between DBQ= and the next ;, the path is C:\Users\Ashish\Desktop\Jan.xls.  This will change to C:\Users\John\Desktop\Data\Jan.xls

b. From DefaultDir= till next ;, change the folder path to the new path where the mail recipient has saved the file.  So, on my system, between DefaultDir= and the next ;, the path is C:\Users\Ashish\Desktop\.  This will change to C:\Users\John\Desktop\Data\

RSS 65 Comments…

 Share your views
  1. I'm in the middle of using this to setup a pilot test. One question - could it be setup so that the consolidation page on the data is within the same workbook as the data entry sheets? I also notice that one of my columns being imported changes from a decimal value to a date format? Have you heard of this happening before? Just curious?

    • Hi,

      No major changes at all. In step 7, do not open a new workbook. In Consolidated.xls., open another worksheet and all other steps should remain the same. I am not sure of why the conversion is happening. I have not experienced it.

  2. Hi Ashish,
    I think I'm doing something wrong. I've created my spreadsheet with the 9 tabs I wish to consolodate based on the instructions from your link. When I'm following the steps to name the tables and consolodate them, in step 11, now I cannot find the names I created for the tables? I've tried to troubleshoot this, but I can't figure out why it's not picking up the tables. If I'm using Office 2007 is there any additional steps I need to take? Would you be willing to take a look at the file and advise if I'm missing something? I'm at my wits end and would appreciate a fresh set of eyes (Brain cells) if you're able to. Let me know and I'll forward you what I've created.

    • Hi,

      Please upload your workbook to SkyDrive and share the link of the workbook here.

      • No problem, and thank you for taking a look at it. Can you advise how to access SkyDrive (Never heard of it - I'm a little new to some of this, so thanks for your patience and understanding! :) )

        Once it's uploaded, I'll email you the link as requested and a description of what I'm trying to accomplish.

  3. Hi,
    I tried to post this to the conversation on consolidating data on multiple tabs to one tab, but received a server error indicating it did not save my post. Attempting to post this way instead.

    It's been posted to SkyDrive at (Greg Tunney's SkyDrive/Public), file name is : Prototype-2013 Timesheet v5.1.

    The 'template' tab is a timesheet entry template tab I'd use to create new individual sheets for users. I've entered 3 users as a test (Greg, Sandra,Val) and followed your steps to setup the tables and use CTRL F3 to name them. Note-When this didn't work, I attempted the table names again, but changed the scope in an attempt to troubleshoot it. The issue I'm having is when I attempt to consolidate the 3 sheets onto an empty sheet, I cannot find the 3 table names I've assigned (GregTime, SandraTime, ValTime). Not sure if formating or any functionality I've setup is causing the issue. Would appreciate any feedback you can provide on what I need to adjust. Thank you. Greg

  4. This is fantastic and it worked perfectly for me. Thanks for these clear directions!

  5. Hi!

    I want to start off by saying this is a great guide which helped me a lot. However, I have encountered some problems when trying to import more than 3 sheets from MS Query. Right now I have 5 sheets with 65,536 rows of data in each. When I enter

    Select * from Sheet1
    union all
    Select * from Sheet2
    union all
    Select * from Sheet3

    in the SQL Query everything works fine. However, if I add another line stating

    union all
    Select * from Sheet4

    I get two error messeges. First it says "Internal OLE Automation error" and afterwards "Problems obtaining data". I can combine any 3 combinations of my 5 sheets and get it to work, so the problem is not in any of my sheets. But when I try to import more than 3 sheets I get this error.

    Any idea as to what might cause this?

    Thank you in advance.

    • Hi,

      Please upload your workbook (with the five worksheets) to SkyDrive and share the link of the uploaded file here.

      • Thanks for a quick reply.

        I wish I could do that, but I am afraid that due to the confidentiality of my data I am not going to be able to share it. I understand that this will make it harder for you to help me, but if you have an spontaneous idea as to what might cause this maybe you can share it.

        If not, I will keep on trying to fix it on my own or maybe look for an alternative approach. Thank you for you time though.

        • Hi,

          There is no limit of being able to Pivot only from three worksheets. I have created a Pivot Table from 4-5 worksheets as well. Furthermore, I cannot pinpoint any error in your query.

          You can delete most of your data from the five worksheets (leave only 10 rows of data on each of the five worksheets) and then share the file. That is all the help I can offer.

          • I understand. When I use 3 or more sheets it does load the data up untill about 192,500, thats when I get the error messege. I feel like the wrong is somewhere there.

            I very much wish I could share any data but I am afraid I cannot. I will keep trying with less rows and 5 sheets, and see if it works then.

          • Hi,

            Try this

            1. Create a Pivot Table from the first three worksheets only
            2. 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
            3. Click on the Definition tab and under Command Text, add the following at the end of the SQL query

            union all
            Select * from sheet4

            4. When you click on OK, the counter will run again and the pivot should reflect data from sheet4

            Does this work? If it does, then repeat the steps above for sheet5 as well.

  6. I did what you described several times but didn't get it to work. However, I just now managed to get the whole thing to work but I am not sure how.

    I basically just re-did the whole guide with new names on everything and new filenames. I am wondering what might have caused the issue. Could it be that I had my data-file in xls format and my pivot table file (consolidated) in xlsx format?
    I am going to explore this further in order to realize what exactly I did wrong. Thanks again for an excellent guide and thanks for your time! :)

    • Hi,

      Great to hear that it is working fine. As mentioned, I have several times in the past consolidated/Pivoted data from multiple worksheets without ever facing the problem which you mentioned. Not sure of why you faced this problem.

      You are most welcome. Glad to help.

  7. Very useful post. Solved my requirement within no time.
    Great work.

  8. Hi, when I am making a single pivot out of multiple worksheets, the column headers are not gettin retained. Please help.

    • Hi,

      Could you describe your problem in greater detail.

      • suppose both my data ranges has 4 columns. when i am making the final pivot out of the 2 ranges...through "Multiple Consolidation Ranges" option on Pivot Table wizard, the First Column is automatically becoming my "Row Label" and the rest 3 Columns are getting clubbed under a "Column Label". where the pivot is giving me the sum values under 3 categories (under "Column Label")...I am not being able to use these categories as separate variables in my pivot.

        • Hi,

          "Multiple Consolidation ranges" is not used for creating a Pivot Table from multiple worksheets. To do so, please follow the steps which I have mentioned in this link - by writing an SQL query.

  9. Thank you very much..
    Super way of explaining....

  10. Hi,

    Can u pls send how to load data from different excel files into one another new excel file.

    My project:
    I have different files, but the content in the files is same format. I want to pick only two fields from all the files and load the data into a new excel. Pls suggest.

    Thank you.

    • Hi,

      You may try my solution at this link. To create a Table (instead of a Pivot Table), select Table in step 13. Also, in step 10, click on the + symbol and then select the two columns which you want to consolidate.

  11. Hello,

    I am having the same problem a prior person on this string had....my pivot will only pull the data from the last 3 worksheets in my workbook. I have 7 worksheets that I would like my pivot to pull from. I made sure that I have consistent formatting across all worksheets and I also tried to address the issue through the connection properties steps that you outlined, but no success! - Can you help or provide further guidance?

    Thanks!!

    • Hi,

      How many rows of data per worksheet do you have? Also, as mentioned by Christoffer (whose Comment you are referring to), he just redid the whole thing and it worked fine. So I would suggest that you just start from scratch.

      Also, please be specific about the error which you are facing.

      Lastly, even if on retrying, you do not get your expected result, then upload your file to SkyDrive and share the link of the uploaded file here.

  12. I get a final error message saying "[Microsoft][ODBC Excel Driver] System resource exceeded." My first tab has 13,021 rows of data and the 2nd tab has 12,857 rows of data. Thoughts?

    • Hi,

      Looks like the RAM of your machine is used by some other heavy application. Two worksheets of 13,000 rows is nothing. I very often create a Pivot table from three worksheets with 65,000 rows on each worksheet on a 2 GB RAM machine.

      You may try the following:

      1. Close all applications and then try this method. If it works, then it definitely because of RAM being used by other applications.
      2. If 1 does not work, then try the same process on another machine.

      Also, how many columns do you have on each worksheet?

  13. Hi Ashish thank your very very very much,.... you made my day

  14. Dear Sir,
    I have made a pivot table from merging multiple worksheet into one pivot data source through MS Query, but sir when I mailed to someone the workbook then there need to change User name from Connection string, I want to know that there is way to change connection string automatically when user save the sheet into there system

    • Hi,

      I don' think so. If you have only a few worksheets to create a Pivot Table from and if you are using Excel 2010 or higher versions, then you can use the free Power Query Excel add-in from Microsoft to dynamically combine data from multiple worksheets into one. One can then create a Pivot Table from this dynamically created consolidated sheet.

      A Pivot Table so created will not require any adjustment one the file goes to someone else by mail.

  15. Great, great, great and useful article. Thank you for the super easy to follow steps. Helped me a great deal

  16. Can a pivot table be created from 2 worksheets that do not have the same column names or row name and can not normally be combined for that reason? I have a worksheet containing transactional information on loans and the other worksheet is loan characteristic information. I need information from both places to create pivot tables that contain transactional information and include loan characteristics with both worksheets only have the loan number in common.

  17. Fantastic Ashish... like the way you have explained step by step. Meanwhile, I have a similar question - I use Excel 2010, and need to create a pivot table from two different worksheet having different column names and column nos.

    I believe currently this is possible from Excel 2013, but wanna take your workaround options in 2010. Please advice.

    • Thank you. I am glad that you liked my post. This will purely depend upon what you want done and how your data is arranged. Upload your file to SkyDrive and share the link of the uploaded file here. Please explain your requirement and desired output in detail.

  18. Hi,

    Status of tasks (initiated, pending, closed, etc) are tracked date wise and each sheet holds datewise task status for a particular month. I need a pivot that will display the sum of tasks by status monthwise. Can you advise how to get the monthwise summary.

    Sheet1 (Jan)
    01-JAN-2014 Task 1 (Closed)
    02-JAN-2014 Task 2 (Pending)
    Sheet 2 (Feb)
    01-FEB-2014 Task 1 (Pending)
    02-FEB-2014 Task 2 (Pending)

    Need a pivot that will display,

    January
    > Pending = 1
    > Closed = 1
    February
    > Pending = 2

    Please advise.

    • Hi,

      Have you followed the steps which I have outlined in my original Blog post? If you follow those steps, you will exactly get what you want. Once you create the Pivot Table, you will just have to Group the Dates by Months.

      Hope this helps.

  19. Yes, I got the rest but I wasnt aware of group by the dates. I will try and let you know.

  20. Got it. thanks for the hint.

  21. Hi Ashish,

    really superhelpful. Thanks! It worked for my 20 sheets as well.. Beside some minor formatting differences (numbers to text) there wasn't any real problem.. until now..
    All my sheets are identical in formatting, size and data identification (columns used to set identification/mapping to values), values are of course different.
    It looks something like this:

    First line (Columns: Name, XY, Jan-Dec)
    Second line (Columns: John, YES, 1...)
    Third line (Columns: Jack, NO, 2...)
    ...

    I added another column into my data sheets (all of them of course) and refreshed the table I had linked to those sheets. Then I experienced this weird thing: Some on my mapping/identification cells were simply blank, even though they contain some value in the data sheets. And guess what, this happened only for some of my data sheets (3 out of 20). /again, all my data sheets are the same in everything but values/ Result:

    Data (Columns: John, YES, 1...)
    Mapping (Columns: John, , 1...)

    I tried everything from the beginning, changed some formatting, tried manual links to data sheets (deleted after table refreshing), tried playing with some setting in the query sql code..
    no change, some cells are simply empty.

    Could you please help me with any brainstorming, ideas, whatever, as this little mistake is making all my table completely useless.. and I'm really frustrated already..

    Thanks a lot for any advise!

    Maros

    • You are welcome. If you are adding a new column, then please ensure the following:

      1. Add the column in all worksheets at the same position; and
      2. The spelling of the column heading should be the same; and

      My guess is that while you have added the column to all worksheets, you have not expanded the Named ranges. Ensure that the Named ranges include the new column which you added.

      • Hey Ashish,

        thanks for reply.

        I added column with the same name to the same position into all my data sheets, and I double checked name ranges. All works fine except mapping from 1 column from 3/20 sheets.. Btw, those problematic columns are different from ones I added last.

        (working excellently -> added new column -> some old columns not mapping -> frustration)

        It's really annoying, especially, when I want to use table as a building brick for another analysis..

        Seriously, I have no idea what to do with it..

        Please, if you have any idea, I would really appreciate your help..

        Cheers!

        Maros

  22. Hi Ashish,

    Your solution worked perfectly for me on my desktop
    But my actual file is in sharepoint, not sure how to coonect it to networkdrive?

  23. Can i place Sheets vertically or is there any software

  24. Great steps! I was able to follow without any problems.

    However, how do I add new columns to the final consolidated table? I know how to adds new rows, change and edit data, but if I wanted to add a new column heading how will I do that?

    Thanks!

    • Hi,

      Thank you for the feedback. Once you have created the Pivot Table and now want to add additional columns to the source data sheets (and obviously have the additional columns appear in the Pivot Table Field List as well), try this

      1. Add column heading(s) in source data sheet1 and copy the same heading(s) with in the same cell(s) of the other worksheets as well
      2. Add entries under these columns
      3. Go to Formulas > Name Manager and select the name of the first table > Edit. Extent the range to include the additional column(s) added
      4. Repeat step 3 for all Named ranges
      5. Refresh the Pivot Table. The new columns should now appear in the Pivot Table Field List

      I have tried and tested this on a dataset and it works fine.

      Hope this helps.

  25. Thanks Ashish, this really helped!!

  26. Hi,
    I have been able to get this working with out issue which is great, thanks for posting this as it has been very usefull.
    Is there a way that I can easly "save as" so that i can re use the master version and save old data of a previous month? The one I have is using 10 sheets and covers a month of data that is populated into a Pivot chart, I want to open it again in 12 mths time and still be able to manipulate the Pivot table/chart based on the data from 12 mths ago.
    I have tried changing the connection string but get errors after a couple of saves and I'm trying to make it easy for the end user that will be updated the data and saving the file to start a new month. any thoughts will be great. cheers.
    Mark

    • You are welcome. I am glad you could make use of this technique. Since Save as would either cause a change in the directory or the file name, the connection string would have to be changed. You may want to explore the Power Query option which I have shared in the same article. Via Power Query, you will first be able to append data from all sheets into the Power Query window and we will then be able to create a Pivot Table from it. Since there are no external connections, you will not need to change the Connection string.

      Hope this helps.

Leave a Comment

Your email address will not be published.

*