DON'T Make These 5 Pivot Table Mistakes
Вставка
- Опубліковано 2 лип 2024
- 💥Want to learn ALL of Excel? Go for Excel School - chandoo.org/wp/excel-school-p...
~
Pivot Tables are a staple diet for data analysts all over the world. But are you ruining them with these 5 mistakes? In this video let's understand how 5 silly mistakes can make your pivot tables WRONG.
Video topics ⏱👇:
===============
0:00 - The 5 mistakes you make with Pivots
0:23 - Mistake 1 - Using cell ranges when making pivot
2:08 - Fixing the mistake with tabular data
4:03 - Mistake 2 - Just using default options
5:13 - Mistake 3 - Making a static pivot report
6:35 - Mistake 4 - Pivoting from just ONE dataset
9:43 - Mistake 5 - Not using Power Pivot
11:14 - Learning more about Pivot Tables
📗 Sample Workbooks:
===================
Practice the concepts using the sample workbook data here -
chandoo.org/wp/wp-content/upl...
💻Learn more:
=============
Complete Excel for Data Analysis work - Step by step course 👉Check out my Excel School program to learn and use Excel effectively. chandoo.org/wp/excel-school-p...
Excel Pivot Tables - a complete guide (must read) - chandoo.org/wp/excel-pivot-ta...
Advanced Pivot Tables (playlist) - • Easily link Slicers to...
Getting started with Pivot Tables (make 8 pivots in 10 minutes) - • How to use Pivot Table...
How to make Pivot Tables when you have LOTS of data - • How to make pivot tabl...
Excel for Data Analysis (playlist) - • Beginner to Pro FREE E...
~
What is Ross' favourite Excel feature?
PIVOT, PIVOT, PIVOT!
You have a beautiful day 🌞🌻😃
#Excel #PivotTables - Наука та технологія
Whoa...data model. I love this channel.
Glad you enjoy it!
Mindblown :o
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!
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.
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.
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 ❤
Brilliant refresher on new aspects within excel. Chandoo is so good at communicating in an easy to understand way.
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
Wow Teri.. Thank you so much for your lovely testimonial :)
Many thanks Chandoo. Your lessons are right on point and very rich.
Excellent, thanks Chandoo!
All those copy paste to next column to find variance is gone from today :) Thank you for the video👏
Yar this is really good. I knew when I found Chandoo's youtube channel it'll be awesome. Love this guy
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.
Thank you Malani. It is my pleasure to share what I learn with all of you :)
You are such a talented teacher. Thanks for sharing great tips! I always enjoy your content!
You are so welcome!
As always, thank you. Good reminders and learned something new!
Informative as always !! Thanks Chandoo..
Really start loving your videos...thanks for educating us.
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.
Fantastic information presented in a crystal clear format, thanks Chandoo!
You are welcome Buddha
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!
Thanks for the lovely comment Syed. 😃
Your teaching, Mike and Leila are most useful.
Thanks for your kind teachings.
You're very welcome!
This is so useful, especially the relationship between the two tables.
Thank you Salil...
Thanks Chandoo, You rock!
This is very insightful. Now, I need to learn how to use data model. Thanks for the video.
Chandoo you make excel fun! Thank you!
Excellent tutorial on relationships. Thank you
Great Chandoo these really helpful to whom they just think statically now they have something to go out side the box.
Yes they do
OMG... at 05:18... the 'banded rows' option.... I never knew this one! Thx a bunch... that's awesome! :D
Chandoo this is awesome 🌟
Combining two tables in a pivot saves a lot of time
Thank you very much. Very helpful to me. Nice teaching.
Brilliant learnt connecting 2 table and using measures. Thank you.
Thank you, Chandoo!
Thanks for showing relationship creation.
Thank you very much for the data file so that I can practice it.
Great, Thanks for this info. Really useful.
Thanks for sharing these. #4 for the win!
Excellent video Chandoo!
Nice tips Chandoo!, Thanks
You are amazing. Here I am thinking I was great at Excel. After your videos, I will be for sure! Thanks!
Chandoo thank you big time am learning loads.
Immensely helpful.. thank you... Keep rocking...
Hi Five 🙌 to Chandoo, for the "five" mistakes highlighted with resolutions and it is super useful for sure! 😊👍
Glad it was helpful!
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
Great tips, thanks for sharing.
another awesome video chandoo. 👍👍
@Chando you are my hero, please keep it up your good work-rest will be followed
Thank you for your valuable input explained so beautifully and very to understand, will help so many of us.
You are always welcome Nirmala...
Love you video editing skills!
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...
Excellent. Thank you ❤️
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.
I love this channel!! Thanks chandoo!! I am starting my sql journey. Can u plz share some good resources for learning sql.
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!
YES!!! Power Pivot in Excel opens a whole other world of possibilities.
Incredible chandoo !! you are awesome !!
Thank you so much 😀
Thank very much, very helpful informations
Love it, thank you ❤️
Very informative!!
Pivot table makes data understandable in quick way... Today I learnt new tricks in pivot table...Thank you Sir...😀🙏
awesome. learned something new
Awesome as always
🙏🏽Guru
Wow! Thank you Very much Sir!
You made my Day ❤️❤️❤️❤️
Thanks for valuable class. Could you please explain that auto generation of individual list of things based on one market in one pivot table
Very nice tips thanks 👍
glad to have landed on to this page
Excellent!
Awsome ! God bless you bro
Highly appreciated...
Thank u so much sir 🙏🙏🙏 god blessed you ❤️
Nice work Chandoo.💯. 😊
Wishing you a pleasant weekend 🏡.
Amazing ❤️
Thank you
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..
That is AWESOME 😎
Superb bro...Useful... thanks..
Thank You ❤️
I learnt Power Pivot thru this video. 💪
This was really great - tip 5 is something I really struggled to find.
Glad you found this helpful. More power to you Maarten ⚡⚡⚡
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
Awesome tips chandoo garu 🙏🙏
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
Thank you so much bro
Great video. 👌
Good stuff!
Outstanding Bro, i loved it :)
Very informative video! This data model relationship will work if we have data in different sheets?
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.
i admit 5th mistake... This is very informative. Thank you..
Thanks chandoo Anna.
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
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.
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..
Always Awesome 👌
New sub from Myanmar .. love Ur contents
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
Wow that is sick ❤❤❤❤
Woahh.............. this is superb
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.
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
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?
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?
Thanks Sir 🙏💕💲
Just wholesome!