How to Create a Database from an Excel Spreadsheet

This is an article on how to create a database with data from Microsoft Excel spreadsheets by importing data directly into Access – Microsoft’s database management software, or by exporting Excel data to Microsoft Excel. The format is suitable for most database software. Microsoft Access is a program in the Microsoft Office suite and is available only on Windows.

Table of Contents

Using Microsoft Access

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/6/67/Create-a-Database-from-an-Excel-Spreadsheet-Step-1-Version-5.jpg/v4- 460px-Create-a-Database-from-an-Excel-Spreadsheet-Step-1-Version-5.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/6/67/ Create-a-Database-from-an-Excel-Spreadsheet-Step-1-Version-5.jpg/v4-728px-Create-a-Database-from-an-Excel-Spreadsheet-Step-1-Version-5. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Open Microsoft Access. It’s a red app with an A icon. This opens the Access form page.

Access is designed to be used with Excel and is integrated with Excel in the Windows-only Microsoft Office Professional suite.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/2/22/Create-a-Database-from-an-Excel-Spreadsheet-Step-2-Version-5.jpg/v4- 460px-Create-a-Database-from-an-Excel-Spreadsheet-Step-2-Version-5.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/2/22/ Create-a-Database-from-an-Excel-Spreadsheet-Step-2-Version-5.jpg/v4-728px-Create-a-Database-from-an-Excel-Spreadsheet-Step-2-Version-5. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Click Blank database . This is the option in the upper left corner of the window.

If you want to use a different form to create an Access database, feel free to choose the form you like.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/2/2f/Create-a-Database-from-an-Excel-Spreadsheet-Step-3-Version-5.jpg/v4- 460px-Create-a-Database-from-an-Excel-Spreadsheet-Step-3-Version-5.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/2/2f/ Create-a-Database-from-an-Excel-Spreadsheet-Step-3-Version-5.jpg/v4-728px-Create-a-Database-from-an-Excel-Spreadsheet-Step-3-Version-5. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Click Create when asked. This option is in the lower right corner of the currently displayed window. The Access database opens after this operation.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/6/65/Create-a-Database-from-an-Excel-Spreadsheet-Step-4-Version-5.jpg/v4- 460px-Create-a-Database-from-an-Excel-Spreadsheet-Step-4-Version-5.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/6/65/ Create-a-Database-from-an-Excel-Spreadsheet-Step-4-Version-5.jpg/v4-728px-Create-a-Database-from-an-Excel-Spreadsheet-Step-4-Version-5. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Click the External Data tab. This is the tab in the menu bar at the top of the Access window.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/6/67/Create-a-Database-from-an-Excel-Spreadsheet-Step-5-Version-5.jpg/v4- 460px-Create-a-Database-from-an-Excel-Spreadsheet-Step-5-Version-5.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/6/67/ Create-a-Database-from-an-Excel-Spreadsheet-Step-5-Version-5.jpg/v4-728px-Create-a-Database-from-an-Excel-Spreadsheet-Step-5-Version-5. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Click Saved Imports . You will find this option in the leftmost part of the External Data toolbar. An instant menu is displayed here.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/8/89/Create-a-Database-from-an-Excel-Spreadsheet-Step-6-Version-5.jpg/v4- 460px-Create-a-Database-from-an-Excel-Spreadsheet-Step-6-Version-5.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/8/89/ Create-a-Database-from-an-Excel-Spreadsheet-Step-6-Version-5.jpg/v4-728px-Create-a-Database-from-an-Excel-Spreadsheet-Step-6-Version-5. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Select File . This is the option in the currently displayed menu. When you click this, another menu appears.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/d/d0/Create-a-Database-from-an-Excel-Spreadsheet-Step-7-Version-5.jpg/v4- 460px-Create-a-Database-from-an-Excel-Spreadsheet-Step-7-Version-5.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/d/d0/ Create-a-Database-from-an-Excel-Spreadsheet-Step-7-Version-5.jpg/v4-728px-Create-a-Database-from-an-Excel-Spreadsheet-Step-7-Version-5. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Click Excel . This option is available in the menu just displayed. The data entry window will open after the click.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/d/d1/Create-a-Database-from-an-Excel-Spreadsheet-Step-8-Version-5.jpg/v4- 460px-Create-a-Database-from-an-Excel-Spreadsheet-Step-8-Version-5.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/d/d1/ Create-a-Database-from-an-Excel-Spreadsheet-Step-8-Version-5.jpg/v4-728px-Create-a-Database-from-an-Excel-Spreadsheet-Step-8-Version-5. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Click Browse in the upper-right corner of the window.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/3/3e/Create-a-Database-from-an-Excel-Spreadsheet-Step-9-Version-5.jpg/v4- 460px-Create-a-Database-from-an-Excel-Spreadsheet-Step-9-Version-5.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/3/3e/ Create-a-Database-from-an-Excel-Spreadsheet-Step-9-Version-5.jpg/v4-728px-Create-a-Database-from-an-Excel-Spreadsheet-Step-9-Version-5. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Select the Excel spreadsheet. Go to the folder where your Excel spreadsheet is saved, and then click the Excel spreadsheet that you want to open.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/8/89/Create-a-Database-from-an-Excel-Spreadsheet-Step-10-Version-5.jpg/v4- 460px-Create-a-Database-from-an-Excel-Spreadsheet-Step-10-Version-5.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/8/89/ Create-a-Database-from-an-Excel-Spreadsheet-Step-10-Version-5.jpg/v4-728px-Create-a-Database-from-an-Excel-Spreadsheet-Step-10-Version-5. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Click Open . This is the option in the lower right corner of the window.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/4/43/Create-a-Database-from-an-Excel-Spreadsheet-Step-11-Version-4.jpg/v4- 460px-Create-a-Database-from-an-Excel-Spreadsheet-Step-11-Version-4.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/4/43/ Create-a-Database-from-an-Excel-Spreadsheet-Step-11-Version-4.jpg/v4-728px-Create-a-Database-from-an-Excel-Spreadsheet-Step-11-Version-4. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Select the data transfer method. Click the circle button to the left of one of the following options:

