Ladies and gentlemen, introducing the Exact function
Like a two-dimensional (or flat) database, Excel is capable of storing many different types of data from small business contacts to personal income tax records.
In both of these examples, accuracy is essential to make sure you have the information you need when you need it.
In any data entry situation, people often transpose numbers or mistype a name in a spreadsheet. It is very difficult to tell the difference between 6886 and 6868 or John and Johm when you have long strings of numbers or text in a busy Excel worksheet.
Using Excel’s built-in Exact function, you can make Excel do the work for you when you want to find out whether two cells contain exactly the same information.
The Exact function works equally well for text as it does for numbers. Read on to learn how you can automate the time-consuming task of checking for accuracy in your worksheets.
Using Excel’s Exact Function
Suppose you have a simple worksheet in Excel that looks like the image below:
Notice that in the strings of numbers in the A and B columns, it is difficult to tell whether the number in cell A1 matches the corresponding number in B1. This is true for all of the numbers down the list.
By using the Exact function in cell C1, you can ask Excel to indicate whether the number in cell A1 exactly matches the one in B1. If the numbers match, Excel returns a value of TRUE. If the numbers don’t match, Excel returns a value of FALSE.
To begin, click on the Formulas tab on the Ribbon and then on the Text button. Locate and click on the formula titled Exact. Notice that even though the Exact formula is classified as a text function, it works equally well on numbers.
You should now be looking at Excel’s Function Arguments window. It is here that you will specify which cells to compare for accuracy. In the Text1 box, type in A1 and in the Text2 box, type in B1. Then, click the OK button.
You will notice that Excel returns a value of FALSE in the C1 cell. This is because there is a mismatch between the value in A1 and the value in B1. It would appear that the person who entered the data into these two cells transposed the middle two numbers.
Carrying the formula in C1 all the way down to C15, you can see where the typist made errors when entering these numbers. Notice that the values in A1, A6, A9, A11, and A14 do not match their corresponding values in column B.
These are indicated with the FALSE value in column C. Those values in the remaining A cells exactly match the corresponding values in column B. These are indicated with the TRUE value in column C.
Note that in our example, we could also simply use the equals sign since we are working with just numbers. I could put =A1=B1 into cell C1 and I would get FALSE.
However, using the equals sign with text will ignore case. So, the word excel and the word EXCEL would give a result of TRUE since the case is ignored.
As a final note, if you just want to compare only the number of characters in two cells, you can use the len function. In our above example, if I used the formula =len(A1)=len(B1), I would get a result of TRUE. It would also give me true for every example above since both rows have four characters.
Although technically classified as a text function by Excel, the Exact function works well to find and eliminate typing and data entry errors in large spreadsheets.
The human eye often has trouble telling the differences between two numbers and transposing numbers is the number one data entry error. Using the Exact function, you can make Excel do the work of finding these errors.