Access Data from Excel Table

Learning Outcomes

  • Create Access data table from Excel data

There are scenarios where data has been created or stored in Excel and now needs to be added into an Access database. As an example, say that the original company accountant only knew how to create Excel spreadsheets. Now, however all company accounting information needs to be in an Access database. How would you do this?

We’ll walk through importing data from Excel into Access so follow these steps.

  1. Open the Excel workbook and look through the data to:
    1. Clean up the data if there are errors or gaps in the data fields.
    2. Use the Data Tools group, Remove Duplicates button to eliminate any potential make duplicate information that may being exported.
    3. Check to see that columns have headings and are consistent data types (e.g. all currency, percentage, etc.) and correct if necessary.
      Excel spreadsheet screenshot of table data checking for duplicates window.
  2. Save and close the Excel worksheet and open Access.
  3. In the External Data tab, click the New Data Source button, From File, Excel option.
    Access screenshot of External Data tab, New Data Source, From File, Excel button highlighted.
  4. The Get External Data – Excel Spreadsheet window opens. Find the excel file you wish to import into Access using the Browse button.
  5. Next is deciding where to store the imported data. The three options are:
    1. A new table in current database,
    2. Appending a copy of the records in an existing table, or
    3. Linking to the data source by creating a linked table.
    4. For this example choose to create a new table.
      Access screenshot of Get External Data - Excel spreadsheet wizard window open.
  6. The Import Spreadsheet Wizard is now opened, and the Excel table is displayed. Make sure the checkbox for First Row Contains Column Headings if that is the case. Click the Next button.
    Access screenshot of Import spreadsheet wizard window open containing data.
  7. The next wizard window allows you to be specific about the field information being imported by modifying the field information in the Field Options area. For our purposes the defaults work.
    Access screenshot of Import spreadsheet wizard window open containing data.
  8. The next wizard window allows you to define a primary key for the new table. Primary keys uniquely identify every record in a table to be able to return data quickly. For this example, we’ll let Access add the primary key.
    Access screenshot of Import spreadsheet wizard window open containing data.
  9. Access is now ready to import the Excel file. There is a checkbox to select if you would like the table to be analyzed after the data is imported. It will remain unchecked for this example. Now click the Finish button.
    Access screenshot of Import spreadsheet wizard window open saying where to import to table and finishing wizard.
  10. Access now asks if you would like so save these import steps just walked through. If so, click the Save import steps checkbox. We’ll leave it unchecked for now.
    Access screenshot of Import spreadsheet wizard window open asking if the steps should be saved for next time.
  11. The Excel information is now imported into Access and can now be use in Access or even added to as more Excel information is discovered.
Access screenshot of imported Excel spreadsheet customer contact data in new Access table.

Practice Questions

Now that you’ve seen how to import Excel data into a new Access table, watch this video that walks through the steps and demonstrates how to add Excel data to an existing Access table. While the video shows an older version of Word (Microsoft Word 2016), the same steps work in other versions of Word, including 2019 and 365.

You can also view a transcript for the video “How to Import Excel Data into Access” here (opens in new window).