Excel PivotTables Made Easy - And Why Things Go Wrong!

[@houstonvanhoy2198]

Mynda: Your closing music is so much nicer than some of the music which other Excel gurus - who will remain unnamed here - use in their videos. Thank you.

[@MyOnlineTrainingHub]

Thanks for your kind words, Houston 😊

[@melissalee5064]

This is the FIRST time I have watched an excel pivot table training sesh that I could truly follow and understand clearly. I super appreciate your video and sharing your training for everyone! Finally, I am NOT intimidated by pivot tables and the data!! THANK YOU :)

[@MyOnlineTrainingHub]

Wow, that's awesome to hear, Melissa!

[@abednegomulumbi6975]

I used to think PIvot tables are complicated, but now I have seen where the controls are. Thank you once more, great teacher

[@MyOnlineTrainingHub]

So pleased to hear that, Abednego!

[@Ntentes95]

You are the best excel teacher on youtube by far. Simple and clear lessons.

[@MyOnlineTrainingHub]

Wow, thank you, Nick!

[@3DAlphaStrike]

This is the clearest explanation of what the underlying table structure needs to be in and why it needs to be that way. Showing the “partially pivoted” data being reformatted correctly so that power pivot can do its job was exactly what I needed for it to sink in. Thank you!

[@MyOnlineTrainingHub]

So pleased to hear that, Trevor!

[@chrism3782]

Thank you for this, finally, Pivot Tables explained in a simple easy to follow lesson with working examples. Unfortunately, I am still on 2010 but even that didn't hinder the tutorial. Thank goodness for Mynda, I learned so mush from this that every other tutorial missed...

[@MyOnlineTrainingHub]

So pleased I could help, Chris 😊

[@shashanksuthar8489]

Thank you very much, by seeing your videos I feel excel learning is way more easier than I was thinking. Thank a lot.

[@MyOnlineTrainingHub]

Wonderful to hear 😁

[@paddleboardadventures6392]

I usually figure out my pivot table adventures...eventually...through trial-and-error. Now I understand them. Brilliant as always Mynda!

[@MyOnlineTrainingHub]

So pleased to hear that :-)

[@s.y.daniel2137]

Me too

[@davegoodo3603]

Thanks Mynda, this is a great message! It helps me to remember “Keep it Simple”, Pivot Tables can get unwieldy very quickly without having a clear plan of attack as you have graciously shown us. I have learned a lot from your video, thanks as always and keep them coming!

[@MyOnlineTrainingHub]

Thanks for your kind words, Dave!

[@BevRoot]

Thank you for focusing on how to create the original dataset. That's what is missing in so many online videos on Pivot Tables. excellent!

[@MyOnlineTrainingHub]

Glad it was helpful, Bev!

[@VirtualArcades]

Solid and easy to understand instructions without too many assumptions from an instructor pov. Nice work!

[@MyOnlineTrainingHub]

Glad you enjoyed it!

[@carolind6264]

This was very helpful! I really appreciated the “what not to do” section. I’ve tried pivot tables before and they never worked, probably because my data set was incorrect. Very useful video!

[@MyOnlineTrainingHub]

Great to hear!

[@alinaingunza7067]

It starts at 7:39 for those wondering

[@hardikey]

You are genius. Many thanks for helping students like us. Wish you lot's of happiness, prosperity, excellent life and peace of mind.

[@MyOnlineTrainingHub]

Thanks so much! Best wishes to you too 😊

[@gatasucia5973]

Thank you! I love how clear your instructions and explanations are.

[@MyOnlineTrainingHub]

Glad it was helpful!

[@vijayarjunwadkar]

Thanks Mynda! Even after spending good amount of time on most Excel features, there is always something new to learn and your videos help in that! Keep doing the great work! 😊👍

[@MyOnlineTrainingHub]

You're very welcome!

[@GurayVural-nz1lk]

Very good video explaining pivot table basics in such a clear and easy-to-follow way. Thank you!

[@MyOnlineTrainingHub]

Glad you enjoyed it!

[@wayneedmondson1065]

Hi Mynda. As always, an impressive presentation. Easy to follow and full of useful and timely information. Looking forward to more. Thanks for sharing and Thumbs up!!

[@MyOnlineTrainingHub]

Thanks so much, Wayne!

[@Carl0347]

Thank you. I learn so much from your videos and yours is the most easiest to understand than other instructional videos. I really appreciate your ability to teach your expertise effectively. Thank you once again.

[@MyOnlineTrainingHub]

Wow, thanks for your kind words!

[@moiz6330]

Thanks for your awsome support. The best thing is you also provide practice file. Great job

