Create an Excel PivotTable Based on Multiple Worksheets

[@lynnmcfadden655]

that click click just saved hours of my life!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[@montanadamron3277]

I'm doing something similar with 800+ records across 25 Worksheets. You saved me a lot of headache! Thanks for the video!

[@sohel_chhipa]

Thank You so much for sharing this awesome trick. Now I am able to create piot table for multiple sheet.

[@Cool_Demeanor]

Thanks for making this video. It helped me a lot. Thank you so much.

[@OfficeNewb]

Glad it helped!

[@jakedunakey3741]

Honestly this was the easiest tutorial on building a pivot table from multiple sheets. Very easy to build your own pivot table and sorts the data correctly to easily produce a graph.

[@SagarK77]

Wonderful helping tricks. Thanks 👍

[@OfficeNewb]

Happy to help!

[@786valerie]

Thank you! This is the best tutorial I’ve found for what I needed to do.

[@pavanvuppala6858]

Thank u very much from bottom of my heart because it's really helped me a lot and I think I can ask them to raise my salary now

[@dhanlimbu9728]

very easily explained. thank u so much

[@DineshTenali-JSP]

Thank you ❤ you made my day and Subscribed as a grattitude🤝

[@OfficeNewb]

Awesome, thank you!

[@wooohhoo]

Thanks !!! How to put more data in Row filter, to look it in tabular form. Same as we look in non consolidated pivot chart.

[@mauraortiz4675]

Thank you very much. I am using this for accounting reports. Thank you a thousand times

[@billypuntove]

Amazing!! Thanks for this.

[@medzakaria2820]

you are a life saver

[@OfficeNewb]

Thank you

[@Up4Excel]

Excellent tip on how to access that old pivot table wizard. Anyone know what other forgotten Excel features might still be accessible like that?

[@CRinderknecht]

I was getting so frustrated with other explanations because they didn't answer the question of more than two tables, particularly for Office 365. THANK YOU!

[@KenBraverman]

very nice - cool taking advantage of old pivot table wizard

[@mightysun89]

Thank you. I was doing like copy, paste...

[@nachusambasivam]

This is a very helpful and simple way to explain. Thanks. How would i use it to move rows and columns like in a regular pivot as in consolidated it does not allow to choose our own

[@Said_Soliman]

Perfect, it was very useful for me, many thanks

[@SwapperTheFirst]

Hi Kyle! Good usage of Pivot, thanks! But maybe this is case a better "candidate" for PowerPivot addon of Excel? It would be interesting to see if PowerPivot can understand the primary key (Region). Also it could be a good example for Power BI desktop if PowerPivot will have troubles with this very simple data model.

[@emmanueluy7603]

How to retain column headers?

[@umeshmishra2010]

GREAT JOB SIR

[@thenewdataguy]

Thanks for this well explained video!

[@ashishh1323]

Can the column data & column headers be different across different tabs? Will that work when trying this pivot table?

[@OfficeNewb]

Each range of data should be arranged in cross-tab format, with matching row and column names for items that you want to summarize together. Do not include any total rows or total columns from the source data when you specify the data for the report. The following example shows four ranges in cross-tab format.

[@tripuramalluyashwanth8701]

Superrrrrrrr Usefulllllllllll

[@dreamworldliving5061]

Hi friend.. have any option for adding new sheet. Pls explain

[@prashant00744]

NICE 1 SIR

[@OfficeNewb]

Thank you!

[@RaviKumar-rm5re]

Great Brother you go really deep.,...exactly how to play with multiple field list without using power query..because what i thought before launching 2010 people use to get all there work done in excel 2007..how do they do that..

[@krisko963]

Tqqqqqq very much .... awesome!

[@mayankspgupta]

I have a data with standard columns in 4 workbooks. I have applied multiple pivots in one file and want to copy/move all those pivots in other workbooks as well. I tried the same, but when I copied the pivot to other workbook, then source data is reflecting of the initial work book. is there any means by which the copied pivot gets the source range from the new/current workbook ( I select complete sheet as source data).

[@virginia1652]

This is beautiful thank you!!!

[@erlindaeloriaga4607]

Using this pivot table, when I do some corrections on any of data from the source worksheet, would it show the changes on the privot table? Also, if I add more rows to any of the source worksheet, would it also add to the pivot table as well?

[@jkldsldif]

How can you retain column headers?

[@arindajordan179]

Did you ever get an answer? It's a bit frustrating trying to switch columns and rows

[@imranashraf2530]

but i want in column label 3 column of sum and 1 of count . how can i change from value field settings because if i change to sum all column calculate sum if i select count then all calculate count but i want manually on each column ? please help

[@alrafi7176]

Nice💖

[@chrisoli7751]

I'm curious. Can I use a master sheet with data and link that data to go into multiple sheets? I run a 24 team baseball league and use excel for all data. Each team has a tab for it's players and salary. I wanted to use a master sheet with all the players and the players salary and link that to all the 24 team tabs. So if a trade occurs I could just switch the teams in the master sheet and it will then automatically swap those players in the individual team tabs

[@pirga19]

But what if i enter new data into table the pivot doesn't update when i press refresh. I need to select ranges as TableName/Column name not as values in certain range of cells. How to do that?

[@abanash4149]

hi there, i would like to do the same thing but when i want to summarize the vakue by min it turns out zero...is there any way to show the nearest smallest value instead of zero

[@muhammedasifkhurshid1156]

Thanks and regard. Very clear.

[@queenbean5573]

Thank you! What a life saver!

[@galoshel]

this pivot table works only for 4 sheets? I have 7 sheets... how to collect all data?

[@raytorro1098]

Very clear tutorial! Thank you

[@lisamarie6506]

Very interesting '. I'm learning new things

