Power query for me but I guess that since there are so many new functions in Excel, it’s difficult for even seasoned users to know where to begin with complex problems.
@@Michael-sn1wq Many Excel users struggle with it. I have a playlist dedicated to Power Query, I hope it help: ruclips.net/video/wk1s0wE5Gww/видео.html&pp=iAQB 😊
LET function .... I have used and trained Excel for years but for some reason I steered clear of this function, perhaps that is why I have never understood it and known what is it for. Is it something really needed? Thank you Mynda for fantastic videos, Gregory (PS. Just noticed someone already mentioned this function. And there is a video on it. So looks like one of those days I need to break ice with this strange function)
I love this type of videos. It's not just step by step tutorial, but more a worflow with best practices during which you can learn multiple things by seeing them in real usecases.
Wow!!! This is wonderful video, Mynda!!!! The reason it is wonderful is because you tell the truth about data analysis: you often do not need tools like R and Python. Excel is right there in front of you ready to sever : ) Then you go on and show a great suite of Excel tools and even showed the Data Analysis Descriptive Statistics tool!
Brilliant thank you. You asked what everyone would like more of, I personally would like more on automation. I'm a Procurement Manager and therefore work with a load of numbers but not in an accounting setting. Bid comparisons, analysis of those, tracking invoice payments against contracts, indices etc. it would be great to help me simplify my work. I've already incorporate a lot of your suggestions, but I can always do better. Thanks
Thanks for the suggestion and your support! 🥰 please check out my video on LET: ruclips.net/video/o3ysGnl8ieM/видео.html And my video on LAMBDA: ruclips.net/video/cGxjWOY8h98/видео.html
Thank you for the video, you're simply brilliant! I would be happy to learn more about statistical tools in Excel and also about various uses of the SEQUENCE formula.
Excellent! Topic which interests me is: Can Excel now replace Access for smaller databases? Two main functions which Access has served: 1. Long-term reliable data storage; 2. Error checking of the data.
Great question! In theory, you can use Excel as a database, but it's not as robust as a proper database. You can use Power Pivot to model the data across multiple tables with relationships to connect them, like you can in access. And you can use Power Query to perform error checking and data cleaning tasks.
Thank you Mynda. Regarding your question: I'd like to learn more about power pivot. Would be great to see a video in the near future. Thanks in advance!
Wow, it is a fascinating new world. I feel a little bit overwhelmed since I feel like I would like to understand it all. I will start step by step and purchase the Dashboard course now that has a discount. And then look for more topics. Thanks for opening my eyes Mynda.
So pleased it was helpful. There is a lot to Excel, but learning something new each week will be super rewarding. Excel skills will never go to waste 😁
Hello, I am a financial advisor and I manage my clients' money. I need two dashboards: - One to track my prospects and view sales metrics. - One to report to my clients about their assets (investments and goals). Which one would you recommend? I appreciate your contributions, they are incredibly valuable. Thank you
I'm not sure why you're asking me which one I recommend when it seems you need both as they serve different purposes. If you'd like to learn how to build your own interactive dashboards, please consider my course: www.myonlinetraininghub.com/excel-dashboard-course
Hi Mynda, the topic I'm more interested in is creating an Excel Dashboard TEMPLATE. I work with the same data download format for housing markets say 6 different times per week, say 6 different jobs. I want to build a template of say 8 key metrics I need from my data. After the template is built, I would use it to add different data sets several times per week.
That's a great idea! You may also be able to use Power Query to automate gathering the data, then you can just point Power Query to a different file for each housing market. If you'd like to learn how to build dashboards, please consider my Excel Dashboards + Power Query course bundle available here: www.myonlinetraininghub.com/excel-dashboard-course
This may seem silly. But I struggle with modifying pivot table formats. I try to create from an existing layout, but they never come out the way I want. I end up using formatting using format/cells.
There is no such thing as a silly question :) Struggling with PivotTable formats is a common issue. PivotTables often use default formatting, which might not align with your needs. Here are some quick tips: use 'Field Settings' to adjust the layout and format for each field, explore 'PivotTable Styles' in the Design tab for predefined styles, and ensure 'Preserve cell formatting on update' is checked in PivotTable options. This will maintain your formatting even after refreshing the data. Consider changing the report layout under the Design tab for a more customized approach. Hope this helps!
Ah, that's a shame. Don't give up on it. One day it will click for you. Here's my 4 minute video that might help: ruclips.net/video/-4yCXpv-drg/видео.html
Well, since you asked…. My biggest (hmmmmm….) is how to read the order of NESTED formulas. In other words, which part of the entire formula is read first which would feed the information needed to activate the second function which etc etc. That would be a GREAT help. Thanks
Hi videos are always useful. Thank you so much. My query is related to using power pivot. 1. I struggle with building relationships 2. To combine tables as you have done in this video, do all your separate tables have to have same fields etc for them to work in the combining?
Thank you! To answer your second question, the first file (sample file) has to contain all the columns your other files could possibly contain, but the other files don't have to contain all the same fields. Note: this combining of files in the video was done in Power Query, not Power Pivot. Building relationships in Power Pivot can be tricky and it's such a critical step in ensuring your model works correctly that it might be worth you taking a Power Pivot course to learn the fundamentals: www.myonlinetraininghub.com/power-pivot-course
And for big data, that require more than the limit rows excel offers (1,048,576) what will a solution to data analytics? I'm so used to do everything in excel, but I have a special task to do that involves at least 5 files with that limitation of rows.
You can still use Excel. See this video which uses Power Query and Power Pivot which can handle 100s of millions of rows of data: ruclips.net/video/McLQ3gEGbHQ/видео.html
Thanks for the suggestion. I have found arrays much easier to understand since dynamic array formulas were introduced: www.myonlinetraininghub.com/excel-dynamic-arrays
Thank you Mynda. Regarding your question, yes I have the feature of (=SEQUENCE(1,141,MIN(C9:C58)+Work!K5,1)) what if I want to show the project baseline on weekly basis?
Dear Madam, Thank you very much for the "ANOTHER GREAT VIDEO". I have a request, how to creat an excel formula, the inside of an excel formula contains, arrays, conditions, etc etc so while creating the formula how to understand that elements and putting them correctly, I hope you got my point. I will be very grateful if you make it easy for us.
Thanks for sharing. DAX is one of those things that requires practice. If you'd like to get your skills up to speed quickly, please consider my Power Pivot and DAX course: www.myonlinetraininghub.com/power-pivot-course
Power Query is, indeed, a feature that many Excel users find hard to work with. I have a playlist dedicated just to it: ruclips.net/video/wk1s0wE5Gww/видео.html&pp=iAQB I hope you will find the answers to your questions there 😊
You are looking Beautiful. Thanks for the video. can you please make video on Substitute function in excel (Advance level).that i wish to understood better.
Amazing! Thanks. I like : pivots and Data Analysis tools and I'm eager to know about Power Pivot. Question thought : is 'Office Pro Plus 2021' able to provide this IA Data Analysis tool? Thanks a lot
Hello, I've building a pivot tabel of my company's balance. It's works fine except for the fact that i don't know how to handle the 5 level i've used for classify all the balance voices. I'd like ask you if exist a tecnique to avoid in a pivot tabel to see empty level, of course i would like to don't use +/-. thanks in advance
Difficult to say without seeing the file and structure of your data. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Good stuff "mate", thanks again! I would like to know more about power query and then specifically how to handle data from multiple sources which contain different deliminators and thousands/decimal settings. For example, masterdata is loaded from an Excel file (with US/English settings on laptop) and the sales table from a CSV (produced from a Dutch database). I somehow always struggle to get this right.
Thanks for watching! Each of your queries would have to be created separately and cleaned before appending/merging them (assuming that's the end goal). I'm not sure there's an easy way to help you remember the steps as such, but once you set up the queries, you should be able to reuse them for future files.
@@MyOnlineTrainingHubThanks for getting back! I just found the "using locale" option, in the "change type" submenu. And it seems I can deviate from the system settings like this.
Oh man! Where was this video 13 years ago? 😉 To answer your question, it would be macros. BUT, from what i know and do, I've just stopped using macros because they "break" all the time, require maintenance, and can destroy the sheet, etc. 😎
I wish I understood better how I could automatize the integration of csv file and split concatenated values from one cell in dedicated Column with corresponding headers e.g in one cell (Length [mm] : 20; Height [mm] . 12; Depth [mm] : 5) tranform to columns with Header Column 1 : Length [mm] : each row value corresponding length e.g. 20 Header Column 2 : Height [mm] ....
Sounds like a job for Power Query. If you get stuck, post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
When I was working, I did a kind of recertification using Excel, and the data was massive. Although I had a few days after training to go about it, I was not familiar with it. Do you know what that could be?
I have not figured out how to work with excel in a good way for statistical analysis. Lets say I have 10-20000 rows of data from four pumps in a factory. I plot a regression that shows there are some outlier points. How do I exclude these points moving forward in the statistical analysis (before I make more calculations or the regression analysis etc.) ? In the plot I can see the x,y of the points, but going into the table with 10000+ rows it is a lot to scroll down and find the points and delete them. Using filter on the column dosnt work either as there are data for the other pumps in the same row that is not outliers. In R/Python there are some options forexample making a new df excluding these points. But I dont an Excel equavalent to this way without ending up with alot of individual tables. What if it is not outliers I want to remove, but only make the an regression analysis on a section of data and another analysis on another part.
You could possibly use the FILTER function to exclude the outliers and create a subset of your data for further analysis. FILTER function is covered here: ruclips.net/video/ZCQAweoAdOw/видео.html
My issue is using Power Query to clean up data generated by badly designed reports. I have tried to use Power Query but can't seem to get data into the proper rows and columns.
Thanks for sharing! In future if you get stuck, you're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
I wish I could learn VBA. I want to learn it so bad. I dont know where to start. I am Chartered Accountant and proficient in excel but VBA is the missing piece.
What do you want to use VBA for? Many times VBA is not required and it's even less useful now that we have Power Query to automate the gathering and cleaning of data.
Thanks for the video! Always very useful! However I've experienced some issue regarding "Analysis ToolPak". It seems activated but no button was shown on the ribbon. I tried to find what's the problem on the internet but without success...
@@MyOnlineTrainingHub I even tried to logoff and login from my company account but nothing. Very sad about this 😢. I tried to use “Analyze data” (as explained in your video) and looks very cool! But I think that this add-in seems to have more functionalities!
Hi Mynda, could you share a link in your videos where I can find out how to add funds to my stocks portfolio account, I am uk based and use mainly uk stocks but now I have a couple of funds us as as India and uk but I can’t seem to find the correct way to add them to my excell I have the correct ticker but I cant find how to add them in, as excel wants to give me other tickers 😂 Any help would be greatly appreciated thank you Kind regards kev
Ok thank you for your very quick reply.. For example i put theses 2 in like this into spreadsheet HSBC FTSE 100 INDEX INCOME C, but the reply i get is this HSBC HOLDINGS PLC (XNYS:HSBC) and doesn't see anything else Vanguard U.S. Eq Idx £ Acc, and i actually hold these two in my account. so i know they exist 🤣 @@MyOnlineTrainingHub
HI there. first of all thank you for your intelligent videos , second : i would like to ask for your permission to use the data you used in the budget vs actual vs plan video ( I dont want the dashboard i just need the data sheet as it would help me in designing sample data for my clients . )
@@MyOnlineTrainingHub sorry but is designing a budget file from my own with this data and put it on upwork as a portfolio would be considered commercial ?
Sorry , can you tell again who can have the ANALYZE DATA feature?Thanks - I cannot work it out how to add it...365office account and also personal 365 version license.
Hi, why do you use the average? Do you want to calculate an average of % GP (what meaning?) or % GP per country? this is not the REAL %GP!! Indicators (measures) such as % margin, % Discount, Price ... never calculated in ETL process.
Good questions. The inputs are up to you. The purpose of the video is to show you how you can use Excel to perform common calculations that you might ordinarily do in a more convoluted way.
In a lot of ways DAX is very similar to Excel, but there are some nuances that make it mind bending 😁 check out my Power Pivot & DAX course here: www.myonlinetraininghub.com/power-pivot-course
❓What is one Excel feature or function that you wish you understood better?
Improve your productivity in Excel with Power Query: bit.ly/powerquery0124
Power query for me but I guess that since there are so many new functions in Excel, it’s difficult for even seasoned users to know where to begin with complex problems.
@@Michael-sn1wq Many Excel users struggle with it. I have a playlist dedicated to Power Query, I hope it help: ruclips.net/video/wk1s0wE5Gww/видео.html&pp=iAQB 😊
Pivot Table and Mynda Treacy😎😎🌺🌺
There is a module for Data Profiling ?
LET function .... I have used and trained Excel for years but for some reason I steered clear of this function, perhaps that is why I have never understood it and known what is it for. Is it something really needed? Thank you Mynda for fantastic videos, Gregory
(PS. Just noticed someone already mentioned this function. And there is a video on it. So looks like one of those days I need to break ice with this strange function)
100% my most favourite Excel instructor. Everything is done so well and clearly. Thank you and bless you always!
Wow, thank you! 🥰
I love this type of videos. It's not just step by step tutorial, but more a worflow with best practices during which you can learn multiple things by seeing them in real usecases.
Awesome to hear 😁🙏
excellent.....i never get bored with your tutorials. Love it
Mo
Glad you like them!
Wow!!! This is wonderful video, Mynda!!!! The reason it is wonderful is because you tell the truth about data analysis: you often do not need tools like R and Python. Excel is right there in front of you ready to sever : ) Then you go on and show a great suite of Excel tools and even showed the Data Analysis Descriptive Statistics tool!
Thanks so much, Mike!
@@MyOnlineTrainingHub Really Good : )
Brilliant thank you. You asked what everyone would like more of, I personally would like more on automation. I'm a Procurement Manager and therefore work with a load of numbers but not in an accounting setting. Bid comparisons, analysis of those, tracking invoice payments against contracts, indices etc. it would be great to help me simplify my work. I've already incorporate a lot of your suggestions, but I can always do better. Thanks
Interesting 🙏 will keep it in mind.
Let and lambda helper functions. Thank you for all you do to help people become more proficient in Excel. Love all your videos.
Thanks for the suggestion and your support! 🥰 please check out my video on LET: ruclips.net/video/o3ysGnl8ieM/видео.html
And my video on LAMBDA: ruclips.net/video/cGxjWOY8h98/видео.html
Excellent Video. Thank you Mynda.
You are most welcome!
Mynda you are the best! I am checking your news as often as weather forecast.
😁thanks so much!
Macros is the Excel feature I wish I know more about, for some reason I don't see a lot of people talking about it
Thanks for sharing! Macros are not as important as they once were now that we have Power Query to automate the gathering and cleaning of data.
Kindly advise how should I master the art of reconciliation as accountant both bank and vendors accounts. Thanks
Keep Pressing. You are Dynamite!!
Thank you, I will 🙏😁
Thank you for the video, you're simply brilliant! I would be happy to learn more about statistical tools in Excel and also about various uses of the SEQUENCE formula.
Glad you enjoyed it. Thanks for the suggestion 🙏
Anything you could include regarding regular expressions would be good
Thanks. Will keep that in mind.
Thank you for your amazing learning videos. Lambda for me is difficult to understand and find it usefull.
You are welcome! Here is my LAMBDA tutorial: www.myonlinetraininghub.com/excel-lambda-function Hope it helps.
Really fascinating ; you made it really simply and believable.
Great to hear 🙏
I loooove this kind of stuff. I didn't know you can do this in Excel!
So glad it was helpful 😊
Frankly, since the day i follow you. i found excel is very easy to use and no function is hard to learn. Thank you!
Aw, you’re too kind 🙏
@@MyOnlineTrainingHub seriously no unwanted background music, simple and decent way of teaching. Thank you very much!
Excellent!
Topic which interests me is: Can Excel now replace Access for smaller databases?
Two main functions which Access has served:
1. Long-term reliable data storage;
2. Error checking of the data.
Great question! In theory, you can use Excel as a database, but it's not as robust as a proper database. You can use Power Pivot to model the data across multiple tables with relationships to connect them, like you can in access. And you can use Power Query to perform error checking and data cleaning tasks.
Thank you so much for the video
My pleasure!
Excellent! Thank you Mynda! I vote for Power Query!
Thank you 😊
Thank you Mynda. Regarding your question: I'd like to learn more about power pivot. Would be great to see a video in the near future. Thanks in advance!
Thanks for sharing. If you'd like to get up to speed with Power Pivot & DAX, check out my course here: www.myonlinetraininghub.com/power-pivot-course
I really want to understand power query better ! Thank you for the video this is what i need ! ❤
Great to hear! Please check out my Power Query course to get your skills up to speed quickly: www.myonlinetraininghub.com/excel-power-query-course
To think that I was doing this manually last night 😢😢...Thank you for this 🙏🙏🙏
Yay, so glad I could save you some time going forward!
Thank you Mam you are better than the best.power query playlist is dream video series.
So nice of you 🥰
Wonderful 🌹
Thanks for watching!
Good Morning Mynda and thank you for all you do!
Thank you for the support! I really appreciate that 😊
Welcome back, and thank you for sharing this.
Thanks for watching 😊
Wow, it is a fascinating new world. I feel a little bit overwhelmed since I feel like I would like to understand it all. I will start step by step and purchase the Dashboard course now that has a discount. And then look for more topics. Thanks for opening my eyes Mynda.
So pleased it was helpful. There is a lot to Excel, but learning something new each week will be super rewarding. Excel skills will never go to waste 😁
@@MyOnlineTrainingHub Agree 100 %. I started already with the dashboard course amazing, I also purchased the Power Query one…
Hello, I am a financial advisor and I manage my clients' money.
I need two dashboards:
- One to track my prospects and view sales metrics.
- One to report to my clients about their assets (investments and goals).
Which one would you recommend? I appreciate your contributions, they are incredibly valuable.
Thank you
I'm not sure why you're asking me which one I recommend when it seems you need both as they serve different purposes. If you'd like to learn how to build your own interactive dashboards, please consider my course: www.myonlinetraininghub.com/excel-dashboard-course
Hi Mynda, the topic I'm more interested in is creating an Excel Dashboard TEMPLATE. I work with the same data download format for housing markets say 6 different times per week, say 6 different jobs. I want to build a template of say 8 key metrics I need from my data. After the template is built, I would use it to add different data sets several times per week.
That's a great idea! You may also be able to use Power Query to automate gathering the data, then you can just point Power Query to a different file for each housing market. If you'd like to learn how to build dashboards, please consider my Excel Dashboards + Power Query course bundle available here: www.myonlinetraininghub.com/excel-dashboard-course
Thank you! @@MyOnlineTrainingHub
This may seem silly. But I struggle with modifying pivot table formats. I try to create from an existing layout, but they never come out the way I want. I end up using formatting using format/cells.
There is no such thing as a silly question :) Struggling with PivotTable formats is a common issue. PivotTables often use default formatting, which might not align with your needs. Here are some quick tips: use 'Field Settings' to adjust the layout and format for each field, explore 'PivotTable Styles' in the Design tab for predefined styles, and ensure 'Preserve cell formatting on update' is checked in PivotTable options. This will maintain your formatting even after refreshing the data. Consider changing the report layout under the Design tab for a more customized approach. Hope this helps!
Thank you for sharing!
My pleasure!
Dax functions and how to apply then to real cases. thank you. great video!
Thanks for watching! Will keep DAX in mind. 👍
Hi Mynda. I always have trouble with Index and Match to the point I will avoid it all costs if possible.
Ah, that's a shame. Don't give up on it. One day it will click for you. Here's my 4 minute video that might help: ruclips.net/video/-4yCXpv-drg/видео.html
Thank you Mynda.
Thank you for watching and for the comment 😊
Excellent Mynda!!
Thank you for watching and for the comment!
Well, since you asked…. My biggest (hmmmmm….) is how to read the order of NESTED formulas.
In other words, which part of the entire formula is read first which would feed the information needed to activate the second function which etc etc. That would be a GREAT help. Thanks
Check out this video on formula writing: ruclips.net/video/Uq4C9htBzU8/видео.html
This is amazing, thank you Mynda.
Thank you for watching and for the support! It means a lot 😊🙏
Would be the world of List function in Power Query.
Thanks for sharing. Check out these videos: ruclips.net/video/U97r2_O8IyI/видео.html and ruclips.net/video/ljnlEcRxZlo/видео.html
Hi videos are always useful. Thank you so much. My query is related to using power pivot.
1. I struggle with building relationships
2. To combine tables as you have done in this video, do all your separate tables have to have same fields etc for them to work in the combining?
Thank you! To answer your second question, the first file (sample file) has to contain all the columns your other files could possibly contain, but the other files don't have to contain all the same fields. Note: this combining of files in the video was done in Power Query, not Power Pivot.
Building relationships in Power Pivot can be tricky and it's such a critical step in ensuring your model works correctly that it might be worth you taking a Power Pivot course to learn the fundamentals: www.myonlinetraininghub.com/power-pivot-course
Perfect information thanks a lot 🥰🥰🥰
Glad it was helpful! 😁
😀😀😀Great tutorial thank you!
Glad it was helpful! 😊🙏
power query data cleaning 😊
Is the best 😉
thanks
My pleasure 😊
And for big data, that require more than the limit rows excel offers (1,048,576) what will a solution to data analytics? I'm so used to do everything in excel, but I have a special task to do that involves at least 5 files with that limitation of rows.
You can still use Excel. See this video which uses Power Query and Power Pivot which can handle 100s of millions of rows of data: ruclips.net/video/McLQ3gEGbHQ/видео.html
IHi nterested in preparing data for dynamic dashboard step-by-step guide. Thanks
Awesome. Check out this dashboard playlist: ruclips.net/p/PLmd91OWgLVSLy87GfZCR74BG1nhO7ER0s
Excel's Array concept? How it works, when we will use it in forumals .
Thanks for the suggestion. I have found arrays much easier to understand since dynamic array formulas were introduced: www.myonlinetraininghub.com/excel-dynamic-arrays
Thanks a lot, love it!!!
So great to hear 🙏
Thank you so much for this tutorial! ❤
You are so welcome!
thank you very much.
Welcome 😊
Thank you Mynda. Regarding your question, yes I have the feature of (=SEQUENCE(1,141,MIN(C9:C58)+Work!K5,1)) what if I want to show the project baseline on weekly basis?
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Dear Madam, Thank you very much for the "ANOTHER GREAT VIDEO". I have a request, how to creat an excel formula, the inside of an excel formula contains, arrays, conditions, etc etc so while creating the formula how to understand that elements and putting them correctly, I hope you got my point. I will be very grateful if you make it easy for us.
Great suggestion! 🙏
You asked what is one thing we wish we understood better. For me, that would be using DAX with Excel (e.g. measures)
Thanks for sharing. DAX is one of those things that requires practice. If you'd like to get your skills up to speed quickly, please consider my Power Pivot and DAX course: www.myonlinetraininghub.com/power-pivot-course
@@MyOnlineTrainingHub thank you very much for the suggestion. I will follow your advice. Cheers!
I wish if i could understand DAX better. Please do a video series in this.
DAX is a great skill to master, but it takes practice. I have a Power Pivot & DAX course here: www.myonlinetraininghub.com/power-pivot-course
Power Query, everyone in my work is using it now and the advanced editor in it
Power Query is, indeed, a feature that many Excel users find hard to work with. I have a playlist dedicated just to it: ruclips.net/video/wk1s0wE5Gww/видео.html&pp=iAQB I hope you will find the answers to your questions there 😊
Excel has a native histogram feature under: Insert -> Chart -> Insert Statistic Chart
Yes, but unfortunately it doesn’t work with PivotTables, which is why I used the regular column chart.
Hi mam could you also give suggestion how to link excel spread sheet from share point to qr code
Just get the SharePoint 'share' link for the Excel file and create a QR code for it.
power query more realistic examples compare to last video (containing 4 examples) explained
Thanks for the suggestions 👍
what excel function do i need to improve on? If Then functions. I get confused on the syntax, when to add a parenthesis, etc. 😟
Check out this video for IF statements: All 3 Excel IF Statements + how to AVOID PERFORMANCE ISSUES!
ruclips.net/video/0zTeXujtOZI/видео.html
@@MyOnlineTrainingHub thank you!
You are looking Beautiful.
Thanks for the video.
can you please make video on Substitute function in excel (Advance level).that i wish to understood better.
Thanks for the suggestion. 👍
Many thanks for asking. I would like to learn relationship for Data Model in excel
Thanks for sharing. Will keep it in mind.
Amazing! Thanks.
I like : pivots and Data Analysis tools and I'm eager to know about Power Pivot.
Question thought : is 'Office Pro Plus 2021' able to provide this IA Data Analysis tool?
Thanks a lot
Glad you liked it. The Analyze Data tool is only available in Excel 365, sorry.
Super
Thanks 😉
Great video! I do like using Power Query and Power Pivot. Personally, I'd like to know more about the Lambda function.
Awesome to hear. I've done a couple of videos on Lambda, but I'll keep it in mind for more.
Hello, I've building a pivot tabel of my company's balance. It's works fine except for the fact that i don't know how to handle the 5 level i've used for classify all the balance voices. I'd like ask you if exist a tecnique to avoid in a pivot tabel to see empty level, of course i would like to don't use +/-. thanks in advance
Difficult to say without seeing the file and structure of your data. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Good stuff "mate", thanks again! I would like to know more about power query and then specifically how to handle data from multiple sources which contain different deliminators and thousands/decimal settings. For example, masterdata is loaded from an Excel file (with US/English settings on laptop) and the sales table from a CSV (produced from a Dutch database). I somehow always struggle to get this right.
Thanks for watching! Each of your queries would have to be created separately and cleaned before appending/merging them (assuming that's the end goal). I'm not sure there's an easy way to help you remember the steps as such, but once you set up the queries, you should be able to reuse them for future files.
@@MyOnlineTrainingHubThanks for getting back! I just found the "using locale" option, in the "change type" submenu. And it seems I can deviate from the system settings like this.
7:03, I understand why you put the discount% in the values section of the pivot table, but why put the discount% in the rows section?
By putting the discount also in the row labels I can group them into bins.
Oh man! Where was this video 13 years ago? 😉
To answer your question, it would be macros. BUT, from what i know and do, I've just stopped using macros because they "break" all the time, require maintenance, and can destroy the sheet, etc. 😎
Yeah, I try to avoid macros too 😁
I wish it was. Data analytics
Hopefully this video will help you get started with that.
Topic which I wish I understood better: recursive lambdas
Agree, they can be mind bending 🤯check out my video on them here: www.myonlinetraininghub.com/recursive-lambda-functions
I wish I understood better how I could automatize the integration of csv file and split concatenated values from one cell in dedicated Column with corresponding headers e.g in one cell (Length [mm] : 20; Height [mm] . 12; Depth [mm] : 5) tranform to columns with Header Column 1 : Length [mm] : each row value corresponding length e.g. 20 Header Column 2 : Height [mm] ....
Sounds like a job for Power Query. If you get stuck, post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Which function replace vstack function? I don't have Microsoft 365.
There’s no replacement/alternative for VSTACK.
Can you use this with warehouse stock? Love watching your videos I get some great information from them
You can use these techniques with all sorts of data.
Power BI/Pivot (DAX Functions in a bit detail), I wanted to learn better
Thanks for the suggestion. Please check out my Power Pivot & DAX course: www.myonlinetraininghub.com/power-pivot-course
power query, my pdf bank statements can never be loaded
Hi, is there any formula to calculate the no of fractional months b/w two dates.
Both start and end date should be included.
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Hi, thanks.. I will post my query in forum with sample excel file.
Power Query and M Language
Power query is my number 1 Excel skill you must learn 👍 check out my course here: www.myonlinetraininghub.com/excel-power-query-course
When I was working, I did a kind of recertification using Excel, and the data was massive. Although I had a few days after training to go about it, I was not familiar with it.
Do you know what that could be?
No idea, sorry.
@MyOnlineTrainingHub It's reconciliation, not recertification.
By and far the lambda function has me the most confused and I wish i know where and how to apply it.
I hear you! 😁 check out my LAMBDA video here: ruclips.net/video/cGxjWOY8h98/видео.html
does anybody know how to enable de AI field to ask questions in the data analysis part in power query? I don't have it
Power Query doesn't have AI...yet.
index match
Check out this INDEX & MATCH video that'll get you up and running with it in a few minutes: ruclips.net/video/-4yCXpv-drg/видео.html
Nice shirt. 😊
Thanks! 😁
I have not figured out how to work with excel in a good way for statistical analysis.
Lets say I have 10-20000 rows of data from four pumps in a factory. I plot a regression that shows there are some outlier points. How do I exclude these points moving forward in the statistical analysis (before I make more calculations or the regression analysis etc.) ?
In the plot I can see the x,y of the points, but going into the table with 10000+ rows it is a lot to scroll down and find the points and delete them. Using filter on the column dosnt work either as there are data for the other pumps in the same row that is not outliers.
In R/Python there are some options forexample making a new df excluding these points. But I dont an Excel equavalent to this way without ending up with alot of individual tables.
What if it is not outliers I want to remove, but only make the an regression analysis on a section of data and another analysis on another part.
You could possibly use the FILTER function to exclude the outliers and create a subset of your data for further analysis. FILTER function is covered here: ruclips.net/video/ZCQAweoAdOw/видео.html
My issue is using Power Query to clean up data generated by badly designed reports. I have tried to use Power Query but can't seem to get data into the proper rows and columns.
Thanks for sharing! In future if you get stuck, you're welcome to post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
I wish I could learn VBA. I want to learn it so bad. I dont know where to start. I am Chartered Accountant and proficient in excel but VBA is the missing piece.
What do you want to use VBA for? Many times VBA is not required and it's even less useful now that we have Power Query to automate the gathering and cleaning of data.
Thanks for the video! Always very useful! However I've experienced some issue regarding "Analysis ToolPak". It seems activated but no button was shown on the ribbon. I tried to find what's the problem on the internet but without success...
Hmm, not sure why you can't see the button. Try rebooting your PC/restarting Excel.
@@MyOnlineTrainingHub I even tried to logoff and login from my company account but nothing. Very sad about this 😢.
I tried to use “Analyze data” (as explained in your video) and looks very cool! But I think that this add-in seems to have more functionalities!
List function within PQ.
Thanks for sharing. Check out these videos: ruclips.net/video/U97r2_O8IyI/видео.html and ruclips.net/video/ljnlEcRxZlo/видео.html
how to learn all of this?
You can take my Power Query course here: bit.ly/powerquery0124
Dear Mynda,
Data files from January to November are not available for download.
Oops, try again now.
@@MyOnlineTrainingHub Tanks. 🤗
Power quarries
Check out more on Power Query here: www.myonlinetraininghub.com/introduction-to-power-query
Hi Mynda, could you share a link in your videos where I can find out how to add funds to my stocks portfolio account,
I am uk based and use mainly uk stocks but now I have a couple of funds us as as India and uk but I can’t seem to find the correct way to add them to my excell
I have the correct ticker but I cant find how to add them in, as excel wants to give me other tickers 😂
Any help would be greatly appreciated thank you
Kind regards kev
I don't have a video on this exact scenario, but I suspect it will require the India stock exchange followed by the ticker.
Ok thank you for your very quick reply..
For example i put theses 2 in like this into spreadsheet HSBC FTSE 100 INDEX INCOME C, but the reply i get is this HSBC HOLDINGS PLC (XNYS:HSBC) and doesn't see anything else
Vanguard U.S. Eq Idx £ Acc, and i actually hold these two in my account. so i know they exist 🤣
@@MyOnlineTrainingHub
😊
Glad you liked it!
HI there. first of all thank you for your intelligent videos , second : i would like to ask for your permission to use the data you used in the budget vs actual vs plan video ( I dont want the dashboard i just need the data sheet as it would help me in designing sample data for my clients . )
Glad you found them useful! You can use my data for non-commercial use.
@@MyOnlineTrainingHub sorry but is designing a budget file from my own with this data and put it on upwork as a portfolio would be considered commercial ?
Sorry , can you tell again who can have the ANALYZE DATA feature?Thanks - I cannot work it out how to add it...365office account and also personal 365 version license.
Analyze Data is available to all 365 users AFAIK. You don't need to add it. It should just be available on the Home ta.
@@MyOnlineTrainingHub Only for English version. I have the Czech version....Thank you for answer🙂
Data analyzer
Thanks for sharing, David.
Hi, why do you use the average? Do you want to calculate an average of % GP (what meaning?) or % GP per country? this is not the REAL %GP!! Indicators (measures) such as % margin, % Discount, Price ... never calculated in ETL process.
Good questions. The inputs are up to you. The purpose of the video is to show you how you can use Excel to perform common calculations that you might ordinarily do in a more convoluted way.
PowerPivot is not my favorite. But thats just me.
With practice you may grow to love it 😁
I will kill to understand DAX and create measures 😢
In a lot of ways DAX is very similar to Excel, but there are some nuances that make it mind bending 😁 check out my Power Pivot & DAX course here: www.myonlinetraininghub.com/power-pivot-course
Will do. Your videos are helpful, thank you so much
Of all things, unironically Sumproduct.
Here's a video on SUMPRODUCT: ruclips.net/video/tF1kIdki64I/видео.html
Lamba function
Thanks for sharing! Check out these vidoes: ruclips.net/video/XAGdSKMV4wA/видео.html and ruclips.net/video/cGxjWOY8h98/видео.html