[@MyOnlineTrainingHub]

It's my pleasure, Hafiz!

[@daliladuf]

I absolutely love your tutorials! They bring data to life...

[@MyOnlineTrainingHub]

Happy to hear that, Dalila!

[@prasadmoghe1702]

Greetings Mynda, I follow your Dashboard video tutorials and they are fabulous. Your presentation is so nice and simple that I could do it one go. I am lucky to find a teacher like you. Your efforts for education people like us are appreciated. Thanks for all your hard work. Prasad Moghe

[@MyOnlineTrainingHub]

Thanks for your kind words, Prasad! I'm so pleased you found my videos helpful.

[@TheSkiggly]

"lucky to find a teacher like her" - absolutely agree!

[@MurariRoy]

Hello Mynda, Best video I ever find on pivot table. Thank you!

[@MyOnlineTrainingHub]

Wow, thank you! 🥰

[@zerotrace000]

A beautifully done video on pivot tables. I just came across your channel and love it. Bravo!

[@MyOnlineTrainingHub]

Thank you very much!

[@leorc564]

Aunque uso tablas dinámicas por más de 20 años, es muy divertido ver la exposición de Mynda, datos claros, sencillos para entender fácilmente, gracias por tu video, i share your video, thanks a lot!!

[@MyOnlineTrainingHub]

Thanks so much!

[@martyf.8088]

Thanks for getting back to me. I played around and found out how to change column titles (a report with "Sum of Costs" instead of just "Costs" as a column title didn't look good). I've been using Excel for 25 years (don't ask how old I am (😂)) and never used a pivot table but playing around with them now seems like a great tool in the Excel arsenal. Thanks again. Marty

[@MyOnlineTrainingHub]

Great to hear, Marty!

[@tracyt7654]

Will be starting a new job and have been out of the work force for some time. This was very helpful!

[@MyOnlineTrainingHub]

Great to hear, Tracy! Good luck in your new job 😊

[@TheSkiggly]

Looking back over the past months, where I was vs where I am today - I must say how valuable you are to me and other like me. Your lessons make learning easy and the information is always spot on. What's really amazing is - I'm just scratching the surface of this program.

[@MyOnlineTrainingHub]

Wow, that's wonderful to hear. Congratulations on your hard work, Mitch! I hope you continue to enjoy my videos :-)

[@TheSkiggly]

@@MyOnlineTrainingHub If I'm too busy to watch the content you offer- then I'm too busy. I have learned to stop what I'm doing and soak in the info while it's being so graciously offered.

[@carlymardon4234]

I come across your videos the other day and found them really interesting and detailed but most importantly, understandable. I had the chance to utilise the information and something that could have potentially taken a day took just over an hour. Amazing. Also linking the data to a folder, means this will be an ongoing way of extracting information from a number of forever changing data sources 👍🏻 thank you

[@MyOnlineTrainingHub]

Awesome! So pleased to hear you found my videos helpful. Please share them with your co-workers too :-)

[@dragoY9955]

Thank you so so much. Your video has no frill, and the information is precise and concise at the same time. 👍👍👍🙏🙏🙏

[@MyOnlineTrainingHub]

So pleased to hear that, Drago!

[@valentinagrecchi16]

Amazing! Just implemented that and it is brilliant. The "common mistakes" helped me rectify an issue right away instead of going crazy trying to resolve it. Thank you so much.

[@MyOnlineTrainingHub]

So pleased to hear that, Valentina!

[@cshahed]

I understand the power of Pivot Table but somehow never got a grip of it. This clip was a good starter for me to simplify things. Many thanks

[@MyOnlineTrainingHub]

Glad it was helpful, Shaheduzzaman!

[@cmcull987]

Mynda's awesome. Thank you so much. I really am excited about Excel and so grateful I encountered your presentations.

[@MyOnlineTrainingHub]

So pleased you found it helpful 😊

[@AlessandroEstrella]

Great piece of work. I am glad I found you! Thank you for sharing your knowledge.

[@MyOnlineTrainingHub]

Thanks for watching!

[@kirshnersanjose]

A very useful and helpful video, thanks Mynda...

[@MyOnlineTrainingHub]

You're so welcome!

[@beverlitotanghal9923]

Thank you very much for your explaination and advice Mynda. I am always watching your video.

[@MyOnlineTrainingHub]

Thank you! 😃

[@156615]

Ah ! after watching all your other videos i feel , hey i know this tutorial already well. thanks for the training videos

[@signalmas5673]

Yeah... I’ve got this one too. My mantra: Pivot or bust !

[@MyOnlineTrainingHub]

Great to know you've found them helpful, Amrish. Please share it with those who nee to learn PivotTables :-)

[@MyOnlineTrainingHub]

"Pivot or bust", love it :-)

