Microsoft Office Excel 2003

by Shelly, Gary B.; Cashman, Thomas J.; Quasney, James S.

• ISBN13: 9780619200336
• ISBN10: 0619200332
• Format: Paperback
• Copyright: 2003-12-12
• Publisher: Cengage Learning

Summary

For the past three decades, the Shelly Cashman Series® has effectively introduced computers to millions of students, consistently providing the highest quality, most up-to-date, and innovative materials in computer education. We are proud of the fact that our series of Microsoft Office 4.3, Microsoft Office 95, Microsoft Office 97, Microsoft Office 2000, and Microsoft Office XP textbooks have been the most widely used books in computer education. With each new edition of our Office books, we have made significant improvements based on software changes and comments made by both instructors and students. Our Microsoft Office 2003 books continue with the innovation, quality, and reliability that you have come to expect from the Shelly Cashman Series.

Table of Contents

Preface	vii
To the Student	xiv
Project One Creating a Worksheet and an Embedded Chart
Objectives	EX 4
What Is Microsoft Office Excel 2003?	EX 4
Project One - Extreme Blading Second Quarter Sales	EX 5
Starting and Customizing Excel	EX 6
The Excel Worksheet	EX 9
The Worksheet	EX 10
Worksheet Window	EX 11
Menu Bar	EX 11
Standard Toolbar and Formatting Toolbar	EX 13
Formula Bar	EX 14
Status Bar	EX 14
Speech Recognition and Speech Playback	EX 15
Selecting a Cell	EX 16
Entering Text	EX 16
Entering the Worksheet Titles	EX 17
Correcting a Mistake while Typing	EX 18
AutoCorrect	EX 18
Entering Column Titles	EX 19
Entering Row Titles	EX 20
Entering Numbers	EX 21
Calculating a Sum	EX 23
Using the Fi11 Handle to Copy a Cell to Adjacent Cells	EX 24
Determining Multiple Totals at the Same Time	EX 26
Formatting the Worksheet	EX 28
Font Type, Style, Size, and Color	EX 28
Changing the Font Type	EX 29
Bolding a Cell	EX 30
Increasing the Font Size	EX 30
Changing the Font Color of a Cell Entry	EX 31
Centering a Cell Entry across Columns by Merging Cells	EX 32
Formatting the Worksheet Subtitle	EX 33
Using AutoFormat to Format the Body of a Worksheet	EX 34
Using the Name Box to Select a Cell	EX 36
Adding a 3-D Clustered Column Chart to the Worksheet	EX 38
Saving a Workbook	EX 42
Printing a Worksheet	EX 44
Quitting Excel	EX 46
Starting Excel and Opening a Workbook	EX 47
AutoCalculate	EX 48
Correcting Errors	EX 50
Correcting Errors While You Are Typing Data into a Cell	EX 50
Correcting Errors After Entering Data into a Cell	EX 50
Undoing the Last Cell Entry	EX 51
Clearing a Cell or Range of Cells	EX 52
Clearing the Entire Worksheet	EX 52
Excel Help System	EX 53
Obtaining Help Using the Type a Question for Help Box on the Menu Bar	EX 53
Quitting Excel	EX 54
Project Summary	EX 55
What You Should Know	EX 55
Learn It Online	EX 56
Apply Your Knowledge	EX 57
In the Lab	EX 58
Cases and Places	EX 63
Project Two Formulas, Functions, Formatting, and Web Queries
Objectives	EX 66
Introduction	EX 66
Project Two - Blue Chip Stock Club Investment Analysis	EX 67
Starting and Customizing Excel	EX 69
Entering the Titles and Numbers into the Worksheet	EX 70
Entering Formulas	EX 72
Order of Operations	EX 74
Entering Formulas Using Point Mode	EX 75
Copying Formulas Using the Fill Handle	EX 77
Smart Tags and Option Buttons	EX 78
Determining Totals Using the AutoSum Button	EX 79
Determining the Total Percent Gain/Loss	EX 80
Using the AVERAGE, MAX, and MIN Functions	EX 80
Determining the Average of a Range of Numbers	EX 81
Determining the Highest Number in a Range of Numbers	EX 82
Determining the Lowest Number in a Range of Numbers	EX 84
Copying the AVERAGE, MAX, and MIN Functions	EX 87
Saving a Workbook Using the Same File Name	EX 88
Verifying Formulas Using Range Finder	EX 89
Formatting the Worksheet	EX 90
Changing the Font and Centering the Worksheet Title and Subtitle	EX 91
Changing the Background and Font Colors and Applying a Box Border to the Worksheet Title and Subtitle	EX 94
Applying Formats to the Column Titles	EX 96
Centering the Stock Symbols and Formatting the Dates and Numbers in the Worksheet	EX 96
Formatting Numbers Using the Formatting Toolbar	EX 98
Applying a Thick Bottom Border to the Row above the Total Row and Bolding the Total Row Titles	EX 100
Formatting Numbers Using the Format Cells Command on the Shortcut Menu	EX 100
Formatting Numbers Using the Percent Style Button and Increase Decimal Button	EX 102
Conditional Formatting	EX 103
Changing the Widths of Columns and Heights of Rows	EX 107
Changing the Widths of Columns	EX 107
Changing the Heights of Rows	EX 110
Checking Spelling	EX 111
Previewing and Printing the Worksheet	EX 113
Printing a Section of the Worksheet	EX 116
Displaying and Printing the Formulas Version of the Worksheet	EX 118
Changing the Print Scaling Option Back to 100%	EX 120
Importing External Data from a Web Source Using a Web Query	EX 120
Changing the Worksheet Names	EX 124
E-Mailing a Workbook from within Excel	EX 125
Saving the Workbook and Quitting Excel	EX 126
Project Summary	EX 127
What You Should Know	EX 127
Learn It Online	EX 129
Apply Your Knowledge	EX 130
In the Lab	EX 132
Cases and Places	EX 142
Project Three What-If Analysis, Charting, and Working with Large Worksheets
Objectives	EX 146
Introduction	EX 146
Project Three - Aquatics Wear Six-Month Financial Projection	EX 148
Starting and Customizing Excel	EX 149
Bolding the Font of the Entire Worksheet	EX 150
Entering the Worksheet Titles and Saving the Workbook	EX 150
Rotating Text and Using the Fill Handle to Create a Series	EX 151
Copying a Cell's Format Using the Format Painter Button	EX 154
Increasing the Column Widths and Indenting Row Titles	EX 155
Copying a Range of Cells to a Nonadjacent Destination Area	EX 157
Using Drag and Drop to Move or Copy Cells	EX 159
Using Cut and Paste to Move or Copy Cells	EX 159
Inserting and Deleting Cells in a Worksheet	EX 159
Inserting Rows	EX 159
Inserting Columns	EX 161
Inserting Single Cells or a Range of Cells	EX 161
Deleting Columns and Rows	EX 161
Deleting Individual Cells or a Range of Cells	EX 162
Entering Numbers with Format Symbols	EX 162
Freezing Worksheet Titles	EX 163
Entering the Projected Monthly Total Net Revenues	EX 164
Displaying a System Date	EX 165
Absolute versus Relative Addressing	EX 168
Entering a Formula Containing Absolute Cell References	EX 169
Making Decisions - The IF Function	EX 170
Entering the Remaining Formulas	EX 173
Copying Formulas with Absolute Cell References	EX 174
Determining Row Totals in Nonadjacent Cells	EX 175
Unfreezing Worksheet Titles and Saving the Workbook	EX 175
Nested Forms of the IF Function	EX 176
Formatting the Worksheet	EX 177
Formatting the Numbers	EX 177
Formatting the Worksheet Titles	EX 180
Showing the Drawing Toolbar	EX 181
Moving and Docking a Toolbar	EX 182
Adding a Drop Shadow	EX 183
Formatting Nonadjacent Cells	EX 184
Formatting the Assumptions Table	EX 186
Hiding the Drawing Toolbar and Saving the Workbook	EX 187
Adding a 3-D Pie Chart to the Workbook	EX 187
Drawing a 3-D Pie Chart on a Separate Chart Sheet	EX 188
Formatting the Chart Title and Data Labels	EX 192
Changing the Colors of Slices in a Pie Chart	EX 193
Exploding the 3-D Pie Chart	EX 194
Rotating and Tilting the 3-D Pie Chart	EX 194
Showing Leader Lines with the Data Labels	EX 197
Renaming and Reordering the Sheets and Coloring Their Tabs	EX 197
Checking Spelling, Saving, Previewing, and Printing the Workbook	EX 199
Checking Spelling in Multiple Sheets	EX 199
Previewing and Printing the Workbook	EX 200
Changing the View of the Worksheet	EX 201
Shrinking and Magnifying the View of a Worksheet or Chart	EX 201
Splitting the Window into Panes	EX 202
What-If Analysis	EX 204
Analyze Data in a Worksheet by Changing Values	EX 204
Goal Seeking	EX 206
Quitting Excel	EX 208
Project Summary	EX 208
What You Should Know	EX 209
Learn It Online	EX 210
Apply Your Knowledge	EX 211
In the Lab	EX 212
Cases and Places	EX 222
Web Feature Creating Static and Dynamic Web Pages Using Excel
Objectives	EX 225
Introduction	EX 225
Using Web Page Preview and Saving an Excel Workbook as a Static Web Page	EX 228
Web Page Preview	EX 228
Saving a Workbook as a Static Web Page in a New Folder	EX 230
File Management Tools in Excel	EX 232
Viewing the Static Web Page Using a Browser	EX 233
Saving an Excel Chart as a Dynamic Web Page	EX 234
Viewing and Manipulating the Dynamic Web Page Using a Browser	EX 236
Modifying the Worksheet on a Dynamic Web Page	EX 237
Web Feature Summary	EX 238
What You Should Know	EX 239
In the Lab	EX 239
Project Four Financial Functions, Data Tables, Amortization Schedules, and Hyperlinks
Objectives	EX 242
Introduction	EX 242
Project Four - e-Money Lenders Loan Payment Calculator	EX 244
Starting and Customizing Excel	EX 245
Bolding the Entire Worksheet	EX 246
Entering the Section Title, Row Titles, System Date, and Saving the Workbook	EX 246
Adding Custom Borders and a Background Color to a Range	EX 248
Formatting Cells Before Entering Values	EX 249
Entering the Loan Data	EX 250
Creating Cell Names Based on Row Titles	EX 251
Determining the Loan Amount Using Names	EX 253
Determining the Monthly Payment Using Names	EX 254
Determining the Total Interest and Total Cost Using Names	EX 255
Entering New Loan Data	EX 256
Using a Data Table to Analyze Worksheet Data	EX 256
Entering the Data Table Titles and Column Titles	EX 257
Creating a Percent Series Using the Fill Handle	EX 258
Entering the Formulas in the Data Table	EX 259
Defining the Data Table	EX 261
Formatting the Data Table	EX 262
Adding a Pointer to the Data Table Using Conditional Formatting	EX 263
Creating an Amortization Schedule	EX 266
Changing Column Widths and Entering the Titles	EX 266
Creating a Series of Integers Using the Fill Handle	EX 267
Entering the Formulas in the Amortization Schedule	EX 268
Entering the Total Formulas in the Amortization Schedule	EX 272
Formatting New Loan Data	EX 273
Entering New Loan Data	EX 275
Adding a Hyperlink to the Worksheet	EX 277
Assigning a Hyperlink to an Embedded Graphic	EX 277
Formatting and Re-Sizing a Graphic	EX 280
Displaying and Printing a Hyperlinked File	EX 280
Printing Sections of the Worksheet	EX 281
Setting Up a Worksheet to Print	EX 282
Printing a Section of a Worksheet Using the Set Print Area Command	EX 283
Naming Print Areas	EX 284
Protecting the Worksheet	EX 286
Hiding and Unhiding Rows, Columns, and Sheets	EX 289
Hiding and Unhiding Workbooks	EX 290
Formula Checking	EX 292
Background Formula Checking	EX 292
Quitting Excel	EX 293
Project Summary	EX 293
What You Should Know	EX 294
Learn It Online	EX 295
Apply Your Knowledge	EX 296
In the Lab	EX 297
Cases and Places	EX 304
Project Five Creating, Sorting, and Querying a List Objectives	EX 306
Introduction	EX 306
Project Five - Soccer Gear Sales Rep List	EX 308
Starting and Customizing Excel	EX 310
Creating a List	EX 311
Entering the Column Headings for a List	EX 311
Defining a Range as a List	EX 313
Formatting the Insert Row in an Empty List	EX 315
Data Validation	EX 315
Entering Records into a List Using a Data Form	EX 318
Moving from Field to Field in a Data Form	EX 321
Adding Computational Fields to the List	EX 321
Adding New Fields to a List	EX 321
Using Excel's VLOOKUP Function to Determine Letter Grades	EX 323
Guidelines for Creating a List in Excel	EX 327
The List Toolbar	EX 328
The List Button	EX 328
The Toggle Total Row Button	EX 329
The Print List Button	EX 330
Using a Data Form to View Records and Change Data	EX 331
Sorting a List	EX 333
Sorting a List in Ascending Sequence by Name Using the Sort Ascending Button	EX 333
Sorting a List in Descending Sequence by Name Using the Sort Descending Button	EX 334
Sorting a List Using the Sort Command on a Column Heading List	EX 335
Sorting a List on Multiple Fields Using the Sort Command on the List Button Menu	EX 335
Sorting a List on More than Three Fields	EX 338
Displaying Automatic Subtotals in a List	EX 338
Zooming Out on a Subtotaled List and Using the Outline Feature	EX 340
Removing Automatic Subtotals from a List	EX 342
Finding Records Using a Data Form	EX 343
Using Wildcard Characters in Comparison Criteria	EX 345
Using Computed Criteria	EX 346
Querying a List Using AutoFilter	EX 346
Showing All Records in a List	EX 348
Entering Custom Criteria Using AutoFilter	EX 348
Using a Criteria Range on the Worksheet	EX 350
Creating a Criteria Range on the Worksheet	EX 350
Query a List Using the Advanced Filter Command	EX 351
Extracting Records	EX 354
Creating an Extract Range and Extracting Records	EX 354
Enabling AutoFilter	EX 356
More About the Criteria Range	EX 356
A Blank Row in the Criteria Range	EX 356
Using Multiple Comparison Criteria with the Same Field	EX 357
Comparison Criteria in Different Rows and Below Different Fields	EX 357
Using Database Functions	EX 358
Using the SUMIF and COUNTIF Functions	EX 359
Printing the Worksheet and Saving the Workbook	EX 361
Saving a Workbook in Different File Formats	EX 361
Quitting Excel	EX 365
Project Summary	EX 365
What You Should Know	EX 366
Learn It Online	EX 367
Apply Your Knowledge	EX 368
In the Lab	EX 369
Cases and Places	EX 376
Project Six Creating Templates and Working with Multiple Worksheets and Workbooks
Objectives	EX 378
Introduction	EX 378
Project Six - Awesome Images Profit Potential	EX 380
Starting and Customizing Excel	EX 381
Creating the Template	EX 382
Bolding the Font and Adjusting the Row Heights and Column Widths of the Template	EX 383
Entering the Title, Subtitle, and Row Titles in the Template	EX 383
Entering Column Titles and the System Date in the Template	EX 384
Entering Dummy Data in the Template	EX 385
The ROUND Function and Entering Formulas in the Template	EX 386
Saving the Template	EX 390
Formatting the Template	EX 391
Formatting the Template Title and Subtitle	EX 392
Formatting the Column Titles and Total Row	EX 393
Applying Number Formats Using the Format Dialog Box	EX 393
Creating and Assigning a Custom Format Code	EX 395
Creating a New Style	EX 398
Applying a Style	EX 400
Finding a Synonym Using the Research Task Pane	EX 402
Spell Checking, Saving, and Printing the Template	EX 403
Using Templates	EX 403
Creating a Workbook from a Template	EX 404
Adding a Worksheet to a Workbook	EX 406
Copying the Contents of a Worksheet to Other Worksheets in a Workbook	EX 407
Drilling an Entry through Worksheets	EX 408
Modifying the Cleveland Sheet	EX 410
Modifying the Lexington Sheet	EX 412
Modifying the San Diego Sheet	EX 413
Referencing Cells in Other Sheets in a Workbook	EX 414
Entering a Sheet Reference	EX 414
Modifying the Company Sheet	EX 414
Drawing the 3-D Cylinder Chart	EX 419
Formatting the 3-D Cylinder Chart	EX 422
Adding a Chart Title Using the WordArt Tool	EX 423
Adding a Text Box and an Arrow to the Chart	EX 427
Assigning a Comment to a Cell	EX 428
Researching a Topic Using the Research Task Pane	EX 430
Adding a Header, Changing the Margins, and Printing the Workbook	EX 432
Printing All Worksheets in a Workbook	EX 437
Printing Nonadjacent Worksheets in a Workbook	EX 439
Inserting and Removing Page Breaks	EX 439
Hiding Page Breaks	EX 442
The Find and Replace Commands	EX 443
The Find Command	EX 443
The Replace Command	EX 445
Quitting Excel	EX 446
Consolidating Data by Linking Workbooks	EX 446
Searching for and Opening Workbooks	EX 447
Creating a Workspace File	EX 449
Consolidating Data by Linking Workbooks	EX 451
Updating Links	EX 452
Closing All Workbooks at One Time and Quitting Excel	EX 452
Project Summary	EX 452
What You Should Know	EX 453
Learn It Online	EX 454
Apply Your Knowledge	EX 455
In the Lab	EX 456
Cases and Places	EX 463
Integration Feature Object Linking and Embedding (OLE) and Web Discussions
Objectives	EX 465
Introduction	EX 465
Opening a Word Document and an Excel Workbook	EX 467
Linking an Excel Worksheet and Chart to a Word Document	EX 469
Printing and Saving the Word Document with the Linked Worksheet and Chart	EX 471
Editing the Linked Worksheet	EX 472
Saving the Word Document as a Web Page and Using a Discussion Server	EX 473
Replying to a Comment on a Discussion Server	EX 476
Integration Feature Summary	EX 479
What You Should Know	EX 479
In the Lab	EX 480
Appendix A Microsoft Excel Help System
Using the Excel Help System	APP 1
Navigating the Excel Help System	APP 2
Other Help Commands on the Help Menu	APP 8
Use Help	APP 10
Appendix B Speech and Handwriting Recognition and Speech Playback
Introduction	APP 11
Speech Recognition	APP 15
Handwriting Recognition	APP 19
Speech Playback	APP 22
Appendix C Publishing Office Web Pages to a Web Server
Using Web Folders to Publish Office Web Pages	APP 24
Using FTP to Publish Office Web Pages	APP 24
Appendix D Changing Screen Resolution and Resetting the Excel Toolbars and Menus
Changing Screen Resolution	APP 25
Resetting the Excel Toolbars and Menus	APP 27
Appendix E Microsoft Office Specialist Certification
What Is Microsoft Office Specialist Certification?	APP 31
Why Should You Be Certified?	APP 31
The Microsoft Office Specialist Certification Exams	APP 32
How to Prepare for the Microsoft Office Specialist Certification Exams	APP 32
How to Find an Authorized Testing Center	APP 32
Shelly Cashman Series Microsoft Office Specialist Center	APP 32
Microsoft Office Specialist Certification Maps	APP 33
Index	IND 1
Quick Reference Summary	QR 1