This wikiHow teaches you how to create a database using data from Microsoft Excel spreadsheets by importing it directly into Access, Microsoft's database management program. You can also export Excel data to a format that database programs can open. Microsoft Access is a program from the Microsoft Office suite of programs and is only available for Windows computers.
Step
Method 1 of 2: Using Microsoft Access
Step 1. Open Microsoft Access
This program is marked by a red icon with the letter “ A After that, the Access template page will open.
Access is designed to work with Excel and is included with Excel on Microsoft Office Professional plans, and is available for Windows computers only
Step 2. Click Blank database
It's in the upper-left corner of the program window.
If you want to use a different template for your Access database, select the desired template
Step 3. Click Create when prompted
It's in the lower-right corner of the pop-up window. After that, the Access database will be opened.
Step 4. Click the External Data tab
This tab is in the menu bar at the top of the Access window.
Step 5. Click New Data Source
It's at the far left of the toolbar " External Data " After that, a drop-down menu will be displayed.
Step 6. Select Files
This option is in the drop-down menu. After that, a pop-out menu will be displayed.
Step 7. Click Excel
It's in the pop-out menu. After that, the import window will open.
Step 8. Click Browse
It's in the upper-right corner of the window.
Step 9. Select an Excel spreadsheet
Go to the Excel spreadsheet storage folder, and click the spreadsheet you want to open.
Step 10. Click Open
It's in the lower-right corner of the window.
Step 11. Determine the data transfer method
Click the radio button to the left of one of the following options:
- “ Import the source data into a new table in the current database ” – Select this option if you are creating a new database without tables or if you want to add a new table to an existing database. By creating a new table, you can edit the information through Access.
- “ Append a copy of the records to the table ” – Select this option if you are using an existing database and want to add data to one of the tables in the database. By adding an existing table, you can edit the information through Access.
- “ Link to the data source by creating a linked table ” – Select this option to create a link in the database which will open the database in Excel. With this option, you cannot edit information through Access.
Step 12. Click OK
It's at the bottom of the window.
Step 13. Select a worksheet
At the top of the window, click the name of the worksheet you want to import from the selected Excel document.
- By default, Excel creates a worksheet with three worksheets labeled "Sheet 1", "Sheet 2", and "Sheet 3". You can only submit one worksheet for one process. If you store information on all three sheets, you will need to complete the transfer process of the first sheet, then return to the " External Data " tab and repeat all the steps for the other sheets.
- You can delete, add, and edit sheet names in Excel, and any changes you make are displayed in the Access database.
Step 14. Click Next
It's in the lower-right corner of the window.
Step 15. Enable column headings
Click the "First Row Contains Column Headings" box if the Excel sheet has its own column headings in the top row (e.g. row “ A ”).
Uncheck the box if you want Access to create column headings
Step 16. Click Next
Step 17. Edit the spreadsheet columns and cells if necessary
If you want to import all cells from a spreadsheet without changes, skip this step:
- To edit a cell, click the column heading that you want to change, then edit the cell name, data type, and/or whether the cell is indexed or not.
- If you don't want to import cells, check the "Do Not Import Field (Skip)" box.
Step 18. Click the Next button
Step 19. Set the primary key for the database
For best results, leave the default settings as they are (to have Access assign its own keys).
You can set the key yourself by checking the " Choose my own primary key " box and entering the key in the field next to the option. Although not recommended, you can also choose " No primary key"
Step 20. Click Next
Step 21. Add a name
Type the name of the spreadsheet in the "Import to Table" field.
Skip this step if you want to display the database with its default name
Step 22. Click Finish
It's in the lower-right corner of the window.
Step 23. Click Close
It's in the lower-right corner of the window. After that, the import window will close and the database will be created.
You can check the " Save import steps " box first to make sure the program remembers the settings set for this database
Method 2 of 2: Using a Third Party Database Program
Step 1. Open the Excel document
Double-click the Excel document you want to convert into a database.
If you haven't created a document, open Excel, click " Blank workbook ”, and create a document before moving on to the next step.
Step 2. Click File
It's in the menu bar at the top of the Excel window (Windows) or at the top of the screen (Mac).
Step 3. Click Save As
This option is in the menu File ”.
Step 4. Double-click This PC
It's in the middle of the page.
Skip this step for Mac users
Step 5. Select the file format
Click the " Save as type " (Windows) or " File Format " (Mac) drop-down box, then choose one of the following options:
- If you are using a computer-based database application, click Format “ . CSV ” (comma separated values).
-
If you are using a web-based database application, click Format “ . XML ”.
If the Excel document does not have XML data, you cannot select the XML format
Step 6. Click Save
It's at the bottom of the window. After that, the document will be saved with the preferences you set.
Step 7. Create a new database on the database program used
The process will be different, depending on the program used. However, usually you need to open the program, click “ New " (or " File ” > “ New ”), and follow the prompts displayed on the screen.
Step 8. Look for the Import… button
This button is usually displayed after clicking on the menu “ File ”, but the database program used may have its own differences.
Step 9. Select the Excel file
Find and double-click the file that you exported from Excel.
Step 10. Follow the prompts from the database application to import the data
Step 11. Save the database
Usually, you can open the " Save " menu by pressing Ctrl+S (Windows) or Command+S (Mac).
