DON'T Make These 5 Pivot Table Mistakes

[@OhSugarwastaken]

Whoa...data model. I love this channel.

[@chandoo_]

Glad you enjoy it!

[@tonybridgeman]

Mindblown :o

[@caraziegel7652]

I'm so glad that you keep showing up on my youtube front page. I have been using pivot tables on and off for decades, and even took a college course on excel in the middle of that . . but sometimes I forget, or features change when I'm not looking. I knew there had to be a way to combine tables and do calcs and change titles, but I was too lazy to look. I love your videos!

[@maxmorris4562]

0:55 1:27: Problem: After adding people to the data, how to see it appear in Pivot table 1:45 Reason 2:12 Solution: Click any cell, Then table button, OK, Insert pivot table, Tick "Add this data to the data model" Now on new sheet: Department into rows, employer ID number to values , Now when adding extra employees and refreshing, the total will increase 5:23 Problem: How to know how many people we have got by gender or employee type Solution: Employee type to filters, those these means many clicks, Correct way is to right click on employee type, add as slicer Now we will have a floating box of filter choices 6:58 Problem: How to see against the budget how much is the actual salary we are paying and do some variance analysis Solution: Click one cell on the department and salary budget table, control T to add table On the main table, summarise with pivot, Tick: Add this data to the data model Now on new sheet, on Pivot Table fields, can see both tables on the "All" To connect: Pivot Table analyse, Relationships, new, Staff table, department column Departments table, department column Under department table: Drag department to rows, Salary budget to values Under staff table: Drag salary to values Now can compare salary to budget 9:55 Problem: Salary Variance Calculate (Continued from previous) Solution: Right click staff table on Pivot table fields, Add measure, name it "Salary Variance" In formula part: [ sum of salary budget ] - [ sum of salary ] Apply currency now ca drag fx salary variance to values

[@stopthink9000]

Data models are a game changer. It is best to know details about "single to many" and "many to many" behaviors when building relationships. It would also be good to learn about primary key and foreign key when building out your reports. The data modelling /DAX measures features also come in handy in Power BI.

[@BalrogsHaveWings]

More great tips, thanks again Chandoo! Random thought: When it comes to naming ranges, I tend to use a prefix to identify what it is... For example, a table name begins with "tbl", or a list name begins with "lst"... I find this helps me sort of categorise my ranges, search and identify them easily.

[@user-pz6et7bi5w]

I am happy to learn that I was using majority of these features on my day today work. Measures was new to me and few shortcuts are also very helpful. Thanks for this video ❤

[@awill7710]

Thanks!

[@chandoo_]

Thanks for the Super Will 😃

[@ayobnubbee5428]

Thanks Sir Chundoo, with power query, I was getting no joy, build a relationship using the graphics but on the pivot it still gave me totals on all rows. This was quicker and better.

[@salehmuhammad6811]

Great Chandoo these really helpful to whom they just think statically now they have something to go out side the box.

[@chandoo_]

Yes they do

[@Syed48]

I knew most of these tips. But i know them because i watched your previous videos :) I love your videos, small things -like your transition during repetitive steps, the sound during a shortcut etc., speaks a lot about the effort you put to make your videos interactive and succinct at the same time. Thank you, Chandoo!

[@chandoo_]

Thanks for the lovely comment Syed. 😃

[@jobazooka8634]

OMG... at 05:18... the 'banded rows' option.... I never knew this one! Thx a bunch... that's awesome! :D

[@malaninyirenda1190]

Thanks Chandoo, your lessons are always valuable to me since I started following this Channel I have improved greatly with my Excel skills I enjoy watching all your videos. Looking forward to pay for the Excel school one day.

[@chandoo_]

Thank you Malani. It is my pleasure to share what I learn with all of you :)

[@토리SJ]

You are such a talented teacher. Thanks for sharing great tips! I always enjoy your content!

[@chandoo_]

You are so welcome!

[@christopherstich1009]

I’ve been diving into measures in Power BI, but I had no idea they were available inside of pivot tables. Tip #5 will change the way I work!

[@chandoo_]

YES!!! Power Pivot in Excel opens a whole other world of possibilities.

[@sebghatulbarykhabaey6680]

Your teaching, Mike and Leila are most useful. Thanks for your kind teachings.

[@chandoo_]

