00:00Here's a rundown of all possible errors in Excel and how to solve them.
00:03Starting first on the list is the ref error.
00:05The ref error happens when a formula references a cell that is no longer valid, usually because
00:09the reference cells are deleted or overwritten.
00:12To mitigate this, use a sum function instead like this.
00:18Next, name error.
00:20When referencing a text on a formula, enclose the text on a double quotation mark, even
00:24for spaces.
00:25Omitting a double quotation mark can result in name error.
00:28Be sure to double quote all strings.
00:31Name error can also arise from incorrectly entering the function name, which leads Excel
00:35to believe that you are trying to enter a string.
00:37Make sure to use the built-in IntelliSense as you enter the function name by double clicking
00:42on it or pressing the tabs on your keyboard to minimize typo error.
00:47Next in the list, the null error.
00:49This error arises from using the wrong range operator in the formula or trying to intersect
00:54a range that do not overlap.
00:56But make sure that the ranges are entered correctly.
00:59Next one on the list is spill error.
01:01Spill error occurs when a formula produces multiple results but cannot output them on
01:06the sheet as some data or the format of the cell is obstructing it.
01:10As you can see, this number is obstructing its output here.
01:13But this one is interesting.
01:14You can see that the cells are merged together.
01:17So even if you remove this and go here, press F2 and hit enter, you can see it's still showing
01:22spill error because it's trying to produce an output on a single column here.
01:25Now to mitigate this, you highlight the cell, press Ctrl-1 and remove the merge in that cell.
01:30And hit OK.
01:30Now press F2 and hit enter.
01:33Now you can see that the spill error has been resolved.
01:36Next in my laundry list is the calc error.
01:39This error occurs when the Excel calculation engine encounters an unspecified calculation
01:43error with an array.
01:45To resolve it, try rewriting your formula.
01:47Or, if you are using a filter function like me in this example, ensure that the filter
01:52condition exists to start off with.
01:54And also specify the optional if empty argument in the filter function.
02:00Alternatively, you can encapsulate your function with an if error function if the if empty argument
02:07is not available in your function.
02:10Next is the NA error, which often occurs with functions like XLOOKUP, VLOOKUP, HLOOKUP,
02:15LOOKUP, LOOKUP, or MATCH when they fail to find a specific value.
02:18In this example, since lime isn't in my VLOOKUP table, it will return an NA error.
02:23To resolve this, encapsulate your lookup function with an if error function.
02:28The next error on my list is division by zero error.
02:31This error occurs when you try to divide a number by zero or a blank, which is mathematically
02:36impossible.
02:37To get around this, simply encapsulate the division equation by if error function like this.
02:43Next on my list is the VALUE error, which occurs when there's something wrong with the cell
02:48you are referencing.
02:49The error is a general error, and it sometimes can be hard to find the grassroot cause of it.
02:55For my example, it's pretty straightforward.
02:58The last error on my list is the OVERFLOW error, which is denoted by a series of hash or a
03:03number sign.
03:04This is really not an error, but an indication that the content of the cell is too wide to
03:09be displayed within the column width.
03:11This typically happens with date, numbers, or text that exceed the column width.
03:17To resolve this, simply widen the column to fit the content or adjust the cell formatting.
03:24Thank you so much.
03:25Thank you so much for watching.
Comments