Use the Access Wizard to Quickly Export Data to Excel

by Avantix Learning Team | Updated September 18, 2020

Applies to: Microsoft® Access® 2007, 2010, 2013, 2016, 2019 and 365 (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.

More resources

How to Convert a Microsoft Access Report to PDF (3 Ways)

How to Filter a Report on the Fly in Microsoft Access

10 Techniques for Designing Forms in Microsoft Access

Related courses

Microsoft Access: Introduction

Microsoft Access: Intermediate / Advanced

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

VIEW MORE COURSES >

Our instructor-led courses are delivered in virtual classroom format or at our downtown Toronto location at 18 King Street East, Suite 1400, Toronto, Ontario, Canada (some in-person classroom courses may also be delivered at an alternate downtown Toronto location). Contact us at info@avantixlearning.ca if you'd like to arrange custom instructor-led virtual classroom or onsite training on a date that's convenient for you.

Copyright 2024 Avantix® Learning

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 |18 King Street East, Suite 1400, Toronto, Ontario, Canada M5C 1C4 | Contact us at info@avantixlearning.ca

Summary
How to Easily Export Microsoft Access Data to Excel
Article Name
How to Easily Export Microsoft Access Data to Excel
Description
Microsoft Access includes an export wizard that can be used to export tables, queries, forms or reports easily 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 limited number of Microsoft Access users.
Author
Publisher Name
Avantix Learning

Pin It on Pinterest

Share This