Excel Basics 20: PivotTable Power: 14 Amazing PivotTable Reporting Tricks: Slicers to Show Values As
HTML-код
- Опубликовано: 16 ноя 2017
- Highline College Busn 216 Class: Computer Applications taught by Michael Girvin.
Download Excel Start File: people.highline.edu/mgirvin/Y...
Download Excel Finished File: people.highline.edu/mgirvin/Y...
Download Full Written Notes: people.highline.edu/mgirvin/Y...
Class web page link: people.highline.edu/mgirvin/A...
Topics in this video:
1. (00:01) Introduction
2. (01:51) Review of PivotTables we have already completed in this class.
3. (03:15) What is Data Analysis in one sentence
4. (03:34) What’s is Proper Data Set in Excel?
5. (03:50) Example 1: Use VLOOKUP to Add New Fields/Column to the Data Set:
6. (06:50) Example 2: Create Country & Product Category Average Revenue Report
7. (08:14) How to interpret Criteria/conditions/filters in a Cross Tabulated Report
8. (10:00) Indicate Until with Label, Not Number Formatting
9. (10:32) Example 3: Change SUM Function to Average Function
10. (11:34) Example 4: Change the PivotTable Style (If you want)
11. (12:33) Example 5: Name PivotTable
12. (13:23) Example 6: Group Daily Dates into Months and Years
13. (16:54) Example 7: Add Year Slicer to our “Country Report” PivotTable and format the Slicer
14. (18:18) How to Interpret Criteria / Conditions / Filters from Rows area, Columns area and Filter or Slicer area in a PivotTable.
15. (20:00) Example 8: From "SalesData" Table, Create Product Revenue PivotTable Report with Multiple Calculations in One PivotTable
16. (22:08) Example of Text Field in Values area of PivotTable
17. (23:25) Example 9: Connect Slicer to 2 Pivots
18. (25:00) Formulas to Test Show Values As PivotTable Calculations
19. (26:30) Example 10: Add a “Show Values As” “Difference From”
20. (29:40) Example 11: Add a “Show Values As” “% Difference From”
21. (30:22) Example 12: Add a “Show Values As” Running Total”
22. (32:22) Example 13: Count of Phones Purchased Report & % Phones Purchased Report
23. (35:21) Example 14: Show Values As % of Row Total: Does CPA Prep Course Help? Report
24. (37:56) Summary - Conclusion
You are the best - I've gone through 10 videos without falling asleep yet! :) RUclips should be offering awards for people like you. Fantastic knowledge based videos bettering the work place! Instead they give it to kids that seem to shout about some games and opening some new tech
Ditto - this guy definitely deserves an award. An amazing public service! Wow!
Your videos were highly recommended by my boss. I had no idea excel was this kind of tool. Soon she wants me to start pivot tables so here I go.
This is a good video for you with most of what you might do with a PivotTable. You will do great : )
I like your slow and emphasizing tone!
I am glad that you like it! I try to make videos for beginners so that everyone can gain the power of Excel!!!! Thank you for your kind words and thank you for the support with your comments, Thumbs Up and Sub : )
Spending my quarantine time with your amazing classes! Thank you so much
Glad this down time is turning tp tome, especially with the power of PivotTables, Nathalia!!
Simply the BEST :) Thanks Mike for all the effort and the hours you put in all the videos, so guys like me learn more and more.
You are welcome, John!!! Thanks for your continued support : )
That is without a doubt the best video tutorial about pivot table. You are an amazing teacher!
Thank you for your kind words! And thanks for your support : )
Mike, thank you for this powerful knowledge you're offering for free with passion. In fact, attending college classroom is a big waste of my time and money! You have really taught me excel in a way no college has done. All that I know in excel, I owe it to you. You're truly the best.
You are correct: most matriculating college/university Excel classes are a waste for two reasons: 1) most college Excel classes don't teach what is really used in the working world, and 2) most matriculating students don't care. I am glad that you found this and that you care : )
I took a break to mow the lawn and had the next video loaded. While mowing, I just kept thinking, "can't this mower go faster?' so I could get back to the learning. You have a gift.
I love that story!!!!! So glad to help you have fun with learning. Mowing is fun, but not as fun as Excel : )
This is the second time I watched this class and I understand better and got ideas to apply to personal projects. Thank you Mike
you have a talent, you can teach, not a lot people possess that talent, enjoy it.
Thank you for your kind words!!!!
Thanks Mike I find your videos a godsend keeping me sane in my IT role. Amazing how many people don't know the basics of Excel...
I am so glad that the videos help you stay sane in your IT role! Thank you for the support with your comment and thumbs up on each video that you watch : )
Thanks for putting in the timestamps in description and doing the recap at the end of each video. Your organization makes it easy to come back for reference.
You are welcome! Yes, the time hyperlink below video helps a lot, and also the Excel Workbook download and even the pdf notes that summarize the whole video : ) Thanks for your support of the excelisfun channel with comments, Thumbs Up and Sub : )
Mike, Thank you so much for these awesome videos. I am binge watching your videos more than Netflix these days and able to accomplish so much more at work.
Excel is more fun that Netflixs too! Thanks for your support on each video : )
Knew most of it, but the last bit where you pull down the revenue several times and use it in different ways was a new thing, thanks Mike!
You are welcome, Rajan : )
Great video. Learned A LOT and appreciated the review. Will certainly be going back to review (and your notes are SO helpful!). Looking forward to the next one!
Glad the Pivot Fun helps!!! It is the #1 data analysis tool in the world : )
Thank you
For your time and effort
Best excel teacher ever 😊
You are welcome!!!!
Superb & relaxed , humourous & stress-free outlaying, the Excel-layouts, Thank you ,Sir! I have become your "FAN" - for all your videos.
Glad you like the videos, Krishna!!!
Amazing video. A very easy way to understand how Pivot Tables work. Thank you.
Many thanks, Mike! Another great lesson! The only thing I could not understand was how you put total numbers under each year in the Pivot Tables in HW1. In my Pivot Table I had my grand total but for both years, couldn't figure out how to do that to also show totals for each year separately.
Thank you for today's lesson
like like like once again mike! thank you for the energyand passion!
You are welcome, welcome, welcome, Idan!!!! Thanks for your support with your comment, Thumbs Up and Sub : )
So much fun to learn through your videos! Thank you again!
Glad it is fun to learn here at the excelisfun channel, Debry!!! Thanks for your support with your comments, Thumbs Ups and Sub : )
Thank you for great video (as always!) :)
As always, you are welcome and thanks for your support!!! : )
I was really fearful of learning excel on my own, especially since I have no one other than online videos to guide me. I'm so glad that I found your channel, you really help me make this learning journey enjoyable!
You are welcome!
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. You are the best. Thanks a lot to keep love and patience in your teaching field.
Amazing !!
Thank you for your effort to make awesome videos. You are absolutely an expert! I Hope more professors like you in the class. Your explanation so clear and easy to understand. I love every video of yours!
You are welcome so much! I am glad that you love the clear videos : )
Wow!!!!!!!!, how many things We can do with Pivot Table. Thank you Mike.
Great watch, thanks for the tutorial!!!
You are welcome! Thanks for your support with the comment, Thumbs Up and Sub : )
absolutely amazing thank you so much it's really so powerful
Yes, Power!!! Thank you for your support : )
مجهود جبار يستحق التقدير والاحترام معلم فاضل كريم جدا فى تقديم العلم اتمنى لك كل التفوق والتقدم انت افضل قناة على الاطلاق فى تناول موضوعات الاكسيل تحياتى الحارة لك استاذنا الفاضل من مصر لم اجد لك مثيل
GREAT WORK SIR.
Thank you SO much for your awsome videos as always!!!
You are welcome So much!!!
Your channel is amazing!! Thanks for share your knowledge.
You are welcome for the shares! Thanks for your support ion each video : )
thanks as per usual, superb!
You are welcome! Thank you for your support!!
Thank you very much sir for great video!
You are welcome!
Thank you so much for your RUclips PivotTable Lessons
You are welcome so much! Thanks for your support!
Thanks for expanding my knowledge of pivot tables. This course is invaluable.
Glad it helps, Scott! Thanks for the support with your comment, thumbs up and of course your Sub : )
great video. felt a reall got a basic undertanding of the Pivot Table. in about a week I'll hope to have an andvanced understanding of it :)
Great to hear that you are having fun with PivotTables : ) Thanks for your consistent support : )
Great video👍👍👍👍
You are the best Excel teacher in the universe！
Thank you for the kind words! Thanks for the support, liao, with your comment, Thumbs Up and Sub : )
Great stuff! Thanks Mike :-)
You are welcome! Thanks for you support!
Thanks for sharing with us!
You are welcome for the share, cvelez3284!!!!
Learning so much from your videos, so thank you so much for sharing your knowledge. I will keep up supporting and sharing this videos with friends :)
You are welcome! Thanks you so much for your support, Doris!!!
Luv to watch your vedio 😍
thanks so much for this video, amazing
You are welcome so much, How about No!!!!!
Thank you!
: ) : )
you are the best and your are the Excel guru. I would like you to make videos on Duty roasters with conditions.
this dude is a blast yooh thanks man!!!
You are welcome!!! Have fun and learn Excel : )
Wish to have a teacher like u in every field. Thank you for making excel more fun☺
You are welcome for the fun : )
Thank you very much. You are great.
You are welcome very much, Chamchijjige : ) : ) : )
You have the best voice!
Thank you for your kind words and support : )
Of course.
I'm learning so much - including how much I don't know! But I am finding the answers here! Thank you, sir! Are there, by chance, any additional notes for this class aside from the beginning and ending spreadsheets (as there normally are with other videos)? Thank you!
Excellent
Fabulous!
Glad it is fabulous for you, stephanie : )
This is simply incredible work Mike. I can't tell you how much I appreciate you sharing all of this valuable information. You are an excellent teacher, and this could be a model for education moving forward.
Thank you.
Glad my classes help, Daryl!!! I do provide free education, and that is my mission, but now I just need to get more people who want this to find it...
Thanks for you for sharing your amazing knowledge your videops are helping me a lot to grow in my professional carrer
You are welcome for the knowledge and help! Thanks for the support with your comment, Thumbs Up and Subscription : )
thank you very much
master of Excel
You are welcome very much, En A!!!!
Tanq Sir once again for sharing the knowledge with us. Great Video!! :)
You are welcome, Bharat!!!
great job sir, thank you
You are welcome : ) : ) : ) : )
Had a good laugh while you were criticizing the PT Designs haha! Great Video!
Good laugh and fun with Excel - can't beat that : ) Thanks for your support, Yashvardhan!!!!
Thanks mike!
You are welcome! PivotTables are power : )
thank you for this
You are welcome for this!
Thank you Sir. Thank you very much
You are welcome very much!
your videos are mesmerizing
Glad that they help you, Syed : ) Thanks for your support on each video with your comments, thumbs ups and of course your Sub : )
Another great video on Pivot tables. I wonder how much is there to learn in excel. But anyways I have ExcelIsFun for me to help!!!
There is a lot to learn, but the foundation is in this Excel Basics. After this class you move on to my advanced class, and then you will know all you need to do almost anything : )
The first to make thumbs up woww :-) very good wrap up
Thanks for your support, Mohamed!!!
I feel more comfortable working with pivot tables after watching this video.
That is great, Edya!!! PivotTables offer great power to us Excel users : )
Thank you, sir.
You are welcome!
The count of product at 22:43 is a revelation!
Yes!!! Draging a text field to get a count is very nice : )
you are amazing
Glad the videos are amazing for you, Abdullah!!! Thanks for the support with your comment, Thumbs Up and Sub : )
ARG Argentina (Repúbñica Argentina)...Greetings from Argentian!!!!
Of course that is what it means. How did I miss that ...
Thx 🙏
You are welcome, Lalit!!!!
exellent
EXCELlent!!!! Thanks for your support : )
Excellent vedio sir
Glad you like it, Lalu!!!!
@@excelisfun Excel is like an ocean and you are feeding us in a beautiful manner ...the concept of excel working file and the homework is very much useful to practice subsequently...
as wonderful as always. You are God!
Just a guy having fun with Excel ; ) Thanks for watching and supporting with your comment, thumbs up and Sub, Nima!!!
My Excel God!
Glad the videos help, Anjan!!!!
Mike, Following this and all info has been great - I hit a snag wondering if you can create a custom column in a pivot table using a count if function? I want to count all cells that equal 0.
If you want to use a Standard PivotTable, which we are using here, one good way to do this is to add a Helper Column with a logical Formula like B2=0, where B2 is first cell in column with numbers, then use that column in the Values Area with the COUNT Function AND that same column in the Filter Area and filter to TRUE. There are other ways to do this like a Power Pivot Pivot Table and a DAX Formula, but that gets a little complex.
These excel videos are great. Is the class notes removed? I do not see the pdf notes file for this lesson.
Thank you for this great video! And I thought pivot tables were useful before...
You are welcome, Nyuklehead!!! PivotTables are power!!!
I like and comment on your every Video Before watching it Because i know it's gonna be very good video.
Thank you very much! I really appreciate it and it does help to support the channel. I just wish more viewers were like you : )
I changed my Profile do you remember my name ???
No!?!?!? Who are you, you most kind and helpful person?
I comment on your Other videos and you replied. so i thought you remember my name. kz i changed my profile picture a 3 or 4 days ago ....and i'm your viewer ....😊😉
Thank you so much for viewing and being part of our Online Excel Team!!!! Thank you for your continued support : )
Super Sir, U r a genius.. Thanks for the lectures sir.. Please include more pivot table topics for accountants.. U r indeed doing a great work.. Much appreciated.. Could u pl provide me the sequential list of pivot table lectures created by you. Providing link will be useful as all the videos provided by u r juggled.. Sequential link of pivot videos would be really useful.. Thanks in advance...
Glad you like it! Thanks for your support with your comment, Thumbs Up and Sub. I already have 100s of Accounting PivotTable videos. Here are two playlists:
ruclips.net/p/PLrRPvpgDmw0ndqetqYG0ocKrr-3FvouJ-
ruclips.net/p/PL3C9D76E9A2F51440
Please be sure to click Thumbs Up and comment on each video to show support and help promote the videos so even more of our Online Excel Teammates can see the videos : )
UyhhuyhOsks
Who are the people that did not like this video?
Thank you for noticing and say that!!!! : ) I totally wonder my self... I give so much for free, and someone has the nerve to click Thumbs Down? Well the good news is that it is on them: they have to live with it.
They're ungrateful, or ignorant, or rude - any of which, as you say, they'll have to live with the consequences of if that is how they keep going through life. I think about the amount of organisation, planning and work that goes into making these videos and the supporting materials and it is Herculean. Almost unbelievable. I spent enough years at Uni to get a masters degree and I've never had a lecturer put this much effort into any paper I've even taken. And I've had some pretty awesome lecturers!
For someone to give this to the world for free deserves nothing but gratitude. I find it hard to believe someone could really dislike it if they recognise what it is. Even if they don't benefit from it themselves they should realise how greatly it is helping countless other people and be glad of that. I am. We, the countless grateful, thank you. :-D
@@excelisfunyou are just an amazing teacher and an amazing person... I'm an accountant, I've learned so much from your videos, I use excel so often and your videos facilitated and improved my work so much and I use excel greatly in everyday life as well. I can't even find the right words to express my gratitude. long time I could not afford any excel course, but after I found your channel I realized that I have learned 1000 times more than I could learn at any course and now I wont even take any course because I know for sure that I can not find the better teacher or syllabus like yours. I wonder if you have some sort of donation system... and if not ... please make one ... because sometimes I got the sense of guilt that I'm learning so much for free... P.S and its WORLDWIDE ... I'm from Georgia (the country not US state :-) )
@@constantine8601 , I am so happy to hear that the material that I post helps!!! : ) I am happy to provide the free resources to the world. The #1 way that I ask humans to help support is by clicking Thumbs Up and commenting on every video that they watch. So please help support in that way. If you would like to make a donation, there is a paypal donation at my about page, here: ruclips.net/user/ExcelIsFunabout Thanks very much : )
@@excelisfun Great, I will gladly support both ways
Great tutorial. Is it possible to have a slicer linking to to two pivot tables linked to two separate data sources?
With Two Data Sources, I would use Power Query to combine and maybe Data Model with relationships (next video in this series). I have multiple videos about multiple tables and Power Query here: ruclips.net/p/PLrRPvpgDmw0m3ohSvgwoHvd0KO8QsQdiK
It looks like you did not click Thumbs Up. Please support this channel with Thumbs Up : )
ExcelIsFun thank you sir!
@ExcelIsFun Where does your data come from? Do you create it yourself or is it from somewhere? How do you get or make it?
I create it all with randomizing Excel formulas. Here is one video about this:
ruclips.net/video/V4JciRG3ZqY/видео.html
Thanks for your support with Thumbs Up and comments!
Sir, 1or 2more sheets different headings from one pivotable it is possible in excell?
I usually do your HW assignments on a PC but I am on vacation and I have my MacBook with me. When I tried to do the pivot table on the Mac, it list each date individually. How do I get the pivot table to show month and year on the mac?
I have zero idea. I have not used a Mac in years... As has been mentioned in the videos and in the comments for the last couple of years, Mac Excel is no longer a legitimate business tool because Microsoft stopped adding the new analytic tools to Mac Excel for about 5 years. For example, Power Query and Power Pivot and Data Model and Relationships and many more have been added to PC Excel, but not Mac Excel. I don't know why Microsoft decided to do this, but as a one time Mac Excel user (I ran my entire business with Mac Excel in the 1990s), it is really a sad fact : (
Show less
REPLY
ExcelIsFun Thank you for your response. Do you know if this feature is available on the web version of Office 365? If so, is it dependent on which browser you use?
I do not know for sure. The web version has even fewer features than the Mac.
Thank you for getting back to me I appreciate your help.
Hey #mike at 28.56 Why does the cell doesn't show any value after doing different form. if this will happen aren't there will be mismatch in data??? and thankyou for this course again
28:56
Because There is no month before Jan 2016.
@@excelisfun ok yes as it's sees it's previous month that's why
Question, when I do the setup in PT1, I cannot seperate the country and products next to each other. the products is below the country and I have tried everything I can think of. I checked the finished version, and it is the same. Why? and how do I fix it?
Cancel I fixed it. country field settings layout and print and show labels in tablular form
For some reason it doesn't allow me to download the files from your link ... it returns a blank page with a 403 Error, saying I don't have permission to access the resource. HELP
I couldn't find "Show Values As" when right click. Is this normal for Mac version? (I'm using Office365 on Mac)
I am sorry to say, but Mac Excel is totally different than Windows Excel. This has always been the case through history, but it got worse about 10 years ago when Microsoft stopped adding many of the new features to the Mac Excel : (
Like you, I used to be a Mac Excel Guy, and loved my Mac, but for business (almost all businesses in the world who do analysis in Excel use Windows Based Excel) and because I wanted many of the helpful new features, I had to switch to Windows Based Excel.
@@excelisfun I see. Thank you very much for your reply. Still..at my current level, your teaching has helped me greatly. Keep up the good work. Cheers from Thailand :)
@@nobi69 You are welcome!!!!
I'm trying to do this using the free online version of excel therefore a lot of functions aren't available like this group by month. Can anyone confirm that's the case? I tried looking but can't seem to find this option.....
Yes, Online Excel has about 1% of what the real Excel has.
@@excelisfun I was going nuts trying to find it, thanks for confirming! Also have you made a video with the new xlookup function in conjunction with tables and pivot tables?
@@techclassy9977 I have many on that topic.
The most complete XLOOKUP video (but it was made when function was in BETA and did not have the [if_not_found] argument): ruclips.net/video/WO6Kjba5EPc/видео.html
A video that specifically shows Excel Tables, XLOOKUP and PivotTables is here: ruclips.net/video/LpvCj9vCtRA/видео.html
@@excelisfun thanks I'll definitely check it out!
@@techclassy9977 : )
I cannot download files anymore. Does anyone else have the same issue?
The college servers are down. They are trying to bring the servers back up, but it has been difficult. The servers will be back up, but we are not sure when.
@@excelisfun I see, thank you for your answer. I would like to express my appreciation for your awesome teaching and sharing your knowledge for free. I learned a lot from this basic course."
+
All the way up to 20!!!
Argentina i mean...
Of course!!!