Copy data from Excel

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

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

Back to previous page Go to next page

Clean up your data

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

  • 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.

Try it!

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.

Top of page

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×