Import the source data into a new table in the current database – Click this option if you have created a new database without tables or if you want Add new table to existing database. By creating a new table, you can edit the information in Access.
Append a copy of the records to the table – Click this option if you use an existing database and want to add data to a table in the database. By adding existing tables, you can edit the information in Access.
Link to the data source by creating a linked table – Click this option to create the path in the database and you can open the Excel database in Excel. With this method, you cannot edit the information in Access.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/b/bc/Create-a-Database-from-an-Excel-Spreadsheet-Step-12-Version-5.jpg/v4- 460px-Create-a-Database-from-an-Excel-Spreadsheet-Step-12-Version-5.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/b/bc/ Create-a-Database-from-an-Excel-Spreadsheet-Step-12-Version-5.jpg/v4-728px-Create-a-Database-from-an-Excel-Spreadsheet-Step-12-Version-5. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Click OK . This button is displayed at the bottom of the window.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/c/c1/Add-Grid-Lines-to-Your-Excel-Spreadsheet-Step-3.jpg/v4-460px-Add- Grid-Lines-to-Your-Excel-Spreadsheet-Step-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/c/c1/Add-Grid-Lines-to- Your-Excel-Spreadsheet-Step-3.jpg/v4-728px-Add-Grid-Lines-to-Your-Excel-Spreadsheet-Step-3.jpg”,”smallWidth”:460,”smallHeight”:345,” bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Select sheet. In the upper part of the window, you will click on the name of the spreadsheet to import from the selected Excel document.

