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:
- Click the External Data tab in the Ribbon.
- Click the Excel button in the Export group. The wizard appears.
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:
- 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.
- Click the External data tab in the Ribbon.
- In the Export group, click Excel. The wizard opens.
- 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.
- Click OK. If you have chosen to open the destination file when complete, Excel will open automatically.
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.
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
Copyright 2019 Avantix® Learning Inc.
You may also like
You can create calculated fields in select queries in Microsoft Access in the QBE (Query by Example) grid. You’ll need to learn a few syntax rules and then you can create simple to more complex calculations.
Check out these great shortcuts for manipulating controls in Design View in both forms and reports in Microsoft Access.
In Microsoft Access, small green error markers may appear in Design View in forms and reports for a number of reasons. You can turn error checking off completely or set the rules you prefer.
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 | email@example.com