You're very welcome!

[@keeloraz9452]

Yar this is really good. I knew when I found Chandoo's youtube channel it'll be awesome. Love this guy

[@terihandrick2333]

Wow I love #4 & 5 and will start using them tomorrow. You always have the best information to share and in the most simplistic manner!! You rock as always. Thank you

[@chandoo_]

Wow Teri.. Thank you so much for your lovely testimonial :)

[@hilmansyahbo5492]

Hi Chandoo... One thing I don't really like about Table, when the size of table is quietly big, the speed of Pivot Table when refreshed was impacted a lot. I prefer to use a normal range instead of table, but I used dynamic range (name range), so when the data adding, no need to change manually the source of the Pivot Table Range. One thing also I don't like about Table, when we working with macro, when the size of the table is big enough, the running speed of the macro also impacted.

[@Ohman401]

All those copy paste to next column to find variance is gone from today :) Thank you for the video👏

[@BlockFHatter]

Brilliant refresher on new aspects within excel. Chandoo is so good at communicating in an easy to understand way.

[@godxavier]

This is so useful, especially the relationship between the two tables.

[@chandoo_]

Thank you Salil...

[@chrism9037]

Excellent, thanks Chandoo!

[@ayotundeodunlami8573]

Many thanks Chandoo. Your lessons are right on point and very rich.

[@muhammadakteruzzaman2299]

@Chando you are my hero, please keep it up your good work-rest will be followed

[@vijayarjunwadkar]

Hi Five 🙌 to Chandoo, for the "five" mistakes highlighted with resolutions and it is super useful for sure! 😊👍

[@chandoo_]

Glad it was helpful!

[@AP-eb8hd]

Informative as always !! Thanks Chandoo..

[@Pawar154]

Very helpful video sir. Plz suggest do we have to have a good knowledge of excel before jumping into power BI

[@chandoo_]

Hi Jyoti.. You don't always need Excel skills to use or work in Power BI. I made a video comparing both skills for data people. Watch it here - kzbin.info/www/bejne/pIC6q6OAacyIgdE

[@surajbhardwaj428]

Pivot table makes data understandable in quick way... Today I learnt new tricks in pivot table...Thank you Sir...😀🙏

[@SashaHouseOfFashion]

Really start loving your videos...thanks for educating us.

[@quills9900]

Thanks Chandoo Gaaru ... This is fantastic , so easily explained. I had about 10 sheets (each 100 of records) with separate tables , I could manage to apply this data modelling and relationships to generate summarized reports to make few critical decisions on purchasing... thanks a lot again..

[@chandoo_]

That is AWESOME 😎

[@sp-uv6kc]

Excellent tutorial on relationships. Thank you

[@1mmightygyan879]

Thanks for showing relationship creation.

[@himajadeevi1466]

Hi Sir, could you please guide in how to connect jira with power bi using rest api and without any usage of plug-ins... that would be of great help..

[@specificgravity-thedancing9700]

Chandoo you make excel fun! Thank you!

[@mitchielicious1212]

How i wish i have known your channel before. How i wish i could hit like on all of your videos in just 1 click. All your videos are very helpful. Thank you so much

[@dhikeshmm620]

Your videos are awesome, I learned so many things than any course which I done previously....still want to know how to arrange the pivot table out put layout...

[@moutazeldeib9481]

Your lectures are always amazing, I have one question regarding the table department, the departments were inserted by using Unique formula which we can not convert it to a table. So, how you solve this issue?

[@excellaleathers5765]

Chandoo this is awesome 🌟 Combining two tables in a pivot saves a lot of time

[@saaneroa5024]

Excellent video Chandoo!

[@nirmalabolar1382]

Thank you for your valuable input explained so beautifully and very to understand, will help so many of us.

[@chandoo_]

You are always welcome Nirmala...

[@vramarathnam]

Thank you very much. Very helpful to me. Nice teaching.

[@timmytesla9655]

This is very insightful. Now, I need to learn how to use data model. Thanks for the video.

[@txreal2]

3:15 How do I sort by "Count of Employee ID Number"? Thanks

[@MarcelousTV]

You are amazing. Here I am thinking I was great at Excel. After your videos, I will be for sure! Thanks!

[@TinyMagiq]

Brilliant learnt connecting 2 table and using measures. Thank you.

