Struggling to manage your data? Learn Pivot Tables and save hours every week! 👉 link.xelplus.com/yt-g-mistakes-pivottable Want to follow along? Download the exact file I used here: xelplus.ck.page/pivot-table-mistakes
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.
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.
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!
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 :-)
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.
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)
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!
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 '
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
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!
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
"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.
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.
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.
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!
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.
@@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.
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?
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
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
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 😉
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.
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.
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.
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.
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.. 🤔
Regarding your question about row labels and column labels in a pivot table: When you create the pivot table in a compact format with multiple row fields, it will help you understand more 🤎
Struggling to manage your data? Learn Pivot Tables and save hours every week! 👉 link.xelplus.com/yt-g-mistakes-pivottable
Want to follow along? Download the exact file I used here: xelplus.ck.page/pivot-table-mistakes
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
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. 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!
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!
You make every topic approachable.
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 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!
Really awesome spreadsheet for understanding good and bad investments. Thanks.
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
Another great video, Leila. I just used auto refresh in my budget workbook I created following your previous video.
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.
As a casual and somewhat reluctant user of pivot tables, I prefer formulas where reasonably possible, thank you for the reminders - very useful.
You are a godsend! I need your help.
Many thanks. I have to express my gratitude. It's the best channel I watch. Really appreciated your effort ❤
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 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.
Thank you very much Leila for this video.
Love your data model ❤
Awsum detailed explanation with egs.. thank you so much for making our life easier ❤❤
Leila, it would be nice if you showed how to save the cosmetic pivot table changes as the defaults at the end.
Thank you Leila.
You're welcome 😊
I really like the easy way as you explain and actually I didn’t know about the power pivot table! 🎉
Glad you liked it!!
I shall be using the power pivot in my job TODAY
Great 👍👍👍
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
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 '
so helpful. thank you!
You're so welcome!
Great tips thanks Leila!
Thanks for sharing
Great video, Laila!
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
👍
So great thanks a lot
so very helpful... thx!
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!
These are fantastic tips
I love pivot tables ❤
Never Thought I could use it that way ,Good Tips
Glad it was helpful.
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
"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 love your video. I miss the discount code to purchase the courses😢will there any discount codes in coming days?
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
Thanks so much
Hi,thank you for great lessons,
Please explain about the impact of artificial intelligence on Excel, will artificial intelligence replace Excel users
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.
She's so brave!
2:35 Breaking Bad - Niice!!!!! 😉
The food and dating analogy: totally me!
Here are some AMAZING Homemade pizzas!!!!!!!!
Exactly that you said...
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.
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! 👍
I have reached the maximum Excel row. What’s your advised
Should I save my data as csv instead? Please advise
Mam please create a video data drill down vba coading without userform and povit table
Only coading
Don Eladio is not happy these 4 are having a side hustle
Hello Leila, I tried to buy the course but was unable. Any assistance?
Hello Teacher, Please show me about VBA code automatically sent mail for birthday,thanks.
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.
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.
Madam, How can I convert a PDF into Excel, including both tables and content combined?
This should get you started: th-cam.com/video/p2304BjvrB8/w-d-xo.html
I loose it when i cannot follow the rapid movement of the curser.
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?
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
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?
YES TO BREAKING BAD
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.
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 😉
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.
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
Feels worth adding that PowerPivot does not work on Excel for Mac (very sadly so)
Good point. Thank you!
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.
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 😂
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.
breaking bad refrences lol
my favorite series 😀
I am wondering who was the fool that gave you Aldi's food in the same packaging it came with. 🤔
🥰⭐️💎
I want power bi class
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.. 🤔
7:57 my chief just told me: "ah...ok." :(
Regarding your question about row labels and column labels in a pivot table:
When you create the pivot table in a compact format with multiple row fields, it will help you understand more 🤎
Wishful thinking 🥸