This is excellent I was searching something like this for more than a month, then landed here... Please post more videos like this which would be helpful on day to day basis in excel.

@excelenthusiasts

3 жыл бұрын

You are welcome. Thank you for the feedback.

Thank you so much! I spent hours looking for an answer and in nine short minutes, you were able to teach me how to do what I needed to do.

@excelenthusiasts

7 жыл бұрын

You are welcome.

thank you for posting this tutorial....great job Sir!!

@excelenthusiasts

8 жыл бұрын

+Rogel Noble You are welcome.

Woo you are Amazing . I work with large quantity of data and this tutorial just made my life so easy. Thank you so much for sharing.

@excelenthusiasts

7 жыл бұрын

You are welcome. Thank you for your kind words.

very good and straight to the point. I like it. Thank you.

@excelenthusiasts

7 жыл бұрын

You are welcome.

Dear Ashish, I was searching the youtube for any possible solutions to combine multiple worksheets which contains same number of columns and infinite rows. In one of the videos, I could see that I need to have an unique cell for me to combine. Unfortunately I could not create an unique cell, but was on the look out, then your video was in hand. That was wonderful Ashish. I could quickly learn the MS SQL Query method and worked well for me. Thank you and I appreciate your efforts in educating.

@excelenthusiasts

8 жыл бұрын

+Ganesh Manchi You are most welcome. Sorry for the delay in replying.

Thank you so much Sir! That was awesome! Please keep educating us!

@excelenthusiasts

7 жыл бұрын

You are welcome. Thank you for your kind words.

Great content! Thank you for posting this solution, great example of the power of Pivot tables and MS Excel Query!

@excelenthusiasts

8 жыл бұрын

You are welcome.

hi Ashish, very good explanation so that everyone can understand the content easily..i have been looking for this content frm a time being..finally i got it thru your tutorial..thank you for this and keep uploading differnt contents..

@excelenthusiasts

8 жыл бұрын

Thank you for your kind words. I am glad you liked the content.

No add no bakwas direct straight to the point...awsm...

@excelenthusiasts

5 жыл бұрын

Thank you.

Awesome tutorial Ashish.. Thanks for teaching this technique in such a short time.... Regards and keep doing the good work......

@excelenthusiasts

9 жыл бұрын

Manpreet Singh You are welcome.

Superb.. Nice explanation, very much useful topic. Thank you for sharing.

@excelenthusiasts

8 жыл бұрын

Thank you for your kind words.

Thank you for posting this! This has helped me so very much.

@excelenthusiasts

6 жыл бұрын

You are welcome. Glad to hear that.

You're awesome Ashish!! Great job

@excelenthusiasts

6 жыл бұрын

Thank you for your kind words.

Thank you sir! This is very helpful for my work. :)

@excelenthusiasts

8 жыл бұрын

+Shab Chique You are welcome.

thank you so much for such very useful tutorial, great job

@excelenthusiasts

6 жыл бұрын

Thank you.

Thank you Mr. Mathur , This was worthwhile

@excelenthusiasts

7 жыл бұрын

You are welcome. I am glad you liked the solution.

You are my lifesaver. THANKYOU

@excelenthusiasts

7 жыл бұрын

You are welcome.

Brilliant video! This is incredibly useful.

@excelenthusiasts

7 жыл бұрын

You are welcome.

Thank you so much Ashish.. It's very useful..

@excelenthusiasts

7 жыл бұрын

You are welcome. Thank you for the feedback.

Thanks Ashish its really helpful :)

@excelenthusiasts

8 жыл бұрын

+Vipin Singh Thank you.

Thanks for the great Video. This is exactly what I was looking for today..you saved my day. I would like to understand whats the purpose of creating Dummys as well as table-names? and how are they related?

@excelenthusiasts

7 жыл бұрын

You are welcome. When you create a Table, excel assign it a name automatically. Converting the range to a Table is necessary because this takes care of auto expansion. Table names cannot be used in MS Query and that is why we assigned a name called Dummy. Hope this clarifies.

Thank you for posting, very helpful. Quick question: could this technique be used to combine several pivot tables?