[@buddhahat]

Fantastic information presented in a crystal clear format, thanks Chandoo!

[@chandoo_]

You are welcome Buddha

[@EvaDarknes]

Thanks Chandoo, You rock!

[@LizMAlex]

Thanks for sharing these. #4 &#5 for the win!

[@rmmccarthy1240]

Thank you, Chandoo!

[@TY-zl1vw]

Thank you, very informative. Certainly things in the video I didn't know before. What's the advice on whether to place multiple Pivot Tables on same sheet or separate ones ? Kind of poorly planned thing I did was I placed multiple Pivot Tables on same sheet, I end up not able to expend the groups some Pivot Tables, as they would clash into other ones. Before I know about Table feature, My way of doing it was using Named Ranges, OFFESET function. Is this kind of naïve approach now? Edit: Started watching your Advanced Pivot Table tricks playlist to finding out more.

[@chandoo_]

You are welcome T Y. I don't mind placing multiple pivots on the same page. But I do leave enough breathing room for them to grow. If you don't how big they grow to, then you can always place them in separate tabs. It's not like we pay rent to Excel for using cells anyways 🤣

[@TY-zl1vw]

Thanks for the reply and your input. I could have more questions in other areas. Would keep watching more of you videos or re-watching as needed.

[@marceljunioregondi5594]

Chandoo thank you big time am learning loads.

[@cereskinomoto2004]

i admit 5th mistake... This is very informative. Thank you..

[@ank1209]

Very informative video! This data model relationship will work if we have data in different sheets?

[@harishpoalampalli9637]

Incredible chandoo !! you are awesome !!

[@chandoo_]

Thank you so much 😀

[@kamransiddiqui2091]

I love your videos. The videos played a vital role in excelling my skills. Some time ago I watched a short duration refresher video some where it says Exam or Test. Could you share the path or upload it again. Regards always

[@onsenguy]

another awesome video chandoo. 👍👍

[@PremKumar-rf3mo]

Power query data model... My favorite. Thanks You So Much for your kind explanation... Anyway I'm so enjoyed your video.... And today I'm get what I'm looking for... Yes editing in pivot tables.. variance column... Hehe

[@thangaveluraj]

Immensely helpful.. thank you... Keep rocking...

[@tutsecret499]

Thank you very much for the data file so that I can practice it.

[@mohammedehsan5533]

Hi Chandoo, I like your videos. You are a great instructor. Could you please tell me if there is any way I can put comment in the cells inside a pivot table. Thanks

[@richaagrawal376]

I love this channel!! Thanks chandoo!! I am starting my sql journey. Can u plz share some good resources for learning sql.

[@shashirajusiddaraju4979]

Nice tips Chandoo!, Thanks

[@rajeswararaosimma7976]

Thanks for valuable class. Could you please explain that auto generation of individual list of things based on one market in one pivot table

[@Raaja_Babu]

Tum bht mast kaam karta hai chandoo Bhai..Lots of Love from India

[@chandoo_]

Thanks you Raja ji.. :)

[@mvanvelsen]

This was really great - tip 5 is something I really struggled to find.

[@chandoo_]

Glad you found this helpful. More power to you Maarten ⚡⚡⚡

[@canirmalchoudhary8173]

I learnt Power Pivot thru this video. 💪

[@dhebekmn8636]

Thank you sir you video is excellent one about pivot table. In comparison of budget details example, i got count of budget details or total sum of budget value of all department. I couldn't get individual department budget value sir

[@rcrdtlo]

Thank you Chandoo. I have tried to use the measure feature you showed, but my 2 columns in the pivot come from the same field in the table and I have 2 cols in the pivot because I have a table field as type, what do I do?

[@ksumar]

Nice work Chandoo.💯. 😊 Wishing you a pleasant weekend 🏡.

[@prashantadhiakri2964]

Please make a video in creating tools by using excel where one tool can automate your work with different sheets .

[@chandoo_]

This is a generic topic. There are many ways to do it. I suggest learning a bit more about Power Query and VBA as a start. See this video for Power Query - kzbin.info/www/bejne/hpqpcpSVn8-Ue6s and this one for VBA - kzbin.info/www/bejne/a5yQfKOJoNeFhdk

[@prashantadhiakri2964]

