Why Does Excel Say Cell Values Are Not Equal When They Are?
What’s Wrong With Excel?
I try to get on the Mr. Excel forums a few times a week and this question is one of the most popular troubleshooting questions posted. This is quite understandable as humans tremendously excel at processing visually. For example, if we see two red apples sitting on a table we can immediately process the texture, approximate weight, and even the taste of those two objects from a fairly good distance. And because they are both apples we immediately assume these properties are the same for both. This is all due to their similar appearance. But what if you opened up one of those apples and it looked like this:
Not what you were expecting, right? The main point I want to get across to you today is things might not always be as they appear in Excel. The only way to find out is to cut in and see what’s inside! Below I will list a series of tests you can perform on your values to determine why Excel thinks data points are different when they appear to be the same.
Spaces Before or After Your Values
Sometimes when you receive extracted data or you are trying to compare two data sets, ‘ghost’ characters will slip into the cell values and try to play tricks with you. These ‘ghost’ characters take form as spaces and if they occur in the beginning or end of text, we cannot see any visual evidence of their existence! In the example below, Cell C2 is testing to see if A2 = B2. This text is giving us a FALSE which means they do not equal each other. What is causing this ? Both cells have just the word ‘Hello’ in them! Well, if you use the LEN( ) function to determine the length (how many characters) of our ‘Hello’ values, you will see that Value 1 has a length of 5 and Value 2 has a length of 6. This is because there was an extra space entered in Cell B2. Cell B2’s real value is “Hello “.
How Can We Handle This?
Well, Excel has a function called TRIM. What TRIM does is remove all spaces before and after a text string (it ignores spaces in between words). You can see below if we include TRIM in our test we will output a TRUE value.
Use The TRIM Function With LOOKUP Functions
You will most often run into this problem when using Lookup Functions (ie VLOOKUP, MATCH, HLOOKUP, etc…). If you run into a situation where you believe your VLOOKUP should be finding a match, try wrapping a TRIM function around your lookup value. An example formula could look like this:
=VLOOKUP(TRIM(A2), $D$2:$Z$400, 3, FALSE)
Text versus Numerical
Another very popular situation that arises when exporting numerical data from outside databases is mismatching data formats. I find this occurs most often with identification values where your data may have leading zeroes (such as employee IDs or Invoice numbers). In our example below we have two cells containing the value of 200….or so it seems! As you can see by the test in Cell C2, Cells A2 and B2 do not equal each other. For your test in this situation, you can use the ISNUMBER function to, yep you guessed it, test if your cell values are numerical.
So how can we handle this?
To force a text string to become a numerical value you can use the VALUE function. By incorporating the VALUE function in your test you can see (below) the test will successfully compare the two numbers as equal.
Use The VALUE Function With LOOKUP Functions
Similar to how you can use TRIM within a lookup function to cleanup your data, you can also use VALUE in the same fashion with your lookup functions. An example formula may look like this:
=VLOOKUP(VALUE(A2), $D$2:$Z$400, 3, FALSE)
Convert Text Into Values
Another option could be to convert all the text values to numerical ones. If Excel notices a text value that only has numbers in it, the cell will get flagged. If you look very carefully in the above two images (click on them to enlarge), you can see a green indicator in the upper left-hand corner of Cell B2. To convert this cell’s value into a numerical one do the following:
- Select the cell(s) with the green indicator (a little box should appear with a drop down arrow next to the first cell selected)
- Click the drop down arrow
- Select Convert to Number
- Excel with cycle through your selection and convert all your text values to numerical ones (WARNING: with large data sets this will take some time, this is usually a good time to schedule a coffee break)
An Impossible Value
Another scenario I wanted to throw into this post deals with impossible values. More specifically, this can occur with dates users enter in manually. In the example below, Cell C2 is attempting to subtract the two dates in Cell A2 and Cell B2. Only…when the operation is calculated, a #Value! error occurs. This error is being caused by the fact there are not 31 days in the month of April. We can tell this by using the DAY function. The DAY function ouputs the number of days in a date value. Since April 31st is not a possible date value, an error occurs (see Cell B3 below).
How Do You Prevent This?
My recommendation to prevent any input errors is to use Excel’s Data Validation feature. This feature is located within the Data tab of the Ribbon, inside the Data Tools group. If you are trying to prevent users from incorrectly entering in data following a pattern, you can check out my post covering How To Validate Patterns in Excel.
How Do You Handle Your Data?
In this post I described three scenarios I run into all the time while working with data exports from third-party database software. I am sure there are other scenarios I overlooked and I want to know if you have come across any other scenarios where Excel messes with your eyes! I look forward to hearing about your painful data experiences!
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.