@excelenthusiasts

4 жыл бұрын

You are welcome. No.

Excellent video and to the point ! 👌

@excelenthusiasts

7 жыл бұрын

Thank you.

Thank you very much Ashish !!

@excelenthusiasts

8 жыл бұрын

You are welcome.

awesome voice and well done explanation

@excelenthusiasts

8 жыл бұрын

+Natesh Iyer Thank you for your kind words.

Thanks a bunch! Just what I needed :)

@excelenthusiasts

7 жыл бұрын

You are welcome.

Thank you very much. This is a superb video

@excelenthusiasts

7 жыл бұрын

You are welcome.

mate you are an absolute hero!

@excelenthusiasts

9 жыл бұрын

Tom Roberts Thank you for your kind words.

Great!!! It helps me a lot.

@excelenthusiasts

8 жыл бұрын

+Yee Sheen Chua You are welcome.

Thanks very much Ashish, great video! It's a bit tricky way, but I couldn't really find an easier one. Is there a way to put it all as VBA?

@excelenthusiasts

10 жыл бұрын

You are welcome. Yes, one can write VBA code to append data from multiple worksheets into one and then create a Pivot Table.

Thank you for sharing this information I used this information and created a pivot table using multiple sheet, but I was not able to use the time line feature of pivot table. May you please help me. How can I use time line feature , if I will create pivot table using this multiple sheet .

@excelenthusiasts

6 жыл бұрын

I do not know. May be the timeline feature is not available on Pivot Tables created via this method.

Thanks a ton! It works.

@excelenthusiasts

3 жыл бұрын

You are welcome.

Great!! you helped me a lot!! thanks

@excelenthusiasts

4 жыл бұрын

You are welcome.

thanks so much ...you make my project complete...

@excelenthusiasts

8 жыл бұрын

You are welcome.

Very helpful! Thank you for uploading. May I ask what was the point of naming the ranges prior to creating the tables? Could you just rename the tables from Table1, Table2, and Table3 instead and pull from that? Or does Microsoft Query not pick up on Table names?

@excelenthusiasts

9 жыл бұрын

Nathan Ondricek Thank you. Neither can one rename the default Table Names nor can one use them in MS Query.

Nice information. Just a thought. Can't we change SQL statement via testing IsNull function to replace null values with zero. That will eliminate problem of adding superficial 0 we added in the spreadsheet.

@excelenthusiasts

7 жыл бұрын

Thank you. I do not know how to use the SQL programming language. Someone who does can try to use the IsNull.

Thank you very much. learned a lot from your video.Thank Internet & youtube

@excelenthusiasts

8 жыл бұрын

+Sugeeth Bimsara You are welcome.

This is amazing and easy, however if I have two tables in same sheet and in multiple worksheets, how will I import data??? Appreciate you reply. Thank you!

@excelenthusiasts

8 жыл бұрын

+Abu Rayan Thank you. The tables could be anywhere on the workbook. Al Tables could be on the same worksheet or in different worksheets. As you can see in the video, the tables are being recognized by their assigned names (not by their worksheet location).

Very informative video, do you know any way to implement the same technique in smartsheet ?

@excelenthusiasts

5 жыл бұрын

Thank you. No, i do not know how to do it in Smartsheet

GOOD IT HELPED ME A LOT THANKS...

@excelenthusiasts

8 жыл бұрын

+Sagar nara You are welcome.

Thanks sir good job It was too helpful

@excelenthusiasts

5 жыл бұрын

Thank you.

thank you for your immediate response

@excelenthusiasts

5 жыл бұрын

You are welcome.

one more thing any tutorial you recommend for power pivot (basics to advances)

@excelenthusiasts

8 жыл бұрын

+Vikas Goel I do not know of any such tutorial. I have answered a fair number of questions on using the PowerPivot tool in the Knowledge base section of my website - www.ashishmathur.com/corporate-interventions/

super information thanks a lot I really loved it.

@excelenthusiasts

7 жыл бұрын

You are welcome. Thank you for the feedback.

