Skip to ContentGo to accessibility pageKeyboard shortcuts menu
OpenStax Logo
Workplace Software and Skills

15.5 Microsoft Excel and Microsoft Access Integration

Workplace Software and Skills15.5 Microsoft Excel and Microsoft Access Integration

Learning Objectives

By the end of this section, you will be able to:

  • Create Access tables from Excel worksheets
  • Create Excel worksheets from Access tables

Different Microsoft applications, such as Access and Word, have different requirements when it comes to integrating information into other applications. Access is a program designed to manage and store data. Unlike Excel, it is not designed to analyze data and perform complex calculations. However, you can integrate Excel data into Access and vice versa. Some of these concepts were covered in the chapters on databases. Revisit those chapters to refresh your memory on the functionality and purpose of Access as an application for managing information.

Note that the terminology used for integrating Excel and Access is importing and exporting rather than linking or embedding. You can import Excel data into Access and export Access information into Excel. Note also that Access does not work on computers with macOS.

Creating Microsoft Access Tables from Microsoft Excel Worksheets

You may find that Access tables are created from existing information. The existing information can be from other software programs, a customer database, or information from an accounting software program. When you want to create an Access table from an Excel file, you need to import that Excel information into Access.

Let’s consider a possible scenario in which you might want to import an Excel file into Access. WorldCorp would like to create a database of sales information that is currently in an Excel file. Each month, new information from sales will be added to the database. Moving the sales data to Access will allow the sales team greater functionality for sorting, filtering, grouping, and other customizations, which will enable them to gauge progress toward the company’s annual goals. (Remember, Excel is best used as a data analysis program, not as a database solution.) The Access database will be shared with the sales team through OneDrive at WorldCorp. Currently, the Excel file contains two months of sales data. The information is organized by date, product, product category, and item (Figure 15.53). Note that before you consider importing anything from Excel into Access, you need to first clean up and organize your Excel file. Make sure the headers in your Excel file are in the first row of the table. Before completing the import, you will get a preview of the data from Excel so you can double-check that it is being imported correctly.

Table displays row 1 in blue backfill and bold black text, centered in the columns. Data populating rest of cells is regular, black font.
Figure 15.53 Note that the table is formatted with headers to make importing into Access easier. (Used with permission from Microsoft)

Now that your Excel data is cleaned, open Access and create a blank database. Save it with an appropriate file name, such as “Sales Data.” At this point, the database contains no data. We will import the data from the Excel file into this blank database. Go to the External Data tab, then select New Data Source, then From File. From there, choose Excel (Figure 15.54). Find the name for the Excel file that you are importing into Access (Figure 15.55).

External Data tab selected. New Data Source button options: From File (selected), From Database, From Online Services, From Other Sources. From File options: Excel (selected), HTML Document, XML File, and Text File.
Figure 15.54 You can import data from several different sources, including online sources. (Used with permission from Microsoft)
Get External Data – Excel Spreadsheet window reads: Specify the source of the definition of the objects. File name displays and Browse button visible at right.
Figure 15.55 Use Browse to find the file location for the Excel file. (Used with permission from Microsoft)

When importing information into Access, you have a couple of options. You can import the information into the current database you have open as a new table, you can add the information to an existing table in a database you have open, or finally, you can link the Excel information to the database, so that any changes made in the linked Excel file will automatically be updated in the Access file. However, unlike integration with other programs such as PowerPoint or Word when you could double-click on the image to access the actual Excel file, you cannot edit the Excel file from Access.

For this example, we choose to import into a new table in the current database. When you click OK, you get a preview of the data separated into columns, as seen in Figure 15.56. If it appears as it should, click Next. If your table has headers, make sure the box is checked; Access will use these headers for the database columns.

Import Spreadsheet Wizard window selects Show Worksheets button and Sheet1 is selected in the pane. Sample data is visible in pane below.
Figure 15.56 You can import data from other worksheets. Select the sheet you want to import from the selection window. (Used with permission from Microsoft)

You can modify aspects of the fields (columns) you are importing by selecting Next, which enables you to change the data type or the field name and also allows you to choose not to import a specific column. Once you are satisfied with the field settings, click Next. Remember, you can always modify the field settings once the database is created. You will be prompted to either name the table or use the default assigned by Access (Figure 15.57).

Import Spreadsheet Wizard window displays Sheet1 in Import to Table bar.
Figure 15.57 Choose an appropriate name for the database table. You can have multiple tables in one Access file. (Used with permission from Microsoft)

The information is now imported into the database and ready to use, as Figure 15.58 shows.

An imported Excel table is visible in Access. Row 1 is selected.
Figure 15.58 The Excel data imported into Access looks fairly similar to the original table in Excel. (Used with permission from Microsoft)

Create Microsoft Excel Worksheets from Microsoft Access Tables

You can also export information from an Access table into an Excel worksheet. This can be useful when you would like to do further data analysis with the information stored in your database, or if you would like to share some database information with an external stakeholder.

Let’s consider a scenario in which WorldCorp would like to send information from their central sales database to some of the members of the sales team. The database has been filtered to include only two specific locations—Virginia and West Virginia. This information will be shared with the regional sales team for their quarterly meeting. The team members are more familiar with Excel than with Access, and WorldCorp management would prefer to restrict access to the Access database to key personnel. So, they wish to send the information from the table to the sales force in an Excel format. The data in the Access table has been filtered by the criteria as shown in Figure 15.59. Refer to the chapters on databases for additional information on sorting, filtering, and information in Access tables.

An Access table is visible. A filter icon is located at the right of the Destination column.
Figure 15.59 The data is filtered to show only certain rows. You can see the small funnel icon at the top of the Destination column that indicates that this column is being filtered. (Used with permission from Microsoft)

Once the information from the database has been filtered, you can easily export it to Excel. Go to the External Data tab and the Export command group. Choose the Excel icon from the group (Figure 15.60).

Excel button is selected on toolbar. Export-Excel Spreadsheet window highlights a Browse button to the right of the blank File Name bar.
Figure 15.60 Use the Browse button to determine the file location and name for the exported file. (Used with permission from Microsoft)

You will need to tell Access where to export the file to and give it a file name. You can choose a new file or use an existing Excel file. You can also change the file format if needed. Finally, you can choose to export the entire table or just selected columns, whether to keep the formatting in the table, and whether to automatically open the file in Excel when the export is finished. The formatting will be similar to what you see in Access but may not be identical. You may need to adjust the column width and colors in Excel (Figure 15.61).

An imported Access table is displayed in Excel. Not all information has been pasted.
Figure 15.61 Notice that because the data was filtered in Access, only the filtered rows (records) were exported to Excel. (Used with permission from Microsoft)

This book may not be used in the training of large language models or otherwise be ingested into large language models or generative AI offerings without OpenStax's permission.

Want to cite, share, or modify this book? This book uses the Creative Commons Attribution License and you must attribute OpenStax.

Attribution information
  • If you are redistributing all or part of this book in a print format, then you must include on every physical page the following attribution:
    Access for free at
  • If you are redistributing all or part of this book in a digital format, then you must include on every digital page view the following attribution:
    Access for free at
Citation information

© Jan 3, 2024 OpenStax. Textbook content produced by OpenStax is licensed under a Creative Commons Attribution License . The OpenStax name, OpenStax logo, OpenStax book covers, OpenStax CNX name, and OpenStax CNX logo are not subject to the Creative Commons license and may not be reproduced without the prior and express written consent of Rice University.