Recommended Free Tools
iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
To export Access data to Excel, select a table, query, form, report, or open multiple-record view, then choose External Data > Export > Excel. The key decision is whether to export a table or query with formatting: an unformatted export includes all its fields and records, while a formatted export follows what is currently displayed. Check that choice—and the resulting workbook—before sharing or analyzing the data.
How to export Access data to Excel
- Check the source first. Review the object for error indicators or error values; Microsoft warns these can lead to null values in the worksheet.
- Select the object. Choose the table, query, form, or report you want to export. You can also select records in an open multiple-record view.
- Open the export command. In Access, choose External Data > Export > Excel.
- Choose a destination and file format. For a table or query, decide whether to select Export data with formatting and layout.
- Export and inspect the workbook. Check for missing or unexpected fields, nulls, error values, and shifted data. Exported data is a copy; the operation does not convert the Access database into an Excel workbook.
Access exports one database object per operation. If you need data from multiple objects, export each one separately and combine the worksheets in Excel.
Choose formatted or unformatted export
| What matters | Without formatting | With formatting and layout |
|---|---|---|
| Fields and records | For a table or query, exports all fields and records; Format property settings are ignored. | Exports only fields and records displayed in the current view or object. Filters, hidden datasheet columns, and fields omitted from a form or report affect the result. |
| Lookup fields | Exports the lookup field’s ID value. | Exports the displayed lookup value. |
| Hyperlinks | Exports hyperlink contents as text in displaytext#address# form. |
Preserves hyperlinks as hyperlinks. |
| Rich text | Formatting is not retained. | Rich text content is exported without its formatting. |
| Forms and reports | Not available for forms or reports. | Formatted export is the available mode for forms and reports. |
| Report file format | Not available without formatting. | Reports can be exported only in the older XLS format, not XLSX. |
Choose an unformatted table or query export when completeness matters more than display, such as when you need every field for analysis. Choose formatting when the displayed values and presentation are what you intend to share, but first confirm that filters and hidden columns have not excluded needed data. For reports, use the formatted export and account for the XLS limitation.
What Access exports—and what it leaves behind
Calculated and multivalued fields
- Calculated fields: Excel receives the calculated results, not the expressions that produced them. Recreate formulas in Excel if you need editable formulas.
- Multivalued fields: Values are exported as semicolon-separated lists, rather than as separate rows.
Attachments, images, and nested objects
- Pictures, OLE object contents, and attachments stored in the source data are not exported. Add them to the workbook separately if you need them there.
- If a form, report, or datasheet contains subforms, subreports, or subdatasheets, the export includes only the main object. Export nested objects separately when needed.
Dates and Yes/No fields
- Date values earlier than January 1, 1900 are not exported and appear as null.
- If a Yes/No field appears as “#”, Microsoft suggests opening the form in Datasheet view before exporting.
Fix common export surprises
- Expected fields or records are missing: If you exported with formatting, inspect the current view for filters, hidden columns, and fields not shown on a form or report. For a table or query that needs all fields and records, export without formatting.
- Lookup values appear as IDs: This is expected in an unformatted export. Use formatted export if the displayed lookup values are what you need.
- Formatting is absent or inconsistent: Check whether Export data with formatting and layout was selected, or apply the desired formatting in Excel.
- Formulas are missing: Access exports calculated results rather than their underlying expressions; recreate the formulas in Excel.
- Cells contain nulls or error indicators: Correct source errors where possible, then export again and inspect the worksheet.
- A nested object or attachment is missing: Export nested objects separately; add pictures, OLE contents, or attachments to the workbook separately if required.
Reuse a recurring export
If you repeat the same export, save its details as an export specification. Microsoft provides separate guidance for running, scheduling, and managing saved specifications in its Access export-to-Excel guidance. A saved specification can make a recurring task reusable and schedulable; it does not change what Access includes or the limitations of the selected export mode.
#1 Best Overall
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
Supported Access versions
Microsoft’s guidance applies to Access for Microsoft 365 and Access 2024, 2021, 2019, and 2016. The export behavior and options described here are based on that guidance, titled Export data to Excel.
Quick Recap
Rank #4
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
