How to Import Excel Data into Access – Excelchat

We can use Microsoft Access to store a wide range of information for reporting and analysis. In this tutorial, we will learn how to Import Excel Data into a new or existing table in the Access database or link Excel Data to Access. When we import data, Access simply creates a copy of our source data, without making any changes to it.

Figure 1 – How to import excel into access

How to Import Excel Data into New table in the Access Database

1. If we are using Access 2010 or 2013, we will go to the External Data Tab, and select the Excel option in the Import & Link group
2. If we are using Access 2007, we will go to the External Data Tab, click on the Excel option in the Import Group
3. If we are using the Access 2019, 2016 or Office 365, we will go to the External Data tab and select New Data Source in the Import & Link group. Next, we will select From File and then, Excel

Figure 2 – Importing access data into excel

• We will be directed to the External Data dialog box

Figure 3 – Import excel into access

• In the Get External Data dialog box, we will select the location of the file we want to import

Figure 4 – Import excel data into access

• We will mark “Import the source data into a new table in the current database” option

Figure 5 – Microsoft access import from excel

• In the Import Spreadsheet Wizard, we will mark “first Row contains Headings” and we will click Next.

Figure 6 – Import excel data into access

• Next, we can specify the format for each category.

Figure 7 – How to import excel data into access

• We will define our Primary Key, which is the unique signature to our table. We will let Access define this key for the sake of our tutorial.

Figure 8 – Import excel data into access

• We may choose to save Import steps

Figure 9 – How to import excel data into access

• We will enter our desired name for the Table

Figure 10 – Enter the desired name for our table

• Now, if we click on the My New Data Table, we will see our created table

Figure 11 – The New imported Table from Excel

How to import Excel Data into an existing table in the Access Database

We can add a different Excel worksheet to the already existing table in the Access Database. But we have to make sure that the column headings of the new table match with the existing table.

• We will go to the External Data Tab on the Ribbon
• In the Import and Link Group, we will go to From File and choose the Excel Button
• In the Get External Data dialog box, we will select the location of the file we want to import
• We will mark “Append a copy to the records to the table” option and specify the table we want to add our new data.

Figure 12 – Importing excel to access

• We will click Next

Figure 13 – Use Import Spreadsheet Wizard

• Our New Table will be added into the existing table in Access.

How to Link Excel Data to Access

We can link Excel Data to access so that we can make use of reporting and querying tools without storing a copy of our data inside the Access Database. When we link Data to Access, it will simply create a new table that contains links to the source cells. Whenever we modify the source cells, this will appear automatically in the linked table.

• In the Get External Data-Excel Spreadsheet dialog box, we will specify the location of the Excel sheet

Figure 14 – Link Excel Data to Access

• Next, we will mark “Link to a data source by creating a linked table”

Figure 15– Import Excel data into access

• We will specify that the first row contains headings

Figure 16 – Access Importing data from Excel

• We will name the Table

Figure 17 – Link Table to Access

• We will verify that we want to click Table by selecting OK.

Figure 18 – Excel Data into access

• When we click on My Linked Table, the result is the next figure

Figure 19 – New Excel Table from Access

Instant Connection to an Excel Expert

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.