Excel 2003 Personal Trainer

Safari Books Online

Excel 2003 Personal Trainer

By: CustomGuide, Inc.
Publisher:: O'Reilly Media
Released:: November 2004
Pages:: 496

Description

Get on top of all the new features in the latest version of Excel. Beginning with spreadsheet basics, this complete workout takes you through editing and formatting; working with formulas, charts and graphs, macros; integrating excel with other programs; and a variety of advanced topics. It's written in a non-technical and engaging style that you will find fun, easy, clear, and informative. Includes an interactive CD.

Full Description

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.

Table of Contents

Chapter 1 The Fundamentals
1. Starting Excel
2. What's New in Excel 2003?
3. Understanding the Excel Program Screen
4. Using Menus
5. Using Toolbars and Creating a New Workbook
6. Filling Out Dialog Boxes
7. Keystroke and Right Mouse Button Shortcuts
8. Opening a Workbook
9. Saving a Workbook
10. Moving the Cell Pointer
11. Navigating a Worksheet
12. Entering Labels in a Worksheet
13. Entering Values in a Worksheet and Selecting a Cell Range
14. Calculating Value Totals with AutoSum
15. Entering Formulas
16. Using AutoFill
17. Previewing and Printing a Worksheet
18. Getting Help
19. Changing the Office Assistant and Using the "What's This" Button
20. Closing a Workbook and Exiting Excel
21. Chapter One Review
Chapter 2 Editing a Worksheet
1. Entering Date Values and Using AutoComplete
2. Editing, Clearing, and Replacing Cell Contents
3. Cutting, Copying, and Pasting Cells
4. Moving and Copying Cells with Drag and Drop
5. Collecting and Pasting Multiple Items
6. Working with Absolute and Relative Cell References
7. Using the Paste Special Command
8. Inserting and Deleting Cells, Rows, and Columns
9. Using Undo, Redo, and Repeat
10. Checking Your Spelling
11. Finding and Replacing Information
12. Advanced Printing Options
13. File Management
14. Inserting Cell Comments
15. Understanding Smart Tags
16. Recovering Your Workbooks
17. Chapter Two Review
Chapter 3 Formatting a Worksheet
1. Formatting Fonts with the Formatting Toolbar
2. Formatting Values
3. Adjusting Row Height and Column Width
4. Changing Cell Alignment
5. Adding Borders
6. Applying Colors and Patterns
7. Using the Format Painter
8. Using AutoFormat
9. Creating a Custom Number Format
10. Creating, Applying, and Modifying a Style
11. Formatting Cells with Conditional Formatting
12. Merging Cells, Rotating Text, and Using AutoFit
13. Finding and Replacing Formatting
14. Chapter Three Review
Chapter 4 Creating and Working with Charts
1. Creating a Chart
2. Moving and Resizing a Chart
3. Formatting and Editing Objects in a Chart
4. Changing a Chart's Source Data
5. Changing a Chart Type and Working with Pie Charts
6. Adding Titles, Gridlines, and a Data Table
7. Formatting a Data Series and Chart Axis
8. Annotating a Chart
9. Working with 3-D Charts
10. Selecting and Saving a Custom Chart
11. Using Fill Effects
12. Chapter Four Review
Chapter 5 Managing Your Workbooks
1. Switching Between Sheets in a Workbook
2. Inserting and Deleting Worksheets
3. Renaming and Moving Worksheets
4. Working with Several Workbooks and Windows
5. Splitting and Freezing a Window
6. Referencing External Data
7. Creating Headers, Footers, and Page Numbers
8. Specifying a Print Area and Controlling Page Breaks
9. Adjusting Page Margins and Orientation
10. Adding Print Titles and Gridlines
11. Changing the Paper Size and Print Scale
12. Protecting a Worksheet
13. Hiding Columns, Rows, and Sheets
14. Viewing a Worksheet and Comparing Workbooks Side by Side
15. Saving a Custom View
16. Working with Templates
17. Consolidating Worksheets
18. Chapter Five Review
Chapter 6 More Functions and Formulas
1. Formulas with Several Operators and Cell Ranges
2. Using the Insert Function Feature
3. Creating and Using Range Names
4. Selecting Nonadjacent Ranges and Using AutoCalculate
5. Using the IF Function to Create Conditional Formulas
6. Using the PMT Function
7. Displaying and Printing Formulas
8. Fixing Formula Errors
9. Mathematical Functions
10. Financial Functions
11. Date and Time Functions
12. Statistical Functions
13. Database Functions
14. Chapter Six Review
Chapter 7 Working with Lists
1. Creating a List
2. Working with Lists and Using the Total Row
3. Adding Records Using the Data Form Dialog Box and Insert Row
4. Finding Records
5. Deleting Records
6. Sorting a List
7. Filtering a List with the AutoFilter
8. Creating a Custom AutoFilter
9. Filtering a List with an Advanced Filter
10. Copying Filtered Records
11. Using Data Validation
12. Chapter Seven Review
Chapter 8 Automating Tasks with Macros
1. Recording a Macro
2. Playing a Macro and Assigning a Macro a Shortcut Key
3. Adding a Macro to a Toolbar
4. Editing a Macro's Visual Basic Code
5. Inserting Code in an Existing Macro
6. Declaring Variables and Adding Remarks to VBA Code
7. Prompting for User Input
8. Using the If...Then...Else Statement
9. Chapter Eight Review
Chapter 9 Working with Other Programs
1. Inserting an Excel Worksheet into a Word Document
2. Modifying an Inserted Excel Worksheet
3. Inserting a Linked Excel Chart in a Word Document
4. Inserting a Graphic into a Worksheet
5. Opening and Saving Files in Different Formats
6. Chapter Nine Review
Chapter 10 Using Excel with the Internet
1. Adding and Working with Hyperlinks
2. Browsing Hyperlinks and Using the Web Toolbar
3. Saving a Workbook as a Non-Interactive Web Page
4. Saving a Workbook as an Interactive Web Page
5. Import an External Data Source
6. Refresh a Data Source and Set Data Source Properties
7. Create a New Web Query
8. Chapter Ten Review
Chapter 11 Data Analysis and PivotTables
1. Creating a PivotTable
2. Specifying the Data a PivotTable Analyzes
3. Changing a PivotTable's Calculation
4. Selecting What Appears in a PivotTable
5. Grouping Dates in a PivotTable
6. Updating a PivotTable
7. Formatting and Charting a PivotTable
8. Creating Subtotals
9. Using Database Functions
10. Using Lookup Functions
11. Grouping and Outlining a Worksheet
12. Chapter Eleven Review
Chapter 12 What-If Analysis
1. Defining a Scenario
2. Creating a Scenario Summary Report
3. Using a One and Two-Input Data Table
4. Understanding Goal Seek
5. Using Solver
6. Chapter Twelve Review
Chapter 13 Advanced Topics
1. Hiding, Displaying, and Moving Toolbars
2. Customizing Excel's Toolbars
3. Sending Faxes
4. Creating a Custom AutoFill List
5. Changing Excel's Options
6. Password Protecting a Workbook
7. File Properties and Finding a File
8. Sharing a Workbook and Tracking Changes
9. Merging and Revising a Shared Workbook
10. Using Detect and Repair
11. Chapter Thirteen Review