@@chandoo_ thank You 🙏

[@Дарія-в3к]

Hi, love your videos so much, they have a lot of usefull tips. I have a question. Maybe you can clarify, why in the example with staff, after refreshing you must have 259 employes but pivot table shows 255?

[@abhishekshukla9866]

glad to have landed on to this page

[@amanvarma8032]

Hi, Just want to know how operate Puvot with keyboard only while no use of mouse? Are there any shortcuts for using pivot?

[@mindmechanicx]

Hi Chandoo, how to automate excel to have data from master work book into different sheets.

[@jackeroo75]

Love you video editing skills!

[@ammadimam2022]

can u tell me how to keep duplicate values on pivot table like walmart on same report but have have different weight or tolerances values, when we use pivot table it adds them as 1 no matter how many times it was on report i wanna how to keep duplicates u can say of 1 date only.

[@tunbakyu]

New sub from Myanmar .. love Ur contents

[@sakeobulesu7130]

Awesome tips chandoo garu 🙏🙏

[@emoneheart436]

Wow! Thank you Very much Sir! You made my Day ❤️❤️❤️❤️

[@rajshree4635]

4:02 6:34 9:52 (UNABLE TO USE SOME FUNCTIONS DUE TO OLD VERSION OF EXCEL)

[@kiraal3619]

Very informative!!

[@FirestormT]

Awesome as always

[@avmstephen9716]

i have learned excel somewhat in depth by reading your blog , and happy to see you in here. Like excel, i have to develop my knowledge in word especially to handle a database modeling technique for paragraphs (which may contain text and images). It would be grateful if you suggest some suitable tool to handle a paragraphs through data modelling.

[@chandoo_]

Hi Stephen... good to have you here. I never did any extensive work on "text analysis" but this is an area where I will be learning some new techniques and sharing them on the channel in future. Meanwhile, I suggest using a programming language like Python or R to process text for some automated analysis.

[@hui3925]

May I know what is the differences between calculated field and add measure ?

[@u87626]

Thanks Chandoo a lot for sharing knowledge. I got a lot from your videos as I have an interest in Excel but I am also stuck with Microsoft's application i.e, Access. I am also interested in learning that application. So can you also help me or guide me to learn this one. Thank you.

[@natraj5689]

Chandon if we want to arrange the column from largest value to smallest value how can we do that in a pivot table as there is no filter in the second column how can we do that, pls guide?

[@kg.507]

Great, Thanks for this info. Really useful.

[@ytpadyt]

Can Filters depend on other cells with formulas? Thanx

[@krupanarvekar]

Thank u so much sir 🙏🙏🙏 god blessed you ❤️

[@tubeampsrule1]

After everything you've shown us, I keep thinking the best option is to use power query and power pivot for everything.

[@chandoo_]

Absolutely! That said, it is best to be flexible too. Excel offers so many ways to skin a cat.

[@Maryabril]

Chandoo, I see that all of these options we have in pbi, so why using excel? Quick note, i love your videos, just wadering thanks

[@shriram8088]

Thanks chandoo for this video💖.These mistakes we do everyday. 😀Thanks for correcting us. Chandoo i missed your live session on automation in excel. That was so important for me. How can I get that video.

[@AVNISHS1981]

Superb bro...Useful... thanks..

[@ChasingCapella]

Great tips, thanks for sharing.

[@s_bottlerocketfirecracker8828]

“Hi , I think you’re making your pivot tables wrong” - this man knows me; he should be my therapist …

[@chandoo_]

😂

[@sjallmyfandoms9044]

🤣 right?!

[@meghanadighe6421]

Very nice tips thanks 👍

[@PainfullySubjective]

awesome. learned something new

[@karanchand6338]

Hello Sir, Thank you so much for the knowledge that you're sharing. It is really very helpful. I have a question related to this video. I have a list of employees with the amount that is deposited in their bank account in payroll and I have another table from my software it has multiple columns and each employee ID is duplicate. basically it is a bifurcation of salary in tips, basic pay, HRA, commissions etc that is why there are duplicate IDs. I tried to apply the same method that you've been teaching but I'm unable to connect the tables. it throws the error of duplicate values. Could you please help in this matter.

[@vikastaneja7923]

Great video. 👌

[@sendianelake7144]

Awsome ! God bless you bro