How to Use Excel As a Database
By Allen Bethea
The same tabular format that Excel uses to store numbers and formulas may also be used to build a searchable database holding information for your business or personal use. Excel worksheet columns can function like the fields in a database which describe the properties or attributes of the information you want to store. Each worksheet row represents a record or complete description of an item in the database. Once you create your database, you can add additional items or scroll through your entries using Excel's Form tool.
Populate your Database
Step 1
Open a new, blank worksheet to use as your database.
Step 2
Type a unique name for each field in your database in separate columns. Each worksheet column will contain a bit of information about each item you will store. For example, if you want to create a database of your music with the title, artist name, genre and release year of each CD you own, type "Title" in cell A1, "Artist" in B1, "Genre" in C1 and "Year" in D1. Collectively, the fields represent a database table.
Step 3
Type the data for each item in your database. For example, if you have a 2001 Boston Pops CD called "Best of Broadway," type "Best of Broadway" in a row within the "Titles" column, "Boston Pops" in the same row under "Artist," "Broadway" under "Genre" and then "2001" in the "Year" column. Each row you add to your spreadsheet represents a new database record.
Add New Records Using the Form Tool
Step 1
Click the "Home" button, "Options" and then "Quick Access Toolbar."
Step 2
Select "All Commands" from the "Choose commands from" drop-down list.
Step 3
Click "Form," "Add" and then "OK" to add the Form button to the Quick Access toolbar.
Step 4
Click any occupied cell within your database and then the "Form" button. Excel automatically generates a form containing your database fields and its first record.
Step 5
Add a new item to your database with the form. Click "New," type the data for each field of the new record and then click "Close."
Scroll Through your Database
Step 1
Click any cell within your database and then the "Form" button.
Step 2
Click the "Find Next" buttons to view the next item in your database.
Step 3
Click the "Find Prev" buttons to view previous records.
References
Tips
- Use Excel's keyboard short cuts to search for a specific item in your data or navigate through it. The Find function can help you locate an item in your database. For example, if you are looking for a CD by a specific artist, press "Ctrl-F," type the artist's name in the input box labeled "Find what," and then press "Enter." To add a new item to your database, press "Ctrl-End" and then "Enter" to move your cursor to the next empty row in your worksheet database.
Warnings
- Information in this article applies to Excel 2013. It may vary slightly or significantly with other versions of Excel.
Writer Bio
Allen Bethea has written articles on programming, web design,operating systems and computer hardware since 2002. He holds a Bachelor of Science from UNC-Chapel Hill and AAS degrees in office technology, mechanical engineering/drafting and internet technology. Allen has extensive experience with desktop and system software for both Windows and Linux operating systems.