Each column in the list is a separate field. You should enter the field names in the first row of the area of your spreadsheet that will become the list. Each row of the range contains a database record. Enter data in the rows directly below the field names. In the example below, the field names are in row 3. The first record starts in row 4 and contains the data for Amanda Murphy. Note that Column A has a field header of LAST NAME and contains last name information for all the records in the database.

When creating your list in Excel, keep the following database design principles in mind:

Prior to using your list, Excel must determine the area in which the data is actually contained. Excel uses the following approach: it decides the range of the list by what a user has selected. If a single cell is selected, the region is defined as an area bounded by blank rows and columns. If multiple cells have been selected, Excel assumes that the selection is the intended list.

A data form is a dialog box which displays a single record with up to 32 of its data fields, and allows you to browse or edit that record. You can also add or delete records from your list using these forms. To use a data form, choose the Form command from the Data menu and Excel will display a form similar to the one at the right.

If you are using the Criteria option in the data form dialog box, you may search for numbers, text, and even spaces and special characters. To use this option, choose Criteria from the data form dialog box, type in your desired search information, and select Find Next . If you wish to conduct more than one search, when you select Criteria again, choose Clear in the dialog box and type in the new search information.

If you wish to sort based on a single field you must first select a cell in the column you wish to sort by. For example, if you wanted to sort by Location to group everyone from Cambridge, Montreal, and New York together, you would first select a cell in that column.

Then, you would choose one of the two sort tools on the toolbar. The tool with the A-Z arrow will sort in ascending order (i.e. from a to z, 1 to 100, or Jan. to Dec.) while the tool with the Z-A arrow will sort in descending order (i.e. from z to a, 100 to 1, or Dec. to Jan.).

If you don't like the results of a sort, select Undo from the Edit menu or the undo tool button immediately after the sort. Suggestion: A precaution to take if you might want to restore your list to its original order is to number your records. To do this, insert a column before Column A, give it a label heading (i.e. Rec No.) and then sequentially number each of your records. You could then sort the list by this column to return to the original order.

Sometimes you may want to sort based on several columns or fields. For example, if you were sorting based on the Last Name field you might end up with several people with the same last name. In this case, you would want to perform a second level sort based on the First Name field. To perform multi-level sorts, choose Sort from the Data menu, and the Sort dialog box will appear. In the Sort dialog box, you have three sort fields, "Sort By," "Then By," and "Then By." For each sort field, select the column name by which you want to sort and choose Ascending or Descending . Once you have defined your sort fields, choose OK , and Excel will sort your data.

If you wish to apply a filter to your entire list, start by selecting a single cell within that list. If you want to apply the filter only to selected columns, first select the labels for those columns. To turn on the filter, select Filter from the Data menu, and choose AutoFilter from the sidebar menu. Once you choose AutoFilter , Excel will convert the column names to a series of dropdown lists. These lists allow you to select from the first 251 unique field values in that column to filter out the data. One option in each list is Custom .

Sometimes you may wish to filter items that meet certain conditions (e.g. you may only want a list of employees making over $28, 550). In these cases, choose Custom from the list box. A dialog box will appear containing a drop down menu indicating your criteria options (e.g. "is greater than," "equals," "is less than," etc.). Then, type in the criteria to be matched. Choose Top 10 if you want the top ten (or any number, for that matter) or bottom ten values displayed in ascending order.

Once you've got your list filtered, here are some things you might consider doing with it:

To remove filter criteria from a single column, select All from that column's drop-down list box. If you wish to show all hidden rows in your list, then select Filter from the Data menu and then choose Show All . To totally turn off filtering and remove the AutoFilter field arrows, choose Filter from the Data menu, and then select AutoFilter to clear the command.

Before you begin the process of adding subtotals to your list, you must first prepare your list. You should arrange the rows so that the items you wish to subtotal are grouped together. The easiest way to do this is to use the Sort command in the Data menu.

If you wish to generate subtotals within your entire list, select a single cell in the list. Then select Subtotals from the Data menu. The Subtotal dialog box will appear and provide options for identifying the items you want subtotaled. The At Each Change In field allows you to choose the field where the subtotal will be inserted. The Use Function field allows you to determine what summary function you'd like used in subtotaling your subset of data. These summary functions include Sum, Count, Average, Max, and Min. The Add Subtotal To field allows you to identify the column in which the subtotal will appear.

You can have multiple layers of subtotals or subtotals for smaller groups within your original subtotal groups. To create nested subtotals, choose Subtotals from the Data menu, fill in the appropriate fields in the dialog box and choose OK . Then choose the Subtotals command again, select a different label for the At Each Change In field, fill in the other fields and clear the Replace Current Subtotals checkbox.

You may use more than one summary function to calculate data in the same column (perhaps you want a sum and an average of the same data). To do this, choose the Subtotals command from the Data menu, select one function in the Use Function field, and choose OK . Then select Subtotals again and select another function in the Use Function field and clear the Replace Current Subtotals check box.

To replace existing subtotals with new ones, choose the Subtotals command, select the Replace Current Subtotals checkbox, and then choose OK . To remove all subtotals from your list, select the Remove All button from the Subtotal dialog box.

To create a PivotTable you must first select PivotTable and Pivot Chart Report from the Data menu. The PivotTable Wizard will step you through the creation process. There are three major steps in setting up a PivotTable.

     Step 3.

Next, Excel will ask you to select the destination of the PivotTable and make decisions about layout and display options. If you select "New Worksheet," Excel will insert a new sheet in the current workbook and place the PivotTable in this new sheet. If you select "Existing Worksheet," then you must designate where to place the pivot table. At this point, you may choose the layout and display options for the pivot table. If you choose to bypass these options, select "Next" and a pivot table will appear in your workbook. Here you may drag items from the pivot table field list onto the pivot table.

Note that there are four types of field types, row, column, page, and data. Row and Column fields define headings of rows and columns. Page fields filter your view of data in the table by breaking a PivotTable into pages so that you can view one item at a time. Finally, Data fields are the fields which contain the data to be summarized in the table.

It is extremely easy to change the layout of the PivotTable. The easiest way is to drag a field button from one location to another on the table to change the table's organization. You can also use the PivotTable Field option described below to change a field's orientation. If you wish to delete a column, row, or page field, simply drag it outside the PivotTable area to remove it.

There are several ways to modify an existing PivotTable. You can change layout, field options, refresh your table should data in the original list be changed, group or ungroup items in fields, and display table information on different sheets. The Pivot toolbar (which appears automatically when you create a PivotTable) will be most helpful in performing these commands.

If, in step 3 of the wizard, you decide to choose the layout of the pivot table, select Layout in the lower left corner of the dialog box.

The Wizard will display all of the field names from the data source in the form of buttons. Use your mouse to drag field buttons to various locations on the table.

You can choose the PivotTable Field tool from the Pivot toolbar or you can double-click a field button directly in the table if you wish to customize a specific field. Excel will then display a dialog box which allows you to customize the following options:

If you change data in the source list or table, in order for this change to be reflected in the PivotTable, you will need to update the PivotTable. To do this, select Refresh Data from the Data menu or the ! tool on the Query and Pivot toolbar.

The tools can be used to either hide or display detail summary data for a group field, or you could double-click on the group item.

This tool allows you to change the formatting of your pivot table report:

This is the chart wizard tool which allows you to represent your pivot table as a chart:

You can access the grouping and ungrouping tool by selecting the down arrow on the pivot table toolbar. These tools allow you to group or ungroup items within a field. The left pointing arrow is for ungrouping, and the right is for grouping. For example, if you had several date entries in the month of January, you could group them collectively in a specified group field.