Converting an Excel Spreadsheet to an Access 2010 Database

01
of 09

Prepare Your Data

Sample Excel Database. Mike Chapple

After sending out your holiday cards last year, did you make yourself a promise that you would organize your address list to make the process easier next year? Do you have a huge Excel spreadsheet that you can't make heads or tails of? Maybe your address book looks something like the one shown in the file below. Or, perhaps, you keep your address book on (gasp!) scraps of paper.

It's time to make good on that promise to yourself -- organize your contact list into a Microsoft Access database. It's much easier than you may imagine and you'll definitely be pleased with the results. This tutorial will walk you through the entire process step-by-step.

If you don't have your own spreadsheet and want to follow along with the tutorial, you can download the sample Excel file used to generate the tutorial.

Note: This tutorial is for Access 2010. If you are using Access 2010, read Converting an Excel Spreadsheet to an Access 2007 Database. If you are using Access 2013, read Converting Excel to an Access 2013 Database.

02
of 09

Create a New Access 2010 Database

Unless you have an existing database that you use to store contact information, you're probably going to want to create a new database from scratch. To do this, click on the Blank Database icon on the Getting Started with Microsoft Office Access screen. You'll be presented with the screen above. Provide your database with a name, click the Create button and you'll be in business.
03
of 09

Begin the Excel Import Process

Mike Chapple
Next, click the External Data tab at the top of the Access screen and double-click the Excel button to begin the Excel import process. The position of this button is indicated by the red arrow in the image above.
04
of 09

Select the Source and Destination

Mike Chapple
Next, you'll be presented with the screen shown above. Click the Browse button and navigate to the file you'd like to import. Once you've located the correct file, click the Open button.

On the bottom half of the screen, you're presented with import destination options. In this tutorial, we're interested in converting an existing Excel spreadsheet to a new Access database, so we'll choose "Import the source data into a new table in the current database."

Other options on this screen allow you to:
  • Link your database to an Excel sheet so that changes in the source sheet are reflected in the database
  • Import data into an existing database table
Once you've selected the correct file and option, click the OK button to continue.
05
of 09

Select Column Headings

Mike Chapple
Often, Microsoft Excel users utilize the first row of their spreadsheet to provide column names for their data. In our example file, we did this to identify the Last Name, First Name, Address, etc. columns. In the window shown above, ensure that the "First Row Contains Column Headings" box is checked. This will instruct Access to treat the first row as names, rather than actual data to be stored in the list of contacts. Click the Next button to continue.
06
of 09

Create Any Desired Indexes

Mike Chapple
Database indexes are an internal mechanism that can be used to increase the speed at which Access can find information in your database. You can apply an index to one or more of your database columns at this step. Simply click the "Indexed" pull-down menu and select the appropriate option.

Keep in mind that indexes create a lot of overhead for your database and will increase the amount of disk space used. For this reason, you want to keep indexed columns to a minimum. In our database, we'll most often be searching on the Last Name of our contacts, so let's create an index on this field. We might have friends with the same last name, so we do want to allow duplicates here. Ensure that the Last Name column is selected in the bottom potion of the windows and then select "Yes (Duplicates OK)" from the Indexed pull-down menu. Click Next to continue.
07
of 09

Select a Primary Key

Mike Chapple

The primary key is used to uniquely identify records in a database. The easiest way to do this is to let Access generate a primary key for you. Select the "Let Access add primary key" option and press Next to continue. If you're interested in choosing your own primary key, you might want to read our article on database keys.

08
of 09

Name Your Table

Mike Chapple
You need to provide Access with a name to reference your table. We'll call our table "Contacts." Enter this into the appropriate field and click on the Finish button.
09
of 09

View Your Data

You'll see an intermediate screen asking you if you'd like to save the steps used to import your data. If not, go ahead and click the Close button.

You'll then be returned to the main database screen where you can view your data by simply double-clicking on the table name in the left panel. Congratulations, you've successfully imported your data from Excel into Access!
Format
mla apa chicago
Your Citation
Chapple, Mike. "Converting an Excel Spreadsheet to an Access 2010 Database." ThoughtCo, Mar. 4, 2017, thoughtco.com/convert-excel-spreadsheet-access-2010-database-1019966. Chapple, Mike. (2017, March 4). Converting an Excel Spreadsheet to an Access 2010 Database. Retrieved from https://www.thoughtco.com/convert-excel-spreadsheet-access-2010-database-1019966 Chapple, Mike. "Converting an Excel Spreadsheet to an Access 2010 Database." ThoughtCo. https://www.thoughtco.com/convert-excel-spreadsheet-access-2010-database-1019966 (accessed May 27, 2018).