Full Project in Excel | Excel Tutorials for Beginners
Вставка
- Опубліковано 21 лип 2024
- Take my Full Excel for Data Analytics Course! www.analystbuilder.com/course...
In this video we walk through the data cleaning process and dashboard creation in Excel.
Excel Dataset: github.com/AlexTheAnalyst/Exc...
____________________________________________
SUBSCRIBE!
Do you want to become a Data Analyst? That's what this channel is all about! My goal is to help you learn everything you need in order to start your career or even switch your career into Data Analytics. Be sure to subscribe to not miss out on any content!
____________________________________________
RESOURCES:
Coursera Courses:
Google Data Analyst Certification: coursera.pxf.io/5bBd62
Data Analysis with Python - coursera.pxf.io/BXY3Wy
IBM Data Analysis Specialization - coursera.pxf.io/AoYOdR
Tableau Data Visualization - coursera.pxf.io/MXYqaN
Udemy Courses:
Python for Data Analysis and Visualization- bit.ly/3hhX4LX
Statistics for Data Science - bit.ly/37jqDbq
SQL for Data Analysts (SSMS) - bit.ly/3fkqEij
Tableau A-Z - bit.ly/385lYvN
Please note I may earn a small commission for any purchase through these links - Thanks for supporting the channel!
____________________________________________
SUPPORT MY CHANNEL - PATREON/MERCH
Patreon Page - / alextheanalyst
Alex The Analyst Shop - teespring.com/stores/alex-the...
____________________________________________
Websites:
GitHub: github.com/AlexTheAnalyst
Instagram: @Alex_The_Analyst
____________________________________________
All opinions or statements in this video are my own and do not reflect the opinion of the company I work for or have ever worked for
I don't know how to thank you Alex! For you and your videos , I got my first job today as a Data Analyst. Thank you so much. May God bless you♥️
in which company? can you give some reference?
In which company?
WoW Good luck
I hope i too score a good job or long term projects soon.. 💯
Can u suggest me where can we practice of excel
For data analytics do we have knowledge of VBA and macro
Thank you Alex for the knowledge you are imparting to us. I can listen to you forever. We appreciate you. I used this formula formula to create the age bracket which was much simpler so anyone interested should try it;
=IF(L2
IFS is the way to go here. For the purposes of the video, its great to show the nested IF statement, but IFS can be a simpler solution.
Great tips! I chose to use the terms Senior, Middle Age, Young Adult, and adjusted the age brackets so they were more even to Senior 55+, Middle Age 35-54, Young Adult 18-34. I sorted the age column from smallest to largest and noticed there's nobody younger than 25, so I left out "adolescent." The fact that there are no bike purchases between the ages of 18-25 is interesting.
Thank you for this! The IFS() statement is much nicer to look at. Since the formula tends to be on just one line, the nested IF() can be hard to look at but the IFS() is far less cluttered
I was gearing up to write this exact statement 😂
Thank you so much. This was really helpful
Timestamps ✅
02:03 Setting up and Cleaning Data
14:00 Making Pivot Tables
29:10 Creating a Dashboard
35:20 Adding Filter Buttons
39:34 Outro
Why they won't provide how client approach. Or dashboard sample.
Thanks Alex for this video, pls how do u structure the data? I tried mine it is bringing out "this formula is missing a range reference or a defined name" pls what is the way forward. really looking forward from hearing from u.
Could you please share me the raw data , I could not download that file from github
Your video just taught me how to make my first dashboard. It's amazing how you show the world that everybody is a learner and nobody is just perfect. That is humility in it's purest form. THANK YOU SIR.
Man, your channel is beyond amazing. Thank you for all you're doing for the community. It's really priceless.
Thank you! I appreciate it and I'm glad it helps :)
Thanks, Alex, for putting all of this content out there; it really helps in improving my skills as an entry-level data analyst. I was able to move the 10+ miles to the bottom without having to edit it to "more than 10 miles" by right-clicking on it and using the move option.
Thank you!
I can't even begin to thank you, I finally feel like I can actually navigate Excel. I binged the whole series and just finished the project. I'm now excited to try out my new skills. You are the best!
I actually recommended your data analyst bootcamp playlist to one of the doctors in my university. This course is a godsend to anyone who can't afford to pay for paid courses or formal education.
Thank you so much for creating these fascinating tutorial videos!
I was just thinking of building a project to showcase my excel skill, and your video just showed up right away!
Have been following this channel for a while, and this is the most informative and supportive channel I've ever seen!
Thank you so much, Alex!
I just completed the entire excel series. It was the first time ever that I created an entire dashboard myself. I feel really good. And as a beginner in the field, the basic steps to learn about cleaning the data and why that step in the cleaning process is being performed played an important role in understanding its importance. Thanks, Alex!
can you please upload the excel files
are you from IT background?
@@devanshgoyal3188 did you get excel files?
Pls kindly upload the excel files
hello, can you kindly point me to where I can download the dataset used in the video, I have tried downloading the file from the link he provided but it's not working
I like how Alex run into problems and walk you through how to solve them. Just as it's done in real life.
It felt so great achieving the project and having this complete beautiful dashboard! I thought Pivot Tables, Dashboards and other Excel features were hard which made it intimidating for me to learn Excel, but after finishing your tutorials turned out things are actually easy and simple! Thank you Alex for your time and hard work!
I just finished the whole Excel Tutorials for Data Analyists playlist, and it's absolutely fantastic! Really helpful and added a lot to me.
Thanks Alex for the simple yet awesome way of delivering the message!
Please, how do I get the complete video of the data analyst playlist?
Something very cool to know when your making dashboards is that if you press "Alt" while your dragging your charts/slicers/bottons/whatever they will align automatically. This helps a lot to make a cleaner dashboard
Good evening please how did you get the link to download the excel data used for creating the dashboard?
@@oladiipooluwajomilojuibuku7759 The excel file is in the description
@@NgocNguyen-oj4tv but how to download I can't download from there
@@VitthalGaneshPagrut Click on "view raw" or "download"
The best advice, thank you!
I'm on my 6th day of my official Data Analyst journey and I love to experience that even if I have advanced skills in Excel, I can still learn new things from your tutorials. I really liked this project and I can't wait to continue with your Tableau tutorials tomorrow ❤❤❤
Really enjoyed this project! I am a new analyst right here with you in Dallas and this is helping me build my portfolio and moving toward getting hired as an analyst! Thanks!
You are a mind reader.. this channel is a blessing to all of mankind. ❤️
Loved the parts when you say, "Let's think about this", "Nevermind. Guys, that was a terrible idea", and "Excel is trying to destroy my whole video." lol These types of issues shown in real-time where you then walk us through how to solve the problems is why your content stands out above all the rest. As always, THANK YOU ALEX!!
Your Excel course is by far the best course that I have watched . Thanks a lot for your attention and kindness . Really useful and optimum
Thank you so much Alex I created a dashboard in Excel by following your Excel series and as a beginner in this field I feel proud.
Hi Alex!
BIG SHOUTOUT to you for coming up with all these AMAZING REALISTIC videos.
Currently looking for Data Analyst job, and just dropping by your latest video to say THANK YOU!
Thank you for the project. I have been looking for such videos lately. Marriedarrtial Singlestatus sounds like a Harry Potter spell.
😂
😂
😂
I unknowingly casted the spell too .
I worked through this project along with you and I really appreciated brushing up on the keyboard shortcuts and the slicers. Thanks for the content!
Thank you very much Alex. Words can't really capture my gratitude - when I land my first DA gig, I'm definitely sending a "thank you donation" your way.
Thank you so much for this whole series. You helped me refine my basic skills and create my first dashboard. Amazing work man. Please keep it up!
Wonderful video! Thank you Alex
I've spent most of my time learning formulas and concepts in excel but haven't put together any project yet.
This is a great idea for things I can do going forward
Thank you so much for the guidance! I will link my final presentation below with a full breakdown of business task (that I made up, lol), a summary of the findings, deliverables and insights, just to show y'all. Before this I had struggled to even upload data into BigQuery, downloaded tons of software, etc. You were so helpful!
Hey Alex , i just love the way you trying different things to resolve a query and you also shows this in a tutorial so that we can get comfortable with the tutorial
I just completed the excel series in less than 24hrs leisurely! I have to say as someone who was intimidated by excel for some time, you have not only managed to help me understand it, but somehow made it enjoyable. For anyone who sees this I suggest you go through the entire excel series, believe me it is worth it! Thank you so much Alex for your time and effort!
I am unable to download the project dataset any solution pls
I like this video a lot. I love the fact it has a clear goal and you know your audience. I also like how you explained why you have a working sheet and why you are looking through the data to see if it makes sense. The fact you are detailed when explaining your thought process made it easier to understand!
I had no idea you could Find/Replace by row or column!
So glad to hear it was helpful!
Thanks Alex for this project. I have to go through this 2-3 times to get a better understanding of so many new concepts.
Learnt a lot from this series. 🙏
I wanted to learn data analysis on Excel and this is the type of project companies asked for in OA's. I'm really glad that found out your channel. It's really helpful. Thank you so much
Hi, thanks for the resources been shared out.
Pls as a new beginner in data analyst, must we know all the software for data analysis or we should base on a particular one,
Pls replying to this will be helpful.
Thanks.
I've been doing all i can to self study comp sci and data analytics so i get my first job and fund school and turn my life in a new direction. I quit my job so i can get real time to invest in these aspirations. I'll have a job before October. Thank you for doing all this, education should be free for all and i wish great blessing to you and your family thank you so much for giving to so many what the system refuses to make readily available. When i get the job i will be back here again. A great day to you Alex :)
did you get a job?
@@HeavenlyCriticotoo early I think.
Where can i get jobs in this field, i would wish to earn from this skill.
Alex this is great!
I was an active duty Air Force Analyst for 7 years (currently in the reserves now), and i have to say that this is spot on. This is basically how we built our reports and then used PowerPoint to present them. Thank you for all these amazing tutorials !
Thank you for your service, Brandon!
So awesome! Thank you for your service :)
how did you use PowerPoint to present the dashboard?
Alex ruthlessly throwing his Dad under the bus by making sure he's in the old category! Excellent content - much appreciated.
Thank you very much Alex, I just built my first dashboard in Excel following this video and the result is amazing. keep up the good work.
Love the quality of your videos Alex. Thank you so much for everything you do, I know it's not easy.
Continue these amazing videos...you are a one-stop-shop for all the data analytics related content...thanks for your efforts
One of the best videos i've seen so far.
Congratulations Alex, i just don't know how to express how this videos has helped me in my careear.
This is my first time of learning Excel(data analytic). And I learnt a lot, thank you, thank you so much for sharing your knowledge with us 🙏🏼❤️
Thank you for being such an easy going teacher I finally got to this wonderful video. I have a Mac and one thing I figured out is that Excel isn't as user friendly than what it would be if I was to use a Microsoft computer lol go figure. Anyway, Ctrl+A was not working for me but I found out that Ctrl+Shift+v+> works just as well to select all data. I learned a lot from your teachings thank you Alex!
Great project Alex! Projects are the best way to learn and apply your knowledge.
Thank you so much for your information. I got so lost in term of building Excel Projects for my porfolio. You really save my life.
I must confess, I love you!! This is one of the most interesting walkthrough projects I've come across. I followed through and I laughed alot. Thank you so much for this. I hope I'll become the data analyst I dream of.
Hey Alex. This was an amazing tutorial. For the 10 + Miles portion you could have just dragged the column down to the bottom vs renaming. You have to click on the cell right near the bottom border and the cross sign will turn into an arrowed cross sign. When you see the arrowed cross sign just drag it to where you want it and it will bring the corresponding cells to where ever you choose to move it.
By dragging column here, I think you meant row. I did that too initially but realised the it will go back to it's original position every time you refresh the data. So it's better to correct it from the data source
@@taoridirafiu8289 I tried refreshing the data after pulling that row down and it's completely fine. It doesn't go back to its original place and so I think this works!
Another option is to add a blank space between 10 and +. It worked in my pivot table / chart.
Great video! Certainly helpful for beginners. This may be role specific but for me, over 90% of my projects need to be repeated by myself or a user. A very small number are adhoc so I end up automating most of my reporting. It may be helpful to your viewers to make another video where you automate this same report for future data pulls. Power Query/VBA/SQL/Python are generally my tool of choice, depending on the needs of the project.
One more note on why I think it's important to automate (for any beginners that may be reading). If you spend all day refreshing reports using a manual process, you'll have no time to develop new solutions for the business.
One more note for beginners. Alex knows his stuff and is a good resource. His experiences are very similar to mine, just with a different beginning. I find myself often just nodding along.
I haven't gotten to this full project yet but I THANK U for your insight!
Big upvote for you (if you haven't already) to add an automation video per @Cappernikus suggestion @Alex.
I agree about automating the reporting. Especially if you have continuous updates.
Hi, thanks for the resources been shared out.
Pls as a new beginner in data analyst, must we know all the software for data analysis or we should base on a particular one,
Pls replying to this will be helpful.
Thanks.
Hey how do you automate a report? Ima recently hired data analyst that would be very helpful for me at my job
Just completed The project gonna add it to my portfolio THANK YOU SO MUCH ALEX u are helping a lot n if I get a job in Data Analytics its all thanks to u
Thank you so much! Retired teacher here switching careers - so I'm a little on the 'older' :) side. Your videos are helping me so much! I just finished following along with you on this tutorial. You gave me such a boost of confidence that I can do this! Thank you, again!
Please how did you get the data he used in this video? he said he's going to provide a link but I can't find the link
I am an aspiring data analyst and your videos are a great help for beginners like me. Your videos are very informative and easy to grasp. I am learning a lot. Thank you
Hey. How is it going?
How is the going...I am starting now😅
@@lifewithwambuimuthaka hows the going am starting now
@moseslugalia4690 with excel now I can say I am an expert...
Excellent, just finished a couple of Excel courses and with this video I been able to put all the learning in one project, thank you so much Alex!
That's great! Good work!
Hi, thanks for the resources been shared out.
Pls as a new beginner in data analyst, must we know all the software for data analysis or we should base on a particular one,
Pls replying to this will be helpful.
Thanks.
I can't express my gratitute Alex after watching the series i realized in the data world anyone can moved if they have followed the path which you have showned in the series.
Thanks a lot May ALLAH Bless you Man..❤
Great video, Alex. You are my first and best teacher in my DA journey. Thank you, thank you, thank you. Now on to the lovely SQL.
Hi Alex,
28:44 wasn't a terrible idea lol. The rows weren't ordered by ascending hence the change in chart. I'm definitely adding this to my work. Thank you!
Thank you so much Alex. As a nurse trying to get my foot into data analysis i have found your channel so helpful. Thank you for the time you pour into creating for us such exceptional content for free. Much love from Uganda 🇺🇬
Sister God Bless U for being a nurse ok. I know it isn't easy at all and I can empathize transitioning into a different field. But Thank U for your works and best wishes sister in doing data analysis work.
@@AljIsHere128 yes its no easy task but i'm putting in the work and I'm progressing and once i grasp the data analysis concepts i will merge it with my medical knowledge. Thank you so much for reaching out and God bless you too
You’re most welcome! Glad to help :)
Plenty of health domain-specific information for you @Ruthy - you can utilize both skillsets for much higher impact because of it.
I had the privilege of doing some off-grid medical missions next door in Tanzania not far from Uganda and am very hopeful for health improvements in your corner of the world.
You might also find Omdena to be an excellent place to apply data skills for global good - or to eventually partner with them.
Thanx a lot! Your videos are the best. The most important thing in your videos is the sequence - it is just so right to understand analytics the best way.
Awesome.... I have created quite a number of Excel Dashboards in the last couple of years but still I did learn something in this Excel series... Thanks Alex
IFS alternative and short method for your IF function @12:00 which is =IFS(L2>55, "Old", L2>=31, "Middle Age", L2
i'm also exactly thinking that .
Thank you Alex. This is what i was looking for. A simple real project. It was so much helpful!
Hi, thanks for the resources been shared out.
Pls as a new beginner in data analyst, must we know all the software for data analysis or we should base on a particular one,
Pls replying to this will be helpful.
Thanks.
Thanks Alex!
To be Frank I have done many courses but never did a project once. Thanks for your guidance, this is really helpful !!
Thanks Alex, this project was my first hands on experience in creating portfolio projects, looking forward to your other vids.
So dependable! Thank you for the consistent quality content Alex! :)
I try my best! :D
Awesome video! One edit I would make is in the Alignment section and setting up the Dashboard. You can have the charts snap to each other instead of trying to manually line them up. If you click on a chart, go to Format > Align > Snap to Shape. You can also have it snap to the grid as well.
like holding alt when moving?
Thanks a lot Alex! I have just started my path to become a data analyst and this is the perfect project to learn fast !!!
Thank you Alex for this wonderful video. what's most important is u make us change the way we think about numbers and data
Thank you so much for all your mentoring, it has been a great help in my transition to my dream job. I just absolutely love your content and way of teaching. I have learnt a lot from you.
00:00 Learn to create an Excel dashboard with interactive filters
05:08 Cleaned and formatted data for visualization
10:26 Creating pivot tables to build dashboard visualizations.
15:38 Creating a visualization to analyze income and bike purchases
20:27 Creating three pivot tables for bike purchase data
25:42 Create a dashboard to visualize bike purchases by age group.
30:55 Creating a clean dashboard with aligned elements
35:55 Create a dashboard with slicers to filter data by demographics.
Alex, no words to express my gratitute for all your time, effort, patience!
You're helping lots of people - myself included!
Wish nothing but all the best, multiplied by 100.
God bless, man.
Your words @victorbegnini5754 exactly! Thank you Alex!
@@lorraineantonie3775 👏🏻😄
I'm a beginner in becoming a data analyst and I will love to be interacting with a guide. My seniors in the program, I will appreciate to have you guide me through both from knowledge and experience
Great video! A tip for rearranging the order at 22:00, and still make it look clean, is to make it " >10 Miles "
Good idea!
You can also Sort > More Options > Manually sort just FYI!
I put "+10 Miles" too great ideia =)
I needed this. An awesome video. Thank you, Alex.👍👍🔥💯
Hi, thanks for the resources been shared out.
Pls as a new beginner in data analyst, must we know all the software for data analysis or we should base on a particular one,
Pls replying to this will be helpful.
Thanks.
Hi Alex,
Thanks for this amazing excel series, this is my first time learning excel and I must say, it have been the best learning process for me, I can't wait to jump right into your other resources for Data Analyst.
I finished the whole project! Thanks so much! It gave me confidence that I can do this!
Hey Alex,
Learnt alot from this video and did the project simultaneously while watching the video.
Please do post more videos related to such project from intermediate to advance level.
Love from India ❤️
I'm 34 yo and today I learnt that I am a middle aged man Lol Thanks for this awesome video!
Thank you Alex. You deserve a GRAMMY award for all the good work you putting out here
I just completed my own dashboard. Thank you Mr.Alex. I learned a lot from your videos 🎉
I recently started Google's DA course on Coursera based on your recommendations and I found this video really helpful!
However, I had a few questions while I was watching the video
1. Is there a better way to categorize ages? Nested Ifs will not be ideal if you have more than 3 categories.
2. I don't quite understand the placements for categories in the pivot table, how do I know where to place the categories among rows, columns and values? From what I understand, values is what you want to visualize and rows and columns are interchangeable, is that correct?
3. Charts! Line chart felt really off for bikes purchased vs commute distance because it's supposed to be used with data that changes over time, stock price for example, and I thought that it didn't really provide any good insight. Wouldn't a bar chart or stacked bar chart have been better for the commute distance vs bike purchased?
4. Regarding the chart for age bracket, line chart looks really pretty! but I think histogram might have looked better for the uncategorized ages because I aim for line charts only when it deals with variation over time. Any insights on what charts are 'industry standard' or do we just go with what looks best?
5. I recently learned that dashboards are used to provide real-time data analysis and I fail to see how the dashboard you created achieves that. It felt more of a report than a dashboard.
Also ++ Cappurnikus down in the comments for great suggestions.
Thank you so much for this! Excel used to intimidate me but this tutorial helped me become more comfortable with it and learn more things about it!
For the first-time, I create dashboard of my own. Thank you for all the knowledge you pass willingly to your learners.
please how did you download the dataset? I'm unable to download the file from the link he provided
Thank you appreciate it - Have watched all your excel videos under the Data Analyst Playlist! You rock!!
And.. this marks the end of my excel tutorial w Alex, and omw to making my own first dashboard. Thank You so much, Alex!
This is brilliant Alex! This is my first time executing the dynamic dashboard. I fell in love with it!
I'm very glad that i found your channel at the correct time which I needed the most and I've to tell that your teaching is very understandable thank you so much I'll keep following your videos and will learn more and more✨
Hey Alex, thank you very much for your content, I almost have a whole portfolio setup, thanks to you. I wanted to add, 29:13 I just sorted the Age Bracket Column on the Pivot Table in Ascending Order and the line chart came out just fine
Absolutely amazing, I knocked out this full series today. I can wait to make my own Excel project.
My first actual Dashboard/ Small project!
I thought that MS excel was too complicated to start with but you made this one a walk in the park! I have zero knowledge of data analytics
Gonna go watch the Power BI series now!
I am so ecstatic! I just finished your Excel series and am currently working on the Excel project. I am very thankful for your short but concise teaching videos. Next stop: SQL! :)
hey bro good to hear that u done it but i wanna help from u that 7:54 using IF statement not work for me and popup that formula u type contain an error
First iteration done! Thank you Alex for this content. More power to you!
This is a brilliant content Alex! I really appreciate it, it's informative and really helpful to any beginner Excel user.
in 22:20, it helped writing as :"10 Miles +" Like that with space worked for me. Thank you for the tutorial! Really impressive job!
Thank you so much Alex, following your video step by step made me to create my first dashboard and i am very proud of my self staring at it.
I'm so glad I found you on UA-cam. You really have changed my life with the work you do. Thank you!
So awesome to hear! I'm glad you found me too :)
Thank you so much, Alex, for this beautiful series. I have been learning data analysis, and your channel has been super helpful. Keep up a good job!
Awesome video as usual Alex! Small tip for everyone on min 28:30. You can name it however you guys want an simply dragging the cells to change the order, it is also doable by copying and pastying them. 👍
Thank you so for this Alex, as a beginner in this field, your video was very helpful, I just created my first ever dashboard after following the lay down process on your video. looking forward to more videos from you as I seek to get better. Thank you so much!
Can help me with the link
So happy I made my first dashboard in excel today 🔥🔥🔥.. thank you so much Alex
Thank you Alex for this Excel portfolio project upload, what an amazing job! I learned a lot and enjoyed it!
Thanks Alex for this Project. I was able to use the grouping of the age bracket after including the 0-30 for adolescent, 31-54 for Middle age and Above 50 by sorting the pivot table(age bracket column) alphabetically smallest to largest...the line chart will still be perfect.
Thank you for providing this tutorial on an Excel portfolio project Alex! I'm in the middle of it and I'm loving it so far! Will update you when I finish with my thoughts! I just finished the Google Data Analytics program & I thought doing an Excel portfolio project was a great place to start!
Cheers brother!
Great video, I had struggled with data cleaning, this made it easy to understand. Can't wait to put this to work on my data!