Thanks for sharing the video. I have around 10 sheets.. and there is an error saying microsoft query has stopped working when adding the table. Please note, i have used system table. Please let me know if do the table mapping as mentioned in the video, will error go off?

@excelenthusiasts

7 жыл бұрын

Hi. Please read the steps here - www.ashishmathur.com/create-a-pivot-table-from-multiple-worksheets-in-the-same-workbook/

Great video! I had success in creating the pivot table but had issues when I renamed the file. Is there a way to remove old queries and create new ones within the same file?

@excelenthusiasts

8 жыл бұрын

+Bridget F Hi. You may refer to my solution at this link (www.ashishmathur.com/create-a-pivot-table-from-multiple-worksheets-in-the-same-workbook/). Go to the section at the bottom (Update Table/Pivot Table when files are mailed to someone else) and apply the steps mentioned there.

Hi, Thanks for the video , I got the error after Microsoft query "THIS data Source contains no Visible tables" as not able to mapped multiple worksheet, can u suggest

@excelenthusiasts

5 жыл бұрын

Hi. Please follow the steps mentioned here - www.ashishmathur.com/create-a-pivot-table-from-multiple-worksheets-in-the-same-workbook/.

hello Ashish, I keep receiving "cannot add" my second page any time i used the sql statement. any idea?

@excelenthusiasts

7 жыл бұрын

Please follow the steps outlined in this Blog article - www.ashishmathur.com/create-a-pivot-table-from-multiple-worksheets-in-the-same-workbook/

Hi Ashish, thanks for your video. If I have millions rows data in two sheets, can I do this pivot. Is there any restriction in rows criteria in sql query part while append

@excelenthusiasts

4 жыл бұрын

Hi. If your data is that much, then it will be much better to use Power Query and PowerPivot.

@vvvenkatbe

4 жыл бұрын

@@excelenthusiasts thank you for your reply, can I have any tutorial about power query or power pivot for the table having same column names

@excelenthusiasts

4 жыл бұрын

@@vvvenkatbe Hi. See this video - kzread.info/dash/bejne/q4Blk9egg8vKos4.html

Awsm...

@excelenthusiasts

5 жыл бұрын

Thank you.

this is like an alternative to using power pivot for appending data, nice!

@excelenthusiasts

4 жыл бұрын

Thank you.

Thank you for this helpful tutorial. Can a pivot table be created from multiple sheets each with different column names, the majority with dates and some with phone numbers?

@excelenthusiasts

7 жыл бұрын

You are welcome. No. The column names should be the same.

@zvox70

7 жыл бұрын

Ok, thank you.

@excelenthusiasts

7 жыл бұрын

You are welcome.

Thank you for sharing this great work. I have 3 worksheets and I wanted to create a pivot tab which consolidate the data from the 3 worksheets. I followed all the steps as described in your video, and everthing went well. except at the end when I created the pivot tab. In fact it won't sum the values; instead it's giving me zero values...despite having numbers in all worksheets. Would appreciate if you can help me out with this one. THanks

@excelenthusiasts

7 жыл бұрын

You are welcome. Thank you for your kind words. Ensure that the first cell of every column which you have dragged to the value area section of the Pivot Table has a number in each source data worksheet. Then click on Refresh. If this still does not work, then share the link from where I can download your workbook.

@user-fi6sb6ey1c

7 жыл бұрын

Hi, Thank you for your reply. All my data source sheets have numbers, but still not working. In the same time I have noticed something strange, actually when I double click on the pivot tab total for the data to get the details, in the column that has the numbers...the format of the numbers is coming as Standard/ General format and even if I change it to numbers it's not being actually converted, and when I select multiple cells excel is just counting the number of items and doesn't sum the values. So I guess there is a formatting issue here. but I'm sure my numbers in the data source are stored as numbers. How can I share my workbook privately with you? Thanks

@user-fi6sb6ey1c

7 жыл бұрын

I just realized one more thing after I double clicked on the pivot tab total, in the details the numbers of one the worksheets are somehow converted to date and time figures!!! not sure why?

@user-fi6sb6ey1c

