Most Common Excel Errors

[@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

[@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!

[@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!

[@ramruttunaubeeluck9235]

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

[@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!

[@vivekphadke16]

Crisp, Simple explanation with apt examples, thanks Leila

[@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.

[@enriquegamiz6607]

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

[@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)?

[@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.

[@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.

[@DamonAustinJ]

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

[@vickideluca3009]

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

[@LeilaGharani]

You are so welcome!

[@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.

[@frayue]

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

[@petr6617]

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

[@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!

[@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.

[@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.

[@ahmadcheragh4769]

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

[@sachinrv1]

Very useful video. Thanks Leila !

[@LeilaGharani]

My pleasure, Sachin!

[@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.

[@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!

[@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.

[@sieveman]

For #DIV/0 I usually use if(a30,b3/a3,0), but I now might try the IFERROR formula. 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 🙏

[@RA-rh5lb]

Thanks Leila. Wow, i didnt know the difference between IFNA and IFERRor ..

[@bn_ln]

Nice trick with the sum(TEXT)

[@wayneedmondson1065]

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

[@moneysensemap]

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

[@FaysalEasyExcel]

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

[@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!

[@andrewcharlesmoss]

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

[@LeilaGharani]

👍

[@shoppersdream]

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

[@ajaysoneji]

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

[@davidnguyen1485]

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

[@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]

😊😊

[@TheMhujako]

Thank you for sharing these tips. Useful indeed. 😊

[@LeilaGharani]

My pleasure 😊

[@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....

[@aprendiendoavivir2664]

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

[@LeilaGharani]

You're very welcome!

[@Van_Wilder]

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

[@LeilaGharani]

Glad it was helpful!

[@nipunkavinda8254]

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

[@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.

[@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

[@samuelkodjoe1645]

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

[@chh8860]

"Which error do you come across more often?" I do not favor one error message more than another ... I get them All! Any of them can appear ... at any time! I like the brief synopsis of each error and proposed solution. You've given me the idea to post a cheat sheet on my monitor to quickly remind me of the solution for each error. A Request Please: I maintain a very large spreadsheet ... lots of data, formulas, and range names etc. Recently I received "Excel found a problem with one or more formula references in this worksheet. Check that the cell references, range names, defined names, and links ... are correct". Can you please post a video to address that message ... or ... a suggestion how to easily locate the offending reference. Love the effortless presentation ... and useful content ... thank you ... thank you ... thank you.

[@LeilaGharani]

Thank you. A problem could be external links. Check if you have any of those. Then check the names in name manager. If nothing obvious shows up, I would take a copy of the workbook and delete one tab at a time. Save, then reopen and see which tab was the problem. Once you‘ve identified the tab, then delete columns to narrow down the problem. If you find the source let me know what it was. I‘m curious….

[@chh8860]

@@LeilaGharani Thank you ... so much ... standby

[@IvanCortinas_ES]

Easy peasy! Thank you!

[@manjit.sarania]

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

[@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!

[@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!

[@bernielindsey8387]

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

[@prakharmehrishi5284]

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

[@johnmyrthong8904]

It's one of my favorite Tut's

[@ibrahimshoaib3697]

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

[@huhtis6087]

I basically use =IFERROR to "remove" error. Sometimes when I know there are different kind of values in arguments, I add IF/OR function.

[@sikboy4026]

I am proud that you are my compatriot

[@RA-rh5lb]

I come across all the errors you mentioned in your video and I always use the "iferror " function. I never used " IFNA", this is something new for me. Thank you.

[@LeilaGharani]

👍

[@MrPir84free]

Get more N/A than anything; usually cross referencing multiple inputs from various software tools; look specifically for the N/A to identify systems missing some key component.

[@apwnew]

Thanks! You are 🦸‍♀of Excel!

[@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?

[@Ticky66MN]

I'll use iferror most often but only after I'm sure of the errors being generated. Thank you.

[@LeilaGharani]

👍

[@myeshamaloy8385]

This video was was right on time!

[@francesdobbins2964]

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

[@LeilaGharani]

Great to hear!

[@MrAitch_]

Over the years adding IFERROR to any division formula is now automatic to me.

[@markk3701]

That and if a call has no value show no value. Example: =if(a3="","",sum(a3,c3))

[@j.ballsdeep420]

To expand on ☝this brother, you can use your entire IFERROR statement, reused in the beginning of an IF statement where your reference is (or isn't blank), either the true or false value returns no blanks or errors, so: IF(yourformula"",IFERROR(yourformula,return,""),"") Love this one and make sure to setup proper on a table and to use [@[referencecell]] it stays dynamic without dropping your formulas AND limits to table size rather than an entire column if you wanted to drop down and create a stupid large file when you could create 1/100 file size when you're only dealing with

[@j.ballsdeep420]

"Why you'd use tables rather than whole worksheet is if you have tools that refresh upon opening?" If I can create 100x less time for queries referencing anywhere including SQL db, SharePoint, etc, and formulas referencing to other files local or network? Pfffff, bruh. I get that people argue it'll only save me 3min here like 4 or 5 times a day, but let's do this: For one save the time opening each of your references to access at the same time as your queries, scripts, or formulas run, but aside from opening those then it's the time it takes to run them... If I can make 180sec run 100 times faster, that's 1.2sec versus 2min. Extrapolate that out only 4 times a day over 52 weeks when you're building shit... you just saved yourself an hour a week. Take off early on Friday --You're welcome. Continue keeping that mindset and find an hour here, hour there, over some stupid shit. Takes me about 3min to load SSMS, every day, and just having that build into whatever I regular use as a refresh on open to generate my query, alt+f8 to run a macro, poof, generated all my data that have many codependent functions after as a result. Just saved myself like 10min now. Laugh. There's another 50min each week

[@markk3701]

@@j.ballsdeep420 I use the formula I posted for my data dumps mainly. When I pull data out of our data base it doesn't give me everything I need so I use that as part of my algorithm. IFERROR does not always do what I need. I am still interested in the other part of this paragraph your speaking on. Maybe you can do a video.

[@ruan704]

#######

[@WoodenWaresHandwork]

Yours was the only one that worked. Brilliant thank you

[@LeilaGharani]

Good to hear!

[@jameselsing4017]

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

[@reshmasaldanha5087]

Excellent, thank you.

[@DrSaadBHF]

Great job Leila 👏

[@markpodesta4605]

Thank you Leila.

[@IdrisAlOmani]

Awesome. Thanks

[@quentinbricard]

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

[@johnphilippatos]

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

[@MarksExcelIndia]

excellent tutor

[@danielboaglio]

¡Impecable!, muchas gracias

[@1252PFC]

Appreciate it always

[@tifouraamina1761]

😃 thank you 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!

[@quiqueenrikx3992]

Thanks, always so interesting.

[@nihayatsabah2166]

Thanks very much very useful 🌹 🌹 🌹

[@mirsadsid]

Sometimes having an error is a good thing. It helps you see if you have done something wrong and written date incorrectly, this is specially useful when you do look up formula.

[@LeilaGharani]

👍

[@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[...

[@tubetop123]

You are amazing! Thank you ❤️

[@rohitgope4800]

Thank you for those knowledge

[@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.

[@SanthoshKumar-xy3zm]

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

[@giorgidvali951159]

thank you for usefull videos

[@worldtour815]

Very useful

[@samlimam2740]

Thanks Leila for this video !

[@LeilaGharani]

You're so welcome!

[@joeismael9359]

Thank you

[@UpForDebate-999]

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

[@carlheroux777]

The #N/A when a vlookup search a number value in a data column in text value. Gave me some headaches when I was an excel beginer. Haha Now that's the first thing I check when I see #N/A's

[@bestlife5298]

Thank u this is very helpful

[@bijaytanty8434]

Thanks

[@truonghoanghaminh4839]

❤ from Vietnam

[@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

[@SheikhSalahUddinNoor]

Dear Leila Sis, Would you please use one of the frames from the video to set the cover image fopr that video? Because it will help us to find our favourite/required videos easily. It is just an idea. Thank you.

[@valenmolina]

Seriously, Leila's voice is super satisfactory. I don't know why but it sounds so peaceful and relaxing. She should start a podcast

[@leezhengrong7247]

thanks

[@NuradeenTahir]

Nice video. However, you forgot to share the bonus at the end which you promise at the beginning.

[@erdalorals]

Thanks..