Create a Pivot Table from multiple worksheets in the same workbook

{ 328 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

You may watch a short video of my solution here

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\

Leave a Comment Cancel reply

Your email address will not be published.

*

  • 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.

    • Did you solve your issue regarding the columns being changed from decimal value to a date format? I am having the same issue and can not figure out why it is occurring.

  • 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.

      • 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.

        • Hi,

          SkyDrive is a service which allows you to upload files and share with others. Please type SkyDrive in the Google search box and upload your file there. Then share the link of the uploaded file here.

  • 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

    • Greg did you ever get this to work? I am having the exact same issue, I need the consolidated table in the same workbook as the data entry not a separate file. Fingers crossed that you found a solution you can share!

  • 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.

      • 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.

  • 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.

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

      • 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.

  • 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.

  • 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.

  • 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?

  • 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.

  • 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.

  • 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.

  • 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.

  • 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

  • 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?

  • 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.

  • 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.

  • Dear Ashish,
    I found your blog very clear and helpful to me, I am trying to do a pivot with data from 12 sheets with the following info: Category (column C of the original file) and Sub-category (column D) in rows and Amount (column A) in column.
    With the first sheet everything was ok.
    When I add “union all Select * from dummy2” it shows the right amount but not Category nor Sub-category…but the column B of the original file.
    Do you have any clue of what’s wrong?
    Thanks in advance.

  • Hi Ashish,

    Excellent Article. I have few questions.

    1) Can conditions be added to the query? I mean instead of saying bring me all the data (union all), can we write a query saying
    Select * from dummy1
    union all
    Select * from dummy2
    where dummy1.empID = dummy2.empID

    2) For the steps mentioned in you article to work, 3 conditions have to be satisfied (column names, position & no.of columns have to be same)
    Even if one of them doesnt satisfy, the above steps dont work. What is the workaround for it.
    Because I have three named ranges with different column names but I want to combine all three to create a pivot.

    Thanks for all your help.

    • Hi,

      I am not quite clear about your first requirement. Once the Union All query has been run and data gets appended to create one table (all Emp ID’s appear in one column), then what would the sense of dummy1.empID = dummy2.empID be?

      Please explain your question in detail and if possible also share the link from where I can download your workbook.

      • Hi Ashish,

        I am consolidating data from 52 worksheets (52 weeks of a year). This sheet is an ever going sheet for our business and each sheet has 300 rows approx. We do not like entering data on a monthly basis since its a pay roll systems where our labour has to be paid weekly.

        When I try to union all the tables (while getting data from other sources), the SQL query gives me an error saying data is too large. But when I just add 2 sheets initially in the SQL query, and then keep adding new sheets and add to the query as per your guidelines above, the data gets added and the sheet refreshed.

        So my question is – why dont you just add dummy and dummy 1 in the initial SQL query and then keep adding new sheets using pivot table options? You can avoid importing dummy 2 columns in the SQL wizard. I hope you get what I mean.

  • I have this macro but pivot field mention in code i want dynamic any type of data i want pull pivot table please tell me
    Option Explicit

    Private Declare PtrSafe Function SetCurrentDirectoryA Lib “kernel32” (ByVal lpPathName As String) As Long

    ‘—————————————————————————————
    ‘ Author: Rob Bovey
    ‘—————————————————————————————
    Sub ChDirNet(Path As String)
    Dim Result As Long
    Result = SetCurrentDirectoryA(Path)
    If Result = 0 Then Err.Raise vbObjectError + 1, “Error changing to new path.”
    End Sub

    ‘—————————————————————————————
    ‘ Procedure : MergeFiles
    ‘ Author : KL
    ‘ Date : 22/08/2010
    ‘ Purpose : Demonstration (http://www.planetaexcel.ru/forum.php?thread_id=18518)
    ‘ Comments : Special thanks to
    ‘ Debra Dalgleish for helping to fix ODBC driver issue
    ‘ Hector Miguel Orozco Diaz for the “DeleteConnections_12” idea
    ‘—————————————————————————————

    Sub MergeFiles()
    Dim PT As PivotTable
    Dim PC As PivotCache
    Dim arrFiles As Variant
    Dim strSheet As String
    Dim strPath As String
    Dim strSQL As String
    Dim strCon As String
    Dim rng As Range
    Dim i As Long
    Dim shtArray As Variant
    Dim shtCount As Long
    ‘List of sheet names
    shtArray = Array(“Travel_Query”, “Travel_Confirmation”, “Salary_Query”, “Salary_Confirmation”, “Absence”)

    strPath = CurDir
    ChDirNet ThisWorkbook.Path

    arrFiles = Application.GetOpenFilename(“Excel Workbooks (*.xls*), *.xls*”, , , , True)
    strSheet = “Sheet1”

    If Not IsArray(arrFiles) Then Exit Sub

    Application.ScreenUpdating = False

    If Val(Application.Version) > 11 Then DeleteConnections_12

    For shtCount = LBound(shtArray) To UBound(shtArray)
    strSheet = shtArray(shtCount)
    Set rng = ThisWorkbook.Worksheets(strSheet).Cells
    rng.Clear
    For i = 1 To UBound(arrFiles)
    If strSQL = “” Then
    strSQL = “SELECT * FROM [” & strSheet & “$]”
    Else
    strSQL = strSQL & ” UNION ALL SELECT * FROM `” & arrFiles(i) & “`.[” & strSheet & “$]”
    End If
    Next i
    strCon = _
    “ODBC;” & _
    “DSN=Excel Files;” & _
    “DBQ=” & arrFiles(1) & “;” & _
    “DefaultDir=” & “” & “;” & _
    “DriverId=790;” & _
    “MaxBufferSize=2048;” & _
    “PageTimeout=5”

    Set PC = ThisWorkbook.PivotCaches.Add(SourceType:=xlExternal)

    With PC
    .Connection = strCon
    .CommandType = xlCmdSql
    .CommandText = strSQL
    Set PT = .CreatePivotTable(TableDestination:=rng(6, 1))
    End With

    With PT
    With .PivotFields(1) ‘Rep
    .Orientation = xlRowField
    .Position = 1
    End With
    .AddDataField .PivotFields(8), “Sales”, xlSum ‘Total
    With .PivotFields(3) ‘Region
    .Orientation = xlPageField
    .Position = 1
    End With
    With .PivotFields(2) ‘Date
    .Orientation = xlColumnField
    .Position = 1
    .DataRange.Cells(1).Group _
    Start:=True, _
    End:=True, _
    Periods:=Array(False, False, False, False, True, False, True)
    End With
    End With

    ‘Clean up
    Set PT = Nothing
    Set PC = Nothing
    Next shtCount

    ChDirNet strPath
    Application.ScreenUpdating = True
    End Sub

    Private Sub DeleteConnections_12()
    ‘ This line won’t work and wouldn’t be necessary
    ‘ in the versions older than 2007
    ‘*****************************************************************************
    On Error Resume Next: ThisWorkbook.Connections(1).Delete: On Error GoTo 0
    ‘*****************************************************************************
    End Sub

  • Hi Ashish:

    One quick question. in step 6 you mention saving the file containing the tabs jan and feb as consolidated. I think it needs to be the new sheet containing the imported data that needs to be saved as consolidated. Step 6 needs to happen after i open a new file in step 7 and at the end of step 22. please comment.

    Thanks
    Akshay

      • Thanks Ashish:

        I am slightly confused.

        The original data is in monthly_sales.xls. Now I am creating the named data (dummy and dummy1) and saving it as consolidated.xls.
        Then I am opening a new workbook and referring that workbook to monthly_sales.xls.
        But since the dummy and dummy1 are in consolidated.xls, the available tables shows blank in the query wizard.

        can you please clarify.

  • Hi Ashish,

    Thank you so much, this post saved me from all the trouble of doing it manually.
    Awesome step by step help, thanks again!!

  • I got this but I noticed that two of my columns being imported changes from a decimal value to a date format for data of 1 worksheet only for 2nd worksheet data it shows correct format (in decimal only). please help on this

    • Hi,

      This could be an Excel version specific problem. If you can upload the workbook to OneDrive and share the download link with me, then I will check whether this happens on my system as well.

      • Hi,
        I checked it on other systems still its happening.
        I have four worksheets with same no. of columns (say 8 columns) & same title in each sheet. when I combine all four sheets data in one new sheet, it works well but if i add new columns like column no. 9, 10 n so on…… formatting changes automatically for few of earlier columns. if i delete 1 newly added column, formatting changes randomly for few of earlier column. Is there any restriction for no. of columns? or am i doing it anything wrong. Thanks in advance for your help.

  • Hi Ashish,

    I am using Excel 2010 and am attempting the union all via Microsoft query, I am able to get to the dialogue box an enter the SQL but after the “can’t be graphically represented” prompt I get a “Syntax error in query. Incomplete query clause.” message. It then kicks me back to the SQL, I can’t move beyond this point. the tables I am selecting are linked to an external source already, is this causing the issue?

    hope you can help!

    thanks

    A

  • Ashish,
    I am using MS Excel 2010; I got as far as selecting the folder that I created in MS Query; only 2 of the 3 tabs showed up Dummy1 & Dummy2, Dummy did not though it showed fine when I originally renamed it. Also when I tried to move the columns it said there were too many and wouldn’t proceed farther. I have 160 columns, though only 100 rows. Is there a column limit? Great article and any help would be greatly appreciated.

  • Great guide. Many thanks for that. Quick question: auto-refresh the pivot table via VBA. Is it possible?

    Paul

  • Very helpful article and video. Good examples used, easy to understand, explained in layman’s terms. Very valuable. Exactly what I needed. Thank you so much!

  • Hi Ashish
    I watched your YouTube clip on creating a pivot table from multiple worksheets of a workbook

    Thanks you this was very good and easy to understand.

    I am having one issue that I can not resolve.

    When I return the data to excel it does not keep the data in a number format that the source data is in. Some of the data is changed to text format and some is shown as a date format.

    The number format in the original worksheet is unchanged.

    When it comes time to using the sum on the pivot table it does not work as the pivot thinks it is text or a date.

    Do you have any ideas please

  • Hi Ashish
    I have tried this multiple times with no luck. Create all the named ranges, convert to tables and save the file.

    When I choose the file name of the current open file and the query wizard opens, the table names do not show up. I have all of the boxes checked on the options, they still do not show up.

    Now if I open a new file and choose the previous file as the data source, everything shows up fine and I can join the tables and insert the pivot, but the problem is that I need these all in one workbook..

    Any ideas here? I am running Office 2013 professional 32b on Win 7 64b. Not sure if that has anything to do with it or not? I would REALLY appreciate any help you can give, I have spent more than 2 weeks trying to figure this out.

    Thanks
    Marty

  • Hi Ashish
    I was also able to test the same worksheet on Excel 2010 64b on a 64b win 7 machine and it does the same thing. If I create a blank workbook and use the main file as the data source, it has no issue and loads all the tables / columns into the query and can perform the join. I am really stumped here and need this to work so badly!
    Thanks
    Marty

    • I moved the pivot from the summary file into the main file and it shows up, but I can’t get it to update if I change the quantities on the tabs?

      • Hi,

        I am at a loss. All I can now suggest is to move all ranges (from which you want to create the Pivot Table) to individual worksheets and then see if it works. Start from a clean slate. Remove all Tables and Named ranges and try afresh.

        • Ashish
          Thank you for looking at it, I am in such a mess now. I need this to work so badly! Weird that it works fine in an external file but not the same workbook. I have tried everything to no avail. It also works with power pivot but the other employees don’t have the plus version.

  • Hi, THANK YOU for this. Your video was a tremendous help to me today. One problem I am having is that I am trying to combine 6 tabs into a pivot table, but only 4 are showing when I get to Data–>From Other Sources–>From Microsoft Query. I was successful in some workbooks that had 2 tabs, so I’m wondering if there is a limit to the number of tabs that can be combined? Thank you.

  • I watched your video on Consolidate multiple Worksheet into one Excel Pivot table. Impressive techniques. I have tried using this technique in past but was not able to build one single Pivot table with the field I wanted. However, if I create Pivot table from single worksheet then I can get the correct fields, which I wanted. I have five different sheets in single workbook. All have same headings and formatting but different number of rows. First column (Column A) contains Employee ID, Column “B” contains “Type of Leave” and Column “C” contains hours

    When I created Pivot table for Multiple sheets, I am getting only Total hours but not able to get Type of Leave and corresponding hours. I tried with same row numbers for all different sheets but doesn’t get type of leave

    Link for file: https://onedrive.live.com/redir?resid=9609abbf5b972b9!110&authkey=!ANbdZ0pcQwpKu_o&ithint=file%2cxlsx

    How can I resolve this?

    Thanks for your help

    Chirag

  • Hello Ashish,

    Thank you for the tutorial, I’ve been using this every day and appreciate the info.

    My pivot table was working fine and now this is the error that I get when I try and refresh the pivot table:

    [Microsoft][ODBC Excel Driver] The Microsoft Access database engine could not find the object ‘dummy.’ Make sure the object exists and that you spell its name and the path name correctly. If ‘dummy’ is not a local object, check your network connection or contact the server administrator.

    There are a few things that I did that might have affected the file. I re-named my spreadsheet after using the query and I also e-mailed a copy of it to a colleague. Would this be causing the error?

    One thing that is different on my sheet from your tutorial is that I kept both the source data and pivot table in one file. in your tutorial you had a data.xlsx file and a consolidated.xlsx file. Is that an issue?

    Unfortunately I work with confidential data so I am unable to upload the file, is this a quick fix or should I start over again?

    • Hi,

      You are welcome. The error is being caused by renaming the workbook. Furthermore the mail receipient of the workbook has to re-establish the link to MS Query. Read the section titled “Update Table/Pivot Table when files are mailed to someone else” of this Blog article.

      Hope this helps.

  • Hello Ashish,

    I am very impressed with your knowledge of Excel. I had been looking for a more efficient solution to my data tracker and I’ve learned so much from your tutorial. I attempted your solution here and I feel I still need to do more in order for it to do what I need. My data workbook has over 15 sheets – 1 sheet representing each month of the year, plus a Grand Total sheet and sheets with other data. I am tracking the number of hours of consultation per site location. I add up the hours by site location, per month, per quarter, and then add everything all together for a grand total of hours. Currently I keep the site locations on one column, then the hours, within each month’s sheet. I enter site locations as they receive consultation so they are in the order by date. At the end of the month, I add each location’s hours to the Grand Total sheet. I manually do this cell, by cell. Once a quarter passes, I do the same to tally the total hours for the quarter (ex. April, May, June). I know there is an easier and more efficient way to do this. Perhaps you can give suggestions? I appreciate any input. Thank you!

  • Hi, I have tried this blog in both excel 2013 and 2010 and can’t get it to work in either. In 2013 I get up to the SQL query where I see my tables but it keeps putting $ signs after the name of the table, so when I go to change the query to select * from etc it can’t find the tables? In 2010 I create the tables and go to Microsoft query but it can’t even find the tables at all? any help would be great, cheers, Vanessa

    • Hi,

      I cannot figure out why this should be happening. I’ve used this same technique in versions from Excel 2003 to Excel 2013 and it has always worked. If you can share your workbook (over OneDrive/Googls Docs), then I can download and check at my end.

  • Thanks for this great guide.i used msexcel2013 and i managed to exceed the 6xxxx rows limit and have consolidated 14 excel files into one pivot table of 13columns and 970263rows without errors! In some source file the rows were like 290xxx and 170xxx.. Just takes time at 5-10mins everytime i update my pivot.

    • Hi,

      Thank you for your kind words. Please clarify whether you have been able to “Create a Pivot Table from multiple worksheets of a workbook” or “Create a Pivot Table from multiple workbooks”

  • Ashish, I love the approach. I have been able to get this to work with named ranges and withou converting to Excel Tables. I avoid tables when I have less skilled users entering data into my worksheets. Instead, I have used dynamic named ranges via an OFFSET formula (example below). But when I use this approach, the dynamic named ranges don’t show up in the query list after I select the Excel file. Do you know why?

    =OFFSET(‘Current BLIs’!$A$4,0,0,COUNTA(‘Current BLIs’!$A:$A)-2,COUNTA(‘Current BLIs’!$4:$4))

    • Thank you for your kind words. I have never tried to use my Pivoting technique with the OFFSET function. I’ve always only used Tables. Upload your workbook to OneDrive and share the link of the workbook here.

  • Hi
    Thank you for the pivot video.

    I have successfully crated pivot for multiple sheets

    Now I want this template to be used by my team members

    But they cant refresh the pivot on their computer. may be due to the linkings I have done on my computer using SQL

    can you please guide so that we all can use the same template

  • Hi Ashish – Thank you for your very helpful tutorial, i followed it successfully on one workbook. I then tried on a second workbook and i am unable to – I have tried everything and cannot find where i am going wrong. I’m getting Internal OLE Automation error, then Problems obtaining data.
    Your advice would be greatly appreciated.
    Thank you.

  • Hi,

    Thanks a lot giving such beautiful insights.

    I am facing problem with SQL query,u mentioned (SQL query:

    union all
    Select * from dummy2)

    but what code will be for more sheets as I want yo join 5-6 sheets and i not able to make SQL query for the same

    • Hi,

      You are welcome. Thank you for your kind words. The SQL query would be:

      Select * from dummy
      union all
      Select * from dummy1
      union all
      Select * from dummy2
      union all
      Select * from dummy3
      union all
      Select * from dummy4

  • Dear Sir,

    Kindly clear the error . The Number of columns in the two selected tables or queries of a union query do not match.

    • Hi,

      This error arises because of the one of the following reasons:

      1. The number of columns in all named ranges is not the same
      2. The order of columns in all the named ranges is not the same
      3. The spellings of headings in all the named ranges is not the same

      Correct for all these problems and the query should start working just fine.

      Hope this helps.

  • Hi,
    Your method have been very helpful in building the system that I am working on.
    It has worked for all sets of tables, except one.
    That has 3 tables and when I reach the step after the sql commands where we see the consolidated table before moving the data to excel, only the numbers from 3rd table are shown, not the text i.e. not the details like name, region etc.
    Now, without these fields, the subsequent pivot table is incomplete. Earlier I solved this issue by changing the field settings to text of the relevant columns. But now when I do the same for the same table in another excel, the problem remains. I have made a fresh sheet for the excel over and over, but of no use.
    I was hoping you can advise on the same.

      • Hi Ashish,

        I have dataset which is more then 1000000 rows in one worksheet and another dataset with 70000 rows in the second worksheet. Will the solution making a pivot table from two different worksheet will work on this much data as well

        The headers are the same in both the worksheet.

        Please let me know if it can be done

        • Hi,

          Unfortunately MS Query will not recognize named ranges which span more then 65536 rows. There are two ways forward:

          1. Store all your data in CSV files in a certain folder and then via Power Query Query append data from all CSV files (this is very simple to accomplish with the help of Power Query). Once data from all CSV files has been appended, one can transfer the data to the PowerPivot and create a Pivot Table from there.

          2. Split the 1000000 rows of data lying in one worksheet into multiple worksheets with no more than 65000 rows of data in any one worksheet. Now use the MS Query approach.

          Hope this helps.

  • Hello and thank you for this reference. I’ve successfully completed the steps and was able to create a pivot table in a separate workbook. Question, in Step 7 instead of creating a new workbook, can you select a new worksheet and have everything in the same workbook? I think it would be easier to share with Team members instead sending both workbooks and reestablish the path. Thank you in advance for your reply. LisaMarie

      • Thank you, the instruction you suggested helped. I have been updating my spreadsheet without any problems until now. Today when I went in, I received the following error message when performing the update procedure step 6… I first receive an “ODBC Excel Driver” message with an okay button, another box “Problems obtaining data” with an okay button, another box “Query from Excel Files” has failed to refresh” with an OK and Cancel button. Any idea why this would suddenly show up? Thank you.

          • I’m following the steps to update the Table/Pivot Table (which I doing in the same worksheet); I follow the steps down to when up update the SQL Query in the Connection Properties and when I type in the “union all, select *from February10” and click OK… this is the point when I receive the OCBC Excel Driver error as described in the March 9th post.

          • I can send you my spreadsheet, however the security at work blocks Onedrive. I will work on this from home and get you a copy.

  • Is there a limit to the number of sheets that can be combined? I have a spreadsheet of 52 sheets. When I combine more than 23 sheets, the total information does not populate. When I try to combine all 52 sheets, I receive an error message “[Microsoft][ODBC Excel Driver] Query is too complex.” I click ok then receive message “Problems obtaining data.”

    I appreciate your help.

    • Hi,

      Honestly, I have not tried appending data from so many sheets via this technique. The only limitation I am aware of is that any one named range should not exceed 65,000 rows and 124 columns. I do not think there is any row limit in MS Query (I remember having taken it to 300,000 rows). Try using Power Query instead to append data from multiple CSV files lying in one folder.

  • Ashish! Even four years after creating this, you are still available for questions on this, amazing!

    I’m having the problem that my values are being converted from time hh:mm:ss to decimal. Is there a way around this? I’m also having issues where it does not recognize the value in a column (sum=0) but still recognizes as a count. I can share the file with you if I may?

  • Hello Ashish,
    I am excited to see that you are still providing support. I love this tutorial, it is exactly what I needed. I was able to successfully recreate what I needed with dummy info at home but upon returning to work and attempting to complete this with the actual information I am hitting a wall. When I try to get the external data I get the following message “The data source contains no visible tables”. I am very sure that I have created the tables correctly, after re-doing it twice. Might you have any insight as to what my issue might be? I am using Excel 2010

    Thank you!

    • Hi,

      Thank you for your kind words. This error normally occurs when you convert the range into a Table and then define a name. You should ideally first define a name to the range and then convert it to a Table.

      • I am sorry, I nested my reply incorrectly,

        Thank you very much Ashish for responding so quickly. This was my issue and I now have valid tables for my pivot table. However, after adding them in the Query Wizard my error message is ‘Too many fields defined’. The workbook I am working with has 29 sheets with 12 identical columns in each. The rows range from just over 100 to over 800. This is for one year of data and I eventually will have data for 4 years. In researching the error message I see that I can only have 255 columns. Will my only option be to have all the info on one sheet?
        Thank you again!

        • Hi,

          You are welcome. If you have so many worksheets, then I would suggest a different approach

          1. Save all those worksheets as CSV files and place them all in one folder. Remember that all CSV files should have the same number of columns, same order of columns and same spellings of headings
          2. Use POwer Query to append data from multiple CSV files to one running range
          3. Either Load all appended to a worksheet or load the data to the Data Model
          4. Create a Pivot Table.

          • Hello Ashish, I hope you are well. After some computer upgrades I was able to successfully create my pivot table from 4 separate worksheets and it is a thing of beauty! this morning I logged on to review the data and I get the following message upon attempting to refresh my table.

            [Microsoft][ODBC Excel Driver] The Microsoft Access Database engine could not find the object ‘name of one of my worksheets’. Make sure that the object exists and that you spell it’s name and the path name correctly. If ‘name of one of my worksheets’ is not a local object, check your network connection or contact the server admin.

            When I opened the file it prompted me to enable connections which I did so I am not sure how to get the connection to work without recreating the pivot table.

            Continued thanks for all of your help!

            Donna

  • Thank you very much Ashish for responding so quickly. This was my issue and I now have valid tables for my pivot table. However, after adding them in the Query Wizard my error message is ‘Too many fields defined’. The workbook I am working with has 29 sheets with 12 identical columns in each. The rows range from just over 100 to over 800. This is for one year of data and I eventually will have data for 4 years. In researching the error message I see that I can only have 255 columns. Will my only option be to have all the info on one sheet?
    Thank you again!

  • Hi Ashish. I hope you can help me.

    I created the named tables and saved the file but when i go to MS Query, an error message comes up stating “This data source contains no visible tables”

    I did go back to the file and confirmed the named tables do indeed exist. Any idea why the tables are not being recognized?

    • Hi,

      This error occurs when first convert the range into a Table and then assign a name to it. You have to do just the reverse – first convert the range into a Table and then assign a name to it.

  • Hello,

    I am trying to combine 5 tables from the same file. When I go to enter the SQL lines I get the error “Could not add the table ‘Table Name’.” Do you know why I might be getting this error?

  • Hello,

    I am trying to put together a slightly different format with similar data. With all tabs having the same headers, (using your example) I would like to have the pivot show Jan, Feb, March in the pivot then by brand. Ultimately, I would like one pivot to show total by brand by month and not total for all 3 tabs combined.

    example:
    Jan

    Brand 1: total
    Brand 2: total

    Feb

    Brand 1: Total
    Brand 2: total

    March

    Brand 1: total
    Brand 2: total

    how can i accomplish this in excel? Thank you!

    • Hi,

      In all three worksheets, you will have to create a Date column. Once we append data from all worksheets into one large range and create a Pivot Table, we will be able to use the Grouping feature to get months.

      • Hi Ashish,

        thank you for your help! Your tutorial has been extremely helpful to me today 🙂
        I do have one more question. I am able to get the pivot to work beautifully if I create a new workbook to put the pivot in. This would create two files, one for the tables, and one for the pivot. However, I am trying to put it all in one file. Is it possible to have i all in one file instead of two? I tried creating a new worksheet and going to data source. But, I am getting the error “Unrecognized database format” when I try to open the data source.

        Do you have a solution for this?

        Thanks!
        Amy

          • Hi Ashish,

            opening a new worksheet does not work. I opened a new worksheet, saved the workbook, then tried to work off of the new worksheet but it seems to not be able to pull the tables. the error i get is “”Unrecognized database format” then it lists the path to my file.

  • Hi Ashish,

    This works great for me. My only question is…

    What if I want to summarize each of the tabs by month. So you have dummy1, dummy2 etc. Your tabs are Jan, Feb, Mar. Is there a way to create a month field as part of your selection without adding a month column in each tab? As we name the table name dummy1, we can call it Jan instead and be able to have a field name representing the table name.

    Thanks,

    Frank

    • You are welcome. I do not know what changes are required in the SQL Query to accomplish that. Though I know that it is possible. May be you need to seek assistance from someone who knows the SQL programming language quite well. If you find your answer, please share it with us here.

      Thank you.

      • I do know a little SQL, I was able to modify the view as such…

        select ‘dummy1’ as ‘TabType’, * from dummy1
        union all
        select ‘dummy2’ as ‘TabType’, * from dummy2

        This should insert a column before each of the table fields as what you specify it to be. Easier than inserting a column of data in each table that you don’t necessarily need.

  • Hello,

    My workbook has 14 sheets, 17 columns and 650 rows, with the same content, except for qty. It is an order form. I would like to combine all sheets into one pivot to see total requested orders for each item. This method worked with fewer sheets, but if I add all 14 sheets, quantities will only Count, but do not Sum on the pivot. Tried your trick of adding zero to the quantity column on each sheet, but still does not sum. I do not receive any error messages. Can you please help?

    Thank you!

  • Hi,

    I’m trying to make a template for work which is used for reserving space for different events here. At the end of the FY, we must come up with a report that shows how many different kinds of events we had, the money brought in, etc. I have a workbook with 13 worksheets (1 just for reference to data validation/conditional formatting purposes). I think a pivot table would be a great way to summarize all of this data, however an error message comes up when I try to run the query and says the program can’t find any tables in my workbook. I don’t know how to fix this. I’m also not sure if a pivot table is exactly what I do need, but I need something that can summarize 12 months of data and is printable for a report in the end. Could you help?

    Thanks!

  • Hi,
    I am trying to create pivot table from 3 sheet. few column are same in all three sheet. I cannot use listed steps to create Data tables due to some fields are empty. Can you advise how should in create Pivot table from multiple sheet.

      • Hi,
        All Headers are in same position, but data in some Colum are empty. The error I am getting is This Database contains no visible Tables.

        • Hi,

          Some others have also mentioned that they run into the same problem. From what I can remember, this problem occurs because the order of table conversion and naming of a range is reversed. As you can also see in the video, the order followed is:

          1. First assign a name to the range; and then
          2. Convert the range into a Table

          Please try this on a fresh copy of the workbook.

  • Hi,

    This method is work perfectly for me. However, I have a problem if I saved this on other computer, the connection was lost. How to edit the connection string in order the workbook able to refresh even saved in other computer?

  • I want to create a single pivot table from multiple sheets.
    When I give the below statement in excel 2007
    Select * from SHEET1
    union all
    Select * from SHEET2
    union all

    It is throwing syntax error in query. Incomplete query clause.
    How to solve this? Please support.

  • Hi Ashish,

    How many rows did you manage to read into Excel with this technique?

    I managed about 850 000. I haven’t tried any more than this. Would it be possible to go beyond 1 000 000 rows?

  • Hi Ashish,
    I am trying to create Pivot table from two worksheets. the coding in first few column are same in both worksheet. i have tried using Data query but it is not working. Can you please advise best how should i created summary pivot table

      • Hi Ashish,
        I am trying to summarise the data from two work sheet using power Pivot. when i am merging the data table and selecting column to create relationship, it shows error in merging table.

          • hi Ashish,
            I am able to append the data and create combine table. Now when i summarise data with pivot table. i wont be able to add YTD calculation in Pivot table. Can you advise me how would i add YTD column.

            Thanks
            varun

          • Hi,

            I cannot figure out a dynamic way of accomplishing this. Here are my thoughts:

            1. We can definitely not solve this problem with a conventional Pivot Table because there is no way to add a YTD measure there
            2. Using the PowerPivot, there is a way to add a YTD measure but that measure will get added for each month where as you want it only at the end of every 3 months (rightfully so). So once we write the DAX measure to compute YTD figures and we get a YTD column for each month, we may hide the YTD columns we do not need. This is not a fancy solution but does get use the desired result
            4. We can convert the PowerPivot table to equivalent CUBE formulas and then remove the unrequired columns. While this will work, it will not be dynamic i.e. when you get data for the next few months in your base data worksheets, you will have to copy/paste the CUBE formula cells to show the result for the updated months
            5. Formulas would also pose the same problem of copy/paste as described in point 4 above.

            All in all, I do not have a fancy right click > Refresh solution to this problem.

          • Thanks for the help. I am now using ISERROR& vlookup to consolidate data from both sheet. Would you advise any other formula to summaries data.

  • Hi Ashish,

    Great tutorial; however, I created the same for a petty cash format for my company and it ran fine on testing, but when I saved the file and sent it onward for the Finance team to use, the pivot table portrays the below message on being refreshed:

    Unrecognized database format
    “\\COMPUTER-4\Users\public\Documents\Petty Cash Template – Amex Auto (Repaired).xls”.

    What am I doing wrong, please? Would appreciate a resolution to this.

    • Thank you. The recipient of the workbook has to reestablish the connection to the workbook – please read this section of the Blog “Update Table/Pivot Table when files are mailed to someone else”

  • Hello Ashish,

    I liked your way to combined multiple sheets. its working good for me but when it comes to pivot,something went wrong.

    I am combining YTD data which has saved in two sheets. i have two columns in the pivot i.e. shipment and defect. Shipment for the year seem good but i cant see defect numbers. it shows 0 instead. Would you help ? Thanks.

  • Hi There, I have tried to do this without actually creating a table. The reason why I can’t create the table on the data cause the system won’t allow me to refresh the data if it’s in a table format.

    I was able to define the area and name it. Followed all the step but the only thing is that, it won’t append. Both tables have the exact same column with the exact same title. For example, it creates Revenue in one table and Revenue1 in another table. How do I make it append so it sum together?

  • Hi there, I followed all the steps explained above and achieve my goal. I created a pivot table with around 1000 rows of data collected from 6 sheets. It is working well.
    The file is taking too much time whenever it is opened sometimes 2 minutes. This is creating a panicky situation to the end users. Please suggest some ways how this problem can be solved ?

  • Hi Ashish,

    I followed the above instructions on adding additional worksheets to a pivot table and they seemed to work. However, I received this error message after accepting the changes. The ‘Charles’ that the error message refers to was part of the first table without and problem and no changes were made to his sheet. I attached a photo of the error message below. Thank you again for your help!

    Best,
    Jack
    /Users/JackKavner/Desktop/excel picture.PNG

  • Hi Ashish,

    Thank you very much for these instructions!

    However I’m facing a bad problem! Yesterday I built my workbook using this technique and everything was fine. Today I get back to it and I try to refresh my dataset and I get an error “The microsoft access database engine could not fine the object …”. Nothing changed and I checked in the name manager, everything seems fine.

    Any ideas?

  • Dear sir,

    I generated one pivot table as per the steps mentioned above and I got my result also. The pivot table and source data are available in the same workbook and this workbook is saved in a shared drive.
    I am facing one problem that in some of the systems it is working without any error while in some systems ‘ODBC Drive Login Failed’ error is coming. The users of this file are not sitting at the same building. They are using this file across the complex . Please explaing to solve this problem. Thanks.

    • Hi,

      Sorry for the delay in replying. This baffles me. I cannot understand why it is working for some and not for the others. I hope all users are simply double clicking on the file on the shared drive itself. If some users are copying the file from the network location, pasting it somewhere else and then opening it, the refresh will not work because the path will change. So please ask all users to double click the file for the shared drive itself.

      • Dear Sir,

        Thank you for your reply. Still, I am suffering from this problem. I personally checked on the end users machines, they are not saving it somewhere else and simply double clicking on it. ‘ODBC Excel Driver Login failed’ this message box is appearing. Settings of ‘ODBC data source administrator’ are also same as like my machine on all the end users machine.
        This is something very strange and end users are suffering a lot. If something you have, please comment.

  • Hi, thanks for sharing the video, very helpful.

    Just One question for you: I don’t have Power Query and I would like to use data model for numeric distribution. If I follow the steps above, when I select the last option – Return Data to Microsoft Excel – Pivot Tabel- the option: Add this data to the data model is not active.
    Do you have another solution for this issue? Thanks a lot.

  • thanks a lot Ashish, it worked perfectly for me..

    but I have one question,

    to merge the two work sheets data we use ‘union all’

    here, I wana get the difference between two work sheets data.

    what should I use in the place of ‘union all’? pls let me know..

  • Hi Ashish,
    I am able to create pivot table from multiple sheet. but some reason one of the sum column is not giving any total. i have checked the format which is numbers.
    when i double click on pivot to check why it not giving any sum value, it show cell as text.
    can you advise ho to fix this format issue.

    Regards,
    varun

      • Hi Ashish,
        I am able to fix the format issue. but now another issue arise. when i rollover my file to next month like eg Feb. and add data for the month of feb & refresh pivot it done not add in consolidated pivot summary. Do i have to change data source? & how.

        thanks
        varun

          • Hi,

            I do not know what you mean by “rollover”. However, please refer to the section titled “Update Table/Pivot Table when files are mailed to someone else” in the original Blog article.

  • Hi Ashish,
    I have two worksheets Jan and Feb..As both are dynamic i.e i will add data in future i have converted them into tables ‘Jan1’ and ‘Feb1’, both the tables with extra rows so that i can add the data. When i merged these two table using M S Query and created the Pivot table out of it, i have ‘Blanks’ column in the Pivot table & Pivot Chart, which is not neccessary and silly. I have tried another way by not converting them into tables and used dynamic ranges using OFFSET function. But M S Query is showing an error saying that there are no tables in the source data. Please suggest me how to create a Pivot table without blanks from Dynamic source worksheets

    • Hi,

      You do not need to select any blank rows when converting the range into a Table or defining a name. It is the job of the Table functionality to consider new rows of data. So only name and convert into a Table, the range till where data is.

  • Hi Ashish,
    I am using 2013 excel. when i consolidate data to create pivot table. it runs very slowly. Do i need to add any excel add?

  • Is there any way to get this to work with a *.XLTX file? When I attempted to do these steps, it told me the file wasn’t in the expected format. I am working with a business intelligence package that needs the file in an XLTX format. Thank you.

    • Hi,

      The extension of the file does not matter. I just I tried the steps outlined in my original Blog article on a .XLTX file and I did not get any such error. I guess the mistake you are committing is that you are first converting the range to a Table and then assigning a name. It should be the other way round. First assign a name to it and then convert into a Table.

      Please retry.

  • Hi
    First of all thanks for your support for excel learners.
    I have created same pivot table by two tables in two sheets as you shown in vedio else everything is same. If I insert new data to table is reflecting in pivot on refresh however after closing the worksheet when open it and on inserting data into table it is not reflecting in pivot table on refresh. I had tried multiple times and on multiple systems.

    Please help

  • Hi Ashish
    i have recently installed office 2016 which has built in power query. could you please let me know how i can create pivot table with power query from multiple worksheets in excel 2016?

    thanks

    • Hi,

      My suggestion would be:

      1. Save the worksheets and CSV files and save them all in one folder
      2. Using the Power Query, append data from multiple CSV files into one running range
      3. Load the data to the PowerPivot
      4. Build your Pivot Table from the PowerPivot

  • Hi Ashish,

    I have 899,940 rows in Sheet 1 and 885,000 in Sheet 2. Columns Headers are same. I am not able to create a single pivot using SQL Query. It is giving errors.

    1. Initially I got a message: “This data source contains no visible tables”. I had to check “System tables” after that

    2. In the Query Wizard – Choose Columns window, I see the names of the worksheets with a dolar sign at the end (instead of the name I gave to the array). However, selecting the worksheet name I was able to choose columns.

    3. While executing the SQL command (union all), I got another error: Syntax Error in FROM clause.

    Kindly Note: I tried using Power Query. However Append failed.

    Kindly suggest!!!

    Thank you,
    Suvadeep Dutta

    • Hi,

      The limitation of MS Query is that number of rows on any worksheet should be within 65,000. So the MS Query solution will not work. The append functionality in Power Query should work very well. What problem do you face when you append the data using Power Query?

  • Its working with 10 rows in 2 CSVs.

    Is it because of total number of rows across 2 worksheets are 1784940, “append” is not working?

  • Thank you for creating these learning videos. I found your explanation of how to create a pivot table from multiple worksheets very helpful in consolidating 15 years (and 700K+ rows) of data from 15 worksheets with same column headings into a single pivot table. Of course, now I want to do more with that pivot table! 🙂 Now, I would like to add metric data (not the same column headings) for those 15 years into another worksheet (“METRICS”) where I might add metal pricing for each year, plastic pricing for each year, etc…. I’ve figured out how to ‘relate’ the data by using the year column, but I cannot figure out how to make the data in the METRICS worksheet come up as a item to include in the pivot table values. So, today, I can get the unit price in the pivot table over the 15 year timeframe, but I would also like to see what the metal value was in each of those 15 years from the METRICS worksheet. Can you suggest something to me?

    • Hi,

      I will need to see your workbook. Share the link from where I can download your workbook. Please remove some data from your workbook so that the file size is small and can be easily downloaded. Also, show me exactly what you want your result to look like.

  • Hi thank you so much for the tutorial. I was wondering if it is possible to automatically have the pivot fields update if I add a now column to all the worksheets. I am having issues with the new columns showing up. Thank you!

    Jackie

    • Hi,

      You are welcome. Once you add the column to all ranges, go to Formulas > Name Manager and edit each named range to include the new column you added.

      Hope this helps.

  • Hi,

    Quick question. When I try to move or rename my file in the directory the pivotal table loses connection the objects I established. Is there a quick fix for this?

    Thank you!

  • Hi Ashish,

    Thanks so much for your very useful sharing. But i do exactly the steps but i can not have the expected layout though i have only 3 sheets. JAN to MAR
    1. As i cross check table name, it does not appear in the left corner.
    2. As i use query, the first table memtioned always ” can not add”.
    3. Pivot table attributes will have 3 data: code 1, code 2, code 3, sales 1, sales 2, sales 3. It means that they can not union.

    I am using of Excel 2013 without power query adds_in.

    Could you mind help me to firgure out?
    Tks so much

    • Hi,

      You are welcome. I cannot say what mistake you are committing. All I can request is for you to try again. Reread the steps and look at the video carefully.

  • Is it possbile to run this query instead where I filter data from ProdData Table on a where clause. The output would have same number of columns for both the tables.

    SELECT ProdData.Source, ProdData.Name, ProdData.UserID, ProdData.PersonalId, ProdData.Email, ProdData.Address, ProdData.Address2, ProdData.Address3, ProdData.Address4, ProdData.`Zip Code`, ProdData.City, ProdData.Country
    FROM ProdData
    WHERE ProdData.`Active Check`= “YES”

    UNION ALL

    SELECT TestDataActiveSortBySchemeName.Source, TestDataActiveSortBySchemeName.Name, TestDataActiveSortBySchemeName.UserID, TestDataActiveSortBySchemeName.PersonalId, TestDataActiveSortBySchemeName.Email, TestDataActiveSortBySchemeName.Address, TestDataActiveSortBySchemeName.Address2, TestDataActiveSortBySchemeName.Address3, TestDataActiveSortBySchemeName.Address4, TestDataActiveSortBySchemeName.`Zip Code`, TestDataActiveSortBySchemeName.City, TestDataActiveSortBySchemeName.Country
    FROM TestDataActiveSortBySchemeName

      • I have two tables ProdData and TestDataActiveSortBySchemeName, both have Name, UserID, PersonalID, Email, Address, Address2, Address3, Address4, Zip Code, City, Country as common columns. In addition to these columns, ProdData has 3 more columns Active Check, Vested Check and With Ultimo. Now I want a result in a table with everything from TestDataActiveSortBySchemeName and Select Name, UserID, PersonalID, Email, Address, Address2, Address3, Address4, Zip Code, City, Country from ProdData if ‘Active Check’ is ‘YES’.
        So basically the number of columns that I am selecting from both the tables is same. I want to get all the data from TestDataActiveSortBySchemeName and check ProdData for ‘Active Check’ is ‘YES’ to Select data from ProdData.

  • Thanks ashish for this fantastic vedio, it is very useful for me. I am using office 2013 and there is one feature of timeline slicer, it is worked when I prepared the pivot table directly from single sheet but when I prepared pivot table by using ms query by adding 2 sheets, not able to use time line slice it shows error there is no data formatted as date
    May you pleased help me to get out from this problem.

      • Hi Ashish,

        I followed the original video “Create a Pivot Table from multiple worksheets in the same workbook” for my worksheet. My intent was to create a master table and run pivots off of that. I can create the master table but the numerical data that came over cannot be formatted or summed. If I instead create a master pivot table again the data cannot be summed – only counted. Not sure how to solve this. Has this issue come up before?

        Thanks

        • Hi,

          Ensure that the data type of each numeric column is indeed numbers. Also, use the Power Query solution to create a Pivot Table from multiple worksheets. It gives far fewer problems.

  • Hi,
    This article was of great help to me, thanks. I am running into problems though when I do this exercise for 7 sheets. I get the following error: ODBC Excel Driver Internal Ole Automation Error. Every time I limit the exercise to 4 sheets, it works, but once I exceed that I get this error. Any assistance will be greatly appreciated.

  • Hi there i just get a little confused about the monthly and consolidated.
    Well in the beginning we have monthly file with two sheets. So we set the range names & tables and then we saved it as consolidated. So the difference is that we have ranges & tables in consolidated file. Then we open a new workbook and we choose monthly file to set up the sql??? How is that?? monthly file does not contain any named range or table? So how is it possible to find dummy & dummy1 in the monthly file??. Dummy & dummy1 exist in consolidated file.
    So in the new (3rd) file we choose lets say Pivot table and we transfer the data through sql Select * bla bla command. So we have our pivot in this 3rd file.
    Then we assume taht we must add March sheet in the monthly file. We do so, we save it and then we open consolidated??? And we go to update pivot into consolidated file???But consolidated file does not have any pivot. The pivot was created in the 3rd file…please can you explain this mess??

  • hi. when creating query, i have this error message about ‘too few parameters. expected 34’. How do I solve this?

  • Your workaround above worked like a dream for me. Everything was working fine until this morning. When I refresh the pivot, certain values in a field are appearing ‘blank’, although data exists in the source sheet. And if I double click in the pivot and it opens up the spreadsheet, there are no values in that field. Any idea why this is happening? Please help!

  • Hi Ashish,

    Thanks so much for posting this, this is really helpful. I could do this in the workbook saved in desktop. Would like to know how would I apply this in a workbook placed in SharePoint. Is it possible? If so, please suggest

    • Hi,

      You are welcome. Using Data > Get & Transform, fetch data from SharePoint and then append data. Push data to the PowerPivot and build a Pivot Table from there.

  • Can I create a pivot table using multiple work sheets in the same workbook? I have about 6 work sheets that I need are the source for my pivot table. Seems easier if I could use one workbook. Thank You

      • Ashish – I use Power Query to summarize data from six individual worksheets in a single master table – all in the same workbook. I have no issues adding/deleting rows of data in each worksheet and refreshing the master. I also have no problems adding columns of data to the right side of each worksheet and refreshing the master so long as I duplicate this column addition exactly the same in the other worksheets. Where I run into problems is when I try to delete columns of old data – even when duplicated exactly in all worksheets. Attempting to refresh the master give an error – the query still looks for the deleted columns. Same situation after editing the query itself and deleting the columns there too. Is there a way to remove columns without developing errors? Thanks.

  • Hi Ashish

    I am having a problem with big data. So, the number of rows I have is more than excel limit and I had to put it in two different sheets. Now, the problem is that I want to have one pivot table which included all the data from 2 sheets. Because I need the whole average of one variable (here is the average income in United States) as well as average for each row (here is the average income in each state). I do not know how to have one pivot table from different sheets in which I can also a calculated field. Because I made a pivot table from both sheets but the calculate field is inactive. I watched your video in YouTube but when I write the code to union the tables I got the message that table (table name) cannot be added. I first named a range and then created a table but still it does not work and even in the window (Query Wizard) I cannot even see the table names I made and instead I can just see the name of the sheets I have.

    I hope you understand my question.

    Looking forward to hearing from you,

  • Hi,

    I followed the steps in the attached as I am trying to do this for data sets so large they can’t be on one sheet, when I got to running the SQL code I did the exact same but received error “syntax error in query. Incomplete query clause”.

    it picked up the right tables but I can’t get it to run the aql, any tips?

  • hi
    Ashish its nice to have such video to sort the big challenges of data management.
    I follow ur instructions and I have 11 sheets in my work book above instructions are working till 2 work sheets, as I face problem that its loading more than 2 work sheets but data is not logical.

  • This worked for me with the exception of several months (financial data by month for three separate fiscal years, each fiscal year on a separate worksheet). For some reason November, December, January and February come through with no data and the rest of the months are fine. I refreshed and re-added the months down in the pivot and they keep populating as count instead of sum. If I change them to sum, i get $0 for all data. I tried recreating a new query on a fresh tab and then all but November worked. I tried again then Nov-Feb quit working again. I can’t figure out why they are defaulting to count in the Value section of the pivot and why they aren’t reading the data. All worksheets have identical columns/headers but different amount of rows.

  • Hello Ashish! Happy New Year! First of thank you very much for this very informative video! Job well done!

    Sorry to disturb you I would just like to ask if you encountered this error under Microsoft Query “Too Many Fields Defined”?

    I encounter this error right after step 13. Then it does not allow me to move forward for the next steps in the instructions.

    I am working though with up to column “s” with 300 rows per sheet and the number of sheet is 20.

    I know the sample sheets on this instructions is only 3 sheet but would this instructions still work with what I needed to do with the number of sheets and columns I am working on?

    Would you know of any work around to the error I am facing so that I can still proceed with the single pivot table for all the sheets I am working on?

    Thank you very much once again!