[@ildelisanajera3222]

Very Helpful. Thanks . Do you have a tutorial explaning how manage in a pivot duplicate data in column A and different data in colum B?

[@MyOnlineTrainingHub]

Not sure what you mean. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@rrrprogram8667]

You are master of what you are doing

[@MyOnlineTrainingHub]

You're very kind!

[@LosoIAm]

Such an inspiring tutorial - many thanks, Ma'am.

[@MyOnlineTrainingHub]

So pleased to hear that!

[@olga1033]

Thank you! So many eases! I use pivot tables all the time but on elementary level i think and i match datas above pivot table. This is huge! Thank you!

[@MyOnlineTrainingHub]

You're very welcome, Olga!

[@aboud.1969]

I have been looking for an answer that subtotal always shown, and finally found the solution in your video (it is in the design tab), thanks indeed.

[@MyOnlineTrainingHub]

Glad I could help!

[@adult-coloring]

Thank you. You are a very good teacher.

[@MyOnlineTrainingHub]

Thank you! 😃

[@ryanpope4124]

Really helpful lesson and downloaded sample excel file, thanks!

[@MyOnlineTrainingHub]

Great to hear, Ryan!

[@johnt9186]

As always. Great straightforward summary

[@MyOnlineTrainingHub]

Thank you!

[@melissadelavega187]

Hi Mynda! This is very helpful video Thank you i learned a lot!

[@MyOnlineTrainingHub]

Great to hear 😊

[@MyOnlineTrainingHub]

I'm so glad!

[@ramezattar7796]

That's so helpful Mynda. Still what about explaining what pivoting mean to audience. Thank you

[@MyOnlineTrainingHub]

Great to hear. Good idea about explaining the meaning of pivoting.

[@ricos1497]

I'm a pivot tables man myself, they're great. I probably tend to use DAX more than pivots straight from the data (which I nearly always have in a separate location). One thing I think that is missing from pivot tables is the ability to enter data into them and overtype values. Their layout options are fantastic but it would be great if you could specify a "primary key(s)" on a row that allowed data to be added to a data source directly by simply typing over the values in your pivot. One of the scenarios I regularly encounter is the requirement to produce a Forecast. I present the Actual and previous forecast data by month (or whatever period) in columns and perhaps cost centre or whatever and values in the rows. I'd then like to overtype this previous forecast data with current forecast and "submitting" this would send my new forecast by month/costcentre to the original datasource, which could then be refreshed to show my new forecast, and perhaps make further changes. I feel that pivots have really got the advantage of having the exact presentation required (there really is no comparison in Excel), but lack that "two-way" communication that would make them truly interactive. If I were in charge of Excel, that would be my priority!!!

[@MyOnlineTrainingHub]

I think allowing data entry in PivotTables would be difficult since the PivotTable is aggregating data, so how would it then disseminate the data back into the individual rows that make up the aggregated value? One of the great things about PivotTables is that you can't break them, unlike formulas that allow you to edit them. I'm all for keeping them one way, but perhaps you need another tool for your forecasts.

[@ricos1497]

@@MyOnlineTrainingHub Yep, valid points! You're right about aggregation, that's why I mentioned a primary key, which would essentially be a combination of one or more of your table values. You basically choose the level at which you enter data by aggregating your pivot at that level. Your Actual data might have invoice number, description etc, but your pivot would aggregate by CostCentre or Product level (or a combination of both), which would be the level that you budget and forecast at. By overtyping, your essentially adding rows/data at that aggregated level, rather than disseminating just as you often would a Budget or Forecast in a traditional finance system. I already actually use pivots for this purpose, but instead of overtyping, when they select a row, the data from that is copied to a line above the pivot where the user overtypes and submits. There are plenty of other tools for forecasts, of course, but if you're using Excel, there is nothing as flexible as a pivot for showing data by month with variable number of rows depending on department, region etc. Maybe I'll create an add-in that allows it!

[@ricos1497]

It seems it used to be a function for OLAP datasources, via the "What If Analysis" on the Data tab in pivot table options. Also known as "Writeback". I'm guessing they got rid of it at some point!

[@MyOnlineTrainingHub]

An add-in would be a great idea!

[@blancadelosreyes5852]

thank you mam, so informative and easy to understand...

[@MyOnlineTrainingHub]

Great to hear, Blanca!

[@FernandoLima42]

You are the best Mynda!

[@MyOnlineTrainingHub]