Colophon

Product Details

Title:

Excel 2003 Personal Trainer

By:

CustomGuide, Inc.

Publisher:

O'Reilly Media

Formats:

Print
Safari Books Online

Print Release:

November 2004

Pages:

496

Print ISBN:

978-0-596-00853-6

| ISBN 10:

0-596-00853-8

Customer Reviews

Colophon

Our look is the result of reader comments, our own experimentation, and feedback from distribution channels. Distinctive covers complement our distinctive approach to technical topics, breathing personality and life into potentially dry subjects. Mary Brady was the production editor and the proofreader for Excel 2003 Personal Trainer. The cover image is an original illustration by Lou Brooks. Marlowe Shaeffer and Claire Cloutier provided quality control. Judy Hoer wrote the index.

The cover image of the comic book hero is an original illustration by Lou Brooks. The art of illustrator Lou Brooks has appeared on the covers of Time and Newsweek eight times, and his logo design for the game Monopoly is used throughout the world to this day. His work has also appeared in just about every major publication, and it has been animated for MTV, Nickelodeon, and HBO.

Emma Colby designed and produced the cover of this book with Adobe InDesign CS and Photoshop CS. The typefaces used on the cover are Base Twelve, designed by Zuzana Licko and issued by Emigre, Inc., and JY Comic Pro, issued by AGFA Monotype.

