Do you have an Excel spreadsheet with huge data and scratching your head about how to find discrepancies? A discrepancy in Excel is quite common irrespective of whether you are an expert or an amateur. It’s only human to make mistakes as you input data into the Excel sheet and some parts go wrong.
Mistakes like a missing period or incorrect spelling may change the entire output. Fortunately, Microsoft Excel provides a set of built-in tools that can prove useful to detect inconsistencies. In this post, we will discuss how to find differences or discrepancies in Excel and get a clean data sheet.
What does discrepancy mean in Excel?
A discrepancy in Excel occurs when there’s an inconsistency in the same data in a database. This means that you must have used a different format for two or more of the same data in two or more cells of the spreadsheet. For example,
- My brand name & Co. total sales = 587
- My brand name & Co total sales = 587
As you can see the period is missing in the 2nd example (Co total sales) compared to the 1st one (Co. total sales). This may look normal to you since you already know what is correct, but the computer does not consider it similar.
Likewise, discrepancies in Excel may also arise due to input errors like space after a value, uppercase or lowercase letters, duplicate entries, or inconsistent data formatting. Therefore, we have this detailed guide to explain how to find discrepancies in Excel easily.
How to find discrepancies in Excel
Before you try any of the Excel tools below to find differences in Excel, it’s recommended to review the spreadsheet manually. You can use different tools like highlighting or conditional formatting to align the data in a readable way. You can then compare the rows or columns and look for any inconsistencies like duplicate entries, incorrect spelling, etc.
If that doesn’t help, you can proceed with the below methods to find discrepancies in Excel.
- Use Excel Filter to find differences
- Find discrepancies in Excel using Conditional Formatting
- Identify differences using advanced Excel functions
- Detect discrepancies using Excel Add-ins
1] Use Excel Filter to find differences
Using the Filter function is one of the easiest methods to find out discrepancies in data, especially for Excel sheets with large data and spelling errors. Here we will consider an example of Class 5 test results. Under the Results column, there are two categories Pass and Fail, and some are spelled incorrectly.
Select the entire Result column and click on the Sort & Filter function on the upper right.
This will create a drop-down list in the Result column. Click on it and deselect all the correct data, for example, the ones that are spelled correctly (Fail/Pass). Press OK and the Result column will show only the incorrect data. Now, you can correct them manually.
2] Find discrepancies in Excel using Conditional Formatting
Conditional Formatting helps you to identify and compare two or more rows or columns with each other to identify mismatched data. The quickest way to find the difference would be to highlight cells for Duplicate or Unique entries.
However, you can also use other rules like Top/Bottom rules, Data Bars, Color Scales, or Icon Sets to find discrepancies in Excel data.
3] Identify differences using advanced Excel functions
You can use the IF and IS function to help you to compare cells and detect any differences. So, here, you can use the IF function to find out which cells are identical and correct (TRUE) and which cells are incorrect (FALSE).
Alternatively, you can combine IF and IS functions like ISNUMBER, ISERROR, ISBLANK, etc. to analyze the set value and return TRUE or FALSE based on the outcome.
You can also use the VLookUp function, HLookUp function, or Match function to find any discrepancies in Excel.
Read: Advanced Microsoft Excel Tips and Tricks
4] Detect discrepancies using Excel Add-ins
Microsoft Excel offers a list of add-ins that help you to find differences in Excel easily. For this, click on the File tab > More > Options > Excel Options > Add-ins > Manage > COM Add-ins > Go > Spreadsheet Inquire > OK. Now, you can use the Compare Files command from Spreadsheet Inquire to compare two workbooks and highlight the inconsistencies for each cell.
Or, you can use the Analysis ToolPak (Excel Add-ins) to analyze and detect discrepancies in Excel data.
Read: How to manage Add-ins in Microsoft Office
Alternatively, you can also use the Excel Compare tool to compare Excel sheets and find discrepancies in the data set.
How do I check data correction in Excel?
When you have an Excel sheet where other users can enter their data in a set format specified by you, that’s when you use Data Validation. This tool helps you to correct any anomalies in the data that do not follow the set guidelines or when entered in the wrong format. In this case, Excel prompts an error alert which means you need to find the differences and correct the data using the Data Validation tool.
To know more about how to apply Data Validation to Cells in Microsoft Excel, you can follow our detailed post.
How do I find a mismatch in a column in Excel?
While Conditional Formatting (as explained above) can help you find mismatched data easily, the VLookUp function allows you to compare individual cells.
If using the Unique Equals Operator to compare columns in Excel, create a new Result column. Now, in the Result column, add the formula to compare individual cells. For example, =I2=J2, and it will return the result as False if the data doesn’t match. For the ones that match, the result will be True.
I hope this helps.
