Most Common Excel Errors

Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
How do you fix broken Excel formulas? What if the result of the Excel formula is an error? When you use Excel, chances are you'll come across formula errors. That's not a bad thing because they tell you that something is wrong in your formula. Don't just delete them! You don't want broken formulas in your spreadsheet. In this video you'll learn about the 7 most common formula errors in Microsoft Excel and how to fix them with proper error handling.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/excel-error...
🔑 What You'll Learn:
- Error Overview: Grasp the reasons behind common Excel errors like #VALUE!, #SPILL!, #NAME?, #REF!, #DIV/0!, and #N/A.
- Simple Fixes: Discover quick and straightforward solutions to expand columns for large values, correct formula typos, and address data type mismatches.
- Advanced Error Handling: Explore advanced techniques like the IFERROR and IFNA functions for sophisticated error handling in formulas.
- Error Prevention: Learn how to prevent errors before they occur with best practices and efficient formula construction.
🌍 My Online Excel Courses ► www.xelplus.com/courses/
00:00 Formula Errors in Excel And Their Meaning
00:28 #### Error
00:57 #VALUE! Excel Error
01:52 #SPILL! Excel Error
02:29 #Name! Excel Error
02:53 #REF! Excel Error
03:39 #DIV/0! Excel Error
05:32 #N/A Excel Error
06:55 Wrap Up
🎒 Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎬 LINKS to related videos:
- Basics of VLOOKUP and HLOOKUP: • Excel VLOOKUP: Basics ...
- How to Use XLOOKUP: • How to Use the NEW & I...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 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

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/excel-errors-file

[@RoBel-hb5wv]

Grab the file I used in the video from here

[@jonminer9891]

Hello, Leila. Your lessons are very helpful. I almost said, "nice", but read an article today that had the opinion that "nice" means so many things it is almost worthless. Your lessons are far from worthless! I uses to be a paid Excel programmer years ago. Maybe I will get on board again, with your help! Thanks for sharing! Stay Healthy!

[@adriangoodrich4306]

Ok, I knew all of these. But this is the very best video I have EVER seen that explains the causes to those who do not know, in easily-understandable terms. By a country mile. Yours is easily the best Excel channel on here, because you are the best Excel educator I have seen - again, by a country mile. I have learned more from you about Excel than from anyone or anywhere else. Thank you!

[@LeilaGharani]

Wow, thank you!

[@vivekphadke16]

Crisp, Simple explanation with apt examples, thanks Leila

[@markseguin2544]

Thanks! Another great video. I usually use iferror with a value as opposed to the double quotations. This has also given me an idea about adding specific text in the quotations. Thanks!

[@ramruttunaubeeluck9235]

Hi Leila Thks for the useful excel tips.Most of the time I use the IF ERROR function to fix error

[@DamonAustinJ]

Always good short videos with tons of information. Thank you so much

[@pr0vider]

Flawless as always. Great video Leila. Excel at top level error handling. Thanks for sharing the knowledge. Have a good week.

[@LeilaGharani]

Glad you liked it!

[@rossmith9713]

Very useful. Thankyou. I'm self taught in the main and am confident with the main functions but have never explored what excel can actually do in full.

[@nonoobott8602]

Didn't know that about the SUM function. Very explicit video as always. Thanks for sharing

[@j.ballsdeep420]

Like writing SUM(any cell,another cell)?

[@frayue]

Adding this to my Excel 101 class, thanks a bunch, Leila 🤓

[@BenDieselBasicAutomotive]

Amazing topic!... I have benn waiting for these solutions.... Now I fully understand those errors that usually mess my tables... Thank you again Mam, for elaborating every error that has always been distracting our presentations....

[@ferchossports8918]

Thank you, you helped me a lot, I had been trying to solve this problem for two whole nights and your video finally helped me. Congratulations excellent explanation.

[@moneysensemap]

Thank you for this amazing video. You explained so clearly. I understand it better now. Thanks a lot. Sending much love.

[@wayneedmondson1065]

Great info! Thanks for the tips Leila. Thumbs up!!

[@petr6617]

Thanks Leila for reminding us of the important thing, the difference between IFNA and IFERROR.

[@enriquegamiz6607]

Thank you Leila, I used the IF function, but now I will start using the IFERROR, more flexible, thanks.

[@ruben34]

