Create a Pivot Table from multiple worksheets in the same workbook


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\

RSS 260 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.

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

  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

    • Hi,

      I do not understand what you want to do. Please get rid of all errors on the worksheet and explain what you want to do.

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

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


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

    > Pending = 1
    > Closed = 1
    > 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!


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



  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?


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

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

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

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

  29. Hi,

    Is their any macro for multiple files & multiple sheets data pull in Pivot table

  30. 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 (
    ' 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
    For i = 1 To UBound(arrFiles)
    If strSQL = "" Then
    strSQL = "SELECT * FROM [" & strSheet & "$]"
    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;" & _

    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

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


  32. Hi Ashish,

    your guidance worked superb at consolidating expnese sheets.

    Many many thanks,


  33. 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!!

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

  35. 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!



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

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


  38. 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!

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

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


  41. 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!

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

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

  43. 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:!110&authkey=!ANbdZ0pcQwpKu_o&ithint=file%2cxlsx

    How can I resolve this?

    Thanks for your help


  44. […] already created a pivot table and chart using Microsoft Query. I followed the steps from this blog: Right now, I’m figuring out how I can add new data into pivot table. Is it possible to add […]

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

  46. 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!

  47. Here is the link to an example of my workbook. I copy/pasted into this example so the cells are not adding up like in the original workbook.

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

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

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

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

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

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

  54. Hi Ashish,
    Is there any way to import exiting power pivot model to another / new excel file like Power BI Designer?

  55. Dear Sir,

    Thank you very-much

    Thank you for your very helpful pivot table tutorial video.

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

  57. 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,

      I will need to see your workbook. Upload it to OneDrive and share the download link here.

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

  58. Hi.. its great!
    can u give me SQL for joining multiple sheets...using union all option

  59. […] multiple worksheets. i have created pivot table , chart using microsoft query. followed steps blog: right now, i’m figuring out how can add together new info pivot table. is possible add […]

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

    • Hi,

      Steps 6 should be to create another worksheet in the same workbook. Then follow step 8 onwards.

      Hope this helps.

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

        • Hi,

          Sorry. I cannot pin point the reason for this error. What happens when you follow all steps again?

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

          • Hi,

            There has to be a space between * and from. If this does not work, then I need to check your workbook.

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

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

  62. 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?

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

          • Thank you Ashish,

            I will give this a try and keep you posted on my progress.

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


          • Hi,

            I am not sure of why that error is arising. Upload the workbook to OneDrive and share the download link here. I'll take a look.

  64. 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!

  65. 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?

  66. 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?

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


    Brand 1: total
    Brand 2: total


    Brand 1: Total
    Brand 2: total


    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?


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



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

  69. 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!

  70. 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?


  71. 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,

      For my technique to work, all 3 worksheets should have the same headers, at the same position and should be spelled the same.

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

  72. 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?

  73. Thanks. Beautiful solution!

  74. […] →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 ... […]

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

  76. 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?

  77. 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,

      Please share more details. Where exactly are you getting stuck?

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

          With such scanty information I cannot help you at all. Upload your workbook to OneDrive and share the download link here.

          • September 19, 2016 at 7:01 am

            Hi Ashish,
            Below is the link


          • Hi,

            Append the data from both worksheets by following the technique described in this Blog. Once that happens, you may create a Pivot 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.


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

            You will first have to append data from both worksheets into one please. Once that is done, you may use the SUMIFS() function.

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

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

  80. 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?

  81. 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 ?

  82. 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!

    /Users/JackKavner/Desktop/excel picture.PNG

  83. 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?

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

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

  86. Prasanna Kumar Reddy January 31, 2017 at 5:22 pm

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

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


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

  89. 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?

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

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

Leave a Comment

Your email address will not be published.