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. Continue to read 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.
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.
This Tech Tip is brought to you by the Business and Technology Section ... IT solutions for today's CPAs. For more information and to view an archive of previous Tech Tips, please visit us here.
Do you have specific topics you would like to see covered in Tech Tips? Email any suggestions to firstname.lastname@example.org.
LAST UPDATED 1/27/2011