A good tip is to use F9 in the formula bar to see wich portion of the formula is giving a error. It happened to me to sum 2 cells with numbers but one of them was formated as text, and I couldn't figure out why such a simple calculation was giving an error. F9 helped. Also great work, thank you for making me more productive over the years :)

[@LeilaGharani]

Thanks for the tip, Rúben!

[@equiwave80]

Hi Leila, This was a very useful video. I learnt something new today i.e. the subtle difference between IFNA and IFERROR functions. I tend to make use of the multi-purpose IFERROR function to resolve errors instead of the standard IF function. Thanks for all your efforts!!! Regards, Deepak.

[@davidnguyen1485]

Thank you for useful knowledge! I have learned the new functions of IFERROR and IFNA.

[@nipunkavinda8254]

Very useful to avoid different error types. Thanks...

[@christr1ut87]

That was really cool. I’ve been using excel for a pretty long time at this point and never knew what the meaning of the different errors was. I see almost all of those at various times, but I’m not even entirely sure I noticed they were all different until now.

[@LeilaGharani]

Glad it was helpful!

[@roywilson9580]

Another great video, all of the errors you covered were errors that I found frustrating when I first started using Excel. I would love to see Excel expand the available functions to include something like the DAX Divide() function with error checking built in (and add native error checking to other functions) or failing that at least giving us something along the lines of Try catch and deal with errors.

[@JoelShafron]

A simple solution is to create a Lambda function. Divide= LAMBDA(Numerator,Denominator,IFERROR(Numerator/Denominator,0));

[@roywilson9580]

@@JoelShafron A very nice solution. And now that Excel has a function editor for Lambda functions available as an add in even easier to set up than before :)

[@TV-mb5sd]

😊😊

[@ajaysoneji]

Thank you so much for your support, I was using if formulas only

[@IvanCortinas_ES]

Easy peasy! Thank you!

[@mattschoular8844]

Thanks Leila... inserting the Sum function to reference where text may exist is a new idea for me. Thanks for sharing...

[@LeilaGharani]

Glad it was helpful Matt.

[@FaysalEasyExcel]

Your videos will help me to create dashboard in Excel. Thanks a lot Leila.

[@danielboaglio]

¡Impecable!, muchas gracias

[@TheMhujako]

Thank you for sharing these tips. Useful indeed. 😊

[@LeilaGharani]

My pleasure 😊

[@quiqueenrikx3992]

Thanks, always so interesting.

[@vickideluca3009]

Can’t do my job without your videos!!!! So helpful!!!!!!! Thank youuuuu

[@LeilaGharani]

You are so welcome!

[@shoppersdream]

Very Nice, I came across 6 of them today. Thanks

[@kuldeep.3012]

Hello mam thanks for your video again ❤ I Mostly have been using IFERROR function for 8 years because this can handle any type of error. But I got some new ideas from your this video 🙏

[@DrSaadBHF]

Great job Leila 👏

[@volkerysilvia]

To ignore potential text in calculations you really should use the N() function, not SUM(). What makes #REF! errors particularly insidious is that you cannot see the bad reference, hence cannot tell how you should fix it without fully understanding the formula (and sometimes the actions leading up to the error). This makes this error unnecessarily hard to troubleshoot and correct. It would be really nice if Excel still showed you the bad references in the formula (and the same applies in Google Sheets), and would just not treat them as live references anymore.

[@aprendiendoavivir2664]

Loved it. Everything so simple to understand. Thank you!

[@LeilaGharani]

You're very welcome!

[@IdrisAlOmani]

Awesome. Thanks

[@reshmasaldanha5087]

Excellent, thank you.

[@myeshamaloy8385]

This video was was right on time!

[@manjit.sarania]

Madam really very nice explanation for problem solving on excel errors.

[@bernielindsey8387]

Thank you for another good video. I use IFERROR more than any other you discussed

[@sieveman]

For #DIV/0 I usually use if(a30,b3/a3,0), but I now might try the IFERROR formula. Thanks.

[@tubetop123]

You are amazing! Thank you ❤️

[@EK-my9oe]

Thank you Leila.

[@jackofalltrades12]

Excellent! Thank you.

[@WoodenWaresHandwork]

Yours was the only one that worked. Brilliant thank you

[@LeilaGharani]

Good to hear!

