Up next in 10
Error Handling in Excel: A Complete Guide to Identifying and Fixing Errors
2K views
Feb 7, 2025
Excel formulas not working as expected? Learn how to identify and fix common Excel errors like #N/A, #DIV/0!, #REF!, #VALUE!, and more. This comprehensive tutorial covers everything you need to know about error handling in Excel, including practical examples and powerful functions like IFNA, IFERROR, ISNA, ISERROR, ISERR, and ERROR.TYPE. Whether you're dealing with lookup errors, invalid references, or math mistakes, this video will guide you through detecting, managing, and resolving these issues step by step. Boost your Excel skills and create error-proof spreadsheets today!
View Video Transcript
0:00
hi and welcome today we'll dive into the
0:02
world of error handling in Excel we'll
0:04
start by understanding the common errors
0:06
that occur while using formulas and then
0:08
explore how to effectively handle these
0:10
errors using various Excel functions
0:13
errors are an integral part of working
0:14
with formulas and Excel has specific
0:17
error values to help you identify issues
0:19
let's review each error type in detail
0:21
along with examples and solutions let's
0:24
start with the notavailable error have
0:26
you ever encountered the mysterious
0:27
hashtag n/ a error in your Excel
0:30
spreadsheet the hashtag in/ a error in
0:33
Excel stands for not available it
0:36
typically occurs when a formula or
0:38
function can't find what it's looking
0:39
for this error is excel's way of telling
0:42
you that it couldn't produce a valid
0:44
result based on the data or parameters
0:46
you've provided for example if you're
0:47
trying to calculate a discount using the
0:49
is function it will calculate values for
0:51
matches but for values that do not match
0:54
any condition Excel will return the
0:56
hashtag in/ a error as you see on the
0:58
screen our form for has two conditions
1:01
if neither condition is met it Returns
1:03
the hashtag in/ aerror to fix hashtag
1:07
in/ a errors one method is to use the is
1:10
error function to test if the formula
1:12
return an error the is error function
1:14
takes one argument the value and returns
1:17
true if it finds an error otherwise
1:19
false however the VIS error function
1:22
isn't just limited to detecting the
1:23
hashtag in/ a error it also identifies
1:26
other errors like divide by zero name
1:29
not found null value Etc instead of
1:32
Simply getting true or false you may
1:34
want to return a specific value if the
1:36
condition isn't met for example you may
1:39
want to offer zero no discount if
1:41
neither condition is met in the if
1:43
formula in this case you can use the
1:45
formula shown on screen here the IF
1:48
function works with is error to return a
1:50
default value when the formula
1:51
encounters an error using IF and is
1:54
error together can make the formula more
1:56
complex but fortunately Excel provides a
1:59
simpler alternative
2:00
the if error function the if error
2:02
function takes two arguments the first
2:05
argument is the value to test and the
2:07
second argument is the value to return
2:09
if an error is found this function will
2:11
return zero if it finds an error
2:13
otherwise it will return the original
2:15
value from the formula in cell C2
2:18
instead of creating a separate column to
2:19
handle the error you can directly update
2:21
yours formula to eliminate the hashtag
2:24
in/ a error and generate clean results
2:26
in the discount column this way your
2:29
formula not only calculates the correct
2:31
discount values but also handles
2:33
potential errors gracefully ensuring a
2:35
clean and professional looking
2:41
spreadsheet now let's talk about the
2:43
This na and if na functions in contrast
2:47
to the b error and if error functions
2:49
that handle all types of errors this Na
2:52
and ifna functions are specifically
2:53
designed to handle only the hashtag in
2:55
SL a error if you were certain that the
2:57
error type is hashtag in/
3:00
you should use is na or if na instead of
3:03
is error and if error for more precise
3:05
error handling the B na function Works
3:08
similarly to the is error function it
3:11
takes a value as its argument and
3:13
returns true if the value is hashtag
3:15
and/ a and false otherwise you compare
3:18
the true or false output of the is na
3:21
function with the IF function to produce
3:23
a more useful message or value for
3:25
example if you want to replace the
3:27
hashtag in/ a error with zero you can
3:30
use the IF function as shown on the
3:31
screen this approach customizes the
3:33
output just as we did in the is error
3:35
example the Biff na a function is a
3:38
streamlined alternative to using is na
3:40
with the IF function it works similarly
3:42
to the if error function but is more
3:44
specific as it only catches hasht n/ a
3:47
errors while if error handles all types
3:50
of errors as you can see on the screen
3:53
that if n a function replaces hashtag
3:55
in/ a errors with a value provided in
3:58
its second argument this makes a simpler
4:00
and more efficient option when you know
4:02
the error in question is hashtag in/
4:10
a now you understand that the hashtag
4:13
inav value means the formula cannot
4:16
return a legitimate result and you've
4:18
learned how to handle or fix it
4:20
sometimes you may need to intentionally
4:21
generate a notavailable error you can do
4:24
this by using the na function as shown
4:27
on
4:27
screen the hashtag number error
4:30
indicates an issue with a number in your
4:32
formula often due to an invalid argument
4:35
in a mathematical or trigonometric
4:37
function for instance this error can
4:39
occur if you provide a negative number
4:41
where a positive number is required the
4:43
hashtag name error indicates that Excel
4:46
doesn't recognize a name used in a
4:48
formula as a valid object this error
4:50
often occurs due to a misspelled
4:52
function name an incorrectly spelled
4:54
sheet name or a mped cell reference for
4:57
example as shown on the screen if you
4:59
enter a named range or reference that
5:01
doesn't exist Excel will return this
5:04
error the hashtag RF error indicates
5:07
that your formula contains an invalid
5:09
cell reference this typically happens
5:11
when a cell row or column that the
5:14
formula depends on has been deleted for
5:16
example as shown on the screen I
5:18
initially referenced cell F1 in a
5:20
formula in cellb5 when I deleted cell F1
5:24
the formula in cellb5 returned the
5:27
#f error because it could no longer find
5:29
the reference it relied on that hasht
5:32
value error indicates that your formula
5:34
is using the wrong data type for the
5:36
operation it is trying to perform for
5:38
example as shown on the screen the sum
5:40
formula returns of hashtag value error
5:43
because it attempts to sum a numeric
5:44
value and a text string since the sum
5:47
function only accepts numeric values it
5:49
cannot process the text resulting in
5:51
this error the hashtag div sl0 error
5:56
occurs when a formula attempts to divide
5:57
a value by zero since div division by
6:00
zero is mathematically undefined Excel
6:03
returns this error you will also
6:04
encounter this error if the formula
6:06
tries to divide a value by an empty cell
6:08
as Excel treats empty cells as zero in
6:11
such cases the hashtag null error occurs
6:14
when a formula attempts to use an
6:15
intersection of two ranges that do not
6:17
overlap for example as shown on the
6:20
screen the formula does not include a
6:22
colon or any other valid operator to
6:24
define a proper range Excel interprets
6:27
this as an attempt to find the
6:28
intersection of two separate ranges and
6:31
since they do not intersect it Returns
6:33
the hash null
6:36
error now that you understand how these
6:38
errors occur it's time to learn how to
6:40
handle them using functions like is na
6:42
if na is air is air or if error and
6:46
error. type let's start with the is na
6:49
function we apply it to the range A2 to
6:52
A8 which contains all seven error types
6:55
as you can see on the screen is na
6:57
returns true only for the hashtag in/ a
7:00
error while it returns false for all
7:02
other errors this means that the bis na
7:05
function is specifically designed to
7:07
test for hashtag in/ a errors and
7:09
ignores all other error types next we
7:12
use the Biff na a function this function
7:15
allows you to return a custom message or
7:17
value when the reference cell contains a
7:19
hashtag in/ a error as shown on the
7:22
screen it returns our custom message yes
7:25
only for the hashtag and/ a error in
7:28
conclusion both is in na a and if na a
7:31
functions respond exclusively to Hash n/
7:34
A errors and ignore all other error
7:36
types now let's test the is air function
7:39
unlike is na is air returns true for any
7:42
error except hashtag and/ a this is
7:45
useful when you want to handle hashtag
7:47
and/ a errors separately and prevent
7:50
them from being caught by a broader
7:51
function as demonstrated is air returns
7:54
true for all errors except hashtag and/
7:57
a for which it returns false move moving
7:59
on to the is error function this
8:01
function detects all errors when tested
8:04
against any error it returns true it
8:07
only returns false when no error is
8:09
found as previously discussed his error
8:11
Works similarly to is na but with the
8:14
key difference that it checks for all
8:15
error types instead of just hashtag and/
8:18
a next the if error function goes a step
8:21
further instead of just checking for
8:23
errors it allows you to specify an
8:26
alternate value or message to return if
8:28
the formula results in an error
8:30
as shown on the screen this function
8:32
replaced errors with our custom value
8:34
yes if error is especially useful for
8:36
simplifying formulas and avoiding
8:38
additional if and is error combinations
8:41
finally let's discuss the error. type
8:44
function this function helps identify
8:46
the specific type of error by returning
8:48
a numeric code between 1 and 8 depending
8:50
on the error type for example it returns
8:53
one for the hashtag null error seven for
8:56
the hashtag in/ a error and so on this
8:59
is particularly useful when you need to
9:01
take specific actions based on the type
9:02
of error for instance you could use
9:05
error. type in combination with an IF
9:07
function to display different messages
9:09
or handle errors uniquely based on their
9:11
type thank you for watching if you found
9:13
this tutorial helpful please share with
9:15
others give it a like and don't forget
9:17
to subscribe to the channel hit the
9:19
notification Bell so you never miss an
9:21
update your support helps us create more
9:23
valuable content See you in the next
9:25
video