Part 8. Working with Data
Lists
You can use Excel for more than totaling numbers; Excel can also be used as a simple data management program. Using it, you can keep track of clients, products, orders, expenses, and more. You can set up a data list and use some of Excel's data list features, including sorting, subtotaling, and filtering.
A data list is a set of related information about a particular person, transaction, or event. One piece of information is a field, and one set of fields is called a record. In an Excel data list, you enter the column headings for the fields, and the records in the rows. One way to enter these records is by using a data list form.
Ordering Records with Data
Lists
Entering Worksheet Data
Using a Data List Form
Type a unique heading for each of the columns you want in
your data list. (Excel can get confused if you use the same label in more than
one column header.)
Select all the headers and
format them so they will stand out from the regular listed data (perhaps adding
some cell color and changing the font color).
Type a sample row of the data
you will be inputting with your form. Your columns can include calculated
fields (A x B = C) and numeric styles (Currency).
Select the cells that you want
to use to establish the data list. Include at least one regular row of cells
(with or without data) along with your data list labels.
INTRODUCTION
If you like the regular worksheet style of entering data, you can enter data directly in the cells. Simply select each cell and type an entry, doing so for each record you want in your worksheet. If you prefer to concentrate on one record at a time, however, you can display a data list form onscreen and enter the records in that form (as covered in this task). This will be easier on your eyes and can help to keep you from getting lost in the data. |
TIP
Total sales Notice how the Current Month Sales field on the data list form doesn't allow you to enter information. This is because it is a calculated field (meaning that it contains the answer to Units Sold x Price per Unit = Current Month Sales). To keep from entering invalid data into particular fields, create validation rules (see the following task). |
Open the Data menu and choose Form.
A form, or Range Criteria, dialog box appears displaying the selected cells as
data labels and the row as the first record.
Click the New button; the form changes to display blank
fields, ready for the entry of a new record.
Type the data for a new record
into the data list form boxes, pressing Tab
to move from field to field. Then, click New
to enter the data and clear the form.
Either enter another new
record on the data list form or click the form's Close
button when you finish entering data.
TIP
Record order You don't have to enter records in any particular order. You can later sort or filter your records to display as necessary. See "Sorting Data List Records" and "Filtering Data List Records" later in this part for more information. |
TIP
Correcting typos If you make an error when typing your records and you are still working on that record in the form, you can click the field containing the error and use the Delete or Backspace key to edit your entry. To edit a record in the worksheet, double-click the cell you want to edit, make the change, and press Enter. To learn to modify a data list entry with a form, see "Modifying a Data List Record" later in this part. |
Checking Form Entries with
Data Validation
Click the column header for
the data list field to which you want to apply a data-validation rule (for
example, column C for Region).
Open the Data menu and choose Validation to open
the Data Validation dialog box.
Click the down arrow next to the Allow
field and select Whole Number (sales regions
are whole numbers, never anything else).
Click the down arrow next to the Data
field and select less than or equal to (sales
regions are less than or equal to the total number of regions).
INTRODUCTION
You might find that as you enter records into your data list, you inadvertently make some mistakes. For example, suppose your company has only five sales regions, and you keep entering numbers that can't possibly be an actual sales region, such as 6 or 55. If you set a data validation rule for this scenario, Excel will automatically inform you when you make this type of error. |
Type the number in the Maximum field (in this case, 5 is the maximum number of regions).
Click the OK button to accept your validation rules; in
addition, this will accept Excel's default validation error message (see the
tip "Altering the error message" on this page).
Using the form, type a new
data list record (see the preceding task), but this time type an incorrect
entry, such as 55, in the Region field. When you're finished, click the New button.
Excel selects the cell with
the invalid data and alerts you of your error. Click the dialog box's Retry button and repeat step 7, entering a valid
value in the Region field.
TIP
Altering the error message You can alter Excel's default validation error message in the Error Alert tab of the Data Validation dialog box. Here, you can alter the error alert style, title, and error message. This is convenient when people who aren't familiar with your data list form enter incorrect data, because it enables you to tell them specifically what they need to correct. For example, you could tell them that you only have a total of five regions, and that the number they entered is invalid. |
TIP
Using input messages In addition to validation rules and error messages, you can add input messages directly in your data list. An input message looks like a permanently viewable comment in your data list. For example, you could include an input message that indicates to the person entering data that the Region field only accepts whole values between 1 and 5. That way, the user will think twice before entering an invalid number. |
Searching for a Data List
Record
Unless it's already open on
your desktop, choose Data, Form to open the
data list form for the worksheet data list you already created.
Click the Criteria button.
The form becomes blank,
awaiting your entry of the search criteria. Type the data (last name, for
example) you are searching for, and click the Find
Next button.
Excel displays the first
matching record. If multiple records have the same data, click Find Next to see more records.
INTRODUCTION
Rather than wasting time looking through each row in a worksheet to find the information you want, why not search for the specific record in your data list? You can limit the search to a specific field using the data list form. For example, if you are trying to find information on a particular sales representative, you could search on her last name. |
TIP
Scrolling to search In addition to looking through the data list and using criteria to locate a record on your data list form, you can scroll through the list of records on your form. The problem with this is that the records aren't sorted in any particular order. See the task "Sorting Data List Records" for more information about sorting records. |
Modifying a Data List
Record
Unless it's already open on
your desktop, choose Data, Form to open the
data list form for the worksheet data list you created in the task "Entering Worksheet Data Using a Form."
Follow the steps in the
preceding task to find the record you want to modify (in this example, Michael Wright).
Select the entry in the field
you want to modify, and type over it (in this example, increase the value in
the Units Sold field from 10 to 13).
Click the Close button to accept the change and return to the
worksheet data list; the change will be reflected in your worksheet.
INTRODUCTION
Suppose you've added some records to your data list, but now you need to make some changes to the data. Perhaps you need to adjust the Units Sold field for one of your sales representatives (for example, Michael Wright actually sold 13 units in Region 2, not 10). After you find the record you want to modify (refer to the preceding task), you can modify the record in the data list form. |
TIP
Modifying multiple records If you have more than one record to modify, click the Criteria button in step 4 instead of the Close button to search for the next record you need to modify. |
Deleting a Record from the
Data List
Unless it's already open on
your desktop, choose Data, Form to open the
data list form for the data list you created in the task "Entering Worksheet Data Using a Form."
Follow the steps in the task "Searching for a Data List Record" to find the record you want to delete.
Click the Delete button in the data list form to permanently
remove the record from the data list.
Excel warns you that the
record will be permanently deleted from the data list. If you don't want to
delete the record, click Cancel; otherwise
click OK.
INTRODUCTION
If a record is no longer valid, you can delete it. That row and all its corresponding data are removed from the worksheet. Be warned, though, that if you use the data list form to delete a record, you cannot undo the deletion. Be sure you're deleting the correct record before you confirm the deletion, and consider making backups of the data list on a regular basis so you can recover deleted records in the event of an error. |
TIP
Deleting within a worksheet To delete a record in the worksheet, rather than in the data list form, select the row containing the record and then choose Edit, Delete. The row and the information it contains are deleted from the worksheet, and will no longer display in the data list form. To undo a row deletion, use the Undo button on the Standard toolbar. |
Sorting Data List Records
Choose Data, Sort to open the Sort dialog box (the active
cell must be somewhere in the data list on the worksheet).
Click the down arrow next to the Sort
by field and choose from the list that appears. Then, click the
corresponding Ascending option button to
select it.
Click the down arrow next to the Then
by field and choose from the list. Then, click the corresponding Ascending/Descending option and click OK.
Excel sorts the entire data
list using the criteria you selected; for example, first by Region and then by
Last Name within each region.
INTRODUCTION
It's easy to change the order of your data list. If, for example, you want to alphabetically arrange the names in a data list of sales representatives, you can sort on the Last Name field. Alternatively, you might want to arrange the names of your sales representatives by region; in that case, you could sort by both the Region and Last Name fields. |
TIP
Minimizing the sort If you don't select the area of the data list that you want to sort, Excel sorts the entire list. You can, though, sort only on a particular number of records by first selecting the records in the data list and then following the steps in this task. |
Filtering Data List
Records
Choose Data, Filter, AutoFilter to add drop-down arrows to
each field header.
Click the down arrow next to the column you want to use for
the filtering criteria.
Specify what records you want
to match. You can select a particular value, Top 10,
All, Custom, or a specific record type in that field (for example, 3).
The data list displays only
those records that meet your criteria. Filtered data displays a blue arrow
instead of the default black.
INTRODUCTION
You might not always want to display all records in a large data list. Instead, you might want to work with just a set of records—for example, all sales representatives in Region 3. When you want to work with a subset of records, you can filter the data in a data list. All the records remain in the data list worksheet, but only those meeting the criteria you select are displayed. |
TIP
Redisplaying all records To display all records again, choose Data, Filter, Show All. Alternatively, click the arrow with the different filter color and select All from the drop-down list. |
Removing a Data List
Filter
Choose Data, Filter, AutoFilter.
The arrows on the column
headers disappear, and the entire data list is again visible.
INTRODUCTION
If you have filtered your data list in order to view only particular records, most likely only a subset of your data will be displayed in the worksheet. To avoid confusion, turn off the AutoFilter option when you finish working in a worksheet, so all the data is again viewable. Otherwise, the next time you open the worksheet, you might not remember that there is additional data. |
TIP
Leaving on AutoFilter Alternatively, leave on the AutoFilter, but select (All) in each of the AutoFilter drop-down lists to display all records in the data list. |
Working with Data List
Subtotals
After you've sorted your data
(see the task "Sorting Data List
Records"), choose Data, Subtotals to open the Subtotal dialog box.
Click the down arrow next to the At
each change in field and select the field you want to subtotal.
Click the down arrow next to the Use
function field and select the type of function you want performed on the
selected field.
In the Add subtotal to list, click the check box next to
the field to which you want to add the subtotal. Uncheck any other fields to
avoid subtotaling each field.
INTRODUCTION
Subtotals are an easy way to summarize data in a list. You might, for example, want to total all sales by a particular region and then view a grand total. Or, you might want to total sales by sales representative. You can sort and then subtotal on any field in the data list, as long as the data in that field is of a type that can be "totaled." For this task, first sort the data in ascending order by region; then add subtotals for the year-to-date sales according to region. |
TIP
More sort options To sort your data list, you can also choose Data, Sort, select the field (Region) to sort by, specify how you want to sort (Ascending or Descending), and click OK. Refer to the task "Sorting Data List Records" for more information. |
Click Replace current subtotals if data list has
subtotals; Page break between groups to put
subtotals on a new page; Summary below data
to add subtotal summaries.
Click OK. Excel inserts a subtotal row for each time the
selected field changes, performs the SUM function on the column and adds a
grand total at the end of the data list.
Click the Print Preview button on the Standard toolbar.
The Print Preview window
opens, providing a better look at your data after subtotals have been added
(click Close to return to the worksheet).
TIP
Multiple subtotals You can check more than one check box in the Add subtotal to list box to have Excel calculate the function on each selected field. For example, you could have a subtotal on current month sales and units sold. |
TIP
Removing subtotals To remove the subtotals, choose Data, Subtotals and click the Remove All button in the Subtotal dialog box. |
TIP
No fields selected If there is only one field available to you in the Subtotal dialog box, that means you need to select the entire data list in the worksheet before you complete step 2. This tells Excel which fields you want to include in the sort. |