13.2. The Data Form Excel also gives you another way to look at list data, using a data form window. A data form is a separate window that shows the information from your list one record at a time. To try it, click in any row in your list and select Data  Form. An unusual looking window opens (see Figure 13-6), where you can edit each row using neatly organized textboxes. The data form lets you navigate your list in many different ways: -
Press the down arrow key (  ) to move down one record. You can also click the down arrow at the bottom of the scroll bar, or the Find Next button. -
Press the up arrow key (  ) to move up one record. You can also click the up arrow at the top of the scroll bar, or the Find Prev button. -
To move down 10 records at once, press the Page Down key. -
To move up 10 records at once, press the Page Up key. -
To move to the first record in the list, press Ctrl+ or drag the scroll box to the top of the scroll bar. -
To move to the first record in the list, press Ctrl+ or drag the scroll box to the bottom of the scroll bar. -
To move from one field to the next, press Tab. To move backward to the previous field, press Shift+Tab. You can close the data form at any time by clicking the Close button.  | Figure 13-6. The data form window lets you move through your data one record at a time. You can edit, insert, and delete recordsall from one convenient location. You can move from record to record using the scrollbar, and you can insert and remove records with a single button click. In the top-right corner, you'll see the total number of records in your list and your current position. In this example, the data form is on record 1 of 41. | |
FREQUENTLY ASKED QUESTION
Data Form Versus Worksheet | | Should I use the data form or my worksheet to edit records in a list? At this point, you may be wondering if you should edit data lists directly on your worksheet or by using the data form. It's really a matter of preference. The data form works well if you have data that stretches out over several columns . To edit a row like this in the worksheet view, you'd need to scroll from side to side. The data form, on the other hand, can show you the whole record at onceit simply expands the window and adds text boxes until all the information is visible. The data form also provides single-click access to record insertion and deletion, as well as a nifty search feature. However, the data form lets you see the data in only one record at a time. Overall, the data form may look more familiar to people who have used database programs like Microsoft Access, and who aren't as comfortable with Excel. |
13.2.1. Editing with the Data Form When you make a change using the data form, Excel doesn't apply it automatically. You'll see your changes appear on the worksheet only when you move to another record or close the data form window. You can also reverse all changes you've made to the current record in the data form window by clicking the Restore button. To insert a record into your list, either scroll just past the end of the list or click New. Either way, the text at the top-right corner of the data form window changes to New Record . As long as you enter information in at least one text box, Excel creates a new record when you close the window or scroll to another record. To stop this from happening, click Restore to clear the information you've entered. When you add a record from the data form, it always shows up at the bottom of the list, no matter where you are when you click New.
Tip: When creating a new record, you can press Ctrl+' (hold down the Ctrl key and press the quotation mark key) in any field to copy the value from the last row into the current row. The only trick is that Excel copies the underlying cell value, not the display value. So if you use this approach to copy a date, you'll see the date's underlying serial value (like 38353 for January 1, 2005). Once you hit Enter to commit the new value, you'll see the display value you expect.
Deleting a record is probably the easiest task to perform with the data form window. Just move to the record and click the Delete button. However, be warned that when you delete a record in this way, you won't be able to use Excel's undo feature to restore it. For that reason, Excel prompts you for confirmation every time you click Delete. 13.2.2. Searching with the Data Form The most interesting feature of the data form window is its ability to search for records using the criteria you specify. Here's how it works: -
Click the Criteria button. This action clears all text boxes and prepares Excel to perform a search. -
Specify the criteria that you want to use to perform the search. You specify the criteria by entering text in the appropriate text box. For example, if you want to find all the records that have the text "Travel" in the Category column, type the word Travel into the Category text box. To create more specific searches, you can enter text into more than one field. If you want to match a numeric field, don't bother entering formatting details (like the currency symbol or a comma). These details don't apply during searches, although, if you do enter them, Excel is usually intelligent enough to ignore them. If you want to match text, be aware that Excel allows partial matches. That means that if you enter T in the Category text box, Excel matches both Travel and Tools. Similarly, the search isn't case-sensitive. -
Click Form to return to the record list. Then, click Find Next or Find Prev to start the search. If Excel can't find a match, it makes an intimidating chime and remains at the current record. Otherwise, you'll move to the first matching record either before the current record (if you clicked Find Prev) or after the current record (if you clicked Find Next). Once you've found the record you want, you're free to edit or delete it.
Tip: You can click Find Next or Find Prev to start a search immediately, without clicking Form to switch back to the record view. However, searches always start at your current location, so it's a good idea to return to the form view to see what record you're currently on. Also, if you don't click Form, you might see some misleading behavior. Namely, if Excel can't find a match, it simply returns you to the current record. If you're not already looking at the current record, you might be fooled into thinking that Excel is showing you a successful match.
-
If you want to continue your search to find other matches, click Find Next or Find Prev again. You can continue until you reach the top or bottom of the list. Excel remembers the criteria you specified as long as the search is underway. You can still use the arrow keys or scroll bar to move from record to record, but Find Next and Find Prev always jump to the closest record that matches your search criteria. To change the search criteria, click the Criteria button again. Excel clears all the text boxes and displays the search criteria it's currently using. You can then edit, remove, or add to the criteria.
Note: Every time you close the Data Form window, Excel discards your search criteria.
13.2.3. Advanced Searching In a simple search, you try to find a record by matching values exactly. In an advanced search, the idea is to get a little craftier using conditional operators like less than (<) or not equal to (<>). When you enter a value for a search criteria, Excel automatically assumes you're using an "equal to" comparison. In other words, if you type Travel in the Category field, Excel decides you've typed =Travel . As you learned in Chapter 7, there are many more logical operators than just the equal sign. The others include: -
Greater than ( > ), which finds records where the number, date, or text is larger than the search criteria. In the case of text, the greater than operator uses an alphabetic comparison, which means that banana is deemed to be greater than apple because banana occurs after apple in the dictionary. -
Less than ( < ), which finds records where the number, date, or text is smaller than the search criteria. -
Greater than or equal to ( >= ), which works the same as greater than, except that it also finds records that match exactly. -
Less than or equal to ( <= ), which works the same as less than, except that it also finds records that match exactly. -
Not equal to ( <> ), which finds records where the number, date, or text doesn't equal the search criteria. Using this knowledge, you can use search criteria like >5000 (to find all values larger than 5000) or <>Travel (to find all values that aren't in the travel category). Figure 13-7 shows a search that uses conditional operators.  | Figure 13-7. In this example, the search has become much more specific. Matching records must be in the Tools category, cost less than $50, and not have the ID 365 (which corresponds to a product the searcher wants to ignore). | |
|
