Excel for Accounting - 10 Excel Functions You NEED to KNOW!
Рет қаралды 2,061,316
Күн бұрынGet Access to My Course "Fundamentals of Financial Analysis" here 👉 www.xelplus.com/course/learn-...
If you work in accounting or you are a finance professional, make sure you learn these Excel formulas. They'll make your work life a LOT easier and they work in all Excel versions. All are great for accountants, auditors, data analysts and anyone else who'd like to use Excel more efficiently.
Grab the file I used in the video from here 👉 pages.xelplus.com/functions-a...
Suitable for all Excel versions, these tools are vital in your accounting toolkit:
1. AGGREGATE Function: Summarizes values while ignoring errors and hidden cells. Ideal for dealing with large datasets containing errors.
2. ROUND Function: Rounds off numbers to a specified number of digits. Perfect for financial statements requiring specific decimal precision.
3. EOMONTH Function: Dynamically calculates the end-of-the-month date from a specified date, aiding in monthly financial closures.
4. EDATE Function: Moves a date forward or backward by a specified number of months, useful for financial forecasting and planning.
5. WORKDAY Function: Calculates a date after a certain number of business days, excluding weekends and public holidays. Essential for deadline tracking.
6. 3D Formulas: Simplify formula writing across multiple sheets. Particularly useful for consolidating data from various accounts or departments.
7. SUMIFS/AVERAGEIFS/COUNTIFS: Summarize, average, or count data based on multiple criteria. A must-have for detailed financial analysis.
8. IF Function: Checks conditions and returns specific values based on the outcome. Key for creating dynamic financial models and reports.
9. VLOOKUP Function: Retrieves data from a table based on a specified value. A fundamental tool for account reconciliation and data verification.
10. TRIM Function: Removes unwanted spaces from data, ensuring accuracy in functions like VLOOKUP. Critical for data imported from other systems.
If you have Office 365, make sure you check out this Excel tutorial. You'll find a list of easy and useful Excel functions such as FILTER, SORT, UNIQUE and SEQUENCE functions: • 5 Excel Functions EVER...
00:00 Most Important Excel Functions for Accountants
00:15 AGGREGATE Function
03:16 Excel ROUND Function
04:40 EOMONTH Function
05:53 Excel EDATE Function
07:05 Excel WORKDAY Function
08:43 3D Formulas
10:17 SUMIFS / AVERAGEIFS / COUNTIFS
12:51 Excel IF Function
14:41 VLOOKUP Function
16:50 TRIM Function
_____
Detailed article for Excel functions for accounting: www.xelplus.com/excel-functio...
________
LINKS to related videos:
► 3D Formulas: • Excel 3D Formulas Expl...
► SUMIFS / COUNTIFS: • How to Use SUMIFS, COU...
► IF Function: • Excel IF Formula: Simp...
► VLOOKUP: • VLOOKUP EXPLAINED - 2 ...
► XLOOKUP: • How to Use the NEW & I...
► TRIM Function: • Remove ALL Unwanted Sp...
More accounting & Finance videos: • Unleash your inner Fin...
For more Excel functions, check out this advanced Excel tutorials and functions playlist: • Excel Advanced Formula...
➡️ Join this channel to get access to perks: / @leilagharani
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel #accounting
@LeilaGharani 11 ай бұрын
Grab the file I used in the video from here 👉 pages.xelplus.com/functions-accountants-file
@phandongexcel 4 ай бұрын
I like this video and comment, I’m from Vietnam and you ❤
@usmanpeter-kc2ms 13 күн бұрын
good evening ma,I really love this please how can I get the material?
@essential_luxury3356 2 жыл бұрын
Watching your Excel tutorial has became an essential , forsure you are helping millions of people out there not only polishing their skill but also helping them in landing on opportunities, appreciate mate for your support
@TheWeteyezz Жыл бұрын
I have a huge documentation project, designing and documenting the entire accounting system, and was browsing your videos for inspiration. The aggregate function is going to be so much faster than combining subtotal & iferror! And i was trying to figure an efficient way to calculate due dates on month close components but couldnt figure how to account for holidays and weekends without some elaborate references to some hidden tab! haha you rock
@mhknowledgeacademy6420 Жыл бұрын
I would simply say that you are amazing. The way you explain is very easy to understand. I have been watching your tutorials and it's really good to advance in excel.
@Codger2015 3 жыл бұрын
Not only are you good at Excel you also are good at explaining and making your material easily digested for putting into practice. I have been an Excel user for about 30 years but do not have near the knowledge of use that you do. I use Excel mainly during the last 5 1/2 years only for finances and accounting for home use. Prior to July 2015, I used it during my 21-year employment while working in an Architect firm mainly keeping track of capital improvements, costs of hardware/software, budget planning for capital improvements, and a slew of other uses I encountered. This helped considerably for that period of employment but now makes life so much easier for simple home accounting purposes, not to mention using it for a slew of other reasons besides accounting. Thank you for your awesome videos, they are so helpful and keeps me sharp at 71 years old. And on top of that, you are so pleasant to watch and listen to with a voice that keeps it interesting.
@LeilaGharani 3 жыл бұрын
Thank you for the kind feedback!
@Thukai-Operater Жыл бұрын
Sir what type of work you do in Excel please guide me or share your story.
@chinyereezeigbo5837 2 жыл бұрын
Wow, the trim function and the 3D formula was a WINNER for me! Thanks a million
@elsym613 2 жыл бұрын
Great video. I recently learned how to use the timeline function with pivot tables and I found it very useful when analyzing data for certain periods.
@10x29 2 жыл бұрын
this is amazing. i'm prepping for my new job and am feeling anxious about it. all of your content has not only helped me brush up on the basics, but has also taught me some excellent secret tricks that will surely impress my new employer. ♥ many many many thanks for putting the effort in producing the content for this channel! ♥
@LeilaGharani 2 жыл бұрын
That's great to hear. Good luck with the new job!
@MsRainbow1983 3 жыл бұрын
Amazing video - found my new favourite formula - aggregate!! Thanks Leila for a great and thorough video as always.
@tahoejohnny3991 2 жыл бұрын
Without a doubt one of the best accounting videos I have seen. You get to the point, but doesn't move too quickly. Awesome tutorial. Thank you!
@LeilaGharani 2 жыл бұрын
Glad you enjoyed it!
@MrOPD 3 жыл бұрын
I've been teaching Excel for quite some time now and nested IF functions have always been the biggest brain twister for basically every pupil. Never heard of the IFS function before but holy molly does that take all the complexity out of multiple nested IF functions, it's insane - I'm smiling like a little kid on Christmas eve because this is gonna make understanding Excel so much easier for so many people.
@LeilaGharani 3 жыл бұрын
Great to hear, Peter! Hope it will be helpful for your students.
@nokpiadariusconstantino1333 2 жыл бұрын
@@LeilaGharani улиоыбьяд ч ч в твэцжхцхы🤵👰🌷❤️
@lumai2709 Жыл бұрын
Thank you so much for your kind sharing. This has upgraded my knowledge regarding with excel usage for accounting. May you have a smart brain in every lives, at every age.
@karhukivi 3 жыл бұрын
Leila you're a star! You are keeping us busy with a steady stream of new discoveries. I passed this one onto my brother-in-law who is an accountant and he was astonished - didn't really know what Excel could do,, so many thanks from him too and greetings from Ireland! Stay safe.
@LeilaGharani 3 жыл бұрын
Awesome, George! Hope the tips will be useful for him.
@AndreaUK1973 3 жыл бұрын
Yeah! She is a gem!!
@smitajaiswal5857 2 жыл бұрын
I am looking to start my career in accounts and ur videos r really helpful .I am a nearly 40 yr old lady and really like ur personality n presentation too .Hope I can adapt someful qualities from u .Thnx Leila gharani
@computernerd3922 Жыл бұрын
I am a math major and I do a data analyst job, recently my boss asked me to do accounting for his company I guess he assumes I am a nerd and knows everything 😂 here I am learning accounting
@WISEMARTIAL Жыл бұрын
How's it going for you? Have to used any of these formulaes ?
@Thukai-Operater Жыл бұрын
U got any job in this field ?
@Akpihs23 Жыл бұрын
@@computernerd3922 Your name itself has a nerd in it though 😂
@FriendTrue2008 10 ай бұрын
Interestingly, I am 33 and changed my career too
@abdulhafeezbhatti1787 3 жыл бұрын
A new thing I learn from this video is the AGGREGATE function. Thanks, Leila Excellent work.
@darlowmaxwell7346 Ай бұрын
I have been using excel and all it’s predecessors DOR over 40 years and you taught me many new commands with this video. Amazing! Thanks
@zarinabqureshi4028 2 жыл бұрын
Hey there Leila It was of great help indeed. It made my concept and logic very clear. Moreover the best part is, thet it halped me experiment various subfunctions with confidence and it worked as i want it to work. Bundle of thanks. Keep sharing more....
@bobwhelan5644 3 жыл бұрын
I'm not an accountant, but need to measure P&L for the services we deliver and charge for. I have a task to create a spreadsheet to manage this. The content (and timing) in this video could not be better (or have come at a better time😁 ). So many great tips in once place, thank you Leila.
@LeilaGharani 3 жыл бұрын
I'm happy it's helpful for you, Bob!
@jjey24 3 жыл бұрын
Aggregate & EDATE function is a game changer for me!!
@LeilaGharani 3 жыл бұрын
Glad to hear it!
@tapiwanalwimba6065 6 ай бұрын
So easy to follow and direct to the point, so grateful😍. The comes a time when auditing a client with a lot of data becomes tedious. Especially when it involves data that needs a lot of attention to the date of transactions resulting into data sampling to a human actionable pace but now I am confident with this insight i will be 100 times more efficient and provide quality audit reports with ease. posted 2 years ago but still relevant/ useful. Thank you so much
@konsamprincesingh7763 2 жыл бұрын
You are a saviour. Thank you so much. I learn so many new things about excel from your videos. I have not had any formal training on excel but i am using it and even preparing complicated excel calculation sheets for my office use. It makes life much easier and all thanks to you. Thanks once again.
@teoxengineer 3 жыл бұрын
Thank you, Leila, Also, Sumproduct is so interesting and useful function and it consists of filtering, sumif, and/or/True/False statement in one function. I learned all those very effective functions thanks to you. 👍 I want you kindly to publish a tutorial about Date/Time calculation methods in excel as well. Thank you again
@curiousmind4381 3 жыл бұрын
Hi Leila, this is my first comment on KZfaq channels and believe me I don't have words to appreciate the way you explain!!! Loads and loads of respect from India and thanks again for spreading the knowledge 🙏🙏
@FM-ne5pz 2 жыл бұрын
Your tutorials are effective and efficient. Thank you.
@gholamhosseingorgin4799 2 жыл бұрын
Brilliant Leila, thank you so much for all your explanations! 💫
@seizuresaladventures 3 жыл бұрын
Thanks for the refresher, Leila. Would like to add that adding Tables (ctrl+t) to flat files are pretty useful too 👍
@LeilaGharani 3 жыл бұрын
👍
@maryamerfani5249 2 жыл бұрын
Thank you so much dear Leila. You explain beautifully and your voice has a soft and melodic tone. It is such a delight listening to you.
@LeilaGharani 2 жыл бұрын
Thank you so much!
@mnsoyan 2 жыл бұрын
Very useful tricks! Was super excited about the AGGREGATE function until I realized it ignores only SUBTOTAL but not SUM. Is there a way to make it ignore also the SUM function?
@assemlh 2 жыл бұрын
Leila, thanks for your tutorials, because of your videos Excel looks so much easier :), please don't stop, keep informing us with great and unfamiliar functions (for beginners like me at least)
@raminaghajanzadeh8998 2 жыл бұрын
Thank you so much, please keep going, fast, precise and easy to understand. Awesome.
@avr9747 3 жыл бұрын
Monumental help.so lucid&simplified to the core. best wishes&many thanks
@dipakchoubey 3 жыл бұрын
For the last 5 years I've been struggling to do v lookup as the data was not trimmed. Now with the "trim" I can solve almost all the issues. Aggregate is another best thing. And the 3D was amazing. Most importantly leila tells us the dynamic use techniques of the functions or formulas. Thanks so much leila
@nokpiadariusconstantino1333 2 жыл бұрын
Оушщоущлущбуьюююьвщьуз бэ. Лутщут лут у у ивьвьдтужьщцбджьутщьущужжцэцэцж уул у лллуж. Огузту от. Иоыдтцдьвзудзуххузуз и ин от ви в ин оутзулузхухузул зы тузцз
@eriismaili756 3 жыл бұрын
This was great and not only for accountants 😃
@ameture-fotografer 2 жыл бұрын
Thank you so much for such a great video. I've been a CPA for over 11 years and some of these functions I never knew existed. Keep up the good work.
@LeilaGharani 2 жыл бұрын
Glad you enjoyed the video!
@welahjazbati Ай бұрын
Great tutorial as always...Have been using excel for years and the 3D formula was new for me. Thanks for the great tutorial.
@TheTraffic247 3 жыл бұрын
i remember requesting you to make this video, so thank you. 👍 i would like to see a video on financial modeling functions for financial analysts for projections and asset valuation. that would be great! thanks again for making this video 😊
@rjchua2008 3 жыл бұрын
im still here for u. continue your journey . thanks a lot . go for it.
@ilukenalubasi2586 Жыл бұрын
Your manner of teaching is just amazing and out of this world
@kaushalthakkar3768 2 жыл бұрын
The way you explain is amazing Leila.
@snicho 3 жыл бұрын
Wow! How did I miss that Aggregate replaced Subtotal?! 🤔 And it is more powerful, particularly given that it can ignore errors, etc. Good examples for the three date functions too. Your 'Excel Functions 2021' video referenced at the end also has some good gems! That's what I love about your videos Leila, you ensure that any knowledge gaps are plugged, and you do so in a very approachable and effective manner. 👍 Thank you!
@LeilaGharani 3 жыл бұрын
Thank you very much!
@Nicole-fp6gh 2 жыл бұрын
I missed that one too. I've used Excel so long that I have ways to get to the needed end result, but I've missed Excel updates that make these tasks much easier.
@rameezmuhammed9356 Жыл бұрын
How to get the working tables or the data
@seanbhaney Жыл бұрын
Ditto! I've been using Excel since the cows came home, and I've NEVER used the aggregate function! Today will be the first day!
@mostafa4321 3 жыл бұрын
Leila The day I think I knew everything about Excell, I watch one of your Videos and finds that there is one more thing I do not Know. You are awesome Thank you for all the great content you provide us every day.
@LeilaGharani 3 жыл бұрын
Thank you! It's my pleasure.
@katestan7002 3 жыл бұрын
Same :))))
@nashidmadathilkandypara9160 3 жыл бұрын
Thank you Leila Aggregate Function & using Trim function in inside of vlookup is new knowledge to me
@remediationdepartment4332 Жыл бұрын
very meaningful, useful and practical features, thank you so much for your always helpful videos
@adamdare87 3 жыл бұрын
3D Formulas... 🤯 I have needed this for so long!
@leoleon38 3 жыл бұрын
Exactly!!! I feel i wasted so much of my time before this
@majd8138 3 жыл бұрын
I've used it only once before. But does save time most people don't know about it.
@judyheddamorales7912 2 жыл бұрын
just saw and played this video, It was amazing !!! excited to think how I can apply all of these at work. Thank you so much!
@LeilaGharani 2 жыл бұрын
You're so welcome!
@mubashir38 3 жыл бұрын
OMG. Aggregate is so best. Today I really learnt something from this. You are best Mam.
@amjan 3 жыл бұрын
The Excel goddess delivering the goods as always!!
@danielpeterson4149 3 жыл бұрын
Good stuff. Thanks for putting this out there. Maybe you have covered this in another video, but we also use the nested index/match formula frequently.
@LeilaGharani 3 жыл бұрын
Thanks for the tip, Daniel!
@user-ov5xt1gb6x 11 ай бұрын
Thank you very much It is good that you provide us with the Excel file that you are explaining to allow us to practice on it
@abdulbasith6503 3 жыл бұрын
Good Evening Ms. Leila Gharani today I learning some one formula your teaching normal step by step speech I'm very slow listening power but I did understand. Thanks
@stephenjohnson9632 3 жыл бұрын
iserror function is very helpful when nested in an if function using a vlookup function.
@auricia201 3 жыл бұрын
11:57 thank you! I was trying to do this the other day, with the "month" function to get the value, but it wasn't working 😅 I think I was missing the " " & 🤦🏻♀️ But this way is much better! I'm gonna use it 😁
@LeilaGharani 3 жыл бұрын
Hope it will work out for you.
@charleyjr.iriarte7428 2 жыл бұрын
Thank you, Leila! I will be using it as my ´tutoring´ lesson.
@skd519 27 күн бұрын
You are a good Teacher know how to explain each number and text in understandable way
@awaiskhokhar488 3 жыл бұрын
I was clicked clicked on random website I saw Leila Gharani excel course I was surprised that my favorite mentor has made great course. Udemy Course
@DreamFirms 3 жыл бұрын
Hey Leila, this is a very helpful content!
@pawanraajbahak4272 2 жыл бұрын
my fav learning channel for excel.. thank you so much ..
@oceantsai7455 3 жыл бұрын
Thank you. Please keep making excel tutorial videos for accountants. Thank you
@katherinebernadas3862 3 жыл бұрын
This is very helpful! I learned new excel functions! Thank you very much!!
@LeilaGharani 3 жыл бұрын
You're very welcome, Katherine!
@josemanuelruiz4963 3 жыл бұрын
love this... just minor suggestion, please use accounting format when using amounts values.
@jackbench5427 3 жыл бұрын
Leila, you have to best Tutorials on Excel and so nicely in order so you can pick whatever subject seems the most suitable for the task ahead. Love IT!
@LeilaGharani 3 жыл бұрын
Glad you like them, Jack!
@andreurussanga 2 жыл бұрын
Tip number 9 saved my life! Thank you very much!!!!
@robertgilmour494 3 жыл бұрын
You are the queen of excel! 😍
@blessingchinzambwa7685 3 жыл бұрын
This lady is an excel guru. And the way she knows the new excel versions shows she invests time in it. Amazing, I just love it. 👏😊
@nokpiadariusconstantino1333 2 жыл бұрын
Ултпьт
@mimicatibayan3883 3 жыл бұрын
Ms. Leila, thank you so much.. simple and easy to understand and very useful.. big big help..
@LeilaGharani 3 жыл бұрын
Happy to hear that!
@cavipulvakkani3576 2 жыл бұрын
Very superb way of explanation and very useful and very minute observation for users problem 👏👍
@davidbenedetto3466 3 жыл бұрын
Great stuff as always. One of the powerful features of the EDATE function is that if the date you are seeking does not exist, it will give you the last day of the month. For instance, a U.S. estate tax return is due 9 months after death. If death occurs on 5/31/X1, the 9 month date can’t be 2/31/X2 so it becomes the last day of February, which is the value EDATE returns. If you try to do something equivalent with just the DATE function it would push you into March, which in this case would be wrong. Also, the WORKDAY function looks at how many workdays from the starting point you want to go. U.S. tax returns are only due on workdays. So, if you want to test whether that 9 month date is a workday you need to subtract 1 from the start date when you use the WORKDAY function. Assume the 9 month date is 2/28/X2, you subtract 1 day and then use the WORKDAY function to find the first workday from that date which could very well be 2/28/X2. If it isn’t, the WORKDAY will find the first workday after that date and that would be the due date of the return.
@AnthonyEnglish Жыл бұрын
I used to hate spreadsheets, and how much copying and pasting I was doing to manipulate even fairly small worksheets. Now, thanks largely to your videos and courses, I’m enjoying Excel and showing some tips to colleagues, who are really grateful.
@anv.4614 2 жыл бұрын
Thank you. Well summarised and well explained.
@anicar26 2 жыл бұрын
I’m glad I found your channel. It’s very helpful.
@mtroanoke 3 жыл бұрын
I like to use the clean function in conjunction with the trim function. The clean function gets rid of non-printable characters that appear to be spaces to the naked eye, but aren't really spaces.
@LeilaGharani 3 жыл бұрын
👍
@XMegaJuni 3 жыл бұрын
How did you know i needed this for my exam haha, Thank you!! Love this series
@LeilaGharani 3 жыл бұрын
😁 Good luck with the exam!
@shamshadqazi3520 Жыл бұрын
I learned 3 functions today, enjoyed them. Great of U
@susanwong6471 Жыл бұрын
great tutorial, clearly presented ! thank you so much !
@dlsgl407 3 жыл бұрын
I learned, again, new stuff from Leila - that I can use tomorrow! Thanks so much!
@LeilaGharani 3 жыл бұрын
Wonderful!
@zulhairibaba 3 жыл бұрын
#3 EOMONTH, #4 EDATE. put double negative -- before start date, if you need to utilise dynamic arrays functionalities for "dates array". eg. =EOMONTH(--A1:A20,0), =EDATE(--A1:A20,14),
@manjit.sarania 11 ай бұрын
Not only important but also amazing formula..🙏
@yohannaagbanaji8012 10 ай бұрын
Great video, I wish you include the data set for practice. Great one I must say
@R.0ai 8 ай бұрын
0.15 I was bit shocked. Elon Musk has stolen her logo 😅
@Merrymaking-xl3mo Ай бұрын
Power of Money
@saleemsaeed9550 3 жыл бұрын
Great job.... very easy to understand...u made my life easy 😃
@prabhakarrao4922 2 жыл бұрын
Super big thanks! Have a wonderful day
@manfororkut 2 жыл бұрын
Your voice and language engaged me throughout the video.
@RayaFlora Жыл бұрын
Excellent video! Thank you very much for your hard work.
@jimbob2810 3 жыл бұрын
These videos are excellent demonstrations of new Excel functions and features (at least some are new to this user of Excel for the past three decades.)
@jayxpea 3 жыл бұрын
That was so great and useful. Would appreciate a part two of this!! Thanks!
@dawnwang5901 2 жыл бұрын
Just found this video, it helps. Happy New year!
@Travelwithme0504 2 жыл бұрын
I am useto sum function earlier but now i totally agree to use aggregate function is very much useful. Mam very thankful to make such video.
@rhaps2008 3 жыл бұрын
I am always learning from you thank you so much
@harishagicha2915 Жыл бұрын
Great Mam, thanks a lot very helpful some i already knew but some were new to me
@anjalishailesh3452 2 жыл бұрын
Very informative.. keep uploading more financial and accounting functions.. will be looking forward to it
@LeilaGharani 2 жыл бұрын
Thank you, I will.
@reformerx667 3 жыл бұрын
So helpful. Thank you for sharing.
@saidechchababe9938 3 жыл бұрын
Many many thanks Leila for all what you share 👍👍👍👍
@LeilaGharani 3 жыл бұрын
My pleasure 😊
@doraramos4735 3 жыл бұрын
Great advice Leila it had help me a lot with my accounting SS
@Perfectsound88 3 жыл бұрын
OMG I am in love with ExceLeila.God Bless.😊😊😊
@julionunez7370 3 жыл бұрын
Años usando excel y siempre se aprende algo. Aggregate function is so powerfull to use in the accountant statments. Thanks Leila!
@FerrelFrequency 2 жыл бұрын
Thank you for posting EXTREMELY well made videos that are thorough and very relevant for probably any person in any field. New Subscriber.
@yogatanaka5544 3 жыл бұрын
You are Great @Leila, help many people how to use exact formula :)
@lacylace3175 2 жыл бұрын
Excellent video!! Thank you🙏🏽❤
@wadebrewer7212 2 жыл бұрын
Building a mini MES system in Excel to run a manufacturing site. These are amazing!
@venkat9678 2 ай бұрын
Madam Leila you are excel queen, symbol of extensive knowledge and great Professor who knows how to teach and impart knowledge transfer in simple and clever way to the mind of Learners like me
@LeilaGharani 2 ай бұрын
Thank you for the kind feedback!
Leila Gharani
Рет қаралды 539 М.
💀Skeleton Ninja🥷【にんげんっていいなチャンネル】
Рет қаралды 8 МЛН
Online Training for Everyone
Рет қаралды 3 МЛН
How to automate Accounting Ledger, Trial Balance, Income Statement, Balance Sheet in Excel | English
EXCEL DOERS
Рет қаралды 1,2 МЛН