Excel for Accounting - 10 Excel Functions You NEED to KNOW!
ฝัง
- เผยแพร่เมื่อ 6 มิ.ย. 2024
- 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
Grab the file I used in the video from here 👉 pages.xelplus.com/functions-accountants-file
I like this video and comment, I’m from Vietnam and you ❤
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
Wow, the trim function and the 3D formula was a WINNER for me! Thanks a million
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.
Thank you for the kind feedback!
Sir what type of work you do in Excel please guide me or share your story.
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.
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
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.
Your tutorials are effective and efficient. Thank you.
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!
Glad you enjoyed it!
Amazing video - found my new favourite formula - aggregate!! Thanks Leila for a great and thorough video as always.
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.
Awesome, George! Hope the tips will be useful for him.
Yeah! She is a gem!!
Monumental help.so lucid&simplified to the core.
best wishes&many thanks
Thank you so much, please keep going, fast, precise and easy to understand. Awesome.
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! ♥
That's great to hear. Good luck with the new job!
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.
Great to hear, Peter! Hope it will be helpful for your students.
@@LeilaGharani улиоыбьяд ч ч в твэцжхцхы🤵👰🌷❤️
Brilliant Leila, thank you so much for all your explanations! 💫
I’m glad I found your channel. It’s very helpful.
A new thing I learn from this video is the AGGREGATE function. Thanks, Leila Excellent work.
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
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
How's it going for you? Have to used any of these formulaes ?
U got any job in this field ?
@@computernerd3922
Your name itself has a nerd in it though 😂
Interestingly, I am 33 and changed my career too
Super big thanks! Have a wonderful day
The way you explain is amazing Leila.
Aggregate & EDATE function is a game changer for me!!
Glad to hear it!
Thank you. Well summarised and well explained.
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.
Hi Leila, this is my first comment on TH-cam 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 🙏🙏
This was great and not only for accountants 😃
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
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....
The Excel goddess delivering the goods as always!!
Hey Leila, this is a very helpful content!
great tutorial, clearly presented ! thank you so much !
Tip number 9 saved my life! Thank you very much!!!!
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.
I'm happy it's helpful for you, Bob!
So helpful. Thank you for sharing.
Your manner of teaching is just amazing and out of this world
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?
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
Always great information! Thank you!
Excellent video!! Thank you🙏🏽❤
im still here for u. continue your journey . thanks a lot . go for it.
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)
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
very meaningful, useful and practical features, thank you so much for your always helpful videos
You are the queen of excel! 😍
This is very helpful! I learned new excel functions! Thank you very much!!
You're very welcome, Katherine!
Excellent tips. Thanks!
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.
Thank you so much!
Thanks for the refresher, Leila. Would like to add that adding Tables (ctrl+t) to flat files are pretty useful too 👍
👍
3D Formulas... 🤯 I have needed this for so long!
Exactly!!! I feel i wasted so much of my time before this
I've used it only once before. But does save time most people don't know about it.
Excellent insight! Thanks!
That's cool stuff,liked it so much
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 😊
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.
Thank you! It's my pleasure.
Same :))))
Great video!! Thank you!
I am always learning from you thank you so much
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
Оушщоущлущбуьюююьвщьуз бэ. Лутщут лут у у ивьвьдтужьщцбджьутщьущужжцэцэцж уул у лллуж. Огузту от. Иоыдтцдьвзудзуххузуз и ин от ви в ин оутзулузхухузул зы тузцз
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
my fav learning channel for excel.. thank you so much ..
Excellent video!
Thank you very much for your hard work.
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.
Thanks for the tip, Daniel!
love this... just minor suggestion, please use accounting format when using amounts values.
Great tutorial as always...Have been using excel for years and the 3D formula was new for me. Thanks for the great tutorial.
OMG I am in love with ExceLeila.God Bless.😊😊😊
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!
Thank you very much!
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.
How to get the working tables or the data
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!
iserror function is very helpful when nested in an if function using a vlookup function.
I learned so much! Thanks!
Just found this video, it helps. Happy New year!
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 😁
Hope it will work out for you.
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. 👏😊
Ултпьт
Very informative!!thanks so much..
Very informative. Thank you so much
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.
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.
Great job.... very easy to understand...u made my life easy 😃
Thank you, Leila! I will be using it as my ´tutoring´ lesson.
I learned, again, new stuff from Leila - that I can use tomorrow! Thanks so much!
Wonderful!
How did you know i needed this for my exam haha, Thank you!! Love this series
😁 Good luck with the exam!
Thank you. Please keep making excel tutorial videos for accountants. Thank you
Thank you Leila, you are just wonderful
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.
👍
0.15 I was bit shocked. Elon Musk has stolen her logo 😅
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.
Many thanks, very useful video!
#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),
Thank you, awesome tips
Thanks so much! Save tons of time!!
Not only important but also amazing formula..🙏
YOUR videos helps a lot "love you 3000"
y're such a GENIUS.....thanks for the insight
Fantastic lessons,, thank you. Completed.
You are welcome, Anthony!
Many many thanks Leila for all what you share 👍👍👍👍
My pleasure 😊
Very Informative...
That was so great and useful. Would appreciate a part two of this!! Thanks!
Awesome video thanks for sharing
amazing!!! thank you for sharing!
Really brilliant enough!!
I learned 3 functions today, enjoyed them. Great of U
Excellent, thanks for sharing the video..
Very use full thanks for the video
great video. keep up the good work.