Comparison of two worksheets

In practice, it is sometimes necessary to compare the contents of two spreadsheets. With the exception of specialized Excel plugins that can be used for this purpose, the best way to achieve this is with the help of a formula in which we use the IF function or by using conditional formatting. In the text that follows, you will see how to compare the two tables, which are in two spreadsheets, and contain randomly generated numbers.

Spreadsheets S1 and S2 contain randomly generated data. The first way to compare them is to create a third sheet (call it S3), and to create a comparison formula in it. For example, in cell A1 of worksheet S3 we will write the formula:

=IF(‘S1′!A1<>’S2’!A1,”D”,””)

The formula compares the values ​​of A1 in the first worksheet (‘S1’! A1) and in the second (‘S2’! A1), so if they differ returns the value “D” in the cell, and if they are the same it returns an empty string. It could have been conceived differently, depending on what we want to show as a result. Now you only need to copy the formula into remaining cells within the sheet.

If we want to compare the values ​​of the cells in worksheet S1 in relation to those in S2, we can do so with the help of conditional formatting. Select all the values ​​you want to compare in the first worksheet, then open the Conditional Formatting menu and select the New Rule option. Create a rule using the formula:

=A1<>’S2′!A1

and then set that, if the condition is met, Excel paints the cell background to orange.

If you did everything right in the table within sheet S1 you will see all the differences that exist in relation to the table in sheet S2.