IF statements in Excel to check for bad sorts

An Excel spreadsheet is really easy to corrupt. It's why syntax backed analysis programs have an edge, they keep an audit trail.

Maybe you forgot to extend the auto filter across all of your columns. Or maybe you copy and pasted in some new data that just happened to be sorted by state abbreviation instead of state. Or maybe you did some other wacky thing that screwed up your spreadsheet.

You can't fully prevent corruption, but there are some things you can do. Create copies using save as and check your data often.

Here is one way to check on the go in Excel:

-If you're doing a lot of Excel manipulation (or someone you work with is about to do a lot of manipulation), put an identifier variable in the first column and the last column.

-Create an IF statement in the column right after that last identifier. =IF(logical_test,value_if_true,value_if_false).

-Use the logical test to set the two identifier cells equal to one another.

-My value_if_true is usually 0 and my value_if_false is usually 1. That way when I copy down the formula, I can sum at the bottom. If the sum is anything other than 0 then something is wrong and I can look for the error.

I also keep in an identifier whenever copy and paste is used to merge worksheets. The same IF statement is used.

If you want more Excel tricks, my friend Ann K Emery offers a bunch.