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 😊

[@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!

[@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

[@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 😊

[@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

[@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!

[@Ntentes95]

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

[@MyOnlineTrainingHub]

Wow, thank you, Nick!

[@gatasucia5973]

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

[@MyOnlineTrainingHub]

Glad it was helpful!

[@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!

[@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 :-)

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

[@VirtualArcades]

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

[@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!

[@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!

[@zaidalsajjan2596]

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

[@MyOnlineTrainingHub]

Glad you enjoyed it!

[@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!

[@moiz6330]

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

[@MyOnlineTrainingHub]

It's my pleasure, Hafiz!

[@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!

[@daliladuf]

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

[@MyOnlineTrainingHub]

Happy to hear that, Dalila!

[@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 :-)

[@MurariRoy]

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

[@MyOnlineTrainingHub]

Wow, thank you! 🥰

[@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 😊

[@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!

[@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!

[@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!

[@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 😊

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

[@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!

[@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!

[@kirshnersanjose2101]

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

[@MyOnlineTrainingHub]

You're so welcome!

[@johnt9186]

As always. Great straightforward summary

[@MyOnlineTrainingHub]

Thank you!

[@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!

[@AlessandroEstrella]

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

[@MyOnlineTrainingHub]

Thanks for watching!

[@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!

[@LosoIAm]

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

[@MyOnlineTrainingHub]

So pleased to hear that!

[@beverlitotanghal9923]

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

[@MyOnlineTrainingHub]

Thank you! 😃

[@baKWTFMB]

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

[@MyOnlineTrainingHub]

Very pleased to hear that!

[@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!

[@missadventure7564]

WOw, love your Excel Tee

[@MyOnlineTrainingHub]

Thank you!

[@adult-coloring]

Thank you. You are a very good teacher.

[@MyOnlineTrainingHub]

Thank you! 😃

[@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!

[@melissadelavega187]

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

[@MyOnlineTrainingHub]

Great to hear 😊

[@MyOnlineTrainingHub]

I'm so glad!

[@katrienvanherck2142]

I like the way how you explain Excel !

[@MyOnlineTrainingHub]

She happy to hear that! 🙏😊

[@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

[@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!!!

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

[@arashid6231]

Explain very well thank you

[@MyOnlineTrainingHub]

Glad it was helpful!

[@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

[@IvanCortinas_ES]

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

[@MyOnlineTrainingHub]

Cheers, Ivan!

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

[@hazemali382]

always great Mynda ♥

[@MyOnlineTrainingHub]

Thanks, Hazem :-)

[@jd4tricks]

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

[@MyOnlineTrainingHub]

Yes 👍 as do most functions.

[@andreausa123]

Thank you very much !

[@MyOnlineTrainingHub]

My pleasure 😊

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

[@sopatisserie1282]

Thanks so much for this simple and effective video. It's the best video that I can find to start with PT without going too much into detail. I'll practice all the features that you have included a few times during this week, in order to well assimilate all the mentionned functions. The download Excel practice file is an excellent extra.

[@MyOnlineTrainingHub]

So pleased to hear that :-)

[@leorc564]

Good feature on Excel 2016 for dates. Thank you

[@MyOnlineTrainingHub]

Glad you like it!

[@aeromechsagar]

Thanks Mynda

[@MyOnlineTrainingHub]

My pleasure, Sagar!

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

[@darrylmorgan]

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

[@MyOnlineTrainingHub]

Thanks so much, Darryl!

[@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! 😆

[@chrism9037]

Very good video thanks Mynda!

[@MyOnlineTrainingHub]

Appreciate that, Chris!

[@lewismuriungi4564]

Right now I have some excel dara formatted awfully each month has its own tab and finding it stressful to combine all tabs into one pivot table

[@MyOnlineTrainingHub]

You can use Power Query to automate the consolidating of that data: www.myonlinetraininghub.com/power-query-consolidate-excel-sheets

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

[@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

[@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!

[@martyf.8088]

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

[@MyOnlineTrainingHub]

Thanks, Martin. Rename which titles?

[@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

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

[@jongcheulkim7284]

Thank you.

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

[@ajaycan]

Great tutorial

[@MyOnlineTrainingHub]

Glad you think so!

[@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!

[@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 👏.

[@vinceskinner9357]

Just amazing thank you sooooooooooooooooooooo much.

[@MyOnlineTrainingHub]

:-) my pleasure, Vince! Have fun with PivotTables.

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

[@mohmmadabdo5899]

Thanks a lot..

[@MyOnlineTrainingHub]

Most welcome, Mohmmad!

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

[@YosefWiratama1312]

Great videoosss..

[@MyOnlineTrainingHub]

Glad you think so!

[@HanifTutorial]

Good👍

[@MyOnlineTrainingHub]

Thank you!

[@raghunathbabu1772]

Wt are short keys to create pivot table??

[@MyOnlineTrainingHub]

One key at a time: Alt, N, V, T.

[@ram.m4805]

Mam,how to become pro in Excel like you ?

[@MyOnlineTrainingHub]

Hi Ram, practice and try to learn something new every week 😉

[@ram.m4805]

@@MyOnlineTrainingHub Sure, Thank you

[@silvestro2in1]

Nice

[@MyOnlineTrainingHub]

Thank you!

[@mizanchy530]

Hello, would you able to help me create a spreadsheet/ pivot table? I am willing to pay the fees. Thank you

[@MyOnlineTrainingHub]

I don't do consulting, but if you reach out via email I can refer you to someone who can help: website at MyOnlineTrainingHub.com

[@arturoguzman5230]

😍

[@MyOnlineTrainingHub]

Thanks, Arturo!

[@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!

[@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!

[@united2534]

YOU ARE AN ANGEL

[@MyOnlineTrainingHub]

Glad you found the video helpful :-)

[@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 😁

[@zerotrace000]

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

[@MyOnlineTrainingHub]

Thank you very much!

[@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!

[@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!

[@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

[@matlabkuchbhi3060]

How did you get source data in correct layout...

[@MyOnlineTrainingHub]

I use Power Query: kzbin.info/www/bejne/Y3qwop6daJqUmqM

[@ndutienn]

Thanks . Really helpful

[@MyOnlineTrainingHub]

Glad to hear that, Edward!

[@abednegomulumbi6975]

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

[@ryanpope4124]

Really helpful lesson and downloaded sample excel file, thanks!

[@MyOnlineTrainingHub]

Great to hear, Ryan!

[@FernandoLima42]

You are the best Mynda!

[@MyOnlineTrainingHub]

Thanks for your kind words, Fernando!

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

[@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