7 жыл бұрын

Hi Ashish, never mind, I cleared off all the previous formatting of the entire data source and then reworked it. and repeated the same steps and it's working just fine now. Thank you for your help. Have a great day.

@excelenthusiasts

7 жыл бұрын

Good to hear that the problem is now resolved.

Omg you are my héroe!

@excelenthusiasts

6 жыл бұрын

Thank you for your kind words

Hi Ashish, i have select 2 source tables with blank rows (in future I may add data into that)..now the pivot table which i created using ms query also has blank rows..because of this my pivot charts also has extra blanks..how can I eliminate this

@excelenthusiasts

7 жыл бұрын

Hi. Do not have blank cells in your data source initially. Since you have converted the source dataset into a Table, the rows will expand automatically as and when you add further rows.

Hi Mr Mathur, thank you for this video. I have similar data as you over 4 worksheets and would like to merge into one. I am stuck at step 1 as I realize I am simply unable to name my range cells as 'dummy'. I'm using excel 2016, is this causing the issue?

@deskoh85

7 жыл бұрын

Sir, I managed to get hold of Excel 2007, and your technique works perfectly in Excel 2007

@excelenthusiasts

7 жыл бұрын

Hi. You are welcome. Please refer to the steps outlined here - www.ashishmathur.com/create-a-pivot-table-from-multiple-worksheets-in-the-same-workbook/

@excelenthusiasts

7 жыл бұрын

Good to know that. Thank you.

Hi to all. How can we add an extra table lets say dummy3 in this example? I mean an extra table after we have created the query with the 3 files

@excelenthusiasts

6 жыл бұрын

Hi. Read the contents of the section "Update Table/Pivot Table for addition of new worksheets" at this link - www.ashishmathur.com/create-a-pivot-table-from-multiple-worksheets-in-the-same-workbook/

Hi .. I like to make another pivot table in the same sheet with my first pivot table but with different source data.. can you help me? I cant get it through..

@excelenthusiasts

6 жыл бұрын

Hi. Not clear about your query. Please share more details.

Hi Ashish, Just a query, under pivots i can group a field to Months and week as well (days and set the index to 7) but if i require to group the data based on weeknum,how could i do it (like Wk 41). Using days in group will show the date range as 1/1/1990 to 8/1/1990 but i want to show Weeknumber. Need to do that in Pivot only else i could have done adding 1 extra column in data and putting there the weennum formula but if i have the huge data spanning across differnt sheets, adding a column is big pain and time consuming as well. Any suggestions. Please mail me if question is not clear

@excelenthusiasts

8 жыл бұрын

+Vikas Goel Hi. You will have to create a separate column to extract the week number in your base data sheet and then drag that column to your Pivot Table.

Hi Anshish, Thanks for this video. It will really be great if this works for my data. I followed all the steps till the SQL statements. I get the message that "The number of columns in the two selected tables or queries of a union query do not match". I have 91 columns and between 200 and 5000 rows in each table (5 of them) and they are all created from the same shell. I have even copied and re-pasted the column names from the first table to all the others and manually checked them but I still get the same error. Are my columns too much or what could be the problem?

@excelenthusiasts

6 жыл бұрын

You are welcome. I cannot say. If you are absolutely sure that the number of columns in both datasets is the same, then it looks like there is a "number of columns" limitation. The best approach would be to use Data > Get & Transform to aggregate data from multiple worksheets into a single one.

Hi thanks for sharing the video Just One question How to make weekly report in Pivot table? Thanks

@excelenthusiasts

8 жыл бұрын

Please elaborate.

@gopalDAS-il5tx

7 жыл бұрын

Syed Ahmed v

Hi Ashish, It was very useful. Thank you so much. Also I am working on more than 1 lakh records. Could you please help me to get any video for that ?

@excelenthusiasts

6 жыл бұрын

You are welcome. This technique should work for 1 lakh records as well.

@crajamath

6 жыл бұрын

Hi Ashish, Thanks for the reply. It is not working for me more than 65K in single sheet. wanted to create a table for more than 1 lakh records.

