A way to do an import data check to ensure that some of the columns have not moved around or else columns have been added or deleted.
- 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.
- - 03:32
- - Skill Level: 6
- - What's my skill level
Import Data Check
When pulling information into your spreadsheet, especially when it comes from another system, it is always useful to do some sort of import data check to make sure that what you are receiving is what you expect to receive.
One way of doing this is to actually include the headers twice. So in the spreadsheet, manually, we type the headers we expect to see, & then when we copy/paste from the other system we include the actual header row, we can now do some checks on these headers. For the header check, what we want excel to do is compare what we’ve input here to what we copied & pasted in. A useful feature in excel that can help with that is the EXACT function; we can activate the FUNCTION WIZARD & find the EXACT function. What it says is it checks whether two text strings are exactly the same, and returns true or false. It is also case sensitive, which is important to know. You can specify where the first text is & where the second text is & when we say ok you’ll see we either get a true or a false. To give us a more meaningful description, we can just build a quick IF statement based on this indicator here, if I go to the FUNCTION WIZARD activate IF, and I say IF this cell equals true, if it’s true say “OK” & if it’s false say “check”, when I say “ok”, you get a cell that gives us a description of what is happening with these headers. If we want to get fancy, we can put these two cells all into one formula, & a quick way of doing that is we go to the last formula, highlight it, copy it, & we push the escape key to get out of it, go here we push equals, then we paste our formula then push enter. Now what this is doing is going up, using this cell here, so we can actually now go take the formula, copy the formula out of that cell, push escape, come down here, & where the reference is made to the cell D6, we just replace it with what we’ve just copied, then we push ok. You’ll see we have a single cell formula that handles both those situations. Now lets copy these across to all our headers & what you’ll see if we quiclky look across, is that right at the end here we’ve got a problem cell & what we see what we were expecting for this column was the date & what we got is some other header. This can cause problems later down in our spreadsheet where perhaps we have formula that rely on this column being the date.