Afrikaans
Akan
Albanian
Amharic
Arabic
Armenian
Azerbaijani
Basque
Belarusian
Bemba
Bengali
Bihari
Bosnian
Breton
Bulgarian
Cambodian
Catalan
Cebuano
Cherokee
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
Esperanto
Estonian
Ewe
Faroese
Filipino
Finnish
Frisian
Ga
Galician
Georgian
German
Greek
Guarani
Haitian Creole
Hausa
Hawaiian
Hebrew
Hindi
Hmong
Icelandic
Igbo
Interlingua
Irish
Italian
Japanese
Javanese
Kannada
Kazakh
Kinyarwanda
Kirundi
Kongo
Korean
Krio (Sierra Leone)
Kurdish
Kurdish (Soranรฎ)
Kyrgyz
Laothian
Latin
Latvian
Lingala
Lithuanian
Lozi
Luganda
Luo
Luxembourgish
Macedonian
Malagasy
Malay
Malayalam
Maltese
Maori
Marathi
Mauritian Creole
Moldavian
Mongolian
Myanmar (Burmese)
Montenegrin
Nepali
Nigerian Pidgin
Northern Sotho
Norwegian
Norwegian (Nynorsk)
Occitan
Oriya
Oromo
Pashto
Persian
Polish
Portuguese (Brazil)
Portuguese (Portugal)
Punjabi
Quechua
Romanian
Romansh
Runyakitara
Russian
Samoan
Scots Gaelic
Serbian
Serbo-Croatian
Sesotho
Setswana
Seychellois Creole
Shona
Sindhi
Sinhalese
Slovak
Slovenian
Somali
Spanish
Spanish (Latin American)
Sundanese
Swahili
Swedish
Tajik
Tamil
Tatar
Telugu
Thai
Tigrinya
Tonga
Tshiluba
Tumbuka
Turkish
Turkmen
Twi
Uighur
Ukrainian
Urdu
Uzbek
Vietnamese
Welsh
Wolof
Xhosa
Yiddish
Yoruba
Zulu
In this section of the course, we're going to be taking a look at troubleshooting in Excel.
And we're going to start out in this lesson just by taking a bit of a deep dive into some of the areas
that you might come across when you're working in Excel because it is so important that you understand
the error that you're getting, because that's going to give you more of a chance to be able to fix
it.
And we always want to keep our spreadsheets as error free as possible.
So let's take a look at some of the most common errors.
Now, one error that you might see pop up from time to time is the div. error.
And this is what we call a divide by zero error.
And in general, you'll get this come up when you're trying to divide a number by zero or something
that isn't valid.
So for example, if I was trying to do a calculation here where I wanted to do this number divided by
this number, I'm going to get a div error because I'm trying to divide by zero.
Similarly, if I try and do the same thing, but I'm using a blank cell instead, so I'm doing 20 divided
by blank.
I'm also going to get a div error.
You also might see this type of error if you're trying to do something like an average.
So let's type in average IPS.
I'm going to select my average range, so let's say we're trying to find out the average of these numbers.
My criteria range is also going to be these numbers and my criteria well.
Well, I want to find out the average of everything that's above 50.
So I'm going to put in quote marks greater than 50 and close the bracket.
Now I'm going to get a div error here as well, because the criteria that I've specified is not valid.
I'm trying to find the average of everything in this list that is greater than 50.
And none of these values meet that criteria, and that is why I'm getting the div error.
So those are the types of scenarios where you might find yourself with one of these types of errors.
Let's move on to the name error.
Now this generally comes up when a name you're trying to use is not recognized.
And really, two scenarios spring to my mind when I think of a name error.
The first one is a spelling error in the formula.
So let me just add a value into here.
If I'm trying to do a sum calculation, I just want to add up those two numbers if I've accidentally
spelt some incorrectly.
So maybe I've accidentally pressed an X on the end there and then I'm trying to add these two numbers
together.
I'm going to get a name error because I've spelled the formula wrong.
So if I go back in hit backspace and enter, then my formula is going to work.
You'd also get this type of error if you're using an invalid named range.
So let's say, for example, again, I want to do some calculation, but I want to use the named range,
and I think that I've named these two cells values.
So theoretically, I should just be able to type in the name of that range of cells and get it to perform
the calculation.
Now, if I hit enter, I'm going to get a name error there because the values range doesn't exist.
I haven't set it up.
If I was to now go in, select these two cells and name those values, my formula then works.
So in general, a spelling error in the formula or a missing named range is going to give you a name
error.
Let's move down to the next one, which is an end, a error.
And this is a really common error that you see frequently throughout Excel spreadsheets.
And again, there are a few different reasons why you might get this type of error.
Now, the first one is if you already have a cell that contains an NRA and you're trying to perform
a calculation.
So again, let's use good old some.
If I'm trying to add up this range of numbers.
I'm going to get an error because there's an NRA error in the values that I've selected.
Similarly, I might get an NRA if I do an invalid v look up.
So let's say that I want to look up the word purple in this table over here and get it to return a value.
Well, you can probably imagine what's going to happen here.
If I do a v look up, my look up value is purple.
I'm looking for it in this table over here.
I want to return the values that is column number two, and I want to do an exact match of the word
purple.
And if I hit enter, I'm going to get an error.
Why is that?
Well, it's because the word purple doesn't exist in the table, so invalid lookup values in V lookups
will also throw up and a errors.
Now, a NUM error isn't quite as common, but there are a couple of scenarios where you might get this
error.
For example, if the value in the sale is too large, you're going to get a NUM error.
So if I do something like that?
Say a 40 to the power of 100, that number is so large that it's given me a numb error.
If I modify this number to 10, then Excel can just about cope with that.
You can see it's converted to scientific notation, so values that are too large for the cell will throw
up a numb error.
You also get a numb error if you have your values in the formula in the wrong order.
So, for example, I have two dates just here, and if I want to find out the difference between these
two dates in months, if I do the date diff function.
So let's say C 40 is my first argument.
C 41 is my second argument, and I want to find out how many months there are between these two dates.
If I hit enter, it's going to give me 12.
So that answer is correct.
But what about if I selected these dates in the wrong order?
So instead of C 40, I'm doing C 41, basically minus C 40.
I'm going to get a NUM error because it doesn't make sense to minus this number from this number to
get the result that we want.
So if you have your values in the wrong order, you can also receive a NUM error.
The next very common error that you will see is the value error.
And this generally occurs when the argument is the wrong type.
So if I'm using a date formula, so maybe something like Work Day and I'm typing in a start date, which
is 849, and then I'm specifying the number of days closing the bracket and enter, I'm going to get
a value error because the word red is an incorrect value type for this formula.
When we're using the work day formula, we want to have a start date, which is fine 849.
But then we want to have how many days because what we're looking to return here is a finish date.
So because this isn't a numeric value, it's a text value.
I'm getting an error.
The same thing would happen if I was to use something like the month function, which is basically going
to extract the month from a date.
If I click on the cell and enter, I'm going to get a value error because I don't have a date in this
cell.
I have the word blue.
So basically one of your arguments is of the wrong type.
And then the final error that I want to talk you through is the ref error.
And this is an invalid reference.
Now this generally occurs when a cell has been deleted and it's being used in a formula.
For example, if I type in equal sum and select these numbers, I'm going to get the correct answer.
But what about if I was to delete Column C?
If I deleted that, I'm going to get that reference error.
And you can see in the formula bar, it's telling me exactly where this reference error is occurring.
So all of the values have been removed, and that's why we're getting that reference error.
Now there are a few more errors that might pop up in Excel, but those are definitely the main ones
you're going to come across.
So make sure you understand them so that when you inevitably do come across them, you know how to fix
them.
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.