Advanced Pivot Table Techniques (to achieve more in Excel)
ฝัง
- เผยแพร่เมื่อ 8 มิ.ย. 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
In this video you'll learn the Top 10 Advanced Pivot Table Tips, Tricks & techniques for Microsoft Excel. PivotTables are one of Excel's most important tools. They help you to automatically summarize and aggregate data. This way you don't have to write a single formula. The Pivot table does the aggregations for you.
⬇️ Grab your Pivot table sample file here: pages.xelplus.com/pivot-table...
What You'll Learn:
▪️ Field List Customization: Learn how to adjust field list settings for better organization and efficiency.
▪️ One-Click Multiple Reports: Generate multiple pivot reports for different data categories with just a single click.
▪️ Drill-Down Feature: Uncover the hidden details behind any number in your pivot table with a simple double-click.
▪️ Data Bars Integration: Enhance your pivot tables visually by adding dynamic data bars.
▪️ Calculated Fields: Create and manipulate custom fields in your pivot tables to analyze data more effectively.
▪️ Column Differences Calculation: Easily calculate and compare differences between columns in your pivot tables.
▪️ Smart Custom Number Formatting: Apply custom formatting to display data more intuitively.
▪️ Custom Groupings Creation: Organize your data into meaningful groups based on your criteria.
▪️ Date Grouping Customization: Tailor how your dates are grouped in pivot tables to match your analysis needs.
▪️ Timeline Addition: Add interactive timelines to your pivot tables for easier date filtering.
I'll show you some Excel tricks that you can use in Pivot tables, such as using conditional formatting in a pivot table. How to drill-down and view details of a value; How to generate multiple pivot table reports with one click; how to use custom number formatting to get emojis and icons in your pivot table and how to add a timeline to your report.
Aside from using pivot tables to analyze your data, you can use pivot tables to create interactive dashboards.
00:00 Advanced Pivot Table Tips And Tricks You Need To Know
00:30 Create a Pivot table
00:54 Adjust Pivot table Field List Settings
01:25 Create Multiple Pivot Reports With 1 Click
02:37 Drill Down in Pivot tables For Details
03:06 Add Data Bars to Pivot Tables
04:29 Create Calculated Fields
05:36 Calculate the Difference Between Columns
07:00 Smart Custom Number Formatting
08:15 Create Your Own Custom Groupings
09:23 How to Group Date Fields
10:17 Add A Timeline to Your Pivot Table
11:12 Wrap Up
Learn how to format your source data and create your first Pivot Table in this video:
► Pivot Tables for Beginners: • Excel Pivot Table EXPL...
________________
➡️ Join this channel to get access to perks: / @leilagharani
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel
![BCBA® Exam Practice Questions | Behavior Analyst Exam Practice Questions | [Part 131]](http://i.ytimg.com/vi/S4j-w8Qsaw0/mqdefault.jpg)
Download the Pivot table sample file here 👉 pages.xelplus.com/pivot-tables-advanced
I started a new job a month ago, and because of you, I am WOWING my new boss from watching all of your excel videos. I had never used pivot tables before and he thinks I'm a genius! LOL THANK YOU!
That's great! Good luck with the new job!
@@LeilaGharani i
That's so awesome! I too, recently started a new job requiring alot of Excel. I was honest and upfront letting my boss know I was more on the intermediate side vs advanced level but very willing and very much wanting to learn more. To Excel at Excel ... but I have a question.... if you happen to see my response???
@@cmelfe5888 Sure. I'll do my best
good luck and try to watch all Leila`s videos to make the the spreadsheets easy for navigating through
Leila, I have to say that I find the way you teach, talk and explain things absolutely perfect. I find most tutorials online hard to follow and/or not explained properly. But the way you teach, I feel any user of any level of expertise will both benefit and learn from. You don't rush, you are soft spoken, you move at a perfect pace and I find that I look forward to learning more from you from your other videos. I just wanted to congratulate you on a job well done and I thank you for putting these videos out here for others to learn from. Thank you again.
Hey, can u please help me with this question...
Imagine u r working on a data set that has 20 Call quality Parameters arranged vertically in 20 columns, with 250 entries, from 20 Agents who report to 5 TLs who in turn report to 2 Managers. In four steps explain how you present manager wise Data & TL wise data seperately.
Please do help, it's really very important for me!
I agree 100%
@@naveedwantt2846 This is what ChatGPT says about your question:
1. Sort the data set by Manager and TL columns, so that all the entries for each Manager and TL are grouped together.
2. Use the "Group" function to create separate groups for each Manager and TL.
3. Use the "Sum" function to calculate the total for each Call Quality parameter for each group.
4. Use the "Pivot Table" function to create separate tables for Manager and TL wise data, with the Call Quality parameters as rows and the Manager/TL names as columns.
I have been using Pivots for ~7 yrs now and i can assure, these particular tips can help you do almost anything and everything to summarize data with pivots!
Found the best channel for Excel formulas understanding... Love from India ❤
Why the 91 dislikes? So much time and effort has been spent to produce a professional video that has, from the comments, helped a lot of people to gain a more advanced understanding of pivot tables! Plus, it's totally free!!!
I couldn't agree more!
Haha this was epic. I do a LOT with pivot tables so I wasn't expecting to learn so much, but it turned out I only knew 3 of the tips. Way to go Leila! Thanks for the killer videos for us geeks.
Haha, happy to help :)
If School had more teachers like you, the world would have been a better place :)
Thank you for creating the content and sharing with everyone. Your videos are always clear and practically useful.
There is not one single video that I have watched from your channel that has not taught me a half dozen things that I was not even aware of! I am so appreciative! Great, great, great channel!
The insert timeline part is gold. Thanks!
Our pleasure :)
Hi Leila, hands down, one of the best videos concerning pivot tables that I have seen. You Rock! Please keep sharing your knowledge with others.
Thank you, I got hooked on your training, started with Udemy, now added youtube snapshots. Absolutely brilliant, a mine to explore - makes learning fun and fascinating. I always get extra bits form your training. Wow! X
I particularly liked the use of symbols within custom number formats - new to me, and of much wider use. Oh, and the grouping, but the whole video is excellent.
Glad you liked it, Henry!
Hey, can u please help me with this question...
Imagine u r working on a data set that has 20 Call quality Parameters arranged vertically in 20 columns, with 250 entries, from 20 Agents who report to 5 TLs who in turn report to 2 Managers. In four steps explain how you present manager wise Data & TL wise data seperately.
Please do help, it's really very important for me!
If you think you know it all, Leila will show you a new horizont
😊
Always true 😄
Absolutely!!
@@LeilaGharani can i send an emai? I need some advice from you please .
I hope this is limited to Office products
Leila - not only am I so very grateful that you share your knowledge 3 of these functions I had no idea I could do, and I've already made changes to Pivot Tables I use daily. Thank you so much for intelligent well planned and informatively helpful videos. Subscribed and liked !
No better excel instructor than you on youtube. You actually changed my professional career. Big thank you
Thank you so much for watching and supporting! I’m very happy the tutorials had a positive impact for you.
This is AWESOME!! I use excel a lot and pivot tables but was not aware of all these options! Great - thank you!
When you think you know it all about PT and 90% of the tips show totally new things that are not present in huge books! Great great material Leila! you are AWESOME!
Agree !!
@@vinayramachandran4992
It is super lesson to extend knowledge of pivot table. Dear Leila, thanks you.
Superb information you've got Leila, really maximize efficiency. Good work!
Glad you think so!
I always amazed by leila's creative ideas and thought process.
Thank you 😊
I want to like this more than once! Love Love Love pivot tables!! Thanks for all the great Excel videos. I am teaching a Pivot table class next week and wanted a few more tips to share with the audience and you have provided some great ones here! I love teaching excel and I love that when I prepare for a class I always end up learning more myself!
I love tha fact that your videos are short enough and yet so full of information. thank you for doing this.
Glad you like them!
Upset that there's no button for heart. I loved this vid! Super helpful!
Three things. 1) Your videos never fail to impress. 2) Your videos always keep us engaged through out. 3) Authenticity of your knowledge sharing is unparalleled.
Aww, thank you Sachin! You're too kind!
Thank you, Leila! I'd love to take a whole course on creative dashboards based on advanced pivot table table usage. Maybe you have enough projects to combine into a course for us? 😉
Wow, your videos are such an eye opener, just realised I have been usine Excel at max 5%
I found this video very helpful. I had been doing my column formatting using Value Field Settings. I had no idea that you could format using a right click in a column. I think you saved me at least 5 minutes of monotony per table. That was great. I also didn't know that if you have data formatted as a table that you can just click a cell in that table to create the link. Very good!
Hi Leila, would it be possible for you to do a video on the journey you have made over the last 5 years? Your patience and resilience could be a huge lesson for all of us... many thanks
I'll think about. Thank you for your suggestion, Robert.
Leila, I would love to see a segment on using pivot tables to create graphs to analyze data.
I would love that
bump
@Leila - you deserve millions of subscribers with these videos. Thanks so much and keep up the great job!
Wow, thank you!
Thanks Leila for sharing these tricks! I particularly had a lot more clarity about the difference between Calculated Fields and Items. Keep up the excellent work 🙏
I also took this as a challenge to see if I knew all of these. I was defeated by the "create reports with one click" lol
Will give you about 90% of the credit though - all the other ones I knew I learned from your guides on different videos. As always, top quality content. My go to for everything Excel related :)
Me too. I like that function
This has given me so many ideas for some personal projects of mine. The list should have been 20 not 10!
Hope it will be helpful for your projects.
thank you Leila for your training videos so fluent and so efficient. the best optimized time for a huge added value
I learned a lot in this tutorial, Leila. I expected to pick up a tip or two, but learned so much more. Thank you!
Hi Leila. Some great tips in there.. thanks for sharing them! One I can add.. when you drill down to see the underlying details of a particular Pivot Table cell, if you want just a temporary quick look, you can then delete the created drill down worksheet with CTRL+z. Saves a little time vs. other methods of worksheet deletion such as: right click, delete, confirm delete. Thanks again for all your tips :)) Thumbs up!!
Great tip! Thanks for sharing, Wayne.
th-cam.com/video/yHzT_BUggQk/w-d-xo.html
I never knew how I would use the timeline feature. Now I see that I could use it in one of my reports. As always, Leila shows the community very useful tips and tricks that can be applied easily. I truly envy your knowledge in excel. Thanks Leila!
And she showed us how to use timeline in just a few seconds, yet it was all that we needed to know. She's a fantastic trainer!
Thank you Leila for your quick and precise tips for day to use. Especially with practice material. Much helpful exploring and learning all Excel features. 💐
The grouping by no. of days is really useful to fetch week on week data !!
Thanks a lot Leila...
You are always so clever! I've been working with Pivot Tables for years and didn't know about a 3rd of what you presented. Probably should mention that when you double click on a cell in a pivot table you will only get the first 1,000 rows of that detail. If your original data is > 1,000 rows of data then not all will be present when you double click on the cell.
Thanks Rob, this is called drill down...
Thanks for this important point. Actually I am facing a problem with sum mismatch between pivot table sum and original data table sum. My data table is 8000+ rows. So is this reason why pivot is not working...
@@amolj2249 You can increase the drill down to more than 1000 rows limit....please google for the solution.
Those things really come with time and on the job training but Leila is able to roll all those years of experiences into one for you, amazing !
Thank you!
Supertrick with the Smart Custom Number Formatting!! Thank you for this detailed report Leila!!!
Another incredible presentation. Thanks, Leila.
The one extra tip I would add is, when inserting a pivot table check the "add to data model". This allows you to write Dax measures.
Selecting data model also lets you do distinct counts. Very useful if you have duplicate items in a column but you want to count each item only once.
Another advanced pivot table technique to cover is report connections:-
Use case: add report connections to the timeline so that all connected reports will be updated with the same timeline.
This works with all pivot table slicers too.
Thanks for sharing!
I have not seen any quick informative video like this anytime in my 7 years of professional career ! Thanks a ton !
Thank you Leila! Your video is extremely helpful! I enjoy watching and learning the tricks that you shared.
When i get notification from my mobile, directly i know that comes from Leila, 😁 all others blocked directly 😁
Wow, thank you for your trust.
When Leila challenges to comment "if you knew all these write in comment means you wouldn't know all." I was beaten by icons.
😁
Win+dot made me failed as well on this challenge 😀
All of your videos are very useful. Their tone is simple and very understandable. Anyone get proficient in MS office just by watching those videos. Hats off for you.
Glad you like them!
Great information and fantastic presentation. Many thanks, especially for the timeline information that made payroll reports a breeze to complete.
Wow..just wow. Thank you for the clear instruction!
I enjoy listening to your tutorials. Simple and straightforward. You are good!!
You are simply Great.
تسی کریٹ او جناب ۔
Leila, I have no words to explain! Simply thank you!!! I am benefiting from much more from you
I just love your work, very inspiring, thank you!
Thank you Leila for the wonderful videos. Stay Blessed
Thanks Leila. I am very impressed. I do Mainframe coding and sometimes pull data down to a spreadsheet for analysis. Pivot tables helps a lot. I have worked with pivot tables to do drill down from click on '+' and getting results expanded.
Group data fields..... amazing tip. Thanks
Excellent, my favourite was calculating delta between months.
It is really amazing to know the utility the excel provides for users. Thank you & kudos to you for sharing your knowledge.
Leila, you're absolutely the best teacher I had ever.
Always useful. Thanks for sharing widely and freely. I know I need to take the class and practice but in the meantime your YT videos are extremely useful.
As a user of Excel and Pivot Tables for many years, you have nailed it and taught this old dog some new tricks. Thanks!
Very impressive. I have confusion about days group & stopping auto fit column. You have cleared these. Many Thanks Leila.
Leila, thank you so much for your tips and tricks. It's amazing that you know so much and you are willing to share with us. Thanks again!
Absolutely great video Leila Thanks A lot!
I totally agree with Tetle. Leila you are AMAZING!!!.
Great tips to increase productivity, definitely gonna use 'em in practice. Thanks a lot Leila!
Awesome content and great pacing of instruction. Thanks for sharing! The custom formatting tips were dynamite.
She is really amazing...thank you so much!
Great tips as always! Thanks Leila
Thank you so much for your tutorials. So useful 🙏
So helpful! your tutorial videos are amazing!
Thank you Liela for making the complex things simple to understand :)
Awesome tricks. Thanks Leila
Invaluable! Thanks Leila!
I didn't realize you can add the field the second time, that is a life saver, thank you!
Happy to help!
Thank you so much for sharing your knowledge, very helpful.
I did not know much of these. Very helpful. Thanks for sharing 👍
THANKS FOR SHARING..love it
Thank you so much Leila. Your explained makes me very clear and can useful.👍
Thanks a lot Leila, this video helps a lot for a Data Analyst in our Day to Day work activities.
Thanks! Learned new things today.
It is one of the best videos on Pivot tables I have ever watched. Thanks a lot, Leila.
Wow, thank you!
perfect, perfect, perfect-many thanks
Loved the calculated fields, thank you.
Wow! I saw many videos for pivot tables but in your video so many options you have covered thanks much for showing. I’m pretty confident now much appreciated.
Thank you so much Leila, you get improved my pivot understanding further
Brilliant video. Thanks Leila
Not only is it easy to learn but Leila Provides a free template, love it
You are sacrificing all the knowledgeable treasure of Excel on the world, in return you get the love and respect of the world. Keep getting such awards.
LOVELY TIPS AND TRICKS LIKE YOU
It is very effective. Within 10 mins I can learn so many things.
You are the perfect teacher!
Good one. I really love pivot tables for reports. This is a refresher!
Liela.. you are always amazing..and you have great sense of teaching
Easy to follow and very useful techniques.
I always learn something new from your videos