Thanks for your kind words, Fernando!

[@Ahmed-fq6si]

Suffice it to watch some other youtube videos talking about the same subject to come to the conclusion that you're the best. It is very well done both in terms of form and content.

[@MyOnlineTrainingHub]

Wow, thank you, Ahmed!

[@bobanefecco]

you are crystal clear ! god bless you !

[@MyOnlineTrainingHub]

So glad I can help 😊

[@baKWTFMB]

Thats great. I'v never used pivot tables but now i def will.

[@MyOnlineTrainingHub]

Very pleased to hear that!

[@iankr]

Many thanks, Mynda - this is brilliant. Coincidentally, I discovered only yesterday that you can format your PT in tabular form, so that it looks more like a database. It fills the blank cells on the left with row labels, thereby 'un-nesting' rows where you've got more than one data item in the rows. This is the main thing that always annoyed me about PTs . I absolutely get how quick they are at summarising your data, but it was their look and format that annoyed me. But now that I know this (and have seen you do it so easily) I think I'm converted! This will make it much easier for me to summarise data by account master (which has three dimensions, equating to three data items in the rows) for upload to SAP systems.

[@MyOnlineTrainingHub]

So pleased to hear you are a PivotTable convert, Ian!

[@katrienvanherck2142]

I like the way how you explain Excel !

[@MyOnlineTrainingHub]

She happy to hear that! 🙏😊

[@martyf.8088]

Thank you. Very well done. Is there a way to rename the titles?

[@MyOnlineTrainingHub]

Thanks, Martin. Rename which titles?

[@ckwong48]

Thank you for your video. I have one question. I must update my sales report weekly and use pivot table to help to calculate the weekly performance of our store. When I update and add new data to the table sheet every Monday, refresh pivot table source, the result comes out as unexpectedly wrong. I must insert a new pivot table then can make a correct result. I can not figure out the problem. But I watched your video you told me whenever you added new data, just refresh is Ok. I am thinking if this refresh function is only for editing excel file? Once you close it , then you must re-create a new pivot table again?

[@MyOnlineTrainingHub]

Hi Cathy, closing the file shouldn't make any difference as long as you save it before closing. Please post your question and Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@jd4tricks]

I finally got it! Pivot tables require properly formatted tabular data.

[@MyOnlineTrainingHub]

Yes 👍 as do most functions.

[@anandnagarkatti]

Thank you Mynda. I desperately want to learn how to format a Matrix Table (already pivoted table format in your example) into a data list format. Looking forward for your video on that.

[@MyOnlineTrainingHub]

Thanks, Anand! You can learn how to fix already pivoted data with Power Query here: kzbin.info/www/bejne/Y3qwop6daJqUmqM

[@anandnagarkatti]

@@MyOnlineTrainingHub YES! Thank you so much! This has all the different scenarios I encountered. Again thank you so much!!!

[@abhishekdesai3345]

Hi mynda, can you make video of hide and unhide column as done in this video using + button. Thanks for yours videos.

[@MyOnlineTrainingHub]

I used the Group tool on the Data tab of the ribbon: www.myonlinetraininghub.com/excel-group-and-outline-data

[@mikemellor8972]

Sometimes pivot tables respect a slicer selection and sometimes they don't. I've never been able to work that out. Pivot Table output is ugly by default and you have to spend a lot of time formatting, catching errors like the accidental double count of the grand total in this video, and editing column labels for example to get rid of "Sum of" etc. For an experienced user there's not a lot of productivity gain. I think that a pivot table is intended not for publishing, but as a data source for dashboards and charts. Thanks for the tutorial which I watched to see if there was anything I missed and indeed I did acquire some good tips.

[@MyOnlineTrainingHub]

Cheers, Mike! I've never had PivotTables not respect Slicer selections. I wonder if the Slicer isn't connected to the PivotTable you're wanting to filter.

[@mikemellor8972]

@@MyOnlineTrainingHub if the source data for the pivot table is sliced, inconsistently the pivot table will respect that slicer, and most often it will not. There are a lot of holes in MSXL and this is just one of them. Slicers have been around for a few years, but through several incarnations of Excel, user management of their properties remains non-existent.

[@MyOnlineTrainingHub]

Hi Mike, I'm not sure what you mean by source data being sliced inconsistently and how this would happen. I'd love to see an example. Perhaps you can email it to me: website at MyOnlinieTrainingHub.com

[@ronsss4774]

