Data Analysis for BEGINNERS in Excel in 2024 | No Python Required
Vložit
- čas přidán 23. 07. 2024
- How to analyze data with no programming languages in 10 minutes.
👩🏫 Improve your productivity in Excel with Power Query: bit.ly/powerquery0124
➡️ DOWNLOAD the free example file here: bit.ly/danalysisfile
Have you ever looked at an Excel spreadsheet and wondered, "Where do I even start?" The numbers blur into a maze of confusion, and the data seems like an unsolvable riddle. All while the clock is ticking on your deadline. The good news is, as a data analyst, you don't necessarily need to use complex programming languages like Python or R. In this video, I'm going to show you a real-world example of how we can use Excel to automate gathering and cleaning data, analyzing and exploring data, and then visualizing your findings in a dashboard.
LEARN MORE
===========
📰 EXCEL NEWSLETTER - join 450K+ subscribers here: www.myonlinetraininghub.com/e...
🎯 FOLLOW me on LinkedIn: / myndatreacy
💬 EXCEL QUESTIONS: Get help on our Excel Forum: www.myonlinetraininghub.com/e...
⏲ TIMESTAMPS
==============
0:00 You don't need Python or R to analyze data
0:48 Gathering and cleaning data with one tool
4:28 If you want to improve your productivity
4:42 Creating key statistics
8:13 Analyzing data & creating a dashboard
9:55 If you don't have the Analyze Data feature
10:43 Analyzing data & creating a dashboard
#DataAnalysis #Excel #ExcelTutorial - Věda a technologie
❓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: czcams.com/video/wk1s0wE5Gww/video.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)
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!
100% my most favourite Excel instructor. Everything is done so well and clearly. Thank you and bless you always!
Wow, thank you! 🥰
Mynda you are the best! I am checking your news as often as weather forecast.
😁thanks so much!
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.
Welcome back, and thank you for sharing this.
Thanks for watching 😊
Really fascinating ; you made it really simply and believable.
Great to hear 🙏
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: czcams.com/video/o3ysGnl8ieM/video.html
And my video on LAMBDA: czcams.com/video/cGxjWOY8h98/video.html
Excellent! Thank you Mynda! I vote for Power Query!
Thank you 😊
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 🙏
Good Morning Mynda and thank you for all you do!
Thank you for the support! I really appreciate that 😊
This is amazing, thank you Mynda.
Thank you for watching and for the support! It means a lot 😊🙏
Thank you for sharing!
My pleasure!
Thank you so much for the video
My pleasure!
Thank you so much for this tutorial! ❤
You are so welcome!
Thanks a lot, love it!!!
So great to hear 🙏
Excellent Mynda!!
Thank you for watching and for the comment!
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
I loooove this kind of stuff. I didn't know you can do this in Excel!
So glad it was helpful 😊
thank you very much.
Welcome 😊
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
Perfect information thanks a lot 🥰🥰🥰
Glad it was helpful! 😁
Thank you Mynda.
Thank you for watching and for the comment 😊
😀😀😀Great tutorial thank you!
Glad it was helpful! 😊🙏
Thank you Mam you are better than the best.power query playlist is dream video series.
So nice of you 🥰
Keep Pressing. You are Dynamite!!
Thank you, I will 🙏😁
Dax functions and how to apply then to real cases. thank you. great video!
Thanks for watching! Will keep DAX in mind. 👍
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!
thanks
My pleasure 😊
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…
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
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.
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
Anything you could include regarding regular expressions would be good
Thanks. Will keep that in mind.
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.
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
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.
IHi nterested in preparing data for dynamic dashboard step-by-step guide. Thanks
Awesome. Check out this dashboard playlist: czcams.com/play/PLmd91OWgLVSLy87GfZCR74BG1nhO7ER0s.html
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!
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.
Super
Thanks 😉
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!
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.
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
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!
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, 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: czcams.com/video/wk1s0wE5Gww/video.html&pp=iAQB I hope you will find the answers to your questions there 😊
How about power automate ? Is that app any good ? How about using that to add the Dec data in the video
Yes, you could try using Power Automate to get the data, or Power Query.
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: czcams.com/video/McLQ3gEGbHQ/video.html
Great video as usual .
I believe when we upload files from a folder in power query and select the first file for sample query, if the position of the the file changes in the folder , it effects the query, right? Is there anyway to make it dynamic ? Other than selecting a specific file for sample query?
Well, about an excel function that I wanted to learn better is OFFSET, 😊 thank you
Glad you liked it 🙏 Good point about the position of the file in the folder changing. You could make it dynamic with a modification to the functions to pick up a parameter instead.
For OFFSET, please see this video: czcams.com/video/sRE9jAMqF7A/video.html
@@MyOnlineTrainingHub Thank you for the response:) I have watched the OFFSET tutorials, it’s really simple and that’s what actually I wanted, starting from a basic use to a complex!!
And , I don’t know much about using Parameters in power query, if you have done a tutorial already, pls let me know.
I have a question about combining files from folder, first row of all my files are header, so when I promotes first row as header , it doesn’t applying to the rest of the files, they remain in data and I have to filter to remove them. Is it common? Appreciated your reply. Thank you 🙏
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.
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! 🙏
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
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: czcams.com/video/Uq4C9htBzU8/video.html
power query data cleaning 😊
Is the best 😉
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.
Nice shirt. 😊
Thanks! 😁
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
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
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
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!
czcams.com/video/0zTeXujtOZI/video.html
@@MyOnlineTrainingHub thank you!
Which function replace vstack function? I don't have Microsoft 365.
There’s no replacement/alternative for VSTACK.
I would love to learn how to perform row-dependent calculations in powerquery.
Use case: every month I download a csv file of my electricity consumption which provides a snapshot of the meters in 15 minute increments. So when I sort my data in powerquery and I append a month's worth of data each time. I want to deduct row 2 from row 1, on row 2, to give me the electricity consumption in that 15 min window. I now run this in the data table in my workbook, but looking for ways to do this in powerquery instead.
Try this. Add two Index columns named Index 1 and Index 2 with Index 1 starting from 0 and Index 2 starting from 1. Then create a new merge query doing a self join where you connect on Index 1 in the first instance of the table and Index 2 in the second instance. Go with the Left Outer Join option.
From there expand the new column that will contain a table in each row. You'll probably have to sort your date field again. Then do a subtraction transformation as a new column (e,g. "Consumption" - "Consumption.1"). This will give you the difference for each time interval. Then delete the columns you don't need and rename the subtraction column.
@@brianxyz 😁 thank you!!! this is great, I just tried it out and it works to my expectation. Saturday night hobby :)
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!
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🙂
Would be the world of List function in Power Query.
Thanks for sharing. Check out these videos: czcams.com/video/U97r2_O8IyI/video.html and czcams.com/video/ljnlEcRxZlo/video.html
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 ?
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: czcams.com/video/ZCQAweoAdOw/video.html
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.
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. 👍
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.
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
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: czcams.com/video/cGxjWOY8h98/video.html
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.
I wish it was. Data analytics
Hopefully this video will help you get started with that.
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 😁
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
how to learn all of this?
You can take my Power Query course here: bit.ly/powerquery0124
power query more realistic examples compare to last video (containing 4 examples) explained
Thanks for the suggestions 👍
Dear Mynda,
Data files from January to November are not available for download.
Oops, try again now.
@@MyOnlineTrainingHub Tanks. 🤗
List function within PQ.
Thanks for sharing. Check out these videos: czcams.com/video/U97r2_O8IyI/video.html and czcams.com/video/ljnlEcRxZlo/video.html
😊
Glad you liked it!
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
Data analyzer
Thanks for sharing, David.
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 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.
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
index match
Check out this INDEX & MATCH video that'll get you up and running with it in a few minutes: czcams.com/video/-4yCXpv-drg/video.html
👍😎✊
Thanks for watching 😁
Power quarries
Check out more on Power Query here: www.myonlinetraininghub.com/introduction-to-power-query
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 😁
Vlookup
Check out this VLOOKUP video: czcams.com/video/4-5-TBhOP6Q/video.html
Lamba function
Thanks for sharing! Check out these vidoes: czcams.com/video/XAGdSKMV4wA/video.html and czcams.com/video/cGxjWOY8h98/video.html
Of all things, unironically Sumproduct.
Here's a video on SUMPRODUCT: czcams.com/video/tF1kIdki64I/video.html
Lambda and let
For LET and LAMBDA, please check out these videos:
LET: czcams.com/video/o3ysGnl8ieM/video.html
LAMBDA: czcams.com/video/cGxjWOY8h98/video.html
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