[@samuelkodjoe1645]

I normally use IFERROR more often in my templates...great video!!

[@janellegriego6962]

I love your videos. Do you have anything about dates? I'm trying to figure out the best way to set up a spreadsheet for multiple utilities that includes invoice date and service dates. I want to see the pattern of when they come in etc for the future of planning out my schedule.

[@bestlife5298]

Thank u this is very helpful

[@1252PFC]

Appreciate it always

[@ahmadcheragh4769]

Salam, I use iferror and learnt fromyou to use NA in line charts preventing line not to drop down!!

[@andrewcharlesmoss]

You can also use the ERROR.TYPE function to target specific errors.

[@LeilaGharani]

👍

[@bn_ln]

Nice trick with the sum(TEXT)

[@joeismael9359]

Thank you

[@Van_Wilder]

What a simple yet massively informative video - thank you 👍🏻😉

[@LeilaGharani]

Glad it was helpful!

[@rohitgope4800]

Thank you for those knowledge

[@apwnew]

Thanks! You are 🦸‍♀of Excel!

[@MarksExcelIndia]

excellent tutor

[@Gustavozxd13]

that "name?" error got me because the """"help"""" said something like the admin didn't allow me to use that command, so I had no idea it was just that I typed the command wrong lol great video!

[@giorgidvali951159]

thank you for usefull videos

[@annettehoerdeman7210]

I didn't know about IFERROR nor IFNA. My way around those errors was successful, but more cumbersome. I'm all self taught, so appreciate tips on easier fixes.

[@worldtour815]

Very useful

[@usnr1994]

Love the videos!!. Never realized I could use IFNA or IFERROR as i have a habit of coding the not found option in my lookups, or used VBA macros instead of cell formulas. Never noticed the copy down for years, always used Copy/paste. One gotcha I see a lot is folks who use relative references instead of absolute in their lookups.

[@j.ballsdeep420]

It's fill but nice to find it when you do. If your data is in a table in the first place it will autofill

[@j.ballsdeep420]