[@gintomino4136]

THANKS A LOT!!!!!

[@Tileshopslahore]

If u have 100 sheets then ?

[@Amazzinguy]

Does this work on MAC ...Excel

[@mauroe7631]

Sorry but in my Excel 2016 I cannot have that Wizard as result of Alt, D, P , can you help me please?thank you

[@mohammedmanhal9660]

How can I consolidate if the work sheets dont look same ..

[@JunGuwapo773]

This is what I have been trying to do! Thank you so much!

[@nemanjailic8950]

Great videos! Is there a way to find/search sequential numbers in one column?

[@EDonovan3055]

Thank you so much for your video. Can you clarify one thing? I am trying to combine multiple tabs that each have varied amount or rows, that will fluctuate each time it is being used. I am trying to have the individual tabs as a data dumb for someone else and need one tab to show all the data. However, both your and several other video show one label and different months values. I am trying to do tabs with checks that have been issued from different companies. Each tab showing CK#, CK Date, CK Amt, and Payee Name. I just need a master file without having to copy and paste to create it but cant seem to get that to happen in Excel. Can you advise?

[@SamSung-rh6hx]

what if instead of having three columns for Jan, Feb, and Mar, you had ONE column for Month and in that column you had Jan, Feb, Mar? How would you create your Pivot?

[@dhanishmehta]

Thats the easiest way you taught us...........my query is how do i take sales from one sheet and collection from other sheet ? as your explanation shows the data / columns / format is same.

[@deepakvats7636]

can you please explain your query in detail.like column headers and data to help you in this.

[@handylanudry2892]

how can i use this method to add an additional worksheet of data to an existing pivot table?

[@bernhardsitanggang2733]

Awesome thanks man

[@bassamal-kaaki3253]

How can you use power pivot on Mac? I don’t seem to be able to add the power pivot add-in or the date dimension. Any way I can be able to work on my Mac with power pivot?

[@Devanshbairagi_6727]

Thankyou🙏

[@niekjanvandenhout8457]

Is this possible on Mac? Seems that version doesn't have this specific feature.

[@andyd170]

Thank you!

[@Abhijeet7ful]

Thankyou . . . Sir 😄

[@hariprasad289]

Very nice, could you please share these workbooks

[@baboondocks]

What if I can´t get the wizard to open? Pressing alt+D works. But when typing P nothing happens... (Excel 2016)

[@RenanZanesco]

Same Here

[@TheManInBlack11]

What you can do is right-click in the ribbon, select Customize the Ribbon, in the left-hand column of the Excel Options window, choose All Commands from “Choose commands from” drop down menu. In the list below, look for “PivotTable and PivotChart Wizard.” On the right-hand side where all the tabs are displayed, click an existing tab or create a new tab and click the “New Group” button below. Name it whatever you want. With that group selected and the “PivotTable and PivotChart Wizard” command selected, hit the “Add>>” button between the lists. Click “OK” and now you have the Wizard added as a button to the ribbon.

[@jonasberg5587]

You don't get the same box as OfficeNewb, so it still isn't possible to choose several sheets unfortunately.

[@dstanleyrd]

There is no alt key on an iMac!

[@kjchua4853]

Thank you

[@yousraadly7341]

Thank you .

[@stephanydiaz7400]

Hi, thank you so much for sharing this. I have a question, is there a limit on the number of spreadsheets that you we consolidate? Thank you.

[@Tips_Malayalam]

Very helpful

[@DmitrysGarage]

Just a heads up, but Microsoft's shortcut keys aren't uniform across platforms. So this tutorial won't work on Mac for example, because Alt-D is not a valid shortcut.

[@malvane8061]

Why not use Consolidate of the 4 regions onto a new worksheet, then Pivot the Consolidated data?

[@vikaasb2016]

Super

[@Mansijswarnkar]

How to do it in Mac, thanks

[@ruroshinzynaruto]

Awesome :)

[@DerickPitcher]

This doesn't work for Excel Office 365. I tried adding "PivotTable and PivotChart Wizard" to the quick access toolbar and ribbon and still nothing. I'm guessing they want you to use Power Query instead. My workbook will be used on different versions of Excel, Excel 2007 onward, and I want to use this method for 12 worksheets. I currently have one master worksheet where any data entered on any one of the 12 worksheets is automatically copied to the master, using VBA, for my pivot table to work. The search is on to rectify this issue. If/when I find I'll update this comment.

[@OfficeNewb]

Hello Derick - Weird... I am currently using the latest release of Excel, through Office 365, and the command is still working. Are you working with the Desktop version of Excel?

[@DerickPitcher]

@@OfficeNewb Yes, I'm using the latest desktop version of Excel 365. Weird. I found another way to do this but each table scope has to be set to workbook and not worksheet. When I create a pivot table for one of the twelve sheets then switch from "Active" to "All" in the pivot table field configuration window, I see all tables scoped to workbook. My issue is that I cannot have the 12 of them scoped to workbook since they're 12 of the same, a copy, January through December with all the same table name. If you'd like I can send you a screen recording.

[@automationguide3498]

Thank You !!

[@kimhao98]

It says "Reference isn't valid". Can anyone please help?

[@deepakvats7636]

can you please put your query in detail.

[@amarnadhgunakala2901]

Bravo

[@dagwould]

you don't need to say 'off of that...'; just 'off that...' does the trick grammatically.

[@robbieyoung690]

Just get to the point.

[@LOSTYKmail]

You my man .. I Wana kiss you

[@abhishek758]

When we are deleting lets say one worksheet, then why it is not reflecting in pivot sheet even after refreshing. It still hold name and value of that worksheet which is deleted and refreshed in pivot table. Any solution for that ? pls

[@NicholasNicolaides]

Thank you!!