@excelenthusiasts

6 жыл бұрын

Hi. This technique will work only for upto 65,000 rows of data per worksheet. You may split the rows into 2 worksheets and then it will work fine. An even better option is to use the BI tools of Excel, namely PowerPivot and Data > Get & Transform.

hi Ashish, can we do the same in Mac Excel 2016?

@excelenthusiasts

8 жыл бұрын

+Shiv Raman Hi, I have not tried this on a Mac.

Hi, Thanks for uploading this video, but i have one query when i tried to so many around 100 of column it doesn't work, stated "to many query's", can you pls. help with some other technique. OR i have three files with same header and i want to in one pivot.

@excelenthusiasts

4 жыл бұрын

Hi. I suggest you use Power Query and PowerPivot to build a Pivot Table from such a large dataset.

Hi Ashish, Great Content! I tried to create my pivot table using more than 5 spreadsheets, but after I clicked "ok" in the final step of returning data to excel, it said "ODBC error" then "problems obtaining data" , any idea what's going on? Is there any limit of the number of spreadsheet we can use at one time?

@excelenthusiasts

7 жыл бұрын

Thank you. No, as far as I now there are no limits on the number of worksheets. Read the steps here and retry - www.ashishmathur.com/create-a-pivot-table-from-multiple-worksheets-in-the-same-workbook/

Ashish when I choose my excel so that I can extract value for creating pivot table . I get an error stating unrecognized database format followed by the path where my files is save. Could you please let me know why do I see this error?

@excelenthusiasts

8 жыл бұрын

I am not sure why that error is popping up. Please ensure that you first assign a name to the range and then convert it to a Table.

is there a way to do this using VBA?

@excelenthusiasts

6 жыл бұрын

Should be. I do not provide VBA support

Wow.. I can make a Pivot Table out of data now. Thank you for making this video. Initially, I had faced problem creating PivotTable because one of my worksheet name is "DB2", which is a cell number. First, table name was not appearing on available tables. So, I had to select "System Table" from Query Wizard option and picked "DB2$" as table name. And, during union I had to double quote the table name else it gives syntax error.

@excelenthusiasts

7 жыл бұрын

You are welcome.

Ashish I have assigned names to range, converted them to table , opened a new sheet saved everything , selected From other source and choose from Microsoft query, choose excel files , choose the xls I have saved post this I get an error stating unrecognised database format followed by the path where I have saved my excel. please suggest

@excelenthusiasts

8 жыл бұрын

Hi,I do not know why that is happening. From similar comments posted by other users in the Comments of KZread and my webpage, I understand that this problem happens when one "first converts to a table and then assigns a name". It should actually be to "first assign a name and then convert to a table". I cannot think of any other reason why this is happening.

Thank you, thank you, thank you.

@excelenthusiasts

9 жыл бұрын

John Fite You are welcome.

@eliegoldberg1170

9 жыл бұрын

+Ashish Mathur Ashish. Your video Create a Pivot Table from multiple worksheets of a workbook was great. One question, if I add another worksheet, and create new tables, how do I add that to the MS Query and then to the pivot table output. Thanjks

@excelenthusiasts

9 жыл бұрын

+Shick Yingel You are welcome. Please refer to the method (www.ashishmathur.com/create-a-pivot-table-from-multiple-worksheets-in-the-same-workbook/) outlined under this heading "Update Table/Pivot Table for addition of new worksheets"

thank u..how to combine from multiple workbooks ?

@excelenthusiasts

7 жыл бұрын

Hi. See my solution here - www.ashishmathur.com/create-a-pivot-table-from-multiple-worksheets-in-different-workbooks/

what if we want to link a particular cell range(IN VERTICAL) of 150+ sheets in to one master sheet HORIZONTALLY??? EXAMPLE APRIL 2016 TO MARCH 2017 WORKING DATA OF 150 EMPLOYS IS AVAILABLE IN THEIR RESPECTIVE 150 SHEETS VERTICALLY AND WE WANT TO EXTRACT THEM TO ONE MASTER SHEET IN APR TO MAR FORMAT BUT HORIZONTALLY ????