Also, your comment of absolutes: If you learn to use tables you'll need to learn table referencing rather than cell or columns. Tables are great to keep data dynamic if your references update, but you gotta learn $ = [@[cell]]. Once you get it, visually it's actually easier and writing it out you get shortcuts for the table with all the headers to choose from, same thing if you're referencing another workbook [~.xls]!'table1[...

[@nihayatsabah2166]

Thanks very much very useful 🌹 🌹 🌹

[@erdalorals]

Thanks..

[@tifouraamina1761]

😃 thank you Leila

[@rahulkumaragarwal480]

Hello Leila, which is the most preferred and latest version of excel to use especially for accounting and financials purpose?

[@prakharmehrishi5284]

I like your skills and love your voice as well 👍👍

[@ranaganguli2892]

It's good to know as well need to know..

[@hemvaidya6827]

Excellent

[@samlimam2740]

Thanks Leila for this video !

[@LeilaGharani]

You're so welcome!

[@hansklein3649]

This what amazes me in the iferror function is, that no matter how complex your formula is (like xlookup), you can still put it in the iferror formula to manage the errors.

[@bijaytanty8434]

Thanks

[@jameselsing4017]

Leila --- You are absolutely dynamite. Fabulous and amazing. I wish I had your smarts and energy.

[@johnmyrthong8904]

It's one of my favorite Tut's

[@duncsindevon]

Great tips Leila. The most common error I encounter, especially reusing a spreadsheet is "This worksheet contains lines to one or more external sources that could be unsafe". I am pretty sure I know how to resolve this but your expertise and delivery would be a good topic for your future tips. Cheers Duncan

[@jayo3074]

You need to go to Formulas and then edit links. Then you break any connections your worksheet has to an external source.

[@duncsindevon]

@@jayo3074 I know how to do this Shame but I thought it would be a good topic for Leila to discuss.

[@jayo3074]

@@duncsindevon oh ok my apologies then.

[@Toxic_player23]

well some formulas like the #sum it dosent work idk why but i like cuz i study here for the IT test and this is helping me.Thanks Leila

[@SanthoshKumar-xy3zm]

Thank you very much mam.Well explained.🙏🙏🙏🙏🙏

[@ibrahimshoaib3697]

I use iferror for almost everything. Thanks for the Videos 😊

[@sachinrv1]

Very useful video. Thanks Leila !

[@LeilaGharani]

My pleasure, Sachin!

[@leezhengrong7247]

thanks

[@sikboy4026]

I am proud that you are my compatriot

[@johnphilippatos]

IFERROR for the win! Thanks for the video Leila. This sum capability was new to me 1:41

[@Anikvikram]

I love you Leila you made my work superfast,superb video.One question when there is a large dataset excel hangs a lot showing excel is not responding,sometimes it goes away quickly but sometimes it stays how can we rectify this?

[@kellyross7819]

TY for the video. I do a lot of combining spreadsheets based on matching criteria's from data pulls out of SAP. the most common error is #N/A. I use multiple types of error checking depending on the results i need. If i simply want a result or a value if the result is an error i use IFERROR. If i want a TRUE or FALSE returned on a MATCH function instead of the row # or error i use ISERROR. if i want Specific text returned based on the match being valid or an error i is IF(ISERROR.....)

[@LeilaGharani]

Many thanks for sharing your solution.

[@ThePayanazo]

Such classy lady with classy videos. Thanks for all your job you are amazing. Greetings from Mexico please keep it lime this. Would love to see more videos with PowerShell and SharePoint admin tools like audit logs or APIs

[@KIRAN143K]

So nice

[@Davor_OS]

During last two week I notice two time same error on two different users. Excel wont sum numbers. On first view everthing was good, but error was in circular reference. This is my error example. Greetings from Croatia!

[@kennethenningsson8104]

This is my go-to channel for excel tips and tricks, and now I learned error handling. However, I got a question/proposal. Is there any way to use my shortcuts that are imprinted into muscle memory in the online version regardless of language settings? Frequently I'm using the online versions in shared documents for simple updates but fail when using shortcuts. Either with ctrl or alt combinations. Is there a way to get office 365 to trigger and execute the common shortcuts by any chance... Or simply add a config saying "use desktop shortcuts in eng settings" then my alt+i+m for adding a comment would work and not rewriting cells all the time 🙂

[@pietropalumbo1112]

Can you do a tutorial on giving Letters a numerical value and then a formula to get the average? For example: A=1, B=2, and so on until H, H=0 And then getting the avg? Total attributes is 13, if you need that. Thanks

[@truonghoanghaminh4839]

❤ from Vietnam

[@roeyni]

hi, thanks for your tutorials, those are really helpful. I have one problem I couldn't find answer to in any of the videos I watched or in google: Suppose I have a multi-line text in a cell, and I want to keep the sheet nice and organised, I would like to keep only the first line visible, but when I select this cell, i would like it to automatically expand to show all the text lines it contains, kind of like a tool-tip or a popup. is there a way to do it in Excel without using VB scripts? thanks.

[@mohammedmoslih4016]

Hello Thanks you for great work, your videos very helpful, I have issue when i want to add range of value, lets say: If i have normal range between -2 and 2, if it’s less than -2 it’s abnormal and if its bigger than 2 also it’s abnormal i was able to do for example i used IFS D5>2 it’s abnormal D5

[@quentinbricard]

Since I discovered IFERROR on your channel, it's been my favorite function!

[@Jakeu1701]

I usually use if as I can nest various conditions, but I can see myself using the iferror and ifna as well. They should simplify error handling in dashboards, trackers and reports.

[@j.ballsdeep420]

No need for IFNA _if_ you're using IFFERROR

[@UpForDebate-999]

For the spill problem you can use the @ symbol in front of the range causing the spill

[@francesdobbins2964]

I really enjoyed this video. I'm starting to like Excel.

[@LeilaGharani]

Great to hear!

[@teoxengineer]

Leila, thanks for this tutorial video. What I'm wondering is, why are the error values displayed with the # symbol? The # symbol is also frequently used at the beginning of the syntax in power query DAX formulas. What is the feature of this symbol?

[@LeilaGharani]

Interesting topic. I never thought about that. Maybe it‘s a way of making it stand out so it‘s not confused with normal text.

[@SheikhSalahUddinNoor]

I use IF() to remove #DIV/0! error and I do not use any error remover for #N/A error. Because if there is an #N/A, my report is not completed. I must find the spelling or refence values to fulfill the report. Thank you Dear Leila for making us pro-active!

[@visionarytaufiq8677]

Nice video