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 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!
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
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 ❤
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
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.
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.
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.
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 - th-cam.com/video/nOWzpJ4gUKo/w-d-xo.html
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..
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
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.
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 🤣
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?
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
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...
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
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
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?
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?
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?
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 - th-cam.com/video/PiFAa_jjaEI/w-d-xo.html and this one for VBA - th-cam.com/video/5k-KpSkrROw/w-d-xo.html
When I pick 'Department' from 2nd table I am getting incorrect values from 1st table, any rule? that we have to pick common field (relationship field) from 2nd or last table. please answer?
Sir, I have an question.... I have total 31 sheets combine with power query and consolidated all the data in 1 report.. when I'm refresh it... It's take too long sir..
Hello Sir, I m Xubair 4m Pakistan... sir I humbly request U 2 plx make a detailed and complete course of the following on priority base 1st: SQL 2nd: SSIS 3rd: Power Automate 4th n most important: Python Sir I would highly appreciate Ur efforts and would be more than thankful and grateful. Sir plxxx consider my request as I have synched 2 Ur teaching method which is easy to grasp and is a bit different than usual tutorials where one can jump 4m begginer 2 Expert instantly with Ur basics+ unique tips during the course... I m very much hoping U would read this and help us all vid that...
How to create multiple subsets linked to one pivot table. And once clicked for 1 cell data it will display that particular information only which have multiple rows . Thanks in advance.
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!
Thanks!
Thanks for the Super Will 😃
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 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. 😃
Yar this is really good. I knew when I found Chandoo's youtube channel it'll be awesome. Love this guy
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
You are such a talented teacher. Thanks for sharing great tips! I always enjoy your content!
You are so welcome!
Great Chandoo these really helpful to whom they just think statically now they have something to go out side the box.
Yes they do
Excellent, thanks Chandoo!
Excellent video Chandoo!
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 ❤
Your teaching, Mike and Leila are most useful.
Thanks for your kind teachings.
You're very welcome!
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 :)
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 :)
Informative as always !! Thanks Chandoo..
OMG... at 05:18... the 'banded rows' option.... I never knew this one! Thx a bunch... that's awesome! :D
Brilliant refresher on new aspects within excel. Chandoo is so good at communicating in an easy to understand way.
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’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.
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.
Really start loving your videos...thanks for educating us.
Chandoo you make excel fun! Thank you!
Many thanks Chandoo. Your lessons are right on point and very rich.
@Chando you are my hero, please keep it up your good work-rest will be followed
Excellent tutorial on relationships. Thank you
Thanks Chandoo, You rock!
Very helpful video sir. Plz suggest do we have to have a good knowledge of excel before jumping into power BI
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 - th-cam.com/video/nOWzpJ4gUKo/w-d-xo.html
another awesome video chandoo. 👍👍
This is so useful, especially the relationship between the two tables.
Thank you Salil...
Thank you, Chandoo!
All those copy paste to next column to find variance is gone from today :) Thank you for the video👏
Thank you very much. Very helpful to me. Nice teaching.
Incredible chandoo !! you are awesome !!
Thank you so much 😀
Fantastic information presented in a crystal clear format, thanks Chandoo!
You are welcome Buddha
Very informative video! This data model relationship will work if we have data in different sheets?
Nice tips Chandoo!, Thanks
Immensely helpful.. thank you... Keep rocking...
Chandoo this is awesome 🌟
Combining two tables in a pivot saves a lot of time
Thanks for showing relationship creation.
You are amazing. Here I am thinking I was great at Excel. After your videos, I will be for sure! Thanks!
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 😎
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
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.
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 🤣
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.
Thanks for sharing these. #4 for the win!
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..
Love you video editing skills!
Thank you for your valuable input explained so beautifully and very to understand, will help so many of us.
You are always welcome Nirmala...
“Hi , I think you’re making your pivot tables wrong” - this man knows me; he should be my therapist …
😂
🤣 right?!
This is very insightful. Now, I need to learn how to use data model. Thanks for the video.
Chandoo thank you big time am learning loads.
i admit 5th mistake... This is very informative. Thank you..
Brilliant learnt connecting 2 table and using measures. Thank you.
Hi Five 🙌 to Chandoo, for the "five" mistakes highlighted with resolutions and it is super useful for sure! 😊👍
Glad it was helpful!
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?
Awesome as always
Pivot table makes data understandable in quick way... Today I learnt new tricks in pivot table...Thank you Sir...😀🙏
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
I love this channel!! Thanks chandoo!! I am starting my sql journey. Can u plz share some good resources for learning sql.
Tum bht mast kaam karta hai chandoo Bhai..Lots of Love from India
Thanks you Raja ji.. :)
New sub from Myanmar .. love Ur contents
Nice work Chandoo.💯. 😊
Wishing you a pleasant weekend 🏡.
Great, Thanks for this info. Really useful.
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...
Very informative!!
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
Can Filters depend on other cells with formulas? Thanx
Awesome tips chandoo garu 🙏🙏
glad to have landed on to this page
3:15 How do I sort by "Count of Employee ID Number"? Thanks
Iam planning to join a course, but is it interactive or recorded one.. looking for interactive one....is it available?
Wow! Thank you Very much Sir!
You made my Day ❤️❤️❤️❤️
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
Thank you very much for the data file so that I can practice it.
Hi Chandoo, how to automate excel to have data from master work book into different sheets.
May I know what is the differences between calculated field and add measure ?
I learnt Power Pivot thru this video. 💪
Superb bro...Useful... thanks..
This was really great - tip 5 is something I really struggled to find.
Glad you found this helpful. More power to you Maarten ⚡⚡⚡
🙏🏽Guru
Hi,
Just want to know how operate Puvot with keyboard only while no use of mouse?
Are there any shortcuts for using pivot?
Outstanding Bro, i loved it :)
Great tips, thanks for sharing.
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?
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?
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?
Very nice tips thanks 👍
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
Please make a video in creating tools by using excel where one tool can automate your work with different sheets .
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 - th-cam.com/video/PiFAa_jjaEI/w-d-xo.html
and this one for VBA - th-cam.com/video/5k-KpSkrROw/w-d-xo.html
@@chandoo_ thank You 🙏
When I pick 'Department' from 2nd table I am getting incorrect values from 1st table, any rule? that we have to pick common field (relationship field) from 2nd or last table. please answer?
Sir, I have an question.... I have total 31 sheets combine with power query and consolidated all the data in 1 report.. when I'm refresh it... It's take too long sir..
Thanks for valuable class. Could you please explain that auto generation of individual list of things based on one market in one pivot table
Hello Sir, I m Xubair 4m Pakistan...
sir I humbly request U 2 plx make a detailed and complete course of the following on priority base
1st: SQL
2nd: SSIS
3rd: Power Automate
4th n most important: Python
Sir I would highly appreciate Ur efforts and would be more than thankful and grateful.
Sir plxxx consider my request as I have synched 2 Ur teaching method which is easy to grasp and is a bit different than usual tutorials where one can jump 4m begginer 2 Expert instantly with Ur basics+ unique tips during the course...
I m very much hoping U would read this and help us all vid that...
Thanks Xubi for your love and support. I do have videos on some of the topics already planned. Do check the channel from time to time.
@@chandoo_ Thank U Sir n Love U...
May Allah bless U vid the best of His guidance 💕
Need access training, could you provide course details
Great video. 👌
Thank very much, very helpful informations
awesome. learned something new
Awsome ! God bless you bro
How to create multiple subsets linked to one pivot table. And once clicked for 1 cell data it will display that particular information only which have multiple rows .
Thanks in advance.