No video
Formulas in Excel | Excel Tutorials for Beginners
Рет қаралды 341,212
Күн бұрынTake my Full Excel for Data Analytics Course! www.analystbui...
Excel is one of the most used skills in the data world. In this series we will be walking through all of the most important topics that Data Analysts need to know in order to be proficient in Excel.
Excel Formula Template:
github.com/Ale...
Udemy Excel Courses:
Pivot Tables in Excel: bit.ly/3peELgG
Visualizations and Charts in Excel: bit.ly/30MU9HT
All Excel Essentials: bit.ly/3Fdk6iG
Outline:
0:00 Intro
2:25 Max/Min
4:33 IF/IFS
8:46 Len
10:30 Left/Right
13:29 Date to Text
17:02 Trim
18:27 Concatenate
20:45 Substitute
24:42 Sum/SumIF/SumIFS
28:02 Count/CountIF/CountIFS
30:36 Days/NetworkDays
____________________________________________
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.i...
Data Analysis with Python - coursera.pxf.i...
IBM Data Analysis Specialization - coursera.pxf.i...
Tableau Data Visualization - coursera.pxf.i...
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/...
____________________________________________
Websites:
GitHub: github.com/Ale...
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
@colourNika 10 ай бұрын
Also for those who has #value error in Days function (it's because your system date format doesn't match the given one). Select all dates (h2:h10) -> go to Data tab -> choose Text to Columns from there -> Delimited, Next->, Next ->, step 3 of 3: column date format and chose DMY -> finish
@colourNika 10 ай бұрын
sorry it's MDY
@NightWalker911 10 ай бұрын
@@colourNikaThank you so much!
@luisnavarro4777 10 ай бұрын
great thanks
@abdulraheemsanni9940 7 ай бұрын
Thank you
@MadeForD 7 ай бұрын
tysm!
@alijokhio2995 Жыл бұрын
Note that the IFS function is available in Excel 2019 and later versions. If you're using an older version of Excel, you can achieve a similar result using nested IF functions: =IF(F2:F10 = "Salesman", "Sales", IF(F2:F10 = "HR", "Fire Immediately", ""))
@esieberhemamoke3817 10 ай бұрын
Pls is it same for the Len?
@ayanyemiayannike5063 8 ай бұрын
Thank you so much, this was so useful
@munachiemmanuella3695 4 ай бұрын
I could have been lost without this comment, thank you!
@onitolu9698 4 ай бұрын
Thank you so much
@Super_mic 2 ай бұрын
Thank you for this.
@dhairyasheeljagtap9728 2 жыл бұрын
Your calm composure and humility makes me a compulsive viewer of your content. Please keep up the good work. I am learning a lot from you.
@AlexTheAnalyst 2 жыл бұрын
Thank you so much for the kind words!
@AkporHari Жыл бұрын
I really agree.
@tisanabun823 Жыл бұрын
@@AkporHari honestly sometimes i tell myself that if alex can admit to his mistakes then why can i do the same
@user-wn4vi3mz9w 2 ай бұрын
Sir Can you Leave that courses For Free😢
@AbdulWahid-fi7vs 2 жыл бұрын
Waited almost 5 minutes to comment on your video because i got very slow internet. But i must say you are a genius sir. Plz keep doing your job i am learning from free resources and you are number 1 for me.
@AlexTheAnalyst 2 жыл бұрын
Thank you so much! Much love my friend :)
@user-fp9zq6lw8b 10 ай бұрын
It's amazing how Alex simplifies complex concepts and makes them look super easy compared to some other KZfaq videos on the same. You're doing a great job helping people out there, Alex.
@tanji_shk Жыл бұрын
Hey Alex , Following your bootcamp to be a data analyst . From bcom to aspiring data analyst. Your videos are great source . Thank you so much .😊 Keep up the good work . Many out there who are from non tech and want to switch there career into data analytics. Your hardwork , videos are only source to learn for free for aspirants like me .👍😇
@GM__user Жыл бұрын
Hello! I definitely agree, Alex's videos have been super helpful and make everything seem simple & not as intimidating! Can I ask what the structure you have been following in the bootcamp? I've began with Excel but am not sure what to go with next (Sql vs. R). Any additional tips would be welcomed as well! Thanks.
@prithikabatra5934 Жыл бұрын
@@GM__user Need to learn SQL definitely and you can choose between Python/R .
@molekyuttv5121 Жыл бұрын
Hi Alex, Thanks for creating a boot camp video. I'm a case manager in an insurance company and planning to apply for an analyst role either here in my current company or in a financial/banking institution. You're a great trainer, making the discussion simple, focusing on actual data for visualization. Makes it easier for people like me that are not tech savvy. ❤ God bless you! 🙏
@fpsjohnny3896 2 жыл бұрын
Just graduated with my B.S. in comsci. Got interviews coming up next week for data analyst positions. Thank your for your help Alex, have a good holiday!
@Zain-ng1oq 2 жыл бұрын
did you land the job?
@War-Daddy 2 жыл бұрын
Thanks Alex for the useful content. Also, in a non-weird way, you have a great voice for narrating/hosting/moderating/teaching. You have the kind of voice that companies would pay to use in their advertisements and products!
@AlexTheAnalyst 2 жыл бұрын
Haha thanks! I don't think it's too bad :D
@hammadalamsiddique3730 Жыл бұрын
@@AlexTheAnalyst are you a smoker? Cause your voice is damn good 😃
@UncleSpam1 2 жыл бұрын
YOURE A HIDDEN GEM ALEX THANK YOU FOR THESE ACTUALLY WELL THOUGHT OUT AND PUT TOGETHER VIDEOS
@arunachari2689 Жыл бұрын
You truly make Excel sound so simple and interesting!!
@Dxgreen 2 жыл бұрын
You have been a great mentor Alex. I already know a lot of Excel but I am still going to watch your videos for the support! Keep it up my guy
@ingilisdilidrslrirasefhese7519 2 жыл бұрын
a couple months ago , I began to learn power bi and then my friends recommended me your excel videos as well .I am really happy to meet you here .You are explaining everything in an easy and good way .Thanks a lot, I will continue to watch your videos
@AlexTheAnalyst 2 жыл бұрын
Super glad to hear it! :D
@hendmohamed851 Жыл бұрын
@@AlexTheAnalyst Thank you !Really Appreciate your content! I really want you to Add a Practicing Projects in the description box with every tutorial please i want projects based on every tutorial of you not just any projects
@AkporHari Жыл бұрын
Thank you, Alex, for this really clear explanation. Watching the whole series, and it is really amazing.
@annmariedolan3651 2 жыл бұрын
Thank you for putting out great content every week, I've learned so much from this channel!
@AlexTheAnalyst 2 жыл бұрын
So glad to hear it! Thanks for watching!
@amrkamil85 Жыл бұрын
WOW! Over 30 is old. I'm 38 now. :) Thanks a lot Alex for the great effort. I love your videos.
@valentinrack6028 2 жыл бұрын
Very good video! A compact piece of knowledge about the 80% of functions you will use during analysis in Excel.
@Thuvu5 2 жыл бұрын
Really helpful Excel tips! Love this series you're putting out.
@AlexTheAnalyst 2 жыл бұрын
So glad to hear it! Thanks for watching!
@Mjagcayab 9 ай бұрын
I loved it! I Appreciate you Alex. This is a big help for me starting my career to become a excel expert in future jobs.
@sumedhbarua8363 2 ай бұрын
I am getting loved with data by seeing your tutorial. The way you explain is just amazing!
@MLEfan81 7 ай бұрын
I did this in Finnish Excel and had to figure out the Function names in Finnish and our date format is different so also countin the days did not work right until I corrected them to our format. Still did great and now I know both ways to do them. So Thank you so much from Finland, I really enjoy your way of teaching.
@boky7731 2 жыл бұрын
Can you make a video on the best way to learn SQL and which SQL to learn? I am kinda new to all of this
@AljIsHere128 2 жыл бұрын
So far I am doing a simple basic book called SQL In Easy Steps for overall general SQL understanding. Before tailoring SQL specifically for Data Analysis. I really like the book and it's simplistic manner.
@temidayooluwatunmibi951 Жыл бұрын
I’m happy to start this journey of data analyst, your explanation here is very simple and easy to understand, I hope to learn from you more Alex.
@youkichiba 10 ай бұрын
@16:44 For extracting the year from Date Format Dates, I believe you can just use the =YEAR() function to extract the year. This function only works if the dates are already in the date format though.
@sassy_bitch6142 10 ай бұрын
thanks, its helpful
@ikemsinachidavid7015 6 ай бұрын
Yea cause I tried the way Alex did his and it’s giving me different values. I use Excel 2016, I don’t know if that’s the problem
@OyewoleAdedayo-r1u Ай бұрын
This training really worth paying alot for. Thanks for giving it for free. you are a STAR
@umersohail1951 Жыл бұрын
Thank you, Alex! for providing data and video tutorials. It is making the learning process quick and easy.
@shokoofehheidari5345 Жыл бұрын
How lucky i am that I found your chanel! Thanks a lot
@yagyarajbasnet3720 7 ай бұрын
I was just starting to learn my data analysis and i found your channel. Your channel is a Gold mine for the biggners. Thank you for creating such quality and informative videos.❤
@haerunnissa9978 7 ай бұрын
Same here, Alex help me so much
@ciscokid4266 Жыл бұрын
I learned some of these randomly and need to know basis. But this literally would have saved me so much time if learned in this order and format from the beginning. Thanks so much for your content
@abdullah-khan10 Жыл бұрын
Thank you Alex, you explain all the formula in a precise way which is easy for newbies to learn.
@juanjosegaviriaromero1698 Ай бұрын
I really love all the references to The Office. Thank you very much for the course.
@nathanchamberlain5356 11 ай бұрын
Fantastic and insightful tutorial, perfect pace and I love the way explain each step and relate it to real life situations.
@swathisreekumar2258 2 жыл бұрын
I couldn't help but smile when I noticed all the names of the characters in 'The Office' being used in the worksheet, Toby was fired and Michael was given a bonus. Good one :D
@njabumabu5224 Жыл бұрын
I see someone is a fan of The Office😂😂. Easy to follow tutorial, love it.
@AlexTheAnalyst Жыл бұрын
Just a little :D
@kidus_diaries 5 ай бұрын
Finding implicit the office puns is so entertaining! Good Job!
@ElahehEftekharian Жыл бұрын
Hey, I wanted to give you a shoutout for being a tremendous help! I loved how you added the names of The Office characters - that was really cool of you! :)
@robyn75420 12 күн бұрын
does no ne else find it really cool that he's using 'the office' as reference😂😂
@Oyomikon 7 ай бұрын
Hi Alex, Thanks for this piece. I and my wife enjoy your teaching on this subject. I'd like to add that TRIM removes excess spaces between words. thanks
@toothsieroll6390 Жыл бұрын
Thank you so much for this! Excel has become less intimidating for me thanks to you and your videos! 💛
@shalinivijayravikumar9713 Жыл бұрын
Dear Alex All of your video clips are fantastic. I'm searching for a job as a data analyst and a change in my career. Could you please share a list of steps for creating a portfolio project? As a beginner, if you upload it, it will be really beneficial to me. I appreciate it.
@deimanterepsaite9014 2 ай бұрын
It is so great to watch these vidoes and not fall asleet, it is so refreshing to hear normal human language used.. Thank you a lot for these videos
@nidaqasem4107 2 жыл бұрын
Lots of useful content with great solid examples is especially why I love following this channel. I get to practice this and learn it. Thank you for this and please keep making more excel videos thanks
@pavanreddy9543 Жыл бұрын
Hi Alex, using the function concatenate we created email IDs. But, before that, we may need to make all the letters small as email IDs can not have capital letters. We can also include another function here for the same, ig. Thank You!!
@abdulloshohraufiy7392 3 ай бұрын
WE LOVE YOU ALEX UNCLE FROM UZBEKISTAN
@kelechiemeanuru9994 10 ай бұрын
I always come back to your videos @Alex The Analyst, this is my reference tutorial. very smooth
@violetacorlatan3288 Жыл бұрын
Thank you so much!! I really enjoy it! I recently discovered you and really appreciate what you are doing. You are great!
@vfmosman16 Жыл бұрын
i'm so lucky that i got this videos when i was so down and thinking maybe Excel is not for me ,Really thank you so much
@AlexTheAnalyst Жыл бұрын
You're more than welcome! Glad to hear it helped! :D
@vfmosman16 Жыл бұрын
@@AlexTheAnalyst yes, sure did by the way i'm from Egypt
@1689JeffChavez Жыл бұрын
It's great to be refreshed with excel and realize how important these functions are.
@AlisaHof 2 жыл бұрын
Bok choy and I love you. Have been watching your videos for the past year. So helpful. Thank you!
@AlexTheAnalyst 2 жыл бұрын
I love you more! So glad to hear it :D
@chrisomoha636 7 ай бұрын
Thank you Alex. You have done so well.
@miltonfermine1542 Жыл бұрын
Thanks for this tutorial. I don't know how many times I've tried, but I could never master excel functions. I've been learning since the late 90s. Great video bro.
@sj1795 8 ай бұрын
THANK YOU Alex!! Every video is packed FULL of great info! You are the BEST!
@naivedyaparamartha5710 Жыл бұрын
Thank you Alex for explaning it clearly and simply. Cheers to you and your awesomeness!
@sanandadev4168 6 ай бұрын
I am from Bangladesh. The way you have explained, it is really easy to understand.
@brendapino4238 Жыл бұрын
Alex, Thank you for the explanations and the worksheet! you are making excel entertaining :D
@zouhairaldalil2856 Жыл бұрын
Alex you are the man! Thank you so much for the all the content, learned alot from you and love your videos
@AlexTheAnalyst Жыл бұрын
You're most welcome! Super glad to hear that! :D
@lynnogoi6225 2 ай бұрын
Thank you so much Sir!! I'm really enjoying this bootcamp so far 🌟
@BeingMau Жыл бұрын
Thank you Alex! Very concise and clear as always. I'm learning a lot from your videos. Again, thank you!!!!
@nverma2002 2 жыл бұрын
At 31:06, my days is all different days than yours with same data and formula? For ID - 1001 , Days [ Yours - 5056, Mine - 5231 ] And same for 31:52 (NetworkDays) all different is my office high?
@acrossdeocean Жыл бұрын
Mine is the same
@atem583 Жыл бұрын
It's because of the date, time, and regional formatting in your computer system. You'd have to change date&time setting of your computer then reopen Excel. Works great for me.
@ericfiumano4694 2 жыл бұрын
So much value, Alex. Thanks a lot. I've worked with Excel for some analysis and I confirm that the mentioned functions are pretty well required during the cleaning and analysis processes. Thanks!
@jasxteo 2 ай бұрын
Your videos are very good and easy to follow. Thank you so much as I have a non-science background.
@phaceacid 2 жыл бұрын
Really useful and well explained. Keep the good work Alex!
@glendareeves2317 Жыл бұрын
I have taken other courses in person and online and you are so clear and concise in your explanations!
@1440MAP 8 ай бұрын
Nailed another one! So many uses! Thanks again.
@lorladea Жыл бұрын
Thank you Alex. The formulas were well explained. I enjoy the learning
@jorgdav Жыл бұрын
You're an awesome teacher!!
@XXKASRXX 4 ай бұрын
i dont why IF formula in my version is different , but this is how it worked with me =IF($D$2:$D$10 > 30; "old";"young") by adding $ signs between the selected cells
@yayati2321 2 жыл бұрын
oh my god I just noticed that the names used in the video are all the characters from THE OFFICE lol kudos for it so awesome...
@DustInTheAir 2 жыл бұрын
Hi Alex thanks for your videos! I like when you writing the IFS expression: if they are HRs, fire immediately 🤣🤣🤣🤣
@youvegotmail9385 2 жыл бұрын
The tables have turned!
@Editvila 5 ай бұрын
This awesome, happy to learn this so easily , thank Alex
@fatimakamali8699 2 жыл бұрын
Jalapeño person here 😁 thanks Alex
@farazahmed1668 2 жыл бұрын
Thank you for your precious time.
@philwest2319 2 жыл бұрын
Great video and very helpful. Thank you Alex
@Elthapa 2 жыл бұрын
Thank you Alex. I love your videos and I'm a fan.
@study_bros 25 күн бұрын
SIR, using days and networkdays im not getting some values bcz i think some of those like "8/30/2017" in wrong format!, how to fix this? can someone help?
@polokomonama6526 Жыл бұрын
Thank you so much for this wonderful video, these will certainly come in handy!
@himanshubari8723 6 ай бұрын
Alex woke up and decided to Fire the HR immediately.....🤣, BDW Thankyou so much to make the complex things super simple.
@rivalbagaskara5618 Жыл бұрын
Thanks for great contents, Alex. I have a question. Why is your formula of DAYS format mm/dd/yyyy? while mine is dd/mm/yyyy so the calculation of some rows give #VALUE return. I convert the text date to date format with datevalue and also give the dd/mm/yyyy format so when it is 08/09/2015 for example, give me a return of "08 September 2015" while your case would be "September 09 2015". And I think some of us watchers have some similar problems
@nicholasroberts5003 2 жыл бұрын
Hey Alex I am trying to change career to become a Data Analyst. I must say I am pleased with the effort you placed into creating these videos. Much appreciated bro
@ceciliabarbon2828 Жыл бұрын
super useful, great teacher! Thanks!😄
@davidwakanene4332 Жыл бұрын
Very nice video. Until we got to concatenate I hadn't realised we were using characters in the Office series 😅😅😅
@polly15751 2 жыл бұрын
Thank you! This has been helpful.
@brand1974 2 жыл бұрын
Great video. Thanks! Looking forward to the other ones.
@anupamavaradarajan657 4 ай бұрын
The office fans would be more interested to work and learn from this tutorial :D
@snehadeshpande4155 10 ай бұрын
excellent but the IFS command gives an error in my version of Excel. I just cant even copy that IFS formula to reproduce the results
@msitsolutions8425 Жыл бұрын
🤣 This guy really hates HR . I wonder why
@oluwatobiadeoje1763 Жыл бұрын
Thank you Alex for this great explanation
@IhorSoroka Жыл бұрын
Thank you for this channel!
@rosaliahmuhoho7469 2 ай бұрын
Thank You Alex. Great teacher
@goon5031 2 жыл бұрын
Another great video by Alex.
@shakiruranzel8505 Жыл бұрын
Thanks and great video Alex. I'm still new to the Excel and I tried to follow exactly like in the video but when I type in the formula 'IF', the result only appear on 1 cell and not like how Alex did at 5:44 where they magically appear for the rest of the column. What I did to get the same result was by dragging down the only cell with the formula. Can anyone tell me how he did it ? and thank you so much :)
@kasagavikasagavi3698 Жыл бұрын
Select the cell with formula and then Ctrl+Shift+End (selecting cells up to the right lower intersection of used rows/columns), after that press Ctrl+D (duplicates the cell content, in our case formula, down the column - selected cells)
@Bankfoto Жыл бұрын
exactly my confusion lol. I came into the comment to seek solution.
@addytechie6948 Жыл бұрын
@@kasagavikasagavi3698 Thanks for the tip it really worked
@joshua.akpara87 11 ай бұрын
@@kasagavikasagavi3698 Bless you man
@nauikun Жыл бұрын
Thank you sir , you're a legend.
@marp760 2 жыл бұрын
I really like the way you teach Excel . really fruitful
@DeepakV-jf3nb Жыл бұрын
Why is it we are getting a value error in few cells of days - network days columns? How to solve it?
@abdelrahmansayed4564 10 ай бұрын
This video is incredible!! Thank you so much!
@johnnyscars Жыл бұрын
So clear and concise! Thank you for all this amazing content
@syaoransakura8839 2 жыл бұрын
I am in for this series. Thank you for your hard work, sir
@Zenith73 2 жыл бұрын
Nice tutorial! How do the cells below the formula cell automatically populate? In Excel 2016 when I type the formula and the range, and press 'Enter', it only evaluates for the first cell. I have to drag down to evaluate the rest of the range.
@irinamicov 2 жыл бұрын
You might have figured this out, but I'll just leave this here for anyone else wondering the same. You simply need to convert the data to a table (Insert --> Table) and the functions you apply will automatically run for the whole column.
@joshua.akpara87 11 ай бұрын
@@irinamicov i still don't understand
@86adiel 2 жыл бұрын
12:27 - slicing date with right function (need to be defined as text)
@luxfoodies2701 Жыл бұрын
Hi Alex. This is really helpful to say the least. Thank you.
Alex The Analyst
Рет қаралды 304 М.