Excel 2003 Personal Trainer +CD - Softcover

Customguide Inc

Softcover

ISBN 10: 0596008538 ISBN 13: 9780596008536

Publisher: O′Reilly, 2004

Synopsis

Every time a new version of Microsoft Excel appears, you're introduced to another batch of powerful features--most of which you'll never understand or use. Even if you're experienced with this spreadsheet program, you'll struggle to keep up with all of the improvements. But if you're a beginner, you probably don't know where to start.Now, with Excel 2003 Personal Trainer, beginners and experts alike can become black belts, quickly and easily. This fully illustrated book takes a modular approach to learning, allowing you to start with the fundamentals and work your way to advance topics through dozens of task-oriented lessons---at your own pace. The companion CD tutorial guides you through each lesson interactively.Excel 2003 Personal Trainer includes sections on editing, formatting, functions, formulas, lists, macros, creating and managing workbooks, and creating and editing charts. You'll also learn data analysis, what-if analysis, pivot tables, how get Excel to work with other programs and the Internet, and advanced topics like using detect and repair. Every chapter includes detailed diagrams and a review at the end to help you absorb and retain all you have learned.If you already use Excel, you can dive right into those topics (and only those topics) that you need or want to learn. Unlike many consumer software tutorials that dumb down the material or present it in a confusing fashion, Excel 2003 Personal Trainer is written in a non-technical and engaging style that you will find fun, easy, and most of all, clear and informative. You don't have to wade through tons of jargon and technical information to become proficient with Excel.Part of our new Personal Trainer Series, this book is based on content from CustomGuide (www.customguide.com), a leading provider of computer training materials. Founded by instructors who grew dissatisfied with the industry's dry course materials, CustomGuide offers courseware (for instructors and students), quick references, to software bulletins and e-learning courses that are fun, flexible, and easy to use.

About this title

From the Publisher

Get on top of all the new features in the latest version of Excel. This fully illustrated book takes a modular approach, allowing you to start with the fundamentals and work your way to advanced topics through dozens of task-oriented lessons--at your own pace. It's written in a non-technical and engaging style that you will find fun, easy, clear and informative. Includes an interactive CD.

Excerpt. © Reprinted by permission. All rights reserved.

Chapter 7 WORKING WITH LISTS

CHAPTER OBJECTIVES:
Create a list
Add, find, edit, and delete records
Sort a list
Use the AutoFilter to filter a list
Create a custom AutoFilter
Create and use an advanced filter
Use data validation when entering records to a list

CHAPTER TASK: CREATE A LIST THAT TRACKS
CUSTOMERS AND FLIGHTS

Prerequisites
· Understand how to use menus, toolbars, dialog boxes, and shortcut keystrokes
· Understand how to enter values and labels

Another task Excel can perform is keeping track of information in lists or databases. Some examples of things you might track in a list include telephone numbers, clients, and employee rosters. Once you create a list in Excel, you can easily find, organize, and analyze its information with Excel’s rich set of list-management features.

Working with lists in Excel 2003 is a breeze compared to earlier versions of the program.Microsoft added six major enhancements to list functionality that make the process much more user-friendly. When you create a list using Excel 2003, the following features are engaged automatically: AutoFilter; a list border; an Insert row; resize handles; a Total row; and the List toolbar.

In this chapter, you will learn how to create a list, and then add, modify, delete, and find information in it. You’ll also learn how you can use Excel’s filter commands to display specific information, such as records from a specific Zip Code.

We’ll start this chapter by creating a list. Microsoft has made creating lists easier in Excel 2003 by adding six major enhancements to list functionality. When you designate a range of cells as a list, it automatically has:

· AutoFilter: The AutoFilter feature is now the default setting for lists. It appears in each column header row and allows you to sort your data faster.
· Borders:When you create a list, a dark blue list border outlines the range of cells, separating it from the data in the rest of the worksheet.
· Insert row: A blank row with an asterisk (*) in the first cell appears at the bottom of a created list. Any information typed in this row will automatically be added to the list.
· Resize handles: You can make your list bigger or smaller by clicking on and dragging the handle found on the bottom-righthand corner of the list border.
· Total row: You can easily add a total row to your list by clicking on the Toggle Total Row button on the List toolbar. If you click on any cell in this row, a dropdown menu of aggregate functions appears.
· List toolbar: To aid in making changes to your list quickly, a List toolbar appears whenever a cell is selected within the list range.

Now that we’ve established the new list enhancements for Excel 2003, let’s learn more about them.

1 Start Microsoft Excel, navigate to your practice folder, open the workbook named Lesson 7A, and save it as List Practice.

The List Practice workbook appears on your screen. There are two main components of a list:

· Records: Each record contains information about a thing or person, just like a listing in a phone book. The two records in this list are John Peters and Mary Smith.
· Fields: Records are broken up into fields, which store specific pieces of information. Examples of field names in this set of data are First (first names), Last (last names), and Income (yearly income per person).

In Excel, the columns contain the list’s fields, and the rows contain the list’s records. See Figure 7-1 for an example of how information is stored in columns and rows.

NOTE The Zip Codes in this list are entered as values, not numbers. When you want to enter a number as a label rather than a value, type an apostrophe (’) before the number. If you didn’t add this apostrophe, Excel would remove the leading zeros (0) from any Zip Codes beginning with (0), such as 01586.

Now that you know the basics about list data, let’s create one. Table 7-1 offers a set of guidelines for creating lists.

2 Select the cell range A1:H3 and select Data – List - Create List from the menu.

The Create List dialog box appears. Since you selected the cell range before you started the process, it automatically appears in the box.

This set of data already has field names that you want to use as the list’s headers.

3 Make sure the My list has headers check box is checked and click OK.

Your data has been turned into a list.

4 Click cell A1 to deselect the cell range.
Compare your list to the one in Figure 7-1.

Take notice of the changes: a dark blue border appears around the list; arrows appear in each of the column headings, showing you that AutoFilter is enabled; the column headings are now bold; Resize handles appear; an Insert row is added; and the List toolbar appears.

Creating a list using Excel 2003 is easy enough, but working with them can be another story. This lesson will guide you through a few things that you can do to make working with your lists a little less stressful: splitting and freezing the worksheet so that the field headings always remain visible and using the Total row.

TIP When working with lists, especially longer ones, it is usually also a good idea to split and freeze the worksheet window so the field headings remain visible as you move through the rest of the worksheet.

1 Move the pointer over the vertical split box, located at the top of the vertical scroll bar. When the pointer changes to a , drag the split box down directly beneath row

1.Excel splits the worksheet window vertically into two separate panes.

2 Select Window - Freeze Panes from the menu.The frozen heading row will always be visible at the top of the worksheet, even if the list contains thousands of records.

Edition details

• Publisher: O′Reilly
• Publication date: 2004
• Language: English
• ISBN 10: 0596008538
• ISBN 13: 9780596008536
• Binding: Paperback
• Number of pages: 482