By default, each Excel file has three worksheets named “Sheet 1”, “Sheet 2” and “Sheet 3”. You can only import worksheets one at a time; if all three worksheets have information, you will import one first, then return to the “External Data” tab and repeat all the same steps to import the remaining worksheet.
You can delete, add, and edit the names of these worksheets in Excel, and any changes you make are visible in the Access database.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/1/1d/Create-a-Database-from-an-Excel-Spreadsheet-Step-14-Version-5.jpg/v4- 460px-Create-a-Database-from-an-Excel-Spreadsheet-Step-14-Version-5.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/1/1d/ Create-a-Database-from-an-Excel-Spreadsheet-Step-14-Version-5.jpg/v4-728px-Create-a-Database-from-an-Excel-Spreadsheet-Step-14-Version-5. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Click Next (Continue). This is the button in the bottom right corner of the window.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/6/6a/Create-a-Database-from-an-Excel-Spreadsheet-Step-15-Version-4.jpg/v4- 460px-Create-a-Database-from-an-Excel-Spreadsheet-Step-15-Version-4.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/6/6a/ Create-a-Database-from-an-Excel-Spreadsheet-Step-15-Version-4.jpg/v4-728px-Create-a-Database-from-an-Excel-Spreadsheet-Step-15-Version-4. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Enable column headers. Check the “First Row Contains Cpumn Headings” box if your Excel spreadsheet has column headers in the first row (such as row A ).

Uncheck this box if you want Access to generate column headers.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/9/90/Create-a-Database-from-an-Excel-Spreadsheet-Step-16-Version-4.jpg/v4- 460px-Create-a-Database-from-an-Excel-Spreadsheet-Step-16-Version-4.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/9/90/ Create-a-Database-from-an-Excel-Spreadsheet-Step-16-Version-4.jpg/v4-728px-Create-a-Database-from-an-Excel-Spreadsheet-Step-16-Version-4. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Click Next .

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/2/23/Create-a-Database-from-an-Excel-Spreadsheet-Step-17-Version-4.jpg/v4- 460px-Create-a-Database-from-an-Excel-Spreadsheet-Step-17-Version-4.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/2/23/ Create-a-Database-from-an-Excel-Spreadsheet-Step-17-Version-4.jpg/v4-728px-Create-a-Database-from-an-Excel-Spreadsheet-Step-17-Version-4. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Edit spreadsheet columns and fields as needed. If you want to import all the fields in the spreadsheet without making any other changes, skip this step:

To edit a field, you would click the column header to be changed, then edit the field name, data type, and/or choose whether or not to add to the table of contents.
If you don’t want to import certain fields, click the “Do Not Import Field (Skip)” box.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/3/3b/Create-a-Database-from-an-Excel-Spreadsheet-Step-18-Version-4.jpg/v4- 460px-Create-a-Database-from-an-Excel-Spreadsheet-Step-18-Version-4.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/3/3b/ Create-a-Database-from-an-Excel-Spreadsheet-Step-18-Version-4.jpg/v4-728px-Create-a-Database-from-an-Excel-Spreadsheet-Step-18-Version-4. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Click Next .

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/4/4b/Create-a-Database-from-an-Excel-Spreadsheet-Step-19-Version-4.jpg/v4- 460px-Create-a-Database-from-an-Excel-Spreadsheet-Step-19-Version-4.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/4/4b/ Create-a-Database-from-an-Excel-Spreadsheet-Step-19-Version-4.jpg/v4-728px-Create-a-Database-from-an-Excel-Spreadsheet-Step-19-Version-4. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Set the primary key for the database. For best results, leave the default settings here and allow Access to set the primary key itself.

You can also set your own key by checking the “Choose my own primary key” box and entering information in the field next to that choice, or you can select “No primary key”. key” (Do not set a primary key), but this option is not recommended.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/5/5c/Create-a-Database-from-an-Excel-Spreadsheet-Step-20-Version-4.jpg/v4- 460px-Create-a-Database-from-an-Excel-Spreadsheet-Step-20-Version-4.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/5/5c/ Create-a-Database-from-an-Excel-Spreadsheet-Step-20-Version-4.jpg/v4-728px-Create-a-Database-from-an-Excel-Spreadsheet-Step-20-Version-4. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Click Next .

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/c/cb/Create-a-Database-from-an-Excel-Spreadsheet-Step-21-Version-4.jpg/v4- 460px-Create-a-Database-from-an-Excel-Spreadsheet-Step-21-Version-4.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/c/cb/ Create-a-Database-from-an-Excel-Spreadsheet-Step-21-Version-4.jpg/v4-728px-Create-a-Database-from-an-Excel-Spreadsheet-Step-21-Version-4. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Add name. Enter a name for the spreadsheet in the “Import to Table” field.

