Finding errors in large spreadsheet

[jipajappa]

Hey guys,

I'm new to this forum and have a pretty specific question. I have a feeling there's no quick way to do this, but I wanted to check.

I have been tasked with analyzing a spreadsheet of about 1800 rows of data. Before I actually get into that, however, I have to identify any errors that occurred while entering the data into excel. Basically, when the data was being imported, some got put in the wrong column. For example, one column lists states (GA, CA, etc.) and another column lists years (1992, 1996, etc.). For some reason, in some instances scattered throughout the spreadsheet, the year was put in the state column or the state was put in the year column. Is there a way to quickly identify where all of those errors occurred, or do I just need to manually go through the entire spreadsheet searching for them?

Any help would be much appreciated.

Thank you!

[Fotis1991]

Hi and welcome to the forum.

If your data are as you say, one column with numbers(dates) and another with text(States), one way to do this is using Conditional Formatting, to highlight the errors.

=ISNUMBER($A1)
=ISTEXT($B1)

[jipajappa]

Thanks for that, that will definitely help with a lot of them. What about if there are years in some columns and 2-digit numbers in others, and they might be mixed up. Is there a way to highlight those errors?

[Fotis1991]

Depend on....!

Is the format of the years like this "yyyy"? Are 4 digit numbers? Then maybe you can use LEN function. Something like this.

=LEN($A1)=2