How to check whether data is outside of the range of the excel calculations.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
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.