Section 13.1. The Basics of Data Lists
13.1. The Basics of Data Lists
An Excel data list is really nothing more than a way to store a bunch of information about a group of items. Each item occupies a separate row, and different kinds of information about the item reside side-by-side in adjacent columns . In database terminology, the rows are known as records , and the columns of information are known as fields . For example, the records might represent customers, and the fields might contain things like name , address, and purchase history.
Note: Sadly, Excel 2002 fans can't create the kind of data list described in this section. However, you're still free to organize information in rows and columns, and then use features like searching and sorting (which are described later in this chapter).
| EXCEL 2002 CORNER Lists and Excel 2002 |
| Excel 2002 lacks a few of the list-management frills that are available in Excel 2003. Although you can easily create lists of information in Excel 2002, the program lacks some of Excel 2003's timesaving list-oriented tools, and it doesn't even alert you that you're using a list. For example, once you create a list in Excel 2003, it responds immediately by showing a blue border around the whole list and also helps you by putting list- related features right at your fingertips, including a fancy list toolbar. In Excel 2002, life isn't as convenient . You will need to hunt through the menus to get to list features like sorting and searching. But fear notwith a few minor exceptions, these features are still there. So what's an Excel 2002 fan to do? Start by reading through the beginning of the Section 13.1 if you need to refresh your memory about what list data looks like. Then skip straight to "Using the Data Form" (avoiding the Excel 2003-centric information about creating lists). You can then read through the rest of the chapter to learn about searching, filtering, and sorting a list. |
Excel data lists have a number of advantages over ordinary tables:
-
They grow and shrink dynamically . As a data list changes size , any formulas that use the data list adjust themselves accordingly . In other words, if you have a formula that calculates the sum of a column in a data list, the range that the SUM( ) function uses expands when you add a new record.
-
They have built-in smarts . You can quickly edit and delete records, apply a custom sort order, and search for important records.
-
They excel (ahem) at dealing with large amounts of information . If you need to manage vast amounts of information, ordinary Excel tables can be a little cumbersome. If you put the same information in a data list, you can simply apply custom filtering , which means you'll see only the records that you're interested in.
-
They can link to databases . Data lists are perfectly useful in standalone worksheets. However, they can also double as indispensable tools for navigating information contained in a database. In Chapter 22, you'll learn how to get information out of a database and into an Excel data list. In Chapter 23, you'll see how you can perform the same feat with XML documents.
13.1.1. Creating a List
Creating a list in Excel 2003 is easy. Here's how it works:
-
Choose the row where your want your list to start.
If you're creating a new list, the first row in the worksheet is a good place to begin. You can always shift the list down later by putting your cursor in the top row and choosing Insert
Rows. This first row is where youll enter any column titles you want to use, as explained in the next step.
Tip: Be careful when placing content in the cells beneath your list. Although Excel tries to shift this content out of the way, if your list expands, you can still run into trouble. For example, if your list is only two columns wide, and you have data underneath it that stretches over three columns, the structure of your data below the list may get mangled when the list grows, since that third column won't get moved along with its two adjacent columns.
-
Enter the column titles for your list, one column title for each category you want to create.
To create the perfect list, you need to divide your data into categories. For example, if you're building a list of names and addresses, you probably want your columns to hold the standard info you see on every form ever created: First Name, Last Name, Street, City, and so on. The columns you create are the basis for all the searching, sorting, and filtering you do. For instance, you could sort your contacts by first name or by city.
If you want, you can start to add entries underneath the column headings now (in the row directly below the column titles). Or, just jump straight to the next step to create the list.
-
Make sure you're currently positioned somewhere in the list ( anywhere in the column title row works well), and select Data
List Create List. Excel scans the nearby cells and selects all the cells that it thinks are a part of your list. Once Excel determines the bounds of your list, the Create List dialog box appears, as shown in Figure 13-1.
Figure 13-1. The Create List dialog box displays the cell references for the currently selected range. In this example, the selection includes only the headings (there's no data yet). You can change the range by typing in new information or by clicking the mini-worksheet icon at the right end of the cell range box, which lets you select the range by dragging on the appropriate cells in the worksheet.
-
Make sure the "My list has headers" checkbox is turned on. This option tells Excel you're using the first row just for column headers. Then click OK.
Excel transforms your cell into a data list, like the one shown in Figure 13-2. You can tell that your ordinary table has become a genuine data list by the presence of a few telltale signs. First, all lists are surrounded by a heavy blue border. Second, the column headings appear in boldface. And whenever you're positioned inside the list, you'll see a tiny drop-down arrow, which appears next to the column name for quick filtering (a feature that you'll explore a little later), and a new blank row at the end of the list, with an asterisk in the column on the far left.
|
Creating a list from an existing table is just as easy. All you need to do is click inside the table and choose Data List Create List. Excel automatically adds all the rows under the column headings into the new list.
Keep in mind that lists consist of exactly two elements: column headers and rows. Lists don't support row headers (although there's no reason why you can't turn the typical row title into a separate column). Lists also have a fixed structure, which means that every row uses the same columns. If you have multiple data tables on the same worksheet, you can create a separate list for each one.
Tip: You should always define column headers. If you don't, Excel simply adds unhelpful text like Column1 and Column2 at the top of your columns.
| Up To Speed The Difference Between Excel Worksheets and Databases |
| An Excel data list uses some of the same concepts as a databasenamely, the idea of records and fields. However, databases and Excel worksheets are two very different entities. For starters, databaseswhich programs like Microsoft Access and SQL Server let you createhave much stricter rules than Excel worksheets. Before you can add any data to a table in a database, you must carefully define the table. You need to specify not only the name of each field, but also the type of information the field can contain. Although Excel provides some of these so-called data validation features (which you'll explore in Chapter 15), the program isn't nearly as strict about itvalidation is completely optional. Also, unlike Excel, most modern databases are relational , which means they contain multiple tables that have specific links to one another. For example, a relational database might tie together customers in one table and the orders they've made in another. In Excel, a worksheet can hold multiple lists of data, but there's no way to tie them together. Most importantly, databases play a dramatically different role in the world of business. Typically, Excel is an end user program, which means it's used by ordinary mortals who generally know how to create an Excel file, design what it's going to look like, and then fill it up with data. Databases, on the other hand, are usually created by ex-math majors, and are used to store information, behind-the-scenes, that non-programmer types end up using. For example, every time you use Google or are searching on Amazon for something to buy, you're actually seeing answers that have been stored in, and generated by, massive and powerful databases. In Chapter 22, you'll see how you can use Excel to retrieve information from a database and analyze it in a worksheet. |
13.1.2. Editing a List
Once you've created a list in Excel 2003, there are three basic editing tasks you can perform:
-
Edit a record . This part is easy. Just modify cell values as you would in any ordinary worksheet.
-
Delete a record . First, go to the row you want to delete (you can be in any column). Then choose Edit
Delete Row. Excel removes the row and shrinks the list automatically. -
Add a new record . To add a record, type a new set of values into the columns of the last row in the list. (It's the blank row that has the asterisk * in the left column.) Excel expands the list automatically, as shown in Figure 13-3. If you want to insert a row but don't want it to be at the bottom of the list, you can choose Insert
Rows. This command inserts a new blank row immediately above the current row.
Note: The Delete Row and Insert Rows commands work a little differently with lists than they do elsewhere in your worksheet. When you use these commands inside a list, the operation only affects the cells in that list. For example, if you have a list with three columns and you delete a row, Excel removes three cells, period. Any information in the same row that exists outside the list is unaffected.
|
You may also decide to change the structure of your list by adding or removing columns. Once again, these tasks work in a similar way to inserting or removing columns in an ordinary worksheet. (The big difference, as shown in Figure 13-4, is that any rows or columns outside your list remain unaffected when you add new rows or columns.)
To add a column to the left of a column you're currently in, select Insert Columns. Excel automatically assigns a generic column title, like Column1, which you can then edit. If you want to add a column to the right side of the list, just start typing in the blank column immediately to the right of the list. When you've finished your entry, Excel automatically merges that column into the list. This feature is called auto-expansion, and Excel uses it to try and make it easier to work with lists.
Tip: Don't worryif Excel expands a list against your wishes, you aren't completely powerless. To correct Excel's mistake, look for the lightning bolt icon that appears immediately next to the newly added column. This doohickey is a smart tag that lets you reverse the auto-expansion. Click it once, and Excel displays a pop-up menu giving you two choices: Undo List Expansion (to return your list to its previous size) and Stop Automatically Expanding Lists (to disable this behavior altogether).
|
Deleting a column is a little trickier. The simplest approach is to use the List menu in the List toolbar, which is docked at the right side of the Excel window. First, move to the column you want to delete. Click the word List at the top of the toolbar, and then select Delete Column, as shown in Figure 13-5. Another option is to move to the column header cell and choose Edit Delete Column. The trick with this approach is that the Delete Column option doesn't appear in the Edit menu unless you're in a column header or placeholder cell.
Finally, you can always convert your snazzy list back to an ordinary collection of cells. Just click anywhere in the list, and choose Data List Convert to Range. But then, of course, you don't get to play with your data list toys anymore.
|
EAN: 2147483647
Pages: 185