Super! Does it work for Excel 2010?

@excelenthusiasts

8 жыл бұрын

+Saul Espinoza Yes, it does.

@Soulenergy31

8 жыл бұрын

+Ashish Mathur, I will give it a try Sen Sei! And I'll let u know my outcome!

@Soulenergy31

8 жыл бұрын

It worked thx, is there a way that after performing this trick the shareworkbook option might be enable? I need two users to input their data into the first two worksheets so I can consolidte the two sheets witht he PT, but Excel is asking me "this workbook cannot be shared becaus it contains Excel Tables or XL Maps... covert tables to ranges and remove XML maps..... If i do it i migh screw the Excel trick... Any suggestion?

@excelenthusiasts

8 жыл бұрын

+Saul Espinoza Hi. If a workbook has even one range converted to a Table, then it cannot be shared. To enable sharing, you will have to remove the Table functionality but if you do so, you will lose the functionality of "Pivot Table incorporating new rows of data that will be added to the Source dataset". So if you do not want that functionality, then you need not convert into a Table at all.

Hi Ashish, by far the best video on this topic - hands down!!! May I bounce off a idea/question to you? I have 4 files with the same table structure of data. I (1.) created an named range and then (2.) a table of the name range. Using your method, I "queried" the 4 files/4 tables on 4 sheets in a new workbook in order to combine the 4 sheets on one (new) sheet. I now have the issue that I have 4 tables (2.) created before I created 4 named ranges (1.). (On creating the MS Query link the resulting table is MS table name Table_Query_from_Excel_Files) When I now combine the 4 tables into 1, MS Query cannot find tables in the this workbook. Is there something to the order of executing Step 1. and 2. in this order? I went and took the 4 system tables pointing at each sheet (paying attention to the '$' at the end of each system named element) Could I have gone straight from 4 files to one table w/o the intermittent step of 4 tables in one workbook? Thanks a lot in advance. Cheers Dirk

@excelenthusiasts

7 жыл бұрын

Thank you. Please follow the steps outlined in the Blog article here - www.ashishmathur.com/create-a-pivot-table-from-multiple-worksheets-in-the-same-workbook/

Thanks you for shareing

@excelenthusiasts

6 жыл бұрын

You are welcome.

Excellent

@excelenthusiasts

5 жыл бұрын

Thank you.

This is an amazing tutorial. Thanks I am having a little problem. I am not being able to add more than 6 sheet. if i do so then I cannot the query sheet shows 6 box for the 6 sheet(for me they are months). but it doesnt show any data from the sheet. Please advise.

@excelenthusiasts

8 жыл бұрын

+Arif Uddin Thank you. I need to see your workbook. Upload your workbook to OneDrive and share the download link here.

@arifuddin2942

8 жыл бұрын

+Ashish Mathur onedrive.live.com/redir?resid=B3DC16B8C34242B6!179&authkey=!AF0vLBObejdSTtY&ithint=file%2cxlsx also I tried this... i have added all the tables for 12 months (without $ sign table). clicked next but it doesnt go to the next level. I click cancelled. but it shows me the boxes of 12 month with all the field name and the * at the top. i clicked the SQL icon and applied the union all formula with the 12 tables name. double clicked all the * from all 12 table. it populated the data in the query. and then click file and return to table. and then I got he following message [Microsoft][ODBC Excel driver] Internal OLE Automation error. please advise. Kind regards, Arif

@excelenthusiasts

8 жыл бұрын

+Arif Uddin I face the same problem when adding all 12 tables - it does not go to the next level. I do not know why this is happening. I try later and let you know if I come up with a valid reason.

@djnishu

8 жыл бұрын

+Ashish Mathur thanks. I will wait for your reply. Appreciate a lot for your reply. Regards Arif

@arifuddin2942

8 жыл бұрын

Hi Ashish, Again I would like to say thank for teach me something amazing. Do you think there could be any solution for this? Please advise. Regards Arif

I am needing to write the formula in my reconciliation tab from the July tab. I keep getting an error. I have my monthly bills on each tab and then need the balances for the reconciliation tab. Do I need to find another tutorial for this?