Thanks Mynda. Good intro to PivotTables. Yes, I will be sharing it. Couple of points 1. as usual, you have large test data sets. Personally, when demonstrating, or building a Pivot, I like to use smaller, simpler data sets, ie fewer rows and simple integer values. So I can do cross checks in my head. . 2. When defining a Table, I like to get into the habit of changing the default name to a descriptive one. Granted PivotTable does not pick up on table names directly, but it does use it in the Connection name. And PowerQuery does directly use the table name as base for query name. . 3. As you are setting up the second example by changing the input data I think it would be nice it you mention in passing that the restructure is done using PowerQuery. . 4. I'm surprised that your summary information and links does not include a link to your annual Dashboard webinar replays. Those 3 webinars were actually my introduction to PivotTables and all of the other "PowerTools" . Keep up the good work.

[@MyOnlineTrainingHub]

Cheers, Ron! Thanks for sharing the additional points too.

[@ronsss4774]

00:00 - Intro 00:00 - sample data 00:52 - Attributes of data being input to a PivotTable 01:38 - Define input data as Excel Table 02:00 - insert first PivotTable: Recommended table 02:50 - Insert PivotTable using ribbon 03:29 - Describe the functions of the “PivotTable Fields” pane 03:39 - Create simple “sumif” type pivottable 04:17 - Add columns 04:35 - demonstrates autogeneration of a months group on date (auto 2016 and newer) 04:45 - Manually grouping/ungrouping fields 05:18 - Change function used on numeric values from simple sum using “Summarize Values by” or Value Field Settings” 05:53 - Refresh PivotTable 07:22 - Advantange of PivotTables: Speed and no formula errors 07:36 - Why people find PivotTables difficult 07:41 - #1: Wrong data layout, Pivotted, totals 10:25 - #2: Semi report format data 12:27 - conclusion (your readers might as well have this too, since I went to the effort to create it.)

[@MyOnlineTrainingHub]

Super helpful, Ron! Thank you :-)

[@ivan2951]

Hi Mynda! Do you know that because of your excel tutorials in youtube I've been using pivottable in my work and it helps me a LOT! I've been working in the energy trading industry in the Philippines and our work requires thousands of data being processed and analyzed and we often do forecasting also. Your tutorials here have been a great help for me! Thank you as always!

[@MyOnlineTrainingHub]

Wow! So pleased to hear that! Please share them with your co-workers who might be able to use them too :-)

[@ivan2951]

Will do Mynda! Thank you!

[@sebastiankumlin9542]

The ribbons in the cells in the label row (row 1). I want want these! Mind telling me how I get them? Thanks in advance!

[@MyOnlineTrainingHub]

I'm not sure what you're referring to. What is the timestamp in the video where you see this?

[@sebastiankumlin9542]

@@MyOnlineTrainingHub Thank you so much for answering! I'm referring to the down arrow in each cell in the first row of the table at time 0:19. However I'm not sure what they do or if they are important at all. But I'm sure they are! 😆

[@gillproduction1408]

Can you share its supporting excel file as you shared before for dashboard Thanks for this great info and efforts

[@MyOnlineTrainingHub]

Yes, like all my videos, if there's a file to download then you'll find the link in the video description.

[@gillproduction1408]

@@MyOnlineTrainingHub From last few days i am watching your videos your all videos are excellent, i liked your video which i am watching because your videos are full of required information and very nice

[@megamundus]

Dear Mynda thank you for all you are sharing with us. I personally have been greatly benefited from your training material and i wish you are always happy and healthy. Would you please help me with this: i have an accounting Trial Balance in an excel sheet table with, say, 5 columns for account code, account description, debit, credit and balance. The code is in the form of xx.xx.xx.xxxxxx and can be 2, 5, 8 or 14 characters long, including the dots, depending on the ranking of each account. Is it possible to bring in a pivot table selected accounts , each one in a row, with all data, including the description in a separate column ??? Be well !!!

[@MyOnlineTrainingHub]

I expect so. If you get stuck you can post your question on our Excel forum: www.myonlinetraininghub.com/excel-forum

[@StephenBoothUK]

The part about poorly formatted source data is an excellent example of why the hardest and most labourious part of data analysis is often cleansing the source data to fix the mess that the people who provided it have made. No matter how often we say we want the data as it comes from the system in a tabular format they still want to fiddle with it. I once had to use my employer's grievance procedure to get a manager moved (and eventually sacked when he tried the same with his new business area and their data team hit the same problems) when he just wouldn't stop 'tidying up' the data before passing it on despite the data teams and his own management telling him not to. His actions took a 30 minute job to more than a day, and that's not counting the couple of days he wasted making the mess in the first place. He also couldn't get that Excel is colour blind so summations by font colour won't work and that if you provide someone with figures for October of one year and December of the same year you can't expect them to tell you what the figures were for November.