Melanie Wang designed the interior layout. David Futato designed the CD label. This book was converted by Andrew Savikas and Joe Wizda to FrameMaker 5.5.6 with a format conversion tool created by Erik Ray, Jason McIntosh, Neil Walls, and Mike Sierra that uses Perl and XML technologies. The typefaces are Minion, designed by Robert Slimbach and issued by Adobe Systems; Base Twelve and Base Nine; JY Comic Pro; and TheSansMono Condensed, designed by Luc(as) de Groot and issued by LucasFonts.

The technical illustrations that appear in the book were produced by Robert Romano and Jessamyn Read using Macromedia FreeHand MX and Adobe Photoshop CS.

Service and support by Satisfaction

O'Reilly Books & Videos

Excel 2003 Personal Trainer

Chapter 1 The Fundamentals

Starting Excel

What's New in Excel 2003?

Understanding the Excel Program Screen

Using Menus

Using Toolbars and Creating a New Workbook

Filling Out Dialog Boxes

Keystroke and Right Mouse Button Shortcuts

Opening a Workbook

Saving a Workbook

Moving the Cell Pointer

Navigating a Worksheet

Entering Labels in a Worksheet

Entering Values in a Worksheet and Selecting a Cell Range

Calculating Value Totals with AutoSum

Entering Formulas

Using AutoFill

Previewing and Printing a Worksheet

Getting Help

Changing the Office Assistant and Using the "What's This" Button

Closing a Workbook and Exiting Excel

Chapter One Review

Chapter 2 Editing a Worksheet

Entering Date Values and Using AutoComplete

Editing, Clearing, and Replacing Cell Contents

Cutting, Copying, and Pasting Cells

Moving and Copying Cells with Drag and Drop

Collecting and Pasting Multiple Items

Working with Absolute and Relative Cell References

Using the Paste Special Command

Inserting and Deleting Cells, Rows, and Columns

Using Undo, Redo, and Repeat

Checking Your Spelling

Finding and Replacing Information

Advanced Printing Options

File Management

Inserting Cell Comments

Understanding Smart Tags

Recovering Your Workbooks

Chapter Two Review

Chapter 3 Formatting a Worksheet

Formatting Fonts with the Formatting Toolbar

Formatting Values

Adjusting Row Height and Column Width

Changing Cell Alignment

Adding Borders

Applying Colors and Patterns

Using the Format Painter

Using AutoFormat

Creating a Custom Number Format

Creating, Applying, and Modifying a Style

Formatting Cells with Conditional Formatting

Merging Cells, Rotating Text, and Using AutoFit

Finding and Replacing Formatting

Chapter Three Review

Chapter 4 Creating and Working with Charts

Creating a Chart

Moving and Resizing a Chart

Formatting and Editing Objects in a Chart

Changing a Chart's Source Data

Changing a Chart Type and Working with Pie Charts

Adding Titles, Gridlines, and a Data Table

Formatting a Data Series and Chart Axis

Annotating a Chart

Working with 3-D Charts

Selecting and Saving a Custom Chart

Using Fill Effects

Chapter Four Review

Chapter 5 Managing Your Workbooks

Switching Between Sheets in a Workbook

Inserting and Deleting Worksheets

Renaming and Moving Worksheets

Working with Several Workbooks and Windows

Splitting and Freezing a Window

Referencing External Data

Creating Headers, Footers, and Page Numbers

Specifying a Print Area and Controlling Page Breaks

Adjusting Page Margins and Orientation