Access is the main Microsoft Office application that helps create and manage a database. You can analyze large amounts of information and work on the related data in a more efficient way than other spreadsheet applications, including Microsoft Excel, allow.

However, there may come a time when you need to export data from Access to an Excel worksheet, or vice versa. This guide will show you how to do just that.

How to Import MS Access Data into Excel

The procedure for importing data from Access to Excel is quite simple. Before we begin, ensure that the Microsoft Access file that contains the data you want to import is on your computer’s hard drive. If it is on an external drive, plug in the drive and make sure it is properly connected.

Here is how to import MS Access data into Excel:

  1. Open an Excel worksheet.
  2. Switch to the Data tab on the Office ribbon.
  3. Click the Get Data drop-down in the Get & Transform Data category.
  4. Click From Database and select From Microsoft Access Database.
  5. Locate the Access file and select it. Then click Import.
  6. Select a table in the left pane of the Navigator window.
  7. Click the Load button.
  8. Once you have completed the above steps, your Access database records on the Excel worksheet.

The best part is that importing data into Excel makes a permanent connection that you can refresh. So if you make any changes to your data in Access, you can easily refresh it in Excel. To do this, select a cell inside the Excel table and click on the Design tab in the Office ribbon. Then click Refresh in the External Table Data category.

How to Import Excel Data into an Existing Access Table

Follow the steps below if you would like to import an Excel spreadsheet into an existing MS Access table:

  1. See that the spreadsheet uses the same headers as the existing Access database.
  2. Open the Access database.
  3. Click the Enable Content button if a security warning is displayed.
  4. Switch to the External Data tab on the Office ribbon.
  5. Click Excel from the options.
  6. When the ‘Get External Data – Excel Spreadsheet’ dialog opens, browse to the Excel file in the File Name field.
  7. Select the option that says, ‘Append a copy of records to the table.’
  8. Select the suitable table in the drop-down and click OK.
  9. Select the worksheet you want to import and click Next.
  10. Click Finish.

How to Import Excel Data into a New Table in MS Access

Here are the steps you should take:

  1. Open the Access database.
  2. Click the Enable Content button if a security warning is displayed.
  3. Switch to the External Data tab in the Office ribbon and click Excel from the options.
  4. When the ‘Get External Data – Excel Spreadsheet’ wizard appears, browse to the Excel file in the File Name field.
  5. Select the option that says, ‘Import the source data into a new table in the current database’ and click OK.
  6. Select the Excel worksheet you wish to import and click Next.
  7. If the first row on the Excel worksheet contains headers, mark the checkbox for “First ‘Row Contains Column Headings’ and click Next.
  8. You can select the options for each column or leave it at default.
  9. Click Next.
  10. Accept the ‘Let Access add primary key’ default and click Next.
  11. The Import to Table field bears the name of the worksheet. You can change the name if you wish.
  12. Click Finish.

The Excel worksheet imports into your Microsoft Access table once you have completed the above steps.

RECOMMENDED

Protect PC from Threats with Anti-Malware

Check your PC for malware your antivirus may miss and get threats safely removed with Auslogics Anti-Malware

DOWNLOAD NOW

Pro Tip:

Run regular malware scans to ensure that all your important files are safe. Auslogics Anti-Malware checkmates stubborn malicious items that may corrupt your files or cause your computer to malfunction and crash.  The tool makes it possible to schedule automatic scans that arrest threats and give you continuous protection.