I have data in an Excel spreadsheet that I would like to use in an Access Database. What are the steps?
Importing or linking data from Excel to Access works well if the Excel data is in rows and columns without any subtotal rows, formulas or blank rows or blank columns within the worksheet or range. Column headings should not be wrapped or contain special characters and must be unique names. Then, to cleanly import data:
- Create a blank database in Access (or an existing database you wish to place it) and select the External Data tab > Excel (from the Import group).
- Find the Excel file and select “Import the source data into a new table in the current database” or “link to the data source by creating a linked table.”
- Follow the screens until the process is finished.
Automatic updates
If you have cells that contain formulas, they will import as values. When importing, the Access table is completely separate from your Excel file. Any updates made in Excel or Access after the import are not updated in both applications. However, if you “Link” the data, you can only update from the Excel file, but the data is automatically updated in Access so that it can be used for filters, queries and reports.
Keep in mind just because you have Excel data that imports or links well into Access does not mean that your file is set up ideally to work in Access. In addition, Microsoft Access requires a considerable investment in time to learn how to use it, compared to Excel. But Access works well if you have a lot of data in separate but related tables to analyze.
Learn more
Learn more about database design concepts at ITS’ mini-session on Thursday from 2 to 3 p.m. in the ITS training room, 8011 Wittson Hall. Additional Access training is available in December. See the ITS class schedule and online registration.