Excel Basics 5: PivotTable Power to Create Reports with Various Calculations & Layouts
HTML-код
- Опубликовано: 22 авг 2024
- Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start & Finished File: people.highlin...
Download Full Written Notes: people.highlin...
Class web page link: people.highlin...
Topics in this video:
1. (00:01) Introduction
2. (01:46) Keyboard for PivotTable
3. (04:00) Why a PivotTable is called a PivotTable
4. (04:25) Cross Tabulated Report (Adding with 2 Criteria)
5. (05:15) Calculations with Conditions or Criteria
6. (07:40) Change Calculation in Values area
7. (09:18) Drop Number or Text fields in Values area
8. (11:43) PivotTables on Large Data Set is Easy!!
9. (12:00) Keyboard to jump to bottom of column
10. (13:00) Keyboard for PivotTable on New Sheet
11. (15:30) Format PivotTable with Style
12. (16:05) Slicer to Filter who PivotTable
13. (19:24) Summary - Conclusion
PivotTable Basics, Pivot Table Basics, Learn PivotTables, Learn Pivot Tables, Lesson in Pivot Tables, How to create a PivotTable, PivotTables Are Easy! Pivot Tables Are Easy! How to Create a PivotTable Report, Pivot Table Reports
If I were to thank Microsoft for inventing Excel then I thank you much more than that for making this so simple and easy for us to understand. You're a great teacher. Lots of love and wishes from India 💖👍🤗🙏
You are welcome for the lessons, Ashish!!!! Thanks for the love and wishes : ) : )
@@excelisfun thanks again.
I hope you and your family are doing fine during the pandemic 😊☺️
@@ashishaggarwal2947 I hope you are your family are well too : )
@@excelisfun Yes. We're doing well. Thank and take care.!!
Michael, you are by far the greatest teacher in my life hands down. The thing I love is that you teach things systematically and folks from all ages or levels can follow it through gradually. I have translated your work into Mandarin and did an internal training to my fellow accountants and they were all simply "shocked" to aware how little they know about Excel not to mention what Power BI can do. For majority of the repetitive reports can be done automatically by fresh and better yet timer to refresh. Heck, with most of my work automated, I got time to learn SQL language and Python to advance my career further. But most of all, ExcelisFun is always my place to humble myself :-). Thank you Mr. Girvin. Really appreciated it.
Thank you for your very kind words! You are welcome for all the materials. It sounds like you have been watching and learning a long time.Be sure to help support excelisfun with those comments, Thumbs Ups and of course your Sub : )
The way you speak is comfortable for non-English native speakers. Not too fast and understand totally. I cannot thank you enough.
I am happy to help : )
My mind is blown by your way of teaching and knowledge. PURE PERFECTION. Thank you for sharing these videos for free. Wish I had found your channel earlier!
👋😢🐮😢😫😫
I agree with Traci and James (below): You ARE the best teacher in my life, hands down -- and you DO make Excel fun!!! (Janis Myrick here -- a former Highline Student from 2008 or 2009). I still use Excel almost daily at work -- and I still watch your videos (to refresh my Excel skills). Your videos help me to relearn something I've forgotten. Thank you for being so AWESOME!!!
You are welcome, Fellow Highline Teammate, Janis!!!!! So lovely to hear from you : )
i cannot skip a single second because don't in which second u share useful information. that is the thing makes you different teacher from others... Carry on.. lots of love from india...
That is true. All details in the stories that I construct are import. I am glad that you like it and the story helps you, vivek : )
Hi. I consider myself an excel expert, but being self taught, I know that I have missed stuff along the way. Love your videos, as they fill these gaps, and I just enjoy watching them. Thanks.
I am glad that the videos help fill in the gaps. Thank you for the support with your comments, Thumbs Up and Sub : )
I wish I had you as a teacher when I was in college! Amazing videos Mike, I have learned so much from them.
I am glad that the videos help! Thanks for the support with your comment, Thumbs Up and Sub : )
So agree with you!!!
Mike! I am a big believer that everyone should take their passion and share it with the world to make this place better. You have truly done that, and it is making such a positive impact. Thank you for teaching Excel in a way that people can understand and WANT to learn more. My sincere gratitude.
You are welcome, Christina!!!!
Excellent job! I really appreciate the combination of watching your tutorials and being able to immediately test my knowledge with your spreadsheets.
Yes!!! I love to hear that, M. Liu!! Thanks for your your support : )
just stumble across your video's while looking where i can learn excel, right now i look no further .... Thanks so much
You are welcome, miracle!!!!
Thanks Mike. Even your "beginner" videos contain helpful tips and good spreadsheet practices useful to "non beginners"!
You are welcome! Thanks for your support and appreciation : )
Thank so much for help. You are the best teacher so far.
You are welcome, Kim!!!
This video is really very easy to follow and helpful. PivotTables really are very convenient and efficient. Thank You!
@tvts5157
You comment went away, but I prepared a detailed message about your question for a cross tabulated report. Here it is:
Variable at row header and variable at column header, then intersecting calculation uses the two variables to make the conditional calculation, For example, the sum of sales for the column variable "Northwest" and the row variable "Quad" is 83,155.58. That is the total not for all the sales, but just for sales made in the "Northwest" region for the product "Quad". It is called cross tab, because the variable at the column and the variable at the row come together as a cross - just like the picture at the beginning of the video. Tab means tabulated, like sum. Did you watch the video? I went through all of what I just wrote in careful detail in this video. Check it out - watch it again. A cross tab report is one of the most common reports and if you understand, it brings real power : )
Reply
Reply
You are welcome and glad the video helps : )
@excelisfun I actually asked that question right when I started watching the video because you mentioned cross tabulated reports at the beginning without explaining it. So, I thought that it was something common and that you might not explain it in the video. But, as you said, you explained it in detail in the video further. That is why I deleted my comment as I understood what cross tabulated report meant.
Come to think of that I've been using Excel for ten years pretty much every day, and yet these "basic" videos teach me so much.. Thank you for your time and dedication!
You are so welcome! It makes me happy to hear that you are learn so much from this series. The reality of Excel users is that the vast majority of users are just like you and me (in my early days), we just never got the full story and used Excel without many of the useful tools that Excel offers!
Took this class (as Janis Elain) back in 2008 or 2009 -- and every few years, I re-watch these videos to refresh my memory of the things I may have forgotten. It's always wonderful to see these again! Michael, you are still MY FAVOURITE TEACHER of all time!! (And I've told all my friends about your FREE Excel courses). You're the best!
Janis Elain!!!!!!!!!!!!! I LOVE that you keep boomeranging back : ) What are you doing for work these dyas?
Thanks for the shout out to your freinds! It helps me to achieve my goal of bringing free Excel Education to the world : )
JUST WOW
I by mistake bumped into your videos
And I'm glued I know excel quite well
And still in each video I learned New stuff
And your teaching skills Are just unbelievable
I hope my kids will have such a good teacher as you
Thank you
Yes!!!! I am so happy that what I post helps : )
i have search a lot for excel course but didn't find better than this. thank u very much for providing this course with a detailed explanation.
You are welcome, Shwetank! Thanks for your support with comment, thumbs up and Sub : )
Besides all your clear explanations, logically ( and in a clear entertaining voice ), your videos are loaded with helpful tips, and hot key shortcuts ... tremendous value!
Glad they are valuable to you, Vance! Thanks for your support with Thumbs Ups, Comments and Sub : )
You are a great teacher sir. Thank you for this amazing video's by far the best teacher in Excel I have ever seen.😃
You are welcome, Andy!!!!!
Whever I watch your videos ,I feel so thankful I found such an excellent Excel teacher!
Thanks a lot Mike. Each time I finish one or your videos I feel more confident and happy.
Excellent job mike , waiting the publishing of
Your valuable book , keep us in touch as soon as the book is ready . I hope everybody in the
comments list will like to have it despite the price .
God bless you Mike ,
Your job is too much
appreciated .
You are very welcome, Manouk : ) : )
Hi Michael, I have watched many excel videos and your videos are the best. You have simple way to make people understand easily and make learning excel fun! And your keyboard shortcut really helps to make report finish faster. You are excellent teacher!
You are welcome for the stories that I tell that try to make the complicated, less complicated, Helmy : ) : )
Really appreciate the way you explain, so easy to understand formulas that are sometimes difficult
You are welcome! Thank you for the support with your comment, Thumbs Up and Sub : )
😮 WOW 😮 !!! That was amazing! Loving the pivot tables. Can not WAIT to see what I learn next. EPIC and life changing. Excel is SO fun. Thank you!
Thank you so much for the kind words! I am glad the series helps! Thank you for the support with your comment, Thumbs Up and Sub : )
I even paid an instructor to teach me formulas but it just got me confused. This is so much better!! Excel is really fun!
I'm just starting seriously on my road to Excel, after using it on a basic level years ago. I'm a little intimidated but Michael Girvin is SUCH an amazing and vibrant instructor! Thank you so much!!!!!!
You will do great in this class because for the first 5 videos I give an overview of everything, and then the next 20 videos I go into more details. Are you finding the files so you can follow along? and the pdf notes that summarize everything from the video? Also, here is the full class playlists, that allows you to join and then it remembers where you left off: ruclips.net/p/PLrRPvpgDmw0n34OMHeS94epMaX_Y8Tu1k
You are welcome, and if you have trouble finding what you need, just ask : )
@@excelisfun I have been downloading, saving, and following along with the files. THANK YOU SO MUCH! Very comprehensive! Thanks for the full class playlist also!!
You may regret telling this I can do so! LOL. Thank you again!!
@@karenparsons8085 No regrets. You can help me. How? Just be sure to click thumbs up and leave a comment on each video (this helps promote and get the free Excel eductaion to more people in the world). And of course your Sub : )
Thanks Michael Girvin. Your videos are very informative and teaches in a perfect manner. Pivet Table was my most weak point. Your videos helped me a lot in making feel easy & got rid of my fear. I am saving all your files in a special folder, " Excel Guru Files". Thanks for all your time. You must have received many hundreds of blessings from people like me. Ajay Shah. Dallas. Texas. US
Your blessing and others help a lot thanks! Thanks for the support with your comment, Thumbs Up and Sub : )
The best guy ever, professional lecturer!
Glad it is best for you, Zbigniew!!! Thanks for the support with your comment, Thumbs Up and Sub : )
Hi Mike, Just found your channel on RUclips and also just started to watch your Excel Video's, there is so much enthusiasm in your voice it is lovely to hear. I will be watching a lot more of these.
Glad you found the channel and are finding the videos helpful. Thank you for the support with your comments, Thumbs Up and Sub : )
Your enthusiasm for Excel rings through your voice! It makes an otherwise plain video with just a computer screen interesting :D :D
Glad you like the fun Excel videos, Madhura!!! Thanks for your support on each video with your comment, thumbs up and of course your Sub : )
You've made Excel a lot more learn-able!! Thanks sooo much!!!
I am glad that Excel is more learn-able!!! Thanks for the support with your comment, Thumbs Up and Sub : )
I love the "WELCOMEEEEE" ....its so welcomingly refreshing...makes my day all ready for a new class today from THE Teacher ..
It is because I am happy to see you : )
the way you explain everything makes it so interesting to learn. you are my first choice if I want a solution to my excel problems.
I am glad to help! Yes, a good explanation, or story about the topic, can make the complicated, less complicated : )
Best Excel Instructor period!
Thanks for the kind words! I am glad that the video help! Thanks for the support with your comment, Thumbs Up and Sub : )
Loving your videos! This is an amazing excel tutorial. Thanks for all the hard work!
You are welcome for all the hrd work! Thanks for the support with your comment, Thumbs Up and Sub : )
Thanks a lot . please do not stop , your videos are very useful and are the best in youtube .
You are welcome a lot! I will keep posting : )
@tvts5157
You comment went away, but I prepared a detailed message about your question for a cross tabulated report. Here it is:
Variable at row header and variable at column header, then intersecting calculation uses the two variables to make the conditional calculation, For example, the sum of sales for the column variable "Northwest" and the row variable "Quad" is 83,155.58. That is the total not for all the sales, but just for sales made in the "Northwest" region for the product "Quad". It is called cross tab, because the variable at the column and the variable at the row come together as a cross - just like the picture at the beginning of the video. Tab means tabulated, like sum. Did you watch the video? I went through all of what I just wrote in careful detail in this video. Check it out - watch it again. A cross tab report is one of the most common reports and if you understand, it brings real power : )
Reply
I am learning so much. You really do make it fun. I am on a roll!!!
Rock on, Iris : ) : )
THESE ARE AMAZING!!!! ITS REMEMBERING EVERYTHING WHEN I AM NOT WORKING EVERYDAY AND TRYING TO GET BACK INTO THE JOB MARKET
SUPER GLAD THEY HELP!!!! Thanks for the support with your comment, Thumbs Up and Sub : )
Thanks a lot for teaching Excel God bless you.
You are welcome! Thanks for the support with your comment, Thumbs Up and Sub : )
Thanks Sir
Sir,
Your every video is more than excellent. I am so confident that there is no other advanced tutorial and trainer like you. I am not interested to follow any other trainer's tutorial if you have tutorial in which I am interested to learn. If it is possible, I will give a lot of Thumbs Up in every of your videos. We need trainer like you to make the world more better. I usually suggest to download and follow your videos whoever want to learn excel meeting with me. Thanks a lot to keep love and patience in your teaching field.
Glad that the videos and teaching helps! Thanks for the support with your comments, Thumbs Up and Sub : ) Please tell all your friends and colleagues to come and learn for free at RUclips : )
its and honor and privilege to attend your class !
I am happy to provide it to you : )
Thanks a lot. I really appreciate you for all these lessons. God bless you.
Mike, thank you so much for your greate videos. You are really doing a great job and the videos you make change millions people application system skills.
Yes, sayeed, that has been mt goal here at RUclips since 2008: Free Excel Education for the world : )
Hi Mike, You are a true Excel Genius
Just a guy having fun with Excel ; ) Glad the videos help, Shaju!!!
By far the best tutor I've encountered! Thank you so much '🙏
You are welcome very much!!!!
I think I've been addicted to watching your videos because every day I have to follow at least one lesson.
That is great! They will help you to become awesome with Excel and do great things in your job !!! Thanks for your support with Thumbs Ups, Comments and Sub : )
I am watching one video every day! I have never thought I would ever be good at Excel (in fact, I did not even want to learn excel) but now I feel like an expert haha! You are an amazing teacher!!!! Thank you sir:) Excel is so much fun learning with your videos!
You definitely can become an Expert, Ling! It will be fun : )
Hey Mike, your class are so accurate and helpful, Thanks
You are welcome, becher! Thanks for the support with your comment, Thumbs Up and Sub : )
OMIGOD!!!! Excel really IS fun!!!! Lol...
I'm cancelling my Netflix!!!
Traci Christenson 😂👌🏻
lol is excel useful to see your spending???
Wild😂😂
2:48 Create pivot table: Alt + N + V
4:58 Cross-tabulated report (two criteria (or conditions)
6:16 Can use "ctrl" and selecting desired cells and then look at the status bar
6:57
8:03 Changing calculation of the "Values" column
Select a single cell in the "Values" column -> "Summarize Values By" -> select desired choice
9:31-10:25 What if you need to count how many sales in each region
10:25 Boss wants one report for count and one for the total sales
Highlight entire pivot table -> "ctrl" + "C" -> paste -> remove "Count of Region" -> drag "Sales" to "Values"
11:58 Shortcut to jump to top and bottom
Jump to top - "ctrl" + "up arrow"
Jump to bottom - "ctrl" + "down arrow"
16:46-19:24 Filter
"Analyze" -> insert "Slicer"
Thank you very much for all your effort you put into making all those videos. Reall thankful that I have found it on youtube. I am starting to like Excel more and more because you really make it look easy and fun. So thank you for your passion and hard work.
You are welcome, Lenka!!!!
Thank you. Now I finally understand slicers. A filter on steroids. Thanks
Love your description: A filter on steroids : ) : )
@@excelisfun Your explanation and analysis of Excel are superior.
@@peterwoods35 I am glad my detailed story telling helps! : )
So much fun; love the way you explain
Glad you like the videos and the fun!!!!!
Your way of teaching is very interesting, keep it up 👆
Okay! I will do that : ) Thanks for your support, Abd!!!!
It's really fun to follow through all your work on my own database
Keep it up !!!!
Glad it helps, CoreAmv!!!!
Excellent way to learn very helpful
The way of your teaching is making my mind to listen to your next video.:)
I LOVE your videos! You make everything so concise, easy and fun
You are welcome for the concise, easy and fun Excel, Risha!!!!
Thanks Mike....you're doing a great job!!!
Glad you like the vids! Thanks for the support with your comment, Thumbs Up and Sub : )
very nice sir each and every your notes files so usefull for all love you sir ...
Thanks for the love - I am glad that it all helps : )
you're awesome, prof!
Go Team!!!!!!
Very Good sir your teaching is amazing.
Glad you like the videos and teaching, Shiva!!!!
Teaching is the greatest act of optimism - Colleen Wilcox
with your voice Mr. Mike , Excel is fun indeed . thx
Hi Mike Thankssss a lot for this great reviewww in excel it make us more perfectionist your are awsome I really like watching and hearing you teaches Excel you are the best.
You are welllllllllllllllcommme!!! Thank you very much for your kind words : )
Yet another EXCELlent video. Thanks Mike.
You are welcome : )
Thank you so much for this slicer thing... My life is much more easier now... 🙏🙏🙏
You are welcome! Slicers are so easy. Thank you for your support, anubhav, with your comments, thumbs ups and of course your Sub : )
very clear explanation, thanks so much for doing these videos!
You are welcome, Wayne!!!
heres my notes from this video for others to view:
I took notes using Online Excel in browser which has slightly different access to features/tools
keybind: alt + n, then alt + v = create a pivot table. useful for when you create pivot tables at a high frequency
Why is a PivotTable called a "pivot" table? because you can easily change it on the fly with the click or drag of a field, drastically changing how the data is represented
cross tabulated report - any intersecting value shows by adding with any two conditions
right click pivot table a number value in pivot table -> summarize value by... avg, sum, count, etc. this is a great quick way to change the data's representation for your needs
how to tell if you have a large dataset? try this keybind: ctrl + downArrow. this brings you all the way down the table until you hit an empty cell, then you can check the row number on the left
to increase a column/row's width/height, click the first column/row you want to select, drag select across, then do the click n drag to increase or decrease
to change the design of the pivot table, click Design (or in Excel365, PivotTable), then find the styles ribbon to select/customize a style
to filter a pivot table by a certain value within a field, like Sales Rep for example, go to pivot table's Field List, drag Sales Rep in filters, click the drop down for Sales Rep and select "Add as Slicer". From here you can choose a sales rep to filter by, and you can also select the multi-filter button at the top to select multiple sales reps to filter by.
OR
you can go to ribbon tab Analyze (or "PivotTable" for excel365), click "Insert slicer". from here you can do the same thing as stated above, but its a little easier to access and use.
right click slicer pop-out and click settings to customize it
when right clicking a row or column, you can insert rows/columns above/below || left/right
A+ grade on your notes : ) Thanks for sharing with the whole Team!!!!
@@excelisfun no no, thank you. I didn't think learning excel could actually be fun, but you make it fun! I really appreciate your free education, you are doing a fantastic service.
Thank you, sir. You are magnificent.
Glad it helps, TexMan11!!! Thank you for your support : )
God bless, you have made it too easy to learn
You are welcome for the easy and fun : )
Thanks a lot. I learn a lot of from your online class.
You are welcome! Thanks for watching and thanks for your support with comment. Thumbs up and Sub : )
It' possible to add slicer in pie chart to make the pie chart by pivot table. I add slicer but when use percentage show 100%. Can you help me?
Just brilliant!!!
Glad it helps!!!
great video. thanks for the upload.
You are welcome! Thanks for the support : )
One more time, thank you, great videos.
You are welcome! Your comments on each video are greatly appreciated, daries : )
Really awesome videos! I have learnt so much. Thanks a lot!
You are welcome, Yash : )
I enjoyed following that - I think I learned something too!
Learning something is always EXCELlent! Thank you for the support with your comments, Thumbs Up and Sub : )
wow amazing again. Every video is very informative keep up the good work!
Glad the videos help, Playlist Yee!!!!
Absolutely great video, you are awesome teacher! Thanks for homework files, really helps to learn the formulas and shortcuts!
You are welcome!!!
Thank you for another fantastic video! I can definitely use this to spruce up some of my spreadsheets in the office.
Sprucing up is good ; )
Thank Sir . Very good video . May GOD bless you.
You are welcome, JOHN SOE : ) Thanks for your support on each video you learn from with your comment, thumbs up and of course your Sub : )
Thanks for this video
You are welcome! Thank you for the support with your comment, Thumbs Up and Sub : )
Exceptional trainer!!
Glad it helps, Darin!!!! Thank you for the support with your comment, Thumbs Up and Sub : )
the best education i have ever seen
Thanks for the kind words, lucija!!!! Thanks for your support with your comment, Thumbs Up and Sub : )
Thanks Mike. Very helpful.
You are welcome, Karen! Thanks for the support : )
Using slicer has been such a joy
O, Yes! The simple joys in life: Slicer in a PivotTable : ) Thanks for your support, rc, with your comment, Thumbs Up and Sub !!!!
Love it! Thanks for the video, Mike!
You are welcome, Frank!!!! Thanks for the support : )
Thank you Mr. Mike the video is really useful
You are welcome for the useful video! Thanks for your support : )
ExcelIsFun I'm the one who should thanks u Mr. Mike you are helping us to be great in our Jobs
Bless you! 🙂
Thank you!!! ❤
You are welcome!
it feels as if your my best Friend who make learning alot easy and fun
I am here to teach and help you with Excel, nitesh!!!!
@@excelisfun thanks man !!😊
Excellent!
Glad it is EXCELlent for you, Yew!!!!
Things are getting exciting.
simply incredible please upload more ExcellsFun Python, SAP, C++, ERP, Payroll, Invoicing, etc. videos
Simply amazing.
Amazing, thank you so much!
You are welcome so much! Thank you for the support with your comment, Thumbs Up and Sub : )
Pivot Table is really powerful.
Yes, there are few other tools in the world that help people to easily summarize data to make calculations with criteria than the PivotTable!!!!! Thank you very much for your support, Donghua Ye!!!