Excel Table from Access Data

Learning Outcomes

  • Create Excel table from Access Data

There are times when Access contains data you want to analyze in an Excel spreadsheet. This export may take place once or more frequently depending on the situation. For example, let’s say at work you have a monthly report for your department head which pulls monthly customer contact data from Access into an Excel table. Although Access can create reports, your department manager is more comfortable with Excel and wants the data reported in that program. We’ll walk through setting this scenario up and show you the integration capabilities of Access.

Exporting Data to Excel

Open the Access file to be exported and follow these steps to export the customer contact information into Excel.

Step 1

Before exporting data from Access look through the data to identify any errors (e.g. #Error, #Num!, #Name?, etc.). Fix any errors—otherwise errors might multiply as incorrect information may be placed in incorrect Excel cells in a worksheet. Now that the data is ready go to the next step.

Step 2

Select the External Data tab, Excel button and an Export – Excel Spreadsheet command window opens.

Access screenshot of customer database table and the External Data, Excel button highlighted.

Step 3

Fill out the information asked for in the window:

  1. File name: name for the exported excel file.
  2. File format: older Excel versions are available as well as the current *.xlsx extension default.
  3. Specify export options: Decide whether to export the data format and layout or not.
    1. If exporting the format, the next box/choice becomes available whether to open the destination file after the export is complete or not.
  4. Click the OK button.

Access screenshot of export to excel spreadsheet wizard window open.

Note: To Format or Not To Format?

How should you determine whether to export data with or without formatting?

  • With Formatting: Exporting a table or query with formatting allows the export wizard to use the format property settings, have hyperlink fields exported as hyperlinks and have it use the format settings. With rich text fields text is exported without formatting.
  • Without Formatting: Exporting a table or query without format means all records and fields are exported with formatting ignored. Only lookup ID values are exported, and hyperlinks are exported as regular text.

Step 4

One more window opens indicating the export is successful and asks if these same export steps should be saved. This option is used if you are frequently exporting this same data often. In this case we leave the box unchecked.

Access screenshot of export to excel spreadsheet wizard window open to select save export steps.

Step 5

Choose the Excel workbook and file format. If you selected the open the file when done exporting an Excel worksheet will open with the exported Access data. Now you can use the data to do anything in Excel you wish to do.

Excel spreadsheet screenshot of exported table from Access.

PRactice Questions

This example was using contact information data in Access, but all kinds of data can be exported from Access to Excel. If an error slipped through, you can either fix it in Excel or return to Access and fix the error there and re-export the data into an Excel worksheet.

Note: Export Possibilities

What is and isn’t possible to export to Excel:

  • There are a few types that can be exported to Excel; forms, tables, reports, queries or even a few selected records from a datasheet.
  • Things that cannot be exported to Excel are modules or macros and if there are subreports, subdatasheet or subforum in the data they are not exported and must repeat the export for each of them if you want them exported to Excel.
  • Only one database object can be exported in a single export, but multiple worksheets can be merged in Excel to cobble together one table or report from the individual exports.

Now that you have walked through exporting a table from Access to Excel, watch this short video to see additional ways to export tables, queries and reports from Access to Excel.

You can also view a transcript for the video “Exporting Tables, Queries and Reports” here (opens in new window).