[@MyOnlineTrainingHub]

Sounds like a nightmare, Stephen!

[@StephenBoothUK]

@@MyOnlineTrainingHub he was an extreme case, but poorly formatted data is very common in my experience.

[@DietricWilliams]

I wish I'd have seen this video earlier today instead of having to do all the manual data scrubbing I did. My job uses Mac and I don't think I can use Power Query. What's a fast way to put data in tabular form similar to your last example?

[@MyOnlineTrainingHub]

Glad it was helpful. It depends on what the problems are with the data as to what tools you could use that are available on a Mac. If it's a repetitive task, then you'd be best to write some VBA code to automate it.

[@alexandreguerra6437]

Thanks again! Another great video!

[@MyOnlineTrainingHub]

Glad you enjoyed it, Alexandre!

[@skcp151]

Can you please advise how to convert negative value to positive value in pivot table..esp balance sheet presentation need to show all items as positive

[@MyOnlineTrainingHub]

You should do this in the source data.

[@chrism9037]

Very good video thanks Mynda!

[@MyOnlineTrainingHub]

Appreciate that, Chris!

[@arashid6231]

Explain very well thank you

[@MyOnlineTrainingHub]

Glad it was helpful!

[@MaxoticsTV]

I couldn't compliment you enough on your videos. Have been enjoying them immensely. For what it's worth, some thoughts for future videos. My observation is that pivot tables are difficult (like many computer operations) because of the classic "Noun-Verb" problem. For example, do you enter .5 and format as a percent, or format as a percent THEN enter .5? Despite the laughs I get, most problems users face are ones of philosophical clarity ;) In your first example, the Sales person is a NOUN, and the amount sold is the VERB (how much they sold). We can't really understand anything without both noun/verb attributes. In itself, "dog" imparts no knowledge. Neither does "walk". Only together do they make sense. You can say both "dog walk" and "walk dog". A pivot table is a simple exercise in FLIPPING the emphasis, your subject, from one to another. Because we naturally think in summing columns, or doing math on columns, we need to flip the data depending on if we want to group/calculate on "dogs" or "walks". So when you "pivot", you change to thinking of amounts sold as your Noun (subject) and your salespeople as the VERBs, so to speak. The problem you mentioned in the 2nd set of data demonstrates this philosophical problem. Is a month a noun or a verb? In that case, it is related to the person or the amount sold? As you know, good data is about about not mixing attributes. The data should be an attribute of the Noun (salesman). This is in the same vein as you wouldn't put in a cell for Joe the Salesman "Sold 85 Units January" As they say, a little (philosophical/logical) preparation goes a long way! Maybe you can do a video on this fascinating subject, of how Excel has to make philosophical decisions about which preferences to favor in how people think. For example, Excel favors value first, data type second, whereas Access favors data type first, value second. That's a whole subject in itself I'm sure you can, or have, done well. Again, amazing videos. I wish I had half your patience ;)

[@MyOnlineTrainingHub]

Thanks so much for your kind words! You raise some interesting points that I hadn't thought that deeply about before. I will certainly try to keep that in mind for future videos. One struggle I have is being able to see things from a beginner's point of view. And even if I can see things from their point of view, I can't go back to the very beginning in every video. Instead I have to assume some prior knowledge or every video will be hours long!

[@MaxoticsTV]

@@MyOnlineTrainingHub I'm 59, have been using Excel from the beginning. When it comes to pivot tables I always feel like a beginner, and a dense one at that. I can never quite get it. That's why I was hopeful that your video would finally make me smart ;) I seldom use pivot tables. I write VBA routines to transpose and calculate whatever I need. The stuff I wrote about, that's more me trying to think out loud than my having any truth. So anyone reading, please, just food for thought. I was working on a Python project a while ago and just wanted to do a simple flip of the table, but couldn't figure out how to do it. Pivots are meant to use a sum, average, count on the value because it is "tabulating". Another intellectual hurdle. I had to use a "first" type kludge. Anyway, thanks for the reply. If you do another video on this subject I will certainly get my hopes up again haha!

[@ndutienn]

Thanks . Really helpful

[@MyOnlineTrainingHub]

Glad to hear that, Edward!

[@abednegomulumbi6975]

we Kenyans are backing this page like no one's business! Aaah!

[@RacheB]

your lessons are helpful. just have a question though, is it possible to change the sorting of headers in pivot table? not in ascending nor descending order? let just say that i have a particular order for the reports table. thanks

[@MyOnlineTrainingHub]

Yes, you can left click & drag them manually into whichever sort order you want.

[@tientruong1346]

