To import an Excel worksheet into Microsoft Access for Microsoft 365 on Windows, open the database, choose External Data > New Data Source > From File > Excel, select the workbook, and follow the Import Spreadsheet Wizard. You can create a new Access table, append rows to an existing table, or link to the workbook so Access displays its data without storing a separate copy.
What importing an Excel file does
Importing copies data from a worksheet or named range into an Access table. After the import, the table’s records are stored in the Access database, independently of the original workbook. Editing the Excel file later does not automatically update an imported table, and changes to the Access table do not flow back to Excel.
This is useful when you want to query, filter, relate, or update spreadsheet data within a database. For example, a sales workbook with columns for OrderID, OrderDate, CustomerID, and Amount can become an Access table that you join to a customer table. You can then use Access queries and forms to work with the records.
An import usually handles one worksheet or named range at a time. If a workbook has several sheets, import each relevant one separately, then check whether their fields and identifiers support the relationships you need. Access imports data, not the full Excel experience: worksheet formulas, charts, macros, conditional formatting, and layout are not brought across as equivalent Access features. A formula cell’s displayed or stored value may be imported, but the Excel formula itself does not become an Access formula.
Prepare the workbook before importing
A clean, consistent worksheet makes the wizard’s decisions more reliable and makes the resulting table easier to use. Treat the sheet as a simple data grid: one record per row and one field per column. Avoid title lines, blank spacer rows, subtotals, merged cells, and multiple unrelated tables on the same sheet. Those features can make Access mistake a heading or summary for part of the data.
- Put a single, descriptive heading in the first row of each column, such as CustomerID or OrderDate. Avoid duplicate headings and names that are difficult to distinguish.
- Keep each column’s values consistent. A date column should contain dates, not comments such as “pending”; a quantity column should contain numbers rather than a mix of numbers and explanatory text.
- Check that IDs are preserved as intended. If an identifier contains leading zeros, such as 00427, it may need to be treated as text rather than a number.
- Remove unneeded notes and empty columns, and confirm that the intended data area is easy to identify. A named range can be useful when the sheet includes other content.
- Save and close the workbook before starting. Keeping a backup is sensible if the source data is important.
Formatting alone does not guarantee a particular Access data type. A column displayed with a currency format, for example, still needs its imported values checked. Make sure dates and numeric values are actual Excel values rather than text that merely looks like a date or number.
Import a worksheet into a new table
In Access for Microsoft 365 on Windows, open the destination database and use this workflow:
- Select External Data > New Data Source > From File > Excel, then browse to the workbook.
- Choose Import the source data into a new table in the current database and select OK.
- In the wizard, select the worksheet or named range you want. Review the preview to confirm that it shows the intended data.
- If the first row contains field names, select First Row Contains Column Headings. Check that the headings appear as column names rather than as the first record.
- Review the fields and any options the wizard offers, then continue through the prompts. Choose whether Access should add a primary key, use a field you specify when that option is available, or leave the table without a primary key.
- Give the table a clear name, finish the wizard, and optionally save the import steps if you expect to repeat the same import.
A primary key uniquely identifies each record. If the source has a stable, unique ID, using it can make later updates and relationships easier. If you do not have one, Access can add an AutoNumber key. That creates a new identifier for each imported record; it does not reconstruct a meaningful ID from the spreadsheet. Avoid relying on a row number as a permanent identifier, because sorting or editing the worksheet can change row order.
When the wizard finishes, open the table in Datasheet view and inspect the first and last records, the field names, and several values in each column. A successful wizard run confirms that Access completed the operation, not that every value was interpreted correctly.
Append Excel rows to an existing table
Choose the append option when the workbook contains additional records that belong in a table already in the database—for example, a new month of transactions. In the wizard, select Append a copy of the records to the table and choose the destination table. Appending adds records; it does not update matching records or replace old ones.
Before proceeding, compare the workbook’s columns with the destination table’s fields. The fields should represent the same information and use compatible data types. A workbook column named ClientCode should not be mapped to an Access field that stores a different value simply because the names look similar. Check required fields, validation rules, and the primary key too. If the incoming rows contain IDs already present in the table, a uniqueness rule may prevent duplicates from being added.
For repeated monthly files, test the process on a copy of the database or a small sample first. Then check how many rows were added and whether any records were rejected. If the source layout changes from month to month, a saved import specification may no longer be appropriate; review the mapping rather than assuming the same steps will still fit.
How Access handles headings and data types
Access creates table fields based on the imported data and the choices available in the wizard. It may infer a type from values in a column, so inconsistent entries can cause trouble. For example, if a column mostly contains numbers but includes “unknown,” that entry may be converted, rejected, or handled differently than expected. The precise result depends on the data and import choices, so inspect the table and any import error information rather than assuming every row was accepted unchanged.
Think about how the field will be used, not just how it looks in Excel. Postal codes, product codes, and account identifiers are often better stored as Short Text because arithmetic is not meaningful and leading zeros matter. Dates should be stored as Date/Time if you need date filtering or calculations. Amounts and counts should use suitable numeric types, while long descriptions may need a text field with more capacity. You can adjust field properties in Access after import, but changing a type can affect values that do not fit the new type.
Column headings become field names when you tell the wizard that the first row contains headings. Use unique, understandable names and avoid treating a heading row as a record. If Access creates generic field names or imports the headings as data, the heading option was likely missed or the selected range did not start where expected.
Common import problems and how to address them
- Some records are missing: Check the selected worksheet or range, blank rows, and the import results. Confirm that the data did not stop at an unexpected blank area or that rows were not rejected because of a type or validation issue.
- Leading zeros disappear: The values may have been interpreted as numbers. Format the source identifiers consistently as text before importing, then verify the stored values in Access.
- Dates appear incorrectly: Check whether the Excel cells contain real dates or text, and whether the values are ambiguous across date conventions. Inspect representative records after import.
- Import errors appear: Access may report conversion problems or values that cannot be added. Review the generated error information, correct the source or destination table design, and rerun the import after resolving the cause.
- The append option fails: Check field compatibility, required fields, primary-key duplicates, and validation rules. An append operation cannot silently make incompatible table designs equivalent.
When a result looks wrong, do not repeatedly import into the production table without checking what happened. A failed or partial append followed by another attempt can create duplicates if some rows were already added. Use a staging table or a backup copy when the records are valuable, and compare counts and unique IDs before merging data into the main table.
Import, append, or link: which should you choose?
Choose import when Access should keep its own copy of the data. It is appropriate for a one-time transfer, for data that needs database-specific cleanup, or when the workbook should no longer be the live source. Choose append when new spreadsheet rows belong in an existing Access table and the fields match that table’s design.
Choose link when the workbook should remain the source and Access needs to read its data without importing a second stored copy. In the wizard, select Link to the data source by creating a linked table. Access creates a linked table that refers to the workbook; changes made in Excel can then be reflected when the linked data is viewed, subject to the workbook and connection being available. A link is not the same as a database import, and Excel files do not provide the same multi-user editing and data-integrity features as an Access table.
One common misconception is that importing creates a live connection. It does not: an imported table is a copy. Another is that linking turns an Excel worksheet into a fully managed Access table. The linked data remains in Excel, so workbook availability, structure, and limitations still matter. If users need reliable shared updates, validation, and relationships, storing the data in Access may be more suitable than leaving it in a workbook.
Verify the result and repeat the process safely
After importing, verify the table before building queries or reports on it. Compare the row count with the intended source range, inspect records near the beginning and end, and check fields that commonly cause trouble, such as dates, identifiers, and amounts. Confirm that the primary key has no unintended duplicates and that the field types support the tasks you plan to perform. If an import error table or report was created, review it and resolve any rejected records.
If the same workbook structure will arrive regularly, Access may offer the option to save the import steps at the end of the wizard. Saved steps can make a repeat operation more consistent, but they do not remove the need to verify the new workbook’s layout and results. A source file with renamed columns or changed data types may require the process to be adjusted.
Access is a Windows desktop application; there is no Microsoft Access app for macOS. On a Mac, you can prepare or edit the Excel workbook, but you need Access on a supported Windows computer to run this import workflow. Excel for the web is also not where the import is performed—the destination database and wizard are in Access.
Frequently Asked Questions
Can I import multiple Excel sheets into Access?
Yes. Run the import wizard for each worksheet or named range you need. Access generally creates a separate table for each import unless you append the rows to an existing compatible table. Review the resulting tables and set up relationships if the sheets share identifiers.
Will Access update when I change the Excel file?
Not when you import it: an imported table is a separate copy. If you need Access to refer to data that remains in the workbook, use the link option instead, and keep the workbook available at its expected location.
Can I import Excel formulas into Access?
Access does not convert Excel formulas into equivalent Access calculations. A formula cell’s value may be imported, but the formula logic is not carried over as a working spreadsheet formula. Recreate needed calculations in an Access query or another appropriate database feature.
Why did Access remove zeros from my codes?
Access likely treated the column as numeric, where leading zeros are not significant. Store codes as text in the source and verify the imported field type and values. Changing a number format after import may not restore zeros that were already discarded.
Can I import an Excel file into Access on a Mac?
Access is available for Windows, not as a macOS desktop application. You can prepare the workbook on a Mac, then use Access on Windows to import or link it.
Related
- Excel Data Validation for Dates: Rules and Examples
- Data Validation Checks in Excel: Rules and Examples
- Data Validation Checklist for Excel: What to Check
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Count Excel Without Blanks: COUNTA and Other MethodsTo count cells that are not blank in Excel, use =COUNTA(A2:A20), replacing A2:A20 with your range. COUNTA counts…
- Excel Date Settings: Formats, Systems, and Common FixesExcel date settings control how dates are displayed, interpreted, and calculated. In Microsoft Excel for Microsoft 365 on…
- Find and Replace in Excel: Steps, Options, and TipsTo find and replace text in Microsoft Excel for Microsoft 365 on Windows, press Ctrl+H, enter the text…
- Making a Calendar in Google Sheets: A Practical GuideTo make a calendar in Google Sheets, enter a date from the month you want in a cell…
- How to Delete a Blank Page in Word Without Losing TextTo delete a blank page in Word, turn on formatting marks with Ctrl+Shift+8 on Windows or Command+8 on…
- Google Sheets COUNTIF for Non-Empty CellsTo count non-empty cells in Google Sheets, use =COUNTIF(A2:A100,"<>"), replacing A2:A100 with your range. The criterion <> means…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.