How to compare the values ​​of one book with another in Excel

In Microsoft Excel you can use a formula to compare two similar documents in different books. For example, if a small company has two people who compile financial statements in Excel using the same form, you can create a third book that tells you how the values ​​appear between the cells and if they differ. This is useful for quickly comparing two documents that should be similar.

Write the name of each spreadsheet. For example, a company has sheet1 and sheet2.

Write down the name of the sheets you want to compare in each book. In the example, the company wants to compare page 1 of each book.

Write the cells you want to compare. In the example, the company wants to compare the A1 cells of each document.

Open a new Excel document, and format the document so that it resembles the two documents you are comparing. For example, the label in cell A1 is “Accounts.” In column A, list the different accounts you are comparing. For example, if you are comparing two balances, in type A2 put “Cash”, in type A3 “Accounts receivable”, in type A4 “Fixed assets”. Label box B1 as “Difference”. Use cells B in the column to write the appropriate comparison formula, discussed in step 6.

Decide if you want the book to tell you only if the cell values ​​are different between books, or if you want to know the value of the difference in the cells. The fact that you know if the cells are different is very useful for large books, so it is not necessary to browse and compare books only in search of differences, knowing the real numerical difference between books is useful when you compare numbers and do the analysis of results.

Write the formula “= IF ([sheet1.xls] Sheet1! A1 [sheet2.xls] Sheet1! A1,” Different “,” “)” in column B if you want to know if the cells are different in the two books. Replace the title of the spreadsheet, the name of the page and the cell to match the information you have already written. In the example, if cell A1 in a spreadsheet is US $ 500 and cell A1 in sheet 2 is US $ 550, the cell in the new workbook will be read as “different”. Write the formula “= [sheet1.xls] Sheet1! A1- [sheet2.xls] Sheet1! A1” if you want to calculate the numerical difference between the cells, as in each spreadsheet. This formula subtracts the number from spreadsheet 2 from the number on sheet 1. Replaces the title of the spreadsheet, The name of the page and the cell to match the information you have already written. In the example, if cell A1 in a spreadsheet is US $ 500 and cell A1 in spreadsheet 2 is US $ 550, the cell in the new workbook will be read as “$ 50”.

You may also like...