Outside range check in excel

How to check whether data is outside of the range of the excel calculations

• 03:15
• Skill Level: 5
• What's my skill level
  

  All of our videos are listed by user Excel Skill Level.

  To find out your personal skill level, download our quick assessment.

  Once you've completed the test, send it to us and we'll evaluate for you.

  

• 

Outside range check in excel

When you are using spreadsheets, you often import data by copying a section & pasting it in to a specified area. However with spreadsheets you have to be careful that you don’t go outside the calculated area otherwise those cells don’t fall into the calculation.

So in this example, what we have is a very simple spreadsheet & we’ve indicated with a color where the end of our calculation range is. One trick is to put an alpha letter in this border row & then to perform a simple check so see that these haven’t been overwritten. So for example, in this cell I’m going to do an IF statement, & all it’s going to say it’s going to look at one of the key columns & say if that cell there equals the alpha we’ve pit in, in this case an X. If that’s the case then we can say ok, however if that’s not the case we have to assume that it’s been overwritten, we’ll maybe put “error”. When I say ok, you’ll see at the moment it’s ok, let’s pretend we go here, & we copy this section & we come here, & we paste it in, you’ll see that this overwrites our border, immediately our error check picks it up. The problem with this is that typically, your error check can only focus on one column, & what happens if only this column is overwritten. So another way to do it is in the boarder cell is to actually put some numbers, in this case I’ve put a one in each cell. And now for my error check I actually some up these & compare them to what I expect them to be. So for example I ‘m going to use the IF function & say if the logical test is of the sum, this area, & I expect it to equal to one, two, three, four. If that is the case, then I want it to say ok, if not I want it to say error. When I say ok, you’ll see in this case that it’s fine, & again if we copy this area here & we paste it in, because we overwrite these boarders we are immediately warned. And in this case we could have multiple violations of it or only one, either way we’ll pick it up because we are addressing all the columns.