@excelenthusiasts

7 жыл бұрын

Your question does not seem to be related to my video at all. Post your question in some MS Excel forum.

Hey Ashish! This is a great post. I am receiving an error message as another person 3 months ago (Jian Meng). I tried to refresh the data and get an ODBC Error. It could not find local object. I followed the instructions to retry the steps and get the same error message. Any help would be greatly appreciated.

@excelenthusiasts

6 жыл бұрын

Hi. Have you followed the steps mentioned here - www.ashishmathur.com/create-a-pivot-table-from-multiple-worksheets-in-the-same-workbook/

Very good

@excelenthusiasts

4 жыл бұрын

Thank you.

May I know if there is table row limitation for MS query? I opened From MS query but don't see the table (76490 rows in the excel file) that I've created?

@excelenthusiasts

7 жыл бұрын

Hi. I think the number of rows per Table in MS Query is 65,000. Break that table into 2 tables and see if it works. A much better technique would be to use Power Query and PowerPivot.

Super sir

@excelenthusiasts

6 жыл бұрын

Thank you.

and i have a one query, suppose there are more than 20 sheets in the workbook, then do we need to follow the union all process (copy n pasting "select * from abc") for 20 times? or do you have any simplified query that can be union all the sheet? please assist.

@excelenthusiasts

8 жыл бұрын

Yes, if there are 20 named ranges, then there will be 19 Union all statements.

@Arifkhan-oz1wb

8 жыл бұрын

+Ashish Mathur - Thank you for the reply. Dont we have any short query like union all sheets?

@excelenthusiasts

8 жыл бұрын

Hi. Even if there is, I do not know that technique.

Hi Ashish, I get error "no visible tables", however while i do CTRL F3 i saw list of 3 ranges and tables as well

@vgoel78

8 жыл бұрын

+Vikas Goel got the solution now, my mistake..i was ctrl shift arrow keys to select the data , it should have been Ctrl A.Nice tutorial ..thanks

@excelenthusiasts

8 жыл бұрын

+Vikas Goel You are welcome.

Is there a limit on the amount of data sheets that you can connect? Thank you.

@excelenthusiasts

7 жыл бұрын

No. I do not think so.

@stephanydiaz7400

7 жыл бұрын

Ashish Mathur Thank you for your feedback.

Hello Sir, when i change the values from count to Sum im getting zero as result, there is no blank cells in the value columns please suggest

@excelenthusiasts

7 жыл бұрын

Hi. I cannot say why that is happening unless I see the workbook. Upload your workbook to OneDrive and share the download link here.

Can you easily add additional worksheets to the existing query?

@excelenthusiasts

5 жыл бұрын

Hi. Follow the steps outlined in my Blog article - www.ashishmathur.com/create-a-pivot-table-from-multiple-worksheets-in-the-same-workbook/

Does the pivot table update if the monthly sales data changes?

@excelenthusiasts

5 жыл бұрын

Yes.

@letsgetit2939

5 жыл бұрын

@@excelenthusiasts thank you for the quick response

@excelenthusiasts

5 жыл бұрын

@@letsgetit2939 You are welcome.

What can I do if the number of columns does not match between my data sources? Thanks

@excelenthusiasts

7 жыл бұрын

Hi, You will have to ensure that. If the number of columns are not the same, then Power Query can help.

Does this require you to have microsoft sql server installed on your system?

@excelenthusiasts

7 жыл бұрын

No. You just need MS Excel.

@aamirarfin

7 жыл бұрын

Thank you Sir. Another query that I had was, in this case, you can filter data "worksheet wise"(that is jan, feb, march) in pivot chart because you have a date column. If suppose you had to create the same table but instead of the worksheets split month wise, It would be lets say store wise( for eg - Store A, Store B, Store C). How can you filter a pivot table or pivot chart then? The only thing I can think of is to have a separate column in all the worksheets with the name of the store repeated in each row. Any alternative?

@excelenthusiasts

7 жыл бұрын

