How to Create a Database from an Excel Spreadsheet

Written by Jack Lloyd

Last Updated: October 21, 2019 Tested

This wikiHow teaches you how to create a database using data from a Microsoft Excel spreadsheet by importing the data directly into Access, which is Microsoft's database management software, or by exporting the Excel data into a format that works with most database software. Microsoft Access is a part of the Microsoft Office software bundle and is only available for Windows.

Method 1

Method 1 of 2:

Using Microsoft Access

Download Article

1
Open Microsoft Access. It's the red app with an A . Doing so opens the Access template page.
- Access is designed for use with Excel and comes bundled with Excel in Microsoft Office Professional and is only available for Windows.
2
Click Blank database . This option is in the upper-left side of the window.
- If you want to use a different template for your Access database, select the template that you want instead.
Advertisement
This option is in the bottom-right corner of the pop-up window. Your Access database will open.
It's in the menu bar at the top of the Access window.
You'll find this in the far-left side of the External Data toolbar. A drop-down menu will appear.
It's in the drop-down menu. Selecting this option prompts a pop-out menu.
This option is in the pop-out menu. Clicking it prompts the import window to open.
It's in the upper-right part of the window.
Go to the folder in which your Excel spreadsheet is located, then click the Excel spreadsheet which you want to open.
It's in the bottom-right corner of the window.
11
Specify how to transfer to the data. Click the radio button to the left of one of the following:
- Import the source data into a new table in the current database - Choose this option if you created a new database with no tables or if you want to add a new table to an existing database. By creating a new table you can edit the information in Access.
- Append a copy of the records to the table - Choose this option if you are using an existing database and want to add the data to one of the tables in the database. By appending an existing table, you can edit the information in Access.
- Link to the data source by creating a linked table - Choose this option to create a hyperlink in the database, which will open the Excel database in Excel. With this method, you cannot edit the information in Access.
You'll find this at the bottom of the window.
13
Select a sheet. At the top of the window, click the name of the sheet that you want to import from your selected Excel document.
- By default, Excel creates workbooks with three spreadsheets labeled "Sheet 1," "Sheet 2," and "Sheet 3." You can only transfer one sheet at a time; if you have information on all three sheets, you must complete the transfer with one sheet and then go back to the "External Data" tab and repeat all the steps for each remaining sheet.
- You can delete, add, and edit the names of these sheets in Excel, and whatever changes you make will appear in the Access database.
It's in the bottom-right corner of the window.
15
Enable column headings. Check the "First Row Contains Column Headings" box if your Excel sheet has its own column headings in the top row (e.g., the A row).
- Uncheck the box if you want Access to create the column headings.
17
Edit your spreadsheet's columns and fields if needed. If you want to import all the fields from the spreadsheet without change, skip this step:
- To edit a field, click the column header you want to change, then edit the name of the field, the data type, and/or whether or not it is indexed.
- If you don't want to import a field, check the "Do Not Import Field (Skip)" box.
19
Set the primary key for the database. For best results, leave the default setting here as-is to let Access set the key.
- You can also set your own key by checking "Choose my own primary key" and entering it in the field next to that option, or you can select "No primary key" (not recommended).
21
Add a name. Type a name for the sheet into the "Import to Table" field.
- Skip this step to leave the database set to its default name.
This option is in the lower-right side of the window.
23
Click Close . It's in the bottom-right corner of the window. This will close the import window and create your database.
- You can first check the "Save import steps" box if you want to ensure that Access will remember your settings for this database.

Method 2

Method 2 of 2:

Using Third-Party Database Software

Download Article

1
Open your Excel document. Double-click the Excel document which you want to convert into a database.
- If you haven't yet created your document, open Excel, click Blank workbook , and create your document before proceeding.
It's in the menu bar that's either at the top of the Excel window (Windows) or at the top of the screen (Mac).
You'll find this option in the File menu.
4
Double-click This PC . It's in the middle of the page.
- Skip this step on a Mac.
5
Select a file format. Click the "Save as type" (Windows) or "File Format" (Mac) drop-down box, then select one of the following:
- If you're using a computer-based database application, click a .CSV (comma separated values) format.
- If you're using a Web-based database application, click an .XML format.
  - If your Excel document doesn't have any XML data in it, you won't be able to choose XML.
It's at the bottom of the window. This will save your document using your selected preferences.
This process will vary depending on the application that you're using, but you'll usually open the application, click New (or File > New ), and follow any on-screen instructions.
It's often found by clicking the File option, but your database application may vary.
Locate and double-click the file you exported from Excel.
You can usually open the "Save" menu by pressing Ctrl + S (Windows) or ⌘ Command + S (Mac).

Community Q&A

Search

Add New Question

Question

What is the difference between a database and a spreadsheet?

wikiHow Staff Editor
Staff Answer
This answer was written by one of our trained team of researchers who validated it for accuracy and comprehensiveness.

wikiHow Staff Editor

Staff Answer

A spreadsheet stores information organized into rows and columns and is usually best used by one person at a time. A database might also have information organized into rows and columns, but it isn't limited to just that and can store information according to a variety of different methodologies. Databases are typically made with multiple simultaneous users in mind and security features. Databases also allow for more complex and time consuming searches or operations and can eliminate some of the redundancy that becomes necessary when using spreadsheets extensively.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 4 Helpful 15
Question

How do I view this page in French?

Community Answer

Copy and paste onto Google Translate; when you copy the translated words onto Excel, it will automatically format it to its original form.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 9 Helpful 13
Question

I've imported data from Excel, but where does it go, and how do I access the information to populate my database?

Community Answer

You can go to "Insert > Pivot Table" and select "Use external connection" and pick the connection you've established. This will allow you to create a pivot table based on that external Access database.

Thanks! We're glad this was helpful.
Thank you for your feedback.
If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
Yes No

Not Helpful 2 Helpful 0

Ask a Question

200 characters left

Include your email address to get a message when this question is answered.

Submit

Tips

There are several free online database websites that you can use to create a database, though you'll have to sign up for an account with most of these services.

Thanks
Helpful 1 Not Helpful 0
If you don't have fully functional database software, you may also need a separate program to open database files on PC or Mac.

Thanks
Helpful 0 Not Helpful 1

Submit a Tip

All tip submissions are carefully reviewed before being published

Name

Please provide your name and last initial

Submit

Thanks for submitting a tip for review!

Warnings

Excel data doesn't always transfer over to a database as neatly as you might hope for.

Thanks
Helpful 0 Not Helpful 0

Log in

How to Create a Database from an Excel Spreadsheet

Steps

Using Microsoft Access

Using Third-Party Database Software

Community Q&A

Tips

Warnings

You Might Also Like

About This Article

Reader Success Stories

Is this article up to date?

Quizzes

You Might Also Like

Featured Articles

Trending Articles

Featured Articles

Featured Articles

Watch Articles

Trending Articles

Quizzes

Explore Categories