Excel as a Database

Using Excel as a Database
Excel's secret identity is as a database. Remember, any information that can be stored in rows and columns can fit into a spreadsheet. Building a database in a spreadsheet is another way of applying this row-column logic.

1. Create a List of Data (Database)	2. Sort and Filter the List
3. Search the List	4. Use the Data Form

Create a List of Data (Database)

Step 1: Understand an Excel List

Excel's database function is described as a list as opposed to database, since Access is Microsoft's relational database. It can be as simple as a grocery list, but most often its information is organized into rows and columns-a table. Each column contains a certain type of information: last name, address, phone number, or some other data. The rows contain the records in the database. Excel instantly recognizes a list in a worksheet and offers several functions for dealing with the list. First, you must create the list.

Step 2: Start a New Worksheet

Press Ctrl+N or click the New button on the toolbar to start a new worksheet in Excel. You could also set up a list in an existing worksheet. Make sure the list is separate from other information in the worksheet.

Step 3: Enter Column Labels and Data

The list must start with column labels (which are called "fields") in the first row. These fields not only provide a heading for the items in that row, but Excel uses them when you search, sort, or filter the list. Once you've labeled your columns, type information into each row in the list. The contents of each column should match the column title. When you get to the end of a row, press Enter to move to the next one. Any formatting in one row is copied to the next row as you type.

Step 4: Save the List

Save the list when you're done entering information. Open the File menu, choose Save As, and use the Save As dialog box to save the list just as you would any other worksheet in Excel.

Step 5: Add a Record

To add a record to the end of the list, press Ctrl+End to select the last cell in the list. Click in the first column of the next row and type in the new record. To insert a record into the middle of the table, select the row just below where you want the new record inserted; then open the Insert menu and choose Rows. Finally, type the information into the row, being careful not to leave any blank cells.

Step 6: Remove a Record

To remove a record from your list, begin by selecting the row that contains the record. Open the Edit menu and choose Delete to open the Delete dialog box; choose Shift Cells Up and click OK.

Sort and Filter the List

Step 1: Sort the List

Select the column you want to sort by clicking the column heading or selecting a single cell in that column. Then open the Data menu and select Sort to view the Sort dialog box. You can sort by as many as three criteria; enter the criteria by selecting from the drop-down lists. (Information in these lists is gathered from the table's headings in the worksheet.) Select either Ascending (A to Z; smallest to largest) or Descending (Z to A; largest to smallest). Click OK; the list is sorted to your specifications.

Step 2: Filter the List

Filtering lets you see only part of the list based on criteria you select. To filter, create a list in your document, open the Data menu, choose Filter, and select AutoFilter. Each heading in your list now has a drop-down list summarizing the entries that appear in that column. Select an entry in a column's drop-down list to display only those records whose entries in that column match the one you selected. (For example, select Dem to view a list of Democratic presidents.) To refine your list, choose more columns to sift through, such as Democratic presidents who served more than one term in office.

Step 3: Create a Custom Filter

When you choose Custom from a column's drop-down list, a Custom AutoFilter dialog box appears. You can use it to select specific items from that column. For example, you could choose Begins With and then a letter or number.

Step 4: Find the Top 10

Choose Top 10 from a column's drop-down list to open the Top 10 AutoFilter dialog box, where you display the top (or bottom) 10 values in the column. For example, you can find the 10 biggest numbers in a table. (To use this feature, you must choose a column with values, not text.) If you need to view the top 5 (or 3 or 21) values instead of the top 10 values, increase or decrease the value in the spin box in the middle of the dialog box. In addition to being able to view a specific number of items in a list, you can select to view the top percentage of items in the list-just use the drop-down list on the right side of the dialog box.

Step 5: Un-Filter

To clear the filtering done by the various AutoFormat headers, choose All from the column's drop-down list. Switch off the AutoFilter feature by opening the Data menu, choosing Filter, and selecting AutoFilter. This removes the check mark next to the AutoFilter entry in the menu, returning your list to normal operation.

Search the List

Step 1: Open the Find Dialog Box

Click the Edit menu and choose Find to open the Find dialog box. This dialog box is your headquarters for locating any lost tidbit of information not just in a list, but in any worksheet. Type the text or value you want to locate in the Find What box and then specify whether Excel should search by rows or columns. Tell Excel what part of the cell it should look in, be it the cell's formula, value, or comments. If the item you're looking for is case sensitive, check Match Case. Release the hounds by clicking Find Next.

Step 2: Locate the Cell

The cell containing the information you searched for is located and selected. If the information can't be found, Excel tells you so. Click OK and try again. (You might need to modify your entry; check its spelling.)

Step 3: Search and Replace

Clicking the Replace button in the Find dialog box magically transforms it into the Replace dialog box, which can be used to search for and replace information. Begin by entering the information you want to find in the Find What field; then type the text or value that you want to replace that information in the Replace With field.

Step 4: Click Find Next

When the text is found, click Find Next to find the next occurrence, click Replace to replace it, or click Replace All to replace every instance of the text. Click the Close button to return to your worksheet.

Use the Data Form

Step 1: Choose Data, Form

Excel examines your worksheet for a list; if there's one found, it displays a data form containing items in the list. (If there's no list found, Excel displays a warning message.) Note that the column headings from the list are displayed along the left side of the dialog box. Use the Find Prev and Find Next buttons to scan additional records in the list.

Step 2: Enter a New Record

Click the New button to add a new record to the list. The data form goes blank; fill it in. Press Enter to add the record.

Step 3: Edit a Record

Edit a record by changing the information displayed in the data form's dialog box. Press the Enter key after making changes.

Step 4: Delete a Record

Locate the record you want to delete, highlight the information, and then click the Delete button. Excel asks if you're sure-click OK to delete or Cancel to keep the record.

Step 5: Search the List

The data form can be used to locate specific records in the list. Begin by clicking the Criteria button and then fill in one (or more) of the fields with information you want to match-for example, a city, street, name, or phone number. (Click the Clear button to clear out old information or to start over.) Click Form to return to the main data form; note that only those records matching the criteria you entered are displayed. (If no matches are found, the Find Prev and Find Next buttons beep when you click them.) Click the Close button when you're done using the data form; any changes you've made are reflected on the list.