You are most welcome. The column by which you want to filter should be in the base data. If it isn't and you want the worksheet name to be the slicer/filter, then it will be better to use Power Query/PowerPivot.

I get "Unrecognized Database" opening file into query. Could I have a setting off?

@excelenthusiasts

8 жыл бұрын

+Devang Desai Hi. Please refer to the steps mentioned at this link - www.ashishmathur.com/create-a-pivot-table-from-multiple-worksheets-in-the-same-workbook/

I am running with an issue, I have two work sheets and I created tables for both separately but when I go to query wizard there it shows table of second sheet only. Can you please suggest why this is happening?

@excelenthusiasts

3 жыл бұрын

I suggest that you solve this problem using Power Query and PowerPivot.

Sir after SQL statement I am getting an error of (syntax error FROM clause). Kindly help me on this. Thanks in advance.

@excelenthusiasts

6 жыл бұрын

Please ensure that you follow all steps shown here - www.ashishmathur.com/create-a-pivot-table-from-multiple-worksheets-in-the-same-workbook/

Thank for the great video on pivot table, however sum functionality is not working, upon selecting sum its just showing 0 but number are showing the accurate number.

@excelenthusiasts

7 жыл бұрын

Hi. Refer to the steps at this link - www.ashishmathur.com/create-a-pivot-table-from-multiple-worksheets-in-the-same-workbook/

can we do the same if top row(labels) in one worksheet doesn't match with the other worksheets. 2. if voucher # is in 3rd column of the worksheet 1 and Voucher # is in 10th column of worksheet 2.is this still applicable

@excelenthusiasts

8 жыл бұрын

+saima qaiser No. There has to be structural similarity across all worksheets.

If the Data sheets combined exceed the >1M+ row limit in Excel, will this still work?

@excelenthusiasts

3 жыл бұрын

No, it wont. In that situation use the Power Query/Power Pivot.

@bobnelson2800

3 жыл бұрын

@@excelenthusiasts thank you!

@excelenthusiasts

3 жыл бұрын

@@bobnelson2800 You are welcome.

Hi Ashish, I'm trying to use your steps to create a pivot table from my sales teams' pipeline spreadsheet in which each agent has his own tab with the same column names and formats. I've followed your steps but I get the "syntax error in query. Incomplete query clause". My SQL statements says: Select * from Gary union all and then the same for the rest of my team. If I add ' before and after the name of the agent I get the following: "Could not add the table "Gary". I've stored a copy on my desktop, my tables all have the right range and names and now I'm completely stuck. Please help me out if you can.

@excelenthusiasts

9 жыл бұрын

Jonne Ziengs Hi. Upload your workbook to OneDrive and share the link of the workbook here.

@excelenthusiasts

9 жыл бұрын

Jonne Ziengs Hi, it works absolutely fine for me. Here is my query Select * from Gary union all Select * from Gordon union all Select * from Ian union all Select * from Kathryn union all Select * from Kevin union all Select * from Roxy union all Select * from Shaun union all Select * from Vere

@jonneziengs

9 жыл бұрын

Ashish Mathur Hi, fantastic!!! it now works perfectly! I had spaces between the lines so I'm guessing that's what it was. Thank you so much!

@excelenthusiasts

9 жыл бұрын

Jonne Ziengs You are welcome.

Hi, great with 6 sheets. Not working with 7. ([Microsoft][Ovladač ODBC pro Excel] Vnitřní chyba automatizace OLE. in Czech, something like [Microsoft][Controle ODBC for Excel] Inner error of OLE automatization.) Is there a limit for number of sheets? Thanks a lot. Zdenek

@excelenthusiasts

9 жыл бұрын

Hi. I have tried it for upto 5 worksheets and it works fine. I would not be surprised if there is some internal limit in MS Query.

Thanks for the Video sir... It's working for me with less data, but not working on huge data.......... any reason........... Its not showing the data name i mean the one you named dummy.... Plz help me...

@excelenthusiasts

8 жыл бұрын

+Md Zahed Ali Hi. I need to see your workbook. Upload it to OneDrive and share the download link here.