Join the Pivot Table Essentials course 👉 www.xelplus.com/course/excel-pivot-tables/ 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.
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.
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!
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.
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!
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.
Hello Leila That was the creators. that made (Row label). Now you are creating on There foundation. And doing good educational videos on Excel. I enjoy, and easy to understand Thanks :-)
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!
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!
"Row labels" appears in pivot tables because one may use multiple fields as labels for rows. In the default layout which is compact form, all labels from all these fields appear in a single column whereas in tabular form, each field appears in its own column.
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)
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
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 '
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
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.
Hello Leila, excellent channel, very happy with it. My question is how to leave only the data in the "timeline pivot table" for the months for which I only have information, and not show me all the months of the year, with empty buttons. That is, I only want it to show me the buttons for those months that refer me to the information. Thank you very much, I'm still tuned!
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!
Hi Leila, would appreciate if you could create a video on the new office 2024. Excel 2024 vs excel 2021 features and formula functions available. Thank you.
@@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 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 😉
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?
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
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
Leila, I may have missed it, but... If I'm not mistaken, Slicer still can't handle pivot tables that have different data sources. And treating this with hundreds of lines of VBA and a class module is... embarrassing.
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 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.
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.
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....
But when I see the pivot it shows different value and when he opens up that shows different value which leads to confusion..😅 @psycho .. boss neega idha vechu yedo vilaiyadina maari yennaku thoonudu.. 🤔
Join the Pivot Table Essentials course 👉 www.xelplus.com/course/excel-pivot-tables/
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?
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.
This tutorial is very helpful, thank you and I was able to enable all the functions of my Excel.
and how did you achieve it?
At BNH Software I got a key and it solved all my problems hahaha
It's good to know because sometimes Excel tells me that some functions are disabled and it frustrates me.
happy to help
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
As a casual and somewhat reluctant user of pivot tables, I prefer formulas where reasonably possible, thank you for the reminders - very useful.
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!
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.
Great, thank you! I hope your students will find it helpful.
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!
Leila. You are great. I love the way you present your video. Very clear and informative.
Thank you! 😃
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!
Hello Leila
That was the creators. that made (Row label). Now you are creating on There foundation. And doing good educational videos on Excel. I enjoy, and easy to understand Thanks :-)
I shall be using the power pivot in my job TODAY
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!
Another great video, Leila. I just used auto refresh in my budget workbook I created following your previous video.
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! 👍
Really awesome spreadsheet for understanding good and bad investments. Thanks.
You make every topic approachable.
Many thanks. I have to express my gratitude. It's the best channel I watch. Really appreciated your effort ❤
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.
"Row labels" appears in pivot tables because one may use multiple fields as labels for rows. In the default layout which is compact form, all labels from all these fields appear in a single column whereas in tabular form, each field appears in its own column.
I really like the easy way as you explain and actually I didn’t know about the power pivot table! 🎉
Glad you liked it!!
You are a godsend! I need your help.
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!
Awsum detailed explanation with egs.. thank you so much for making our life easier ❤❤
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
👍
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 '
Leila, it would be nice if you showed how to save the cosmetic pivot table changes as the defaults at the end.
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
Thank you very much Leila for this video.
Great video, Laila!
The food and dating analogy: totally me!
Here are some AMAZING Homemade pizzas!!!!!!!!
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
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.
Hello Leila, excellent channel, very happy with it. My question is how to leave only the data in the "timeline pivot table" for the months for which I only have information, and not show me all the months of the year, with empty buttons. That is, I only want it to show me the buttons for those months that refer me to the information. Thank you very much, I'm still tuned!
Love your data model ❤
Hi,thank you for great lessons,
Please explain about the impact of artificial intelligence on Excel, will artificial intelligence replace Excel users
Never Thought I could use it that way ,Good Tips
Glad it was helpful.
so helpful. thank you!
You're so welcome!
I love pivot tables ❤
Thanks for sharing
Thank you Leila.
You're welcome 😊
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: ruclips.net/video/6lBqYInBldk/видео.html
Or this one: ruclips.net/video/WqTBsOEUOV8/видео.html
I love your video. I miss the discount code to purchase the courses😢will there any discount codes in coming days?
so very helpful... thx!
These are fantastic tips
She's so brave!
Great 👍👍👍
Hi Leila, would appreciate if you could create a video on the new office 2024. Excel 2024 vs excel 2021 features and formula functions available. Thank you.
2:35 Breaking Bad - Niice!!!!! 😉
Thanks so much
Exactly that you said...
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.
Mam please create a video data drill down vba coading without userform and povit table
Only coading
Hello Leila, I tried to buy the course but was unable. Any assistance?
Don Eladio is not happy these 4 are having a side hustle
YES TO BREAKING BAD
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 😉
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?
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.
Hello Teacher, Please show me about VBA code automatically sent mail for birthday,thanks.
Can I add 2 table headings in 1 pivot table
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
Leila, I may have missed it, but... If I'm not mistaken, Slicer still can't handle pivot tables that have different data sources. And treating this with hundreds of lines of VBA and a class module is... embarrassing.
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.
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.
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.
Feels worth adding that PowerPivot does not work on Excel for Mac (very sadly so)
Good point. Thank you!
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?
Madam, How can I convert a PDF into Excel, including both tables and content combined?
This should get you started: ruclips.net/video/p2304BjvrB8/видео.html
I loose it when i cannot follow the rapid movement of the curser.
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 😀
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....
Ye tow Zabardasti ki video hai 😂
But when I see the pivot it shows different value and when he opens up that shows different value which leads to confusion..😅 @psycho .. boss neega idha vechu yedo vilaiyadina maari yennaku thoonudu.. 🤔
🥰⭐️💎
I am wondering who was the fool that gave you Aldi's food in the same packaging it came with. 🤔
7:57 my chief just told me: "ah...ok." :(
Wishful thinking 🥸