ฝัง
- เผยแพร่เมื่อ 2 ต.ค. 2024
- In this tutorial, we will learn the Pivot Table trick that will blow your mind - Year-wise Date Grouping and Report Filter Pages and Pivot table for Beginners
Download Pivot table Excel practice file - yodalearning.c...
When you have a large amount of data in your worksheet and you can’t analyze or sort the data then Pivot Table in Excel helps you to carry out the task easily and more conveniently. Pivot Tables are powerful functions in excel.
Before you create a Pivot Table you need to keep the following things in mind:
1. Your data should be in tabular form
2. The data in the column should be the same. Text and number should not be in the same column
3. No column or row should be left empty. The same was while we do AutoSum
4. The first row should contain a unique, short heading
5. It should provide details of similar records. For example, if sales are the head then it should include data relating to sales and not purchase.
6. After you do this and have your data ready in the form prescribed above you can now create Pivot Table in less than a minute.
Learn more about Advance Excel: bit.ly/35SjX1v
Pivot Table Super Trick in MS Excel | Excel Tutorials 2021
#Pivottable #excel #exceltutorial #advancedexcel
Download Pivot table practice file - yodalearning.com/youtube-case-study/
This practice file is not available even after providing email and name. For subscription only it is asking. Where is the practice file?
Once all the sheets are created, is there a way to go to a particular sheet without having to scroll through all of them?
This is guy is from another planet wooooow
I can't believe I hadn't seen this one before - good tip and even greater passion.
I love how animated this guy is.
Life is filled with monotony. I am glad I was able to bring a smile :) Thanks Regards, Rishabh
Hi
Sir, can u please help me with this question..... You have a PIVOT for a data set of the range A1: H25, will changing the value in cell B:32 change the projections in the PIVOT?
I've never been compelled to comment on any TH-cam video in my life. This is my first comment: AMAZING buddy!!! You just taught me two new things I am glad to know and am sure will be useful in the future! Thank you!!
Hi, how to convert each sheet to PDF automatically which has been automated in this video
Great trick, thank you for being so passionate in sharing the knowledge.
Yes, very true
Courageous Creator Michelle th-cam.com/video/MAjWpPwcTZ0/w-d-xo.html
Please like share and subscribe my channel 🙏😊
Right.easy explained
I finally found a teacher who doesnt make me sleep through his class.. hats off.. thanks load!
Thanks Shalima, Regards Rishabh "Football game commentator"! :)
The enthusiasm kept you on the edge of your seat :-)
That's in-depth knowledge shared with learners.
You are the best.
I wish that I had known about this a long time ago. Thanks for sharing this simple, but amazing pivot table tool.
That's a super trick!... Please keep doing more, you have served to so much people only with this one... And please use the accent of your choice, yours is fantastic by the way!
Thanks for sharing this. However, rather creating different sheets for different customers, we can use slicer option in which the similar data can be seen in one sheet by selecting the respective options.
Bro, can u please help me with this question..... You have a PIVOT for a data set of the range A1: H25, will changing the value in cell B:32 change the projections in the PIVOT?
That's great, what 90% people know is only basic pivot table. This is of immense help..
Brilliant. Just the tip that I needed. I've had many occasions where I had to create multiple pivots for the reason you describe. It was tedious and I had no idea that there was a function that would do this. My day job just got easier. Cheers, mate. But there is one thing. When creating 600 pivot tables in one document, might create too much data for the document to be properly saved?
looool , ive been waiting for this , u saved me lot of time :D thumbs up
Nice trick...I have been using Excel of versions 2003 to 2010 and till now i don't know about this...Thanks for sharing.
One small clarification...If i have to add a new customer, I can refresh the pivot table automatically but does this create a new sheet once the data is refreshed? I hope am clear in my question. If i have to use this option again, it is creating duplicates sheets. So, how can I solve this issue?
Easy way is to create a copy of any of the sheet created through PIvot Table and select the customer you want to view.
Suman Michelle th-cam.com/video/MAjWpPwcTZ0/w-d-xo.html
Please like share and subscribe my channel 🙏😊
Wow amazing ... If only I knew that before 2 years, I wasted too much time to do that manually.. big thank you.
In what scenario would you want 600+ sheets? Wouldn't it be easier to just filter based on the data you want to see at that moment?
My guess would be customers cannot see the data of the other customers. I’d have to think there’s still a better way than 600 tabs.
Wow...I just loved it. (greetings from Sweden)
Good to hear someone being so enthusiastic about an old pivot table "trick".
Most people respond like you do when they see this, but then they ask what's the point? Apart from a way to prepare distribution of specific data to the right people, there are no advantages.
Then add the fact that you must be extremely careful who you send your pivot table to because as long as it is clickable, anyone has access to the entire original table.
Amazing i just simply loved not only the trick but the way you teach is highly professional and comprehensive.
Many Thanks
kaleem Michelle th-cam.com/video/MAjWpPwcTZ0/w-d-xo.html
Please like share and subscribe my channel 🙏😊
Amazing. Im trying to figure out how to add a button that compiles an email for those records with a request email body and vlookup for the seperate email address list by vendor number.
Sir, has been a good time seen this PIVOT options..... A BIG HELLO from Colombia!!!!!
This video is the bomb
Using this trick I can do a year's work in a morning
And dynamically change it to suit interim reports
Much thanks
Yes Indeed!
Great! 😍 I was thinking that I will take admission again for advanced Excel but ap jese teacher agar youtube pe majood hun or wo bhi free of cost parhany k liye to humy kahin jany ki zarurat hi nai hai. I m from Pakistan Sir Rishub thanks for teaching us we are ur internet connected students. Thanks again . . ..
Thanks for sharing
Very useful
Thats a very good learning Thanks for the video but how do I create pages with paste values alone rather than having pivot filter option in each page, pls let me know if possible !
Nice trick. To view a particular Tab, you can just right click and select on the Bottom Left corner where you toggle next tabs.
+Swapnil Sohani Thanks :)
Swapnil Sohani
Wow man!!! This is amazing, such a time saver, just takes productivity in creating reports like those to a whole other level. My brain already went into overdrive ( a min after watching this video) thinking of all the different scenarios where this knowledge will come in handy. Thanks so much for sharing.
Thank you so much for your tutorial! And the Tips you share with us are very good!
Love the passion in your videos Rishab, very crisp and confident with a hint of humor! loved it!
Amazing- THANK YOU. Will use tomorrow. :)
+Jennifer Stockman Welcome !
Jennifer Michelle th-cam.com/video/MAjWpPwcTZ0/w-d-xo.html
Please like share and subscribe my channel 🙏😊
Awesome trick saved ton of time Rihab and I love the way you expailn
This is really cool. Do you have an updated version for Excel 2013 or 16? Or is it the same steps? Thank you very much.
Superb explanation Rishab...Keep this rolling on. God bless you
real time saver .... thanks man..
Hi, if some one double clicks on any of the pivots he could still see all the under lying data.. is there a way to ensure data for the applied filter only is displayed even if anyone chooses to double click on it
man comes with miracle excel.... tricks... called
Superman
@
@
@...
@
Today u will Know that how to make Accounting Software in ms excel
Watch complete video Very Interesting for Accountants must watch
Hai, yur voice seems 100% match with a person from Chennai
well done, but I think you don't need 600 customers to prove your point !!
LOL, my computer illiterate boss, who has no idea about pivot tables, he thinks I am a very hard and efficient worker. Expecting a promotion with a salary increase soon. Yippeeeee!!
Excel complete Training basic to advance level
For a minute ,I thought it was naptool channel😉, but a very useful info
I take it as a compliment :) Sometimes, I get a little more enthusiastic
Woo ... awesome trick
This was great, love your energy and excitement. You sir are a true MS Excel-warrior. Thanks!
I've been using excel to a reasonably capable level for years but lately, pivot tables have changed my life.
Very nice tips Sir...thank for given tip's.. Tomorrow I will be used...
One of my colleagues did this for me tbrough pivot tables and ever since I have been looking forward to this but could not find this on myself. Thank you so much. All your videos are fantastic
Oh man, I am blown away by this guy's excitement, good job anyway. Bravo
Really Really...Thank you...it is much benificial for me.
I must say you have a great Teacher.
Keep going.....Thanks again
annayya... superb... simply superb... i am basically telugu medium student from india.. just now only i saw your video and became a big fan.. i subscribed (y)our channel. thanks sir.. you explaine in a very simple english language. Anyone can understand easily.. please keep sharing.. thank you
I knew about this "trick" but haven't found it useful because it doesn't go far enough. I need to email each tab as a separate file to the customer and only include that customer's underlying data that drives their specific pivot. Don't want one customer to have access to anyone else's data but their own. Would need a macro to iterate through all tabs and copy them into a new workbook. However, that wouldn't work because the raw data tab has all customers and each pivot's data source is set to that. Would need to copy just that customers data and change the pivot data source to the the customer specific copy. That's a lot of custom VBA code. I wish there were a built in option to do this. Otherwise I don't have a use case for so many individual tabs. At over 600 tabs in your example good luck finding the one you need! (there are macros that can auto-generate a table of contents tab with links which would be much better). Easier to just use a slicer.
Hello Sir, When i right and show the menu for grouping .... when i click grouping ...the grouping box doesn't show it to me... it directly group it.... how can i activate or to show me the grouping box when i click it ?
You are very very good
Show Report Filter pages Is not highluted in my EXCEL FILE..
My offixe version is 2019
Thank You sir.
Grouping option you are referring to exists in enterprise version or is it available in microsoft profession plus 2013 too. Because I couldn't find grouping option while working on this example...
How are you going to navigate the 600+ sheets - you would almost need a contents page with hyperlinks lol Do the pivot analyze options change if you change one, does it change formatting options etc for all. I would do this for about 4 - 5 tabs to be honest
excellent. (love the suspense too!)
Hopefully it also provides a way to search and navigate to the specific customer sheets! Otherwise I guess you could devise something using VBA.
Excellent, will try and come up with results , i have 10k plus customers names with their product purchase details country wise, little confuse how to zip that data for easy and quick look.
Make the download option for practice files easier.don't coax visitors to sign up to your regular emails which I had to do here to access pivot files......It took me to a huge data base on google sheet I was perhaps not interested in.. Don't see unsubscribe button either !
Rishabh Sir... as per your accents you seems indian, Please Make videos in Hindi
सरजी
"Show report filter page" highlight नहीं हो रहा है अतः यह सेलेक्ट नहीं हो पा रहा है।
Please guide
Skumar.boi@gmail.com
Some time looks challenging when play with Pivot.. Good, Kvaabs.
Great instruction. What a useful feature. Thank you for sharing.
Trick is superb. I have a question, how to jump targeted customer sheet. It is difficult if 1000 customers 1000 sheets
Wow.... This is very useful.. Thanks for sharing this video 🙏✌️✌️
Yea, i will just filter by customer name and get report. this trick is very useful only in case i want tall criteriawise reports in seperate sheet, it will really save time. thanks for sharing, wonderful.
Sir ur video is no doubt excellent but one queries sir in pivot l can't get grand total becoz my quantity is in 23400unit...and so on.
have a dataset like this: docs.oklahomacounty.org/sheriff/SheriffSales/ (click through and select a date, doesn't matter which one) - How do I keep the data on the left column and put those into columns and then put the data on the right side (the various property details) into columns under each column?
HI , Thank you for this tip. But when it makes so many sheets for each customer, the file size becomes 31 MB. how to fix this
Excellent trick bro. I have a question. If i filter few customers. Is that possible to create only for selected customers. Please reply. It will be helpful to me.
Well good luck trying to find a particular customer's sheet among the 600 sheet tabs on the bottom!
Better to use the filter in the pivot table.
These feature exist on which excel versions besides 2016. I don't have 2016 at home.
I'm assuming that when new data is added to the source table, all the pivot tables will be updated. Likewise, if a new customer is added, will a new tab be automatically created?
it is very usefull i used and saved a lot of time, i am working now to conection the data from different workooboks in the summary one, but it isn't work very well because not all the data can transferred, can you help me?
Can i see sum and average and count altogether for a category in pivote? when we put something in value column it shows only sum, when we summarise it by count or average, it will show that, but can we see all average, count and sum at a same time?
Hello sir,
I have a report but that report does not have numeric data. All letterforms, here how can I use the group option.
All report goes in one row how to change it to column form kindly help I want to get all report in column format.
Thank you very much you, you saved me spending loads of hours working ---
Thank you cheers
Awesome commentating, got me engaged and was fun!
I don't think the super pivot table trick works on early versions of Excel though. Too bad it is a great trick!
Hey that trick was excellent but may i know was it 2016 MS Office version or which one
Super sir I have one doubt this video automatically pivot generates in separate sheets base on customer name
If I want the whole sheet data (not pivot table) automatically generate separate sheets based on customer name
It's possible sir
Please reply
You are a very good instructor; very passionate and knowledgeable! Thank you for explaining this important information.
Thanks for making
Welcome! :)
Voww. That's really new learning from you which I am gonna apply in my excel work future
This is the most important video I watched in my career!
Thank you. Do you have more advanced Excel trick which will help me to do my work easy and fast?
Super trick.
You're right - that final trick was immense!!!
I don't have the option in Pivot it is disabled, even grouping i am unable to do in my Excel , any idea why ?
Good Video and learning
Superb tricks today I have learnt from you ....a big thank you from me 🙏
Hi, Im not able to find option in excel 2016. Can you please help?
Thanks
Very good information shared thank you very much it is of great help....thank you
Nice truck..didn't know Abt it....thnks..really helpful
Thanks a lot, if only copying and pasting was that fast......making macros is.........work.
Superb trick to create the customer report when using the milestone break up for the project PDC dates.
Excelnet you are excellent, amazing trick you shared. Thanks
Amazing, I hadn´t seen before.