Take advantage of the streamlined editing, powerful querying, and helpful reporting capabilities in Access by copying data from an Excel spreadsheet. Put your data into an existing table or into a new, blank table.

Before you copy data, make sure it’s consistent and structured so that it copies cleanly.

Clean up your data

• All data in a column should be the same type and should have a similar format. For example, make sure all phone numbers include an area code but no country prefix, all addresses include city and state but no country, and all prices include cents—even if you list .00.

• Remove any subheadings, summary or comment rows, and blank rows.

  Note If necessary, add these back later using Access query and report grouping and totaling.

  If you’re pasting the data into an existing Access table, make sure that your spreadsheet has the same number of columns—in the same order—as the Access table. (On the other hand, if you’re planning to create a new Access table with this data, name and order the columns however you prefer.)

  Tip It’s good database practice to list each discrete piece of information in its own column. For example, separate first name and last name into different columns, and separate street address, city, state, and postal code.

Add Excel data to a new table

1. Select and copy the data in Excel that you want to add to the table.

2. In Access, select Home > Paste.

3. To indicate whether the first row of your data contains column headings, select Yes or No.

4. If you’d like to, rename the table and fields.

Add Excel data to an existing table

1. Select and copy the data in Excel that you want to add to the table.

2. In Access, open the table you want to paste the data into.

3. At the end of the table, select an empty row.

4. Select Home > Paste > Paste Append.

When you copy Excel data into an Access database, your original data in Excel remains unchanged.