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:
- 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.
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)
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
How to Highlight Records or Values in a Microsoft Access Report Using Conditional Formatting
You can highlight values in fields or records in Microsoft Access reports using conditional formatting. Certain conditions must be met in order for the formatting to be applied …
How to Create a Calculated Field in a Microsoft Access Query
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.
10 Useful Microsoft Access Shortcuts When You're Working with Controls on Forms and Reports
Check out these great shortcuts for manipulating controls in Design View in both forms and reports in Microsoft Access.
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
