Join the Pivot Table Essentials course 👉 www.xelplus.com/course/excel-pivot-tables/ 🥳 Get 20% off until Sep 30th to celebrate the launch. Use coupon code YT at checkout. Mastering Pivot Tables is an important, time-saving skill, and I can’t wait to teach you from the ground up. Which part surprised you the most from the video?
I created a dashboard for my team at work using Excel and my manager said it wasn't updating. In this video you hit on two of the issues I had creating the final dashboard.(auto-refresh and one slicer to control them all) I had to refresh all data at the beginning of each month so he could see the previous months data. I wish this video came out 2 months ago, it would have saved me a lot of time. I think I found the solution in a few of your other videos though. You are my go-to Ex-cel-pert. Thank you. Always love the videos.
For my job, I teach a beginner PT class. I love learning new things here. I'll add your channel to our resources list so students can explore on their own.
You graciously sent a video congratulating me on my retirement. It was great and made me think. Although you've been my instructor for a long time I never subscribed, thumb up or commented to the great person that is on the other side of my screen. I apologize and thank you for keeping me and my team up on the latest on Excel over the years. You are the best.
These days I generally use spillable functions rather than Pivot Tables to achieve what you were doing. Taking your example, I would use the =UNIQUE() function on the Campaign column to get a unique list of campaigns, then the =SUMIF() or =SUMIFS() function with the sum range as the units sold column, the criteria range as the campaigns column, and the criteria as the output of the =UNIQUE() function.
Leila, I’ve been watching your videos since 2020 and you have taught me so much. You make my life easier and your teaching skills are easy to follow and understand. I think I’m going to convince my company to purchase this as part of training. 😉Thank you for all your great suggestions!
In addition to refresh on open, I created a dashboard for my manager and dropped a nice big button control that does a refresh all, so they can add data and see the results quickly. I've taken a couple of your classes but I think I'm going to buy this one. Even if I know a lot of the content, your presentation and examples always give me great ideas!
I'm grateful for your commitment to teaching Excel Leila. Since I started following you I have learnt a lot in excel you're among the best teacher I have ever come across keep up the good work Leila.
Hi , I've been watching ur videos for a while and I would really appreciate it if you clild make a video on 'whether rainmeter slows down your computer system and for which specs is in safe '
Great info as always. Picked up Xlookup and Unique from your previous videos. It's easy to miss features and changes that are being made to Excel and your videos reveal them for people like me. Lambda, PYTHON! Things can sure progress when you're not looking. There are functions that I would still be writing VBA for if not for your videos showing that Excel now has them natively available. Thanks for sharing what you know!
For anyone wondering, like I was, why you wouldn't just merge your tables in Powerquery and use that dataset for the pivot table, using the Data Model is more robust. If you add or remove columns to any of your source tables, the Power Pivot is able to handle that just fine, as long as the columns used to form relationships (as well as the columns you're reporting in the pivot) remain intact. If you've tried adding or removing columns in your source data when working with Powerquery, you know that it's very easy to break the queries requiring you to spend time repairing them. Another plus for the Data Model is you can use DAX to add measure columns next to the table(s) in the Data Model without actually adding them to the source tables, and you can use these measures in your pivots!
You are my hero! Thanks for these videos, I learned so much from your tutorials and tips. Really appreciate your work! (And so impressed by your proficiency)
My biggest issue with Excel as of now is the data model. You see, before I used to create dashboard and reports for myself and team, and then when I discovered that some things need to be done via these "relationships" or Measure, I needed to start from scratch, why? because I did not add the pivot tables to data model. Nowadays, anywhere I need to create a pivot table I create the data model for just in case.
Thanks for the useful information Kindly make video of pivot table on heading more than 500 As I want to select all heading in pivot table on one click
That power pivot tip is cool I figured that was where you were going but I'd go to the powerpivot tab add both tables from there and then create a relationship and then add a pivot table. If it's just one column I'd just create a xlookup because it's quicker. Although even so what if you are missing a record on the other side of the join? The Xlookup gives you an easy way to check that with the missing item value
Very informative video as always! Can you also prepare a tutorial on data transformation using Microsoft product? I will really appreciate it. Thank you so much!
Used PQ and Data model to get my Pivots. Issue is thta when I don’t have the source path correct or source data not connected still the pivot table worked on the already existing cache. How it can be avoided in refresh and to show “0” when no data source is present. My data source is a table. Pls let us know
Question. I have a access db on sharepoint that I use as source data. I can build a data table in excel with my path to the db, then replace with the user's path and save. Then the user can refresh, etc. However, I cannot create a pivot directly to the db and then save with their path. Excel will not allow me to save the report with a user's path. It only saves with MY path but not anyone else's path. Any suggestions?
@@LeilaGharani Yes ma'am, I was just requesting for a tutorial on automating tasks across MS Office Applications using ChatGPT. Would be really great if you can make a video on the same.
The course includes about 8 hours of video content, along with challenges and quizzes. During the launch, you get 20% off, bringing the price down to $119.
You always give nice tips. But please make payment process easy for Indians. My debit card payment shows error in the process. Make available the payment process with UPI
Quick question: Regarding slicers, it doesn't let me directly refresh the pivot table. It says to change datasource, please remove the slicers and then change datasource. Any workaround for that? I am trying to change from range to table for the dataset. Currently I have many slicers connected. It will be highly appreciated if you could help me in that!
Funny you mention this. This is exactly one of the scenarios we cover in the course. The solution is to disconnect your slicer first, update the data source and connect the slicer. You can avoid all of these steps though if you change your data source to a table.
The refresh by opening - almost useless. Simple reason: while you open - you need to refresh the data source - and only then - refresh pivots - (exclude if your source is qwery from another source that already)
I find that a useful way to automatically refresh a pivot is to write a little VBA program that refreshes the pivot on opening/clicking the sheet. You need to store the sub in an onsheetopen event. (Working from my memory here) You can use the recorder to write the program or get chatGPT to do it for you. And thanks Leila for another informative video 😉
I love Excel but in my job , they are switching to goggle Sheets 😢 And I don’t know what to do 😰😭😭😭 all my files are with power Query and power pivot 😫
ohhhhh I feel you. Google Sheets is great don't get me wrong, (i have multiple videos and a course on it) BUT nothing beats Power Query and Power Pivot....
The future undoubtedly belongs to intelligent tools and bots that automate and optimize processes. While humans share their knowledge through videos, bots are continuously evolving to effortlessly handle complex tasks such as data analysis, report generation, and even pivot table management. This significantly reduces manual effort, making lengthy explanations less and less relevant. As a result, many of these channels may not hold the same influence in the future, as bots will take on these tasks more efficiently and in real time.
Yes. I also have a power pivot course. This essentials course is like the prequel to PowerPivot. It's the first step before moving on to power pivot. So much can be done with standard pivot tables already. Once we reach the limits, then Power Pivot is the next logical step. Also there are features available in Pivot Tables that don't work with Power Pivots. This essentials course covers all of that. Hope that's clear.
Join the Pivot Table Essentials course 👉 www.xelplus.com/course/excel-pivot-tables/
🥳 Get 20% off until Sep 30th to celebrate the launch. Use coupon code YT at checkout.
Mastering Pivot Tables is an important, time-saving skill, and I can’t wait to teach you from the ground up.
Which part surprised you the most from the video?
I don't know why people use power Bi 😅. Excel is far better nowadays
I just signed up for the course. I didn't see the 20% discount applied anywhere. I used the link above to get there. What am I missing?
🤣
I created a dashboard for my team at work using Excel and my manager said it wasn't updating. In this video you hit on two of the issues I had creating the final dashboard.(auto-refresh and one slicer to control them all) I had to refresh all data at the beginning of each month so he could see the previous months data. I wish this video came out 2 months ago, it would have saved me a lot of time. I think I found the solution in a few of your other videos though. You are my go-to Ex-cel-pert. Thank you. Always love the videos.
Oh I'm SO glad you found the solution now. Better late than never right?
For my job, I teach a beginner PT class. I love learning new things here. I'll add your channel to our resources list so students can explore on their own.
You graciously sent a video congratulating me on my retirement. It was great and made me think. Although you've been my instructor for a long time I never subscribed, thumb up or commented to the great person that is on the other side of my screen. I apologize and thank you for keeping me and my team up on the latest on Excel over the years. You are the best.
These days I generally use spillable functions rather than Pivot Tables to achieve what you were doing.
Taking your example, I would use the =UNIQUE() function on the Campaign column to get a unique list of campaigns, then the =SUMIF() or =SUMIFS() function with the sum range as the units sold column, the criteria range as the campaigns column, and the criteria as the output of the =UNIQUE() function.
Filter and Pivotby formula also helpful to simplify the auto updates
Thanks, show pivot table in tabular form is such a good tip that I did not know about. I have been changing this manually for an age. Paul
Glad it was helpful!
Leila, I’ve been watching your videos since 2020 and you have taught me so much. You make my life easier and your teaching skills are easy to follow and understand. I think I’m going to convince my company to purchase this as part of training. 😉Thank you for all your great suggestions!
Wow, thank you!
I shall be using the power pivot in my job TODAY
Another great video, Leila. I just used auto refresh in my budget workbook I created following your previous video.
Leila. You are great. I love the way you present your video. Very clear and informative.
Thank you! 😃
In addition to refresh on open, I created a dashboard for my manager and dropped a nice big button control that does a refresh all, so they can add data and see the results quickly. I've taken a couple of your classes but I think I'm going to buy this one. Even if I know a lot of the content, your presentation and examples always give me great ideas!
Thanks for sharing!
Many thanks. I have to express my gratitude. It's the best channel I watch. Really appreciated your effort ❤
Thank you Leila.
I'm grateful for your commitment to teaching Excel Leila. Since I started following you I have learnt a lot in excel you're among the best teacher I have ever come across keep up the good work Leila.
Wow, thank you!
You make every topic approachable.
Leila, it would be nice if you showed how to save the cosmetic pivot table changes as the defaults at the end.
I love it, very impressive thank you so much Leila. I really enjoy watching your videos.
Guys lets share the video and spread the word.
Good work!
Thank you so much!
Thank you very much Leila for this video.
Awsum detailed explanation with egs.. thank you so much for making our life easier ❤❤
Hi , I've been watching ur videos for a while and I would really appreciate it if you clild make a video on 'whether rainmeter slows down your computer system and for which specs is in safe '
Love your data model ❤
Thanks for sharing
Great video, Laila!
so helpful. thank you!
You're so welcome!
Thanks for sharing your Excel Knowledge for free! Please which Software do you use to make and/or edit your video? Thanks in advance for your answer
Great info as always.
Picked up Xlookup and Unique from your previous videos. It's easy to miss features and changes that are being made to Excel and your videos reveal them for people like me. Lambda, PYTHON! Things can sure progress when you're not looking.
There are functions that I would still be writing VBA for if not for your videos showing that Excel now has them natively available.
Thanks for sharing what you know!
Glad you find the content helpful! Excel sure has seen a lot of improvements and additions in recent years.
For anyone wondering, like I was, why you wouldn't just merge your tables in Powerquery and use that dataset for the pivot table, using the Data Model is more robust. If you add or remove columns to any of your source tables, the Power Pivot is able to handle that just fine, as long as the columns used to form relationships (as well as the columns you're reporting in the pivot) remain intact. If you've tried adding or removing columns in your source data when working with Powerquery, you know that it's very easy to break the queries requiring you to spend time repairing them.
Another plus for the Data Model is you can use DAX to add measure columns next to the table(s) in the Data Model without actually adding them to the source tables, and you can use these measures in your pivots!
Thanks for sharing your experience! 👍
You are my hero! Thanks for these videos, I learned so much from your tutorials and tips. Really appreciate your work! (And so impressed by your proficiency)
Thank you for the kind feedback!
Great tips thanks Leila!
My biggest issue with Excel as of now is the data model. You see, before I used to create dashboard and reports for myself and team, and then when I discovered that some things need to be done via these "relationships" or Measure, I needed to start from scratch, why? because I did not add the pivot tables to data model. Nowadays, anywhere I need to create a pivot table I create the data model for just in case.
Thanks for the useful information
Kindly make video of pivot table on heading more than 500
As I want to select all heading in pivot table on one click
Never Thought I could use it that way ,Good Tips
Glad it was helpful.
That power pivot tip is cool I figured that was where you were going but I'd go to the powerpivot tab add both tables from there and then create a relationship and then add a pivot table. If it's just one column I'd just create a xlookup because it's quicker. Although even so what if you are missing a record on the other side of the join? The Xlookup gives you an easy way to check that with the missing item value
👍
She's so brave!
so very helpful... thx!
These are fantastic tips
Thanks so much
Mam please create a video data drill down vba coading without userform and povit table
Only coading
Exactly that you said...
Hello Leila, I tried to buy the course but was unable. Any assistance?
Very informative video as always! Can you also prepare a tutorial on data transformation using Microsoft product? I will really appreciate it. Thank you so much!
I have a few already on the channel. For example: th-cam.com/video/6lBqYInBldk/w-d-xo.html
Or this one: th-cam.com/video/WqTBsOEUOV8/w-d-xo.html
Used PQ and Data model to get my Pivots. Issue is thta when I don’t have the source path correct or source data not connected still the pivot table worked on the already existing cache. How it can be avoided in refresh and to show “0” when no data source is present. My data source is a table. Pls let us know
Question. I have a access db on sharepoint that I use as source data. I can build a data table in excel with my path to the db, then replace with the user's path and save. Then the user can refresh, etc. However, I cannot create a pivot directly to the db and then save with their path. Excel will not allow me to save the report with a user's path. It only saves with MY path but not anyone else's path. Any suggestions?
YES TO BREAKING BAD
Can I add 2 table headings in 1 pivot table
Ma'am Please consider making a video on how to use ChatGPT and other AI tools to accomplish and automate MS Excel tasks 🙏
Hi. I have a few videos on this on the channel. Are you looking for something specific?
@@LeilaGharani Yes ma'am, I was just requesting for a tutorial on automating tasks across MS Office Applications using ChatGPT. Would be really great if you can make a video on the same.
lelila can u pl let know the price n duration of this course .
The course includes about 8 hours of video content, along with challenges and quizzes. During the launch, you get 20% off, bringing the price down to $119.
You always give nice tips. But please make payment process easy for Indians. My debit card payment shows error in the process. Make available the payment process with UPI
Sorry about that. While our course platform doesn’t support UPI payments, it does accept all major credit and debit cards, as well as PayPal.
Quick question: Regarding slicers, it doesn't let me directly refresh the pivot table. It says to change datasource, please remove the slicers and then change datasource. Any workaround for that? I am trying to change from range to table for the dataset. Currently I have many slicers connected.
It will be highly appreciated if you could help me in that!
Funny you mention this. This is exactly one of the scenarios we cover in the course. The solution is to disconnect your slicer first, update the data source and connect the slicer. You can avoid all of these steps though if you change your data source to a table.
The refresh by opening - almost useless.
Simple reason: while you open - you need to refresh the data source - and only then - refresh pivots - (exclude if your source is qwery from another source that already)
I find that a useful way to automatically refresh a pivot is to write a little VBA program that refreshes the pivot on opening/clicking the sheet. You need to store the sub in an onsheetopen event. (Working from my memory here) You can use the recorder to write the program or get chatGPT to do it for you.
And thanks Leila for another informative video 😉
Hey how can I join ur course? And how much is the price?
Good question. I added the link as a pinned comment along with the promotion we're running in september.
@@LeilaGharani thanks. I really want to join. I try to pay today. How many classes have I lost? And when are the classes and what time?
I love Excel but in my job , they are switching to goggle Sheets 😢 And I don’t know what to do 😰😭😭😭 all my files are with power Query and power pivot 😫
ohhhhh I feel you. Google Sheets is great don't get me wrong, (i have multiple videos and a course on it) BUT nothing beats Power Query and Power Pivot....
The future undoubtedly belongs to intelligent tools and bots that automate and optimize processes. While humans share their knowledge through videos, bots are continuously evolving to effortlessly handle complex tasks such as data analysis, report generation, and even pivot table management. This significantly reduces manual effort, making lengthy explanations less and less relevant. As a result, many of these channels may not hold the same influence in the future, as bots will take on these tasks more efficiently and in real time.
What about power pivot course
Yes. I also have a power pivot course. This essentials course is like the prequel to PowerPivot. It's the first step before moving on to power pivot. So much can be done with standard pivot tables already. Once we reach the limits, then Power Pivot is the next logical step. Also there are features available in Pivot Tables that don't work with Power Pivots. This essentials course covers all of that. Hope that's clear.
@@LeilaGharani thsnks for faster and usual response, appreciated
breaking bad refrences lol
my favorite series 😀
Feels worth adding that PowerPivot does not work on Excel for Mac (very sadly so)
Good point. Thank you!
Ye tow Zabardasti ki video hai 😂
🥰⭐️💎
7:57 my chief just told me: "ah...ok." :(
Wishful thinking 🥸