Make the most of the efficient editing, strong querying, and useful reporting capabilities in Access by copying data from an Excel spreadsheet. Insert your data into an existing table or into a new, blank table.
Before you copy data, ensure it’s equal and structured to enable it to duplicate cleanly.
Cleaning your data
- All data contents within a column should be the consistent type and have a corresponding format.
- Delete any subheadings, summary or comment rows, and empty rows.
NB: If applicable, add these back further on with Access query, report grouping and totalling.
If you’re pasting the data into a current Access table, it is crucial you ensure your spreadsheet has the exact number of columns (appearing in the same order) as the Access table. In contrast, if you’re going to produce a new Access table using this data, then you can define and order the columns any way you want.
Add Excel data to a new table
- Choose and duplicate the data in Excel that you want to include in the table.
- Click Home > Paste in Access.
- You’ll be given an option of Yes or No, to highlight whether the initial row of your dataset has column headings.
- Feel free to rename the fields and table, if necessary.
Add Excel data to a current table
- Choose and copy the information in Excel you want to input into the table.
- In Access, select which table you wish to work on and paste the data directly there.
- Click a blank row, by the end of the table.
- Press Home > Paste > Paste Append.
NB: After duplicating Excel data into an Access database, your initial Excel data remains the same.
You’ve probably used Excel to store lists of data, such as contacts.
If your list is getting hard to manage in Excel, it’s a good idea to move it into Access so you can take advantage of its querying and reporting features.
One way is to just copy and paste the Excel data into Access—either into an existing table, or into a new one. Here’s how to do it.
You might need to do some cleanup in Excel first. Access tables require that you structure your data, which helps keep things more accurate.
Make sure all the data in each column is of the same type, such as text, dates, or numbers.
Get rid of any subheadings, summary rows, or blank rows. You can recreate those later using the grouping and totalling features of Access queries and reports.
If you’re going to be pasting the data into an existing Access table, make sure your Excel data has the same number of columns as the Access table, and that the columns are in the same order.
You might need to add a blank column here and there in your Excel worksheet to make them match up.
If you’re creating a new table in Access, just format the Excel worksheet the way you think is most useful.
For example, it’s a good idea to separate first and last names into separate columns…
…and address information is usually more useful if you separate it into columns as well.
After your data is ready, select it…
And copy it to the clipboard.
If you’re creating a new table with this data, in Access, just right-click in the Navigation Pane, and select Paste.
If your copied data includes the column headings, select Yes. Otherwise, select No.
Access creates a new table, and you can now start working with it.
If you’re pasting into an existing table, in Access, double-click the table you’re adding to.
Select the asterisk in the empty row at the bottom to highlight the new row, and then on the Home tab, select the down-arrow under Paste, and then select Paste Append.
The Paste operation might take a couple of tries, if your Excel data doesn’t match up with the columns in your Access table.
If you’re having trouble pasting, double-check the data types of each column in the Access table.
You can do this by selecting the Fields tab, and then clicking in a column. That column’s data type is shown here. Make sure the data you’re trying to paste into that column matches that data type.
After you paste the data, you can start working with it in Access. Because this is a copy of the data, your original data in Excel remains unchanged.