Do you know how I can used a fixed value in a formula on a Pivot Table?

[@MyOnlineTrainingHub]

Hi Tien, not sure what you mean exactly, but this tutorial might help: www.myonlinetraininghub.com/excel-pivottable-calculated-fields

[@tientruong1346]

Thanks for responding so fast! I'm referring to putting a formula in the Calculated Field. I'm trying to put in a formula that has a fixed value but when I add the field to the Pivot Table, it automatically Sums or Counts. For example I want to calculate Occupancy= # units occupied/# of units. The # of units never changes but the # of units occupied does. How can I calculate that on a pivot table? I'm creating the pivot table so I can create a dashboard.

[@IvanCortinas_ES]

Thanks for the good advice Mynda!! Waiting for the powerful Power Query :) !!

[@MyOnlineTrainingHub]

Cheers, Ivan!

[@macfarmw]

This is a very helpful tutorial. Thanks! My results were slightly different when I followed the steps. When I dropped the Order Date field onto the Rows section, the Pivot table displayed the 3 letter months in the Row Labels column. However, the + sign expander was missing and the extra Months column did not appear like in the video. I'll experiment more and see if I can find the setting that causes this difference.

[@MyOnlineTrainingHub]

Glad it was helpful. You may have an earlier version of Excel that doesn't automatically group the dates.

[@aphastus]

That’s a great tip. I myself always been doing tables like the last example, althought I have never had to make extensive data analysis out of them. The thing is that I applied for a job as data analysis technician in my HR department and tried to extract data for my work cneter and I got the same table as the example. I’m trying to make a dashboard with some KPIs to bring along my interview and I’m stuck with a lot of duplicated data that the only thing I can think to do with is tidy up manually 😩

[@MyOnlineTrainingHub]

Sounds like you could use Power Query to automate the cleaning up: kzbin.info/www/bejne/gmWlpoiwmMh_ptE

[@Explorer_00-p5n]

WOw, love your Excel Tee

[@MyOnlineTrainingHub]

Thank you!

[@marcw.5492]

Ive added Pivot tables to connect to Slicers for DATE display on the table, but the new simple Pivot Tables (names showing in the upper left) do not appear on the connection menu ?? they are all connected to same TABLE / Range ....hmmm Thanks for your clear and well doen videos.

[@MyOnlineTrainingHub]

Check out this video about the Pivot Cache: kzbin.info/www/bejne/q3rTf6efbryMm68 if that doesn't help you solve the problem, feel free to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum

[@marcw.5492]

@@MyOnlineTrainingHub sounds like a good idea ! cache would explain lingering items also as I keep seeing stuff floating around that I was sure I got rid of.

[@premrajkingoflove]

There are no recommended pivot tables in the insert tab. It was visible until yesterday!! What do I do to insert a pivot table when there are none in the insert tab?

[@MyOnlineTrainingHub]

Strange, Prem. I'd say you need to update Excel, as that button shouldn't disappear.

[@hazemali382]

always great Mynda ♥

[@MyOnlineTrainingHub]

Thanks, Hazem :-)

[@tysonnsimbeye1853]

How can I get hold of this excel spreadsheet? Please

[@MyOnlineTrainingHub]

The link is in the video description.

[@davidtopp1972]

Was not sure where to post my issue, so here goes. I am using a Mac Book Pro, and I have an Excel file thru the Microsoft Online. My file is a Personal Budget and Check Register. The check register is formatted as a table. I have made several pivot tables, some with monthly data, and some with yearly data. When I add more transactions to my check register, and then refresh the pivot tables, I am constantly presented with the pivot table columns not holding their formatted width. I have searched online, but cannot seem to get a concise answer. Please let me know if there is a solution.

[@MyOnlineTrainingHub]

Hi David, right-click the PivotTable > PivotTable Options > Layout & Format tab > deselect 'Autofit column widths on update

[@paulsingleton6071]

Hi Mynda, I am using a pivot table that I have added to the data model so I can count some distinct values. If I try to use the getpivotdata function and try to reference a date cell I get the #ref! error. I have made sure my data model range is in the same format, am I missing something? Thank you

[@MyOnlineTrainingHub]

Hi Paul, I recommend you use the mouse to reference the PivotTable cell and let Excel build the GETPIVOTDATA formula for you. You can then see the date format it is expecting as this will be hard keyed in the formula. Then you can edit your date cell to use the same format. Hope that points you in the right direction. If you're still stuck, please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@paulsingleton6071]

