Try it! Transcript



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.

Want more?

Video: Move data from Excel to Access

Excel training

Outlook training

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