|
| | 
| 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. |
|
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. |
|
|