@@MyOnlineTrainingHub Thank you Mynda for taking the time to reply. After looking again at the data model I found that the date field had a time stamp appended to the date. Not sure why. After some trial (and error!) I added "...&["&TEXT(Cell#,"yyyy-mm-dd")&"T00:00:00]")" at the end of the getpivotdata formula and it worked. Thanks again for your guidance 👏.

[@davidwillis4545]

How can get the pivot table analyze option to let me create a calculated field? For some reason that option is always shaded out, so i can not choose it?

[@MyOnlineTrainingHub]

That option is not available when you load your data to the Data Model aka Power Pivot. In that case you need to open the Power Pivot editor and enter a calculated column there or write a DAX measure.

[@leorc564]

Good feature on Excel 2016 for dates. Thank you

[@MyOnlineTrainingHub]

Glad you like it!

[@NaamMeinKyaRakhaHai]

Hi Mynda, how would one work with YTD figures in pivot tables that uses sum as a default function for values, which would add all the YTD figures and produce a wrong result, because the latest YTD figure already subsumes all the previous months in the range, right?

[@MyOnlineTrainingHub]

Good question. I'd extract/calculate the movement so you can use monthly figures in the PivotTable. You can use Power Query to automate this for you.

[@darrylmorgan]

Hi Mynda!Really Helpful Tips With Pivot Tables..Thank You :)

[@MyOnlineTrainingHub]

Thanks so much, Darryl!

[@JimDoveMO]

I have a bunch of data which is similar and I thought I could use a pivot table to display it but I don't think this is the correct way. I have the following data: room air temp, supply air temp, and return air temp. The data was taken at different times but has a time stamp associated with it. I can put it all in one table with the headers of day, time, temp, and item. How can I add these three items to one table showing them all as separate line graphs with time as the x axis and temp as the y axis? In other words I would like excel to sort them and put them in chronological order.

[@MyOnlineTrainingHub]

That's doable, Jim. Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum

[@itsnotmysong]

YOU ARE AN ANGEL

[@MyOnlineTrainingHub]

Glad you found the video helpful :-)

[@mbarnes297]

How do I get the pivot table to display the items in a field instead of summing them?

[@MyOnlineTrainingHub]

PivotTables aggregate. If you don't want the data aggregated then you'd need to number each row with a unique value e.g. an index number, and then add that field to the row labels of your PivotTable.

[@Gershom-ze2lq]

thanks this is very helpful

[@MyOnlineTrainingHub]

You're welcome!

[@utubayisi]

Perfect video again thx. I've got this issue: In case the rows under a certain column are supposed to include multiple criteria and it is not preferred to divide the cells into columns, slicers have boxes with multiple criteria as expected. Is there somehow a way to make the boxes include only one criterion in the slicer? Btw it's also needed for the charts. Many thx in advance whoever understands and responds :)

[@MyOnlineTrainingHub]

Thank you! No, there's no way for a slicer to display only one value from a cell.

[@andreausa123]

Thank you very much !

[@MyOnlineTrainingHub]

My pleasure 😊

[@deliawalker8341]

I have learnt so much from watching your video's. Thank you. I have been able to create both the tables and a pivot that saves us some manual formatting and checking data. I have a strange question. Is there a way to format the pivot table to highlight cells (say make the cell blue/ bold), where the pivot table data doesn't align to the back up/ base data? I send out the file for review and people sometimes over write the data where they notice it is wrong. Is there a way to highlight the values they have changed in the pivot?

[@MyOnlineTrainingHub]

Great to hear, Delia! There's no way to automatically highlight changes, but you could provide the file as read only, or at least password protect the PivotTable cells so they can't type in them. Mynda

[@deliawalker8341]

@@MyOnlineTrainingHub thank you! no wonder I can't find it googling the web. :-)

[@txreal2]

Sometimes I'd like to see what the data looks like in a regular table. How do I convert a pivot table I received to a tabular table? Thanks.

[@MyOnlineTrainingHub]

You can double click on the Grand Total cell and Excel will extract the underlying transactions into a table in a new worksheet.

[@txreal2]

@@MyOnlineTrainingHub Thanks!

[@johnborg5419]

Thanks Mynda. : )

[@MyOnlineTrainingHub]

My pleasure, John :-)

[@John.Mann.1941]

I tried twice to download the example file, but what opens is an empty Excel2010 document with no tabs, and the function bar contains "Order ID". The properties data from File/Info shows a size of 436Kb. Do I need a more recent version of Excel?

[@MyOnlineTrainingHub]

Excel 2010 is no longer supported, so it might be a compatibility issue.

[@kathyadams5917]

There's no link to a practice file in the email i received.

[@MyOnlineTrainingHub]

The link is exposed on the page after you enter your email address.