Use the Access Wizard to Quickly Export Data to Excel

by Avantix Learning Team | Updated September 27, 2016

Applies to: Microsoft® Access® 2007, 2010, 2013 and 2016 (Windows)

Microsoft Access includes an easy to use export wizard that can be used to export tables, queries, forms or reports to Excel. Certain objects export better than others. For example, a tabular form exports in a more usable format than a columnar form. When you export data to Excel, a copy of the selected data is sent to Excel. It’s common to have many users in an organization using Excel so data in Excel format can work well, particularly if there are a limited number of Microsoft Access users.

Recommended article: 10 Timesaving Shortcuts in Microsoft Access

Exporting using the Ribbon

To launch the Access to Excel export wizard:

  1. Click the External Data tab in the Ribbon.
  2. Click the Excel button in the Export group. The wizard appears.

Using the External Data tab in the Ribbon in Microsoft Access to export to Excel.

Exporting with or without formatting

You can export table and query data to Excel with or without formatting. However, forms and reports cannot be exported without formatting.

If you export a table without formatting, all fields and records are exported. If you want to export filtered records in a table, you should export with formatting so that only the filtered records are sent to Excel. Also, if you export with formatting, Format property settings are respected, hidden columns will be excluded and lookup values and full hyperlinks are exported.

How Access calculated fields, totals and controls export to Excel

Calculated fields, totals and controls created in Access will export as values, not as calculations.

Exporting to Excel using the Access wizard

To export data to Excel from within Access:

  1. Open the table, query, form or report you wish to export. You will usually achieve the best results if you use a table or query. If necessary, apply a filter. If you don’t need to filter records, simply click on the object in the Navigation Pane.
  2. Click the External data tab in the Ribbon.
  3. In the Export group, click Excel. The wizard opens.
  4. Enter a name for the file (if you enter the same name as an existing workbook in the destination, you will be prompted to replace it), and click on the Browse button to select a location for the file. Choose other options in the wizard such as including formatting, if you want to open the destination file and if you want to export filtered or selected records. If you are replacing an existing workbook, the file must be closed first.
  5. Click OK. If you have chosen to open the destination file when complete, Excel will open automatically.

Micrososft Access export wizard dialog box for exporting to Excel.

When naming the Excel workbook during this process, you may want to include a date in the name if you need to retain copies of the data for specific dates.  At the end of the process or when you return to Access, a dialog box will appear asking if you want to save the export steps. If you are exporting on a regular basis, you can save the Export steps for reuse.

Subscribe to get more articles like this one

Did you find this article helpful? If you would like to receive new articles, join our email list.

To request this page in an alternate format, contact us.

Recommended Microsoft Access training

Microsoft Access: Introduction

Microsoft Access: Intermediate / Advanced

Microsoft Access: Introduction to VBA (Visual Basic for Applications)

VIEW MORE COURSES >

Copyright 2018 Avantix® Learning Inc.

You may also like

Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.

Avantix Learning | 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada M5E 1W7 | info@avantixlearning.ca

Pin It on Pinterest

Share This