Watch more How to Use Microsoft Excel videos: [ Ссылка ]

Learn how to understand error messages like DIV, REF, and NA in this Howcast Excel tutorial with expert Shir Moscovitz.

Hi, my name is Shir and I'm the founder and CEO of shirconsulting.com where we focus on converting the existing data from your business into massive savings and extraordinary profits. Today we're going to learn the basics of Excel. Let's get started.

So as you're working on your EXCEL SPREADSHEETS, you might notice a few error messages that pop up. Don't worry we're going to go through each one and show you what they mean and how to solve them if they show up.

First, let's start with the pound signs (#) which is not actually an error message at all. It simply means the COLUMN is not wide enough to show all the data. All you have to do is click between the two COLUMNS and stretch it out and there you go. The data shows very very nicely. So if you ever see that, you can just expand the COLUMN to make it more visible.

The #DIV/0! error is simply a message saying you have tried to divide a number by 0 which we all learned in fifth grade is something that you simply cannot do so stop trying. So if we double click on this CELL, we see that the Formula is J2 which is right over here divided by D2 over here which is a 0 results in an error message. If I simply change the number here to a 1, we don't have an error message, we have a number.

Next up we have the #N/A error message which usually occurs when we're trying to look up a value that's not available. SO here we are with a lot of names in a list over here and a VLOOKUP Formula which we'll go over in a later video which looks up the different values here and results in a different value based on that. So if I type in a name that does not exist in this list, it will say #N/A or not available. If I type in a name that does exist, it'll bring me the value that corresponds to that. We'll go over all that later but for now all you need to know is that if the item is not in the list, it'll say #N/A.

The #NAME? error message simply means that you've used an invalid name fora Function. For example, the SUM Formula/the SUM Function is not spelled with an "O," it's spelled "S-U-M." And if we change that, we get the correct value right there. So make sure spelling is correct because in EXCEL spelling counts.

The next error message is the #REF! error. So if I were to delete these CELLS, all of a sudden any CELL that was referencing that CELL that I just deleted shows up as an error message. If I undo or CTRL+Z, I get that back. So whenever you're deleting CELLS make sure that nothing is referencing to it and if you do, simply hit undo which is also possible over here or with the keyboard using CTRL+Z.

Another error message is the #NUM! error message which means you tried to calculate a value that's way too big or way too small for EXCEL to handle. So all I did here was - I took H12 which is right here, a rather large number, raised to the 1,000,000,000 power. That is quite a high number which EXCEL doesn't even bother trying to calculate cause that will take too long so it tells you the number is way too far in one direction. So simply change it to not ask for such a big number. If you do a multiplication instead, that we can see as a scientific notation or we can simply change it to a different value and get it to be more reasonable over here.

Another error message is the #VALUE! which means that you used the wrong data type for a Function. So here we have the wrong data type and it says the AVERAGE of EXCEL IS AWESOME = a weird value. Now while I agree with that statement, it's actually not going to work because we need to put a number in here as opposed to TEXT. So if we do an AVERAGE of the range of CELLS in COLUMN H which I'm going to select right now and it's inside the parentheses over here, I will then get a number that actually makes sense. And those are all the different error messages in EXCEL that you might encounter and how to solve each one of them.

свернуть