How to Compare Excel Spreadsheets in Accessby Hunter Taylor
Excel is spreadsheet software that can hold large amounts of data. It is not uncommon for an Excel workbook to have several worksheets with lots of data. An efficient way to compare the data on the worksheets is to use Access. Access is a database program used to track, display and manipulate large amounts of data.
Import the two spreadsheets into an Access database. The spreadsheets should contain data unique to each item. For example, if the two spreadsheets contain accounts receivable information, the item that would make each record unique is the customer's account number. Import the data into tables in Access. Ensure that the columns are similar in name and format.
Place the two tables in a query. Link the tables on a common field in the two tables. The fields that should be common and linked are the ones containing unique values. For instance, in the accounts receivable example, the customer account number field should be linked and common to both tables. Depending upon how the link is done, it is possible to get all the records from one table and only a few records from the second table. Drag the fields to the query grids that need to be compared.
Run the query. The results allow the user to compare the fields across the two tables.