Skip this step if you want to use the default name of the database.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/5/5a/Create-a-Database-from-an-Excel-Spreadsheet-Step-22-Version-4.jpg/v4- 460px-Create-a-Database-from-an-Excel-Spreadsheet-Step-22-Version-4.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/5/5a/ Create-a-Database-from-an-Excel-Spreadsheet-Step-22-Version-4.jpg/v4-728px-Create-a-Database-from-an-Excel-Spreadsheet-Step-22-Version-4. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Click Finish . This is the option in the lower right corner of the window.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/c/ca/Create-a-Database-from-an-Excel-Spreadsheet-Step-23-Version-4.jpg/v4- 460px-Create-a-Database-from-an-Excel-Spreadsheet-Step-23-Version-4.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/c/ca/ Create-a-Database-from-an-Excel-Spreadsheet-Step-23-Version-4.jpg/v4-728px-Create-a-Database-from-an-Excel-Spreadsheet-Step-23-Version-4. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Click Close . This option is in the lower right corner of the window. This closes the data entry window and creates your database.

You can first check the “Save import steps” box if you want to make sure Access remembers the settings for this database.

Using third-party database software

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/2/22/Create-a-Database-from-an-Excel-Spreadsheet-Step-24-Version-3.jpg/v4- 460px-Create-a-Database-from-an-Excel-Spreadsheet-Step-24-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/2/22/ Create-a-Database-from-an-Excel-Spreadsheet-Step-24-Version-3.jpg/v4-728px-Create-a-Database-from-an-Excel-Spreadsheet-Step-24-Version-3. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Open the Excel document. Double-click the Excel document that you want to convert to a database.

If you haven’t created the document yet, open Excel, click Blank workbook , and create the document before continuing.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/8/82/Create-a-Database-from-an-Excel-Spreadsheet-Step-25-Version-2.jpg/v4- 460px-Create-a-Database-from-an-Excel-Spreadsheet-Step-25-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/8/82/ Create-a-Database-from-an-Excel-Spreadsheet-Step-25-Version-2.jpg/v4-728px-Create-a-Database-from-an-Excel-Spreadsheet-Step-25-Version-2. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Click File . This is a choice in the menu bar at the top of the Excel window (on Windows) or at the top of the screen (on Mac).

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/a/af/Create-a-Database-from-an-Excel-Spreadsheet-Step-26-Version-2.jpg/v4- 460px-Create-a-Database-from-an-Excel-Spreadsheet-Step-26-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/a/af/ Create-a-Database-from-an-Excel-Spreadsheet-Step-26-Version-2.jpg/v4-728px-Create-a-Database-from-an-Excel-Spreadsheet-Step-26-Version-2. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Click Save As . You will see this option in the File menu.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/0/0e/Create-a-Database-from-an-Excel-Spreadsheet-Step-27-Version-2.jpg/v4- 460px-Create-a-Database-from-an-Excel-Spreadsheet-Step-27-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/0/0e/ Create-a-Database-from-an-Excel-Spreadsheet-Step-27-Version-2.jpg/v4-728px-Create-a-Database-from-an-Excel-Spreadsheet-Step-27-Version-2. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Double-click This PC . This is the option in the middle of the page.

Skip this step on Mac.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/8/80/Create-a-Database-from-an-Excel-Spreadsheet-Step-28-Version-2.jpg/v4- 460px-Create-a-Database-from-an-Excel-Spreadsheet-Step-28-Version-2.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/8/80/ Create-a-Database-from-an-Excel-Spreadsheet-Step-28-Version-2.jpg/v4-728px-Create-a-Database-from-an-Excel-Spreadsheet-Step-28-Version-2. jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}

Select a file format. Click the “Save as type” checkbox on Windows or “File Format” on Mac, and then choose one of the following options:

If you use a desktop database application, click the .CSV (comma-separated value) format.
If you’re using a web-based database application, you’ll click the .XML format.
- If your Excel document does not have XML data, you cannot select XML.

Advice

You can use online database sites to create databases for free, but you must create an account to use these services.
If you don’t have fully functional database software, you need another program to open the database file on your Windows or Mac computer.

Warning

It is not always possible to smoothly transfer Excel data to the database.

Steps

Using Microsoft Access

Using third-party database software

Advice

Warning

Related Posts