Copy data from Excel to an Access datasheet

You can copy and paste data from Excel into an Access datasheet. Copying and pasting your data can save you time, especially if the data in Excel is already in rows or columns that are the same or very similar to the columns on the datasheet. The steps you take to copy and paste depend on how your data is arranged in Excel, and whether you paste into part of the datasheet or the whole thing.

If your data is listed in rows (i.e., the headings are in a column and the values associated with each heading extend across the same row), you should transpose the data in Excel before you paste it into Access. If your Excel data is listed in columns that are in a different order from the columns in the datasheet, you should rearrange the data before you paste it into Access.

Other ways to use Excel data in Access

This article discusses copying data from Excel and pasting it into an existing Access datasheet. There are two other main ways to use Excel data in Access that you should consider:

  • If you want to bring Excel data into Access but plan to keep updating the data in Excel and want to keep the Access data synchronized, you should link to the Excel data from Access.
  • If you do not yet have a datasheet in Access to store the Excel data, you should import the data from Excel into Access.

For more information about these other methods, see the article Import or link to data in an Excel workbook.

In this article


Transpose data from rows into columns

If your data is listed in rows, with headings in a column, it won’t paste into an Access datasheet correctly unless you first rearrange it into columns in Excel. This process is called transposing. You can transpose data in Excel by copying the data and pasting it into another cell that is outside of the copied range. You select the transpose option when you paste.

If your rows are in the same order as the Access columns (even if there are rows between that you don’t want to copy) you can copy all the rows and then transpose them in one paste operation. If the rows are not in the same order, copy the rows one at a time, and then paste and transpose them in the correct order.

  1. Select the data, and then press CTRL+C.

 Tip    To select multiple non-adjacent groups of cells, press and hold the CTRL key while you select.

  1. Right-click a cell that is outside your selected data (for example, a cell below the lowest row of your selection), and under Paste Options, click the Transpose button:
    Paste Transpose

Top of Page Top of Page

Rearrange the data in Excel to match the order of the columns in the Access datasheet

If your columns are not ordered the same way in Excel and Access, use the following procedure to rearrange the columns in Excel before you copy and paste the data into Access.

  1. Open the workbook that contains the data you want to paste into Access.
  2. Insert a new worksheet. For more information, see the Excel article Insert or delete a worksheet.
  3. In the first row of new worksheet, enter the Access column names, one per column.
  4. Copy the data for the first Access column into column A:
  1. Click the tab of the worksheet where the names or codes are listed.
  2. Select the list of names or codes and then press CTRL+C
  3. Click the tab of the new worksheet.
  4. Select cell A2, and then press CTRL+V.
  1. Copy the data for the second Access column into column B:
  1. Copy the data for the next Access column into the next column in Excel:
  1. Repeat step 6 until you have copied all the data onto the new worksheet.

After the data is ordered correctly, copy and paste it into Access.

Top of Page Top of Page

Copy data from Excel and paste it into Access

If the Excel data is arranged in rows, transpose the data into columns before you copy it. If the Excel data is in columns that are not in the same order as the Access columns, rearrange the data in Excel before you copy the data and paste it into Access.

The method to copy data from Excel and paste it into Access depends on whether there are fewer columns of data in Excel than in Access. If there are the same number of columns in Excel, you paste into Access by using the asterisk (*) row selector. If there are fewer columns in Excel, you select the target columns in Access and then paste.

 Note    If there are more columns in Excel than in Access and you need to paste the data from those columns, you must first add a column to the Access datasheet – i.e., add a new field to the table. For more information, see the Access article Add a field to a table.

Copy and paste data into the same number of columns

 Important    Some Access datasheets have a column named ID that is automatically assigned a value when a record is added. The column displays (New) until data is added to another column in the row, at which point the value of ID is automatically assigned. If your Access datasheet has this kind of column, add an empty column in front of the first column of data in Excel and include the empty cells when you select the data to copy.

  1. In Excel, select the data, and then press CTRL+C.

 Tip    To select multiple non-adjacent groups of cells, press and hold the CTRL key while you select.

  1. In Access, right-click the asterisk on the datasheet, and then click Paste.
    Shortcut menu of new record on Access datasheet

Copy data from fewer columns and paste it into part of a datasheet

  1. In Excel, select the data, and then press CTRL+C.

     Tip    To select multiple non-adjacent groups of cells, press and hold the CTRL key while you select.

  2. In Access, in the new row of the datasheet, select the columns in which you want to paste, and then press CTRL+V.

Top of Page Top of Page