New Perspectives on Microsoft Office Excel 2003, Introductory, CourseCard Edition

by Parsons,June Jamrich

• ISBN13: 9781418839062
• ISBN10: 141883906X
• Edition: 1st
• Format: Paperback
• Copyright: 2005-03-24
• Publisher: Course Technology

Summary

Part of the New Perspectives series, this text offers a case-based, problem-solving approach and innovative technology for meaningful learning of Microsoft Excel 2003.

Table of Contents

Preface	v
Office	1	(2)
Read This Before You Begin	2	(1)
Using Common Features of Microsoft Office 2003	3
Preparing Promotional Materials	3	(2)
Exploring Microsoft Office 2003	4	(1)
Integrating Office Programs	5	(3)
Starting Office Programs	5	(3)
Switching Between Open Programs and Files	8	(1)
Exploring Common Window Elements	9	(1)
Using the Window Sizing Buttons	9	(2)
Using Menus and Toolbars	11	(1)
Viewing Personalized Menus and Toolbars	11	(4)
Using Task Panes	14	(1)
Opening and Closing Task Panes	14	(1)
Navigating Among Task Panes	15	(1)
Using the Research Task Pane	16	(6)
Working with Files	18	(1)
Creating a File	19	(1)
Saving a File	19	(2)
Closing a File	21	(1)
Opening a File	22	(5)
Getting Help	23	(1)
Using Screen Tips	23	(1)
Using the Type a Question for Help Box	23	(2)
Using the Help Task Pane	25	(2)
Using Microsoft Office Online	27	(2)
Printing a File	29	(1)
Exiting Programs	30	(1)
Quick Check	31	(1)
Tutorial Summary	31	(1)
Key Terms	31	(1)
Review Assignments	31	(1)
SAM Assessment and Training	32	(1)
Quick Check Answers	32
Excel Level I Tutorials	1	(192)
Read This Before You Begin	2	(1)
Using Excel to Manage Data	3	(48)
Creating a Sales Order Report	3	(1)
Session 1.1	4	(1)
Introducing Excel	4	(3)
Understanding Spreadsheets	4	(1)
Parts of the Excel Window	5	(2)
Navigating a Worksheet	7	(2)
Navigating Between Worksheets	9	(1)
Developing a Worksheet	9	(2)
Entering Data into a Worksheet	11	(8)
Entering Text	11	(1)
Entering Several Lines of Text Within a Cell	12	(1)
Entering Dates	13	(1)
Entering Values	14	(1)
Entering Formulas	14	(3)
Using AutoComplete	17	(2)
Changing the Size of a Column or Row	19	(4)
Session 1.1 Quick Check	23	(1)
Session 1.2	23	(1)
Working with Ranges	23	(3)
Selecting Ranges	24	(1)
Other Selection Techniques	25	(1)
Moving a Selection of Cells	26	(2)
Calculating Sums with AutoSum	28	(2)
Working with Rows and Columns	30	(3)
Inserting a Row or Column	30	(2)
Deleting a Row or Column	32	(1)
Editing Your Worksheet	33	(1)
Undoing an Action	34	(1)
Working with Worksheets	35	(2)
Adding and Removing Worksheets	35	(1)
Renaming a Worksheet	36	(1)
Moving a Worksheet	37	(1)
Using the Spell Checker	37	(1)
Previewing and Printing a Worksheet	38	(5)
Working with Portrait and Landscape Orientation	40	(1)
Printing the Worksheet Formulas	41	(2)
Session 1.2 Quick Check	43	(1)
Tutorial Summary	43	(1)
Key Terms	44	(1)
Review Assignments	44	(2)
Case Problems	46	(3)
Internet Assignments	49	(1)
SAM Assessment and Training	50	(1)
Lab Assignments	50	(1)
Quick Check Answers	50	(1)
Working with Formulas and Functions	51	(44)
Developing a Family Budget	51	(1)
Session 2.1	52	(1)
Working with Excel Functions	52	(4)
Copying and Pasting Formulas	56	(6)
Using Relative and Absolute References	58	(3)
Working with Other Paste Options	61	(1)
Changing the Magnification of a Worksheet	62	(3)
Using the Insert Function Dialog Box	65	(3)
Filling in Formulas and Series	68	(4)
Auto Fill Options	70	(1)
Filling a Series	70	(2)
Working with Date Functions	72	(1)
Session 2.1 Quick Check	73	(1)
Session 2.2 (Optional)	74	(1)
Working with Financial Functions	74	(8)
Using the PMT Function	77	(5)
Working with Logical Functions	82	(2)
Using the IF Function	82	(2)
Session 2.2 Quick Check	84	(1)
Tutorial Summary	84	(1)
Key Terms	85	(1)
Review Assignments	85	(2)
Case Problems	87	(6)
Internet Assignments	93	(1)
SAM Assessment and Training	93	(1)
Quick Check Answers	94	(1)
Developing a Professional-Looking Worksheet	95	(50)
Formatting a Sales Report	95	(1)
Session 3.1	96	(1)
Formatting Worksheet Data	96	(6)
Using the Formatting Toolbar	97	(2)
Copying Formats	99	(1)
Using the Format Cells Dialog Box	100	(2)
Changing Font Type, Size, Style, and Color	102	(3)
Aligning Cell Contents	105	(3)
Indenting and Wrapping Text	106	(2)
Other Formatting Options	108	(1)
Working with Cell Borders and Backgrounds	108	(6)
Adding a Cell Border	108	(3)
Applying Background Colors and Patterns	111	(3)
Session 3.1 Quick Check	114	(1)
Session 3.2	114	(1)
Formatting the Worksheet	114	(5)
Merging Cells into One Cell	115	(1)
Hiding Rows, Columns and Worksheets	116	(1)
Adding a Background Image	117	(2)
Formatting Sheet Tabs	119	(1)
Clearing and Replacing Formats	119	(3)
Using Styles	122	(2)
Creating a Style	122	(1)
Applying a Style	123	(1)
Using AutoFormat	124	(2)
Formatting the Printed Worksheet	126	(9)
Defining the Page Setup	126	(2)
Working with Headers and Footers	128	(3)
Working with the Print Area and Page Breaks	131	(4)
Session 3.2 Quick Check	135	(1)
Tutorial Summary	135	(1)
Key Terms	135	(1)
Review Assignments	135	(2)
Case Problems	137	(5)
Internet Assignments	142	(1)
SAM Assessment and Training	142	(1)
Quick Check Answers	143	(2)
Working with Charts and Graphics	145	(48)
Charting Sales Data	145	(1)
Session 4.1	146	(1)
Excel Charts	146	(1)
Creating a Chart Using the Chart Wizard	147	(11)
Choosing a Chart Type	149	(2)
Choosing a Data Source	151	(2)
Choosing Chart Options	153	(3)
Choosing the Chart Location	156	(2)
Moving and Resizing an Embedded Chart	158	(1)
Updating a Chart	159	(1)
Creating a Pie Chart	159	(4)
Rotating the Pie Chart	161	(1)
Exploding a Slice of a Pie Chart	162	(1)
Session 4.1 Quick Check	163	(1)
Session 4.2	164	(1)
Modifying a Chart	164	(4)
Editing the Data Source	164	(2)
Changing the Chart Location	166	(1)
Changing Chart Options	167	(1)
Formatting Chart Elements	168	(10)
Formatting Chart Text	168	(2)
Inserting Unattached Text	170	(2)
Working with Colors and Fills	172	(2)
Using a Graphic Image as a Background	174	(2)
Changing the Axis Scale	176	(2)
Working with Three-Dimensional Charts	178	(2)
Using the Drawing Toolbar	180	(5)
Displaying the Drawing Toolbar	181	(1)
Working with AutoShapes	181	(2)
Formatting an AutoShape	183	(2)
Printing Your Charts	185	(1)
Session 4.2 Quick Check	186	(1)
Tutorial Summary	186	(1)
Key Terms	186	(1)
Review Assignments	187	(1)
Case Problems	187	(5)
Internet Assignments	192	(1)
SAM Assessment and Training	192	(1)
Quick Check Answers	192	(1)
Excel Level II Tutorials	193
Read This Before You Begin	194	(1)
Working with Excel Lists	195	(58)
Tracking Vehicle Data	195	(1)
Session 5.1	196	(1)
Introduction to Lists	196	(1)
Planning and Creating a List	197	(1)
Freezing Rows and Columns	198	(1)
Using Find and Replace	199	(1)
Creating a List Range	200	(2)
Sorting Data	202	(5)
Sorting a List Using Sort Buttons on the Standard Toolbar	203	(1)
Sorting a List Using the AutoFilter Sort Options	204	(1)
Sorting a List Using the Sort Commands	205	(2)
Maintaining a List	207	(5)
Using the Insert Row to Add a Record	207	(1)
Using a Data Form to Add a Record	208	(2)
Using a Data Form to Search for a Record	210	(1)
Using a Data Form to Delete a Record	211	(1)
Session 5.1 Quick Check	212	(1)
Session 5.2	212	(1)
Filtering a List Using AutoFilters	212	(3)
Using Custom AutoFilters to Specify More Complex Criteria	215	(2)
Using Conditional Formatting	217	(2)
Using the Total Row to Calculate Totals in a List	219	(3)
Inserting Subtotals into a List	222	(2)
Using the Subtotals Outline View	224	(2)
Session 5.2 Quick Check	226	(1)
Session 5.3	226	(1)
Creating and Using PivotTables to Summarize a List	226	(2)
Creating a PivotTable	228	(6)
Laying Out a PivotTable Directly on a Worksheet	231	(3)
Changing the Layout of a PivotTable	234	(3)
Formatting Numbers in a PivotTable	234	(1)
Modifying the Layout of a PivotTable	235	(1)
Sorting Items Within a PivotTable	236	(1)
Adding a Field to a PivotTable	237	(1)
Adding a Page View of a PivotTable	238	(3)
Removing a Field from a PivotTable	240	(1)
Hiding Field Items on a PivotTable	240	(1)
Refreshing a PivotTable	241	(2)
Creating a PivotChart	243	(1)
Session 5.3 Quick Check	244	(1)
Tutorial Summary	245	(1)
Key Terms	245	(1)
Review Assignments	245	(1)
Case Problems	246	(5)
Internet Assignments	251	(1)
SAM Assessment and Training	251	(1)
Quick Check Answers	251	(2)
Working with Multiple Worksheets and Workbooks	253	(48)
Tracking Cash Flow	253	(1)
Session 6.1	254	(1)
Using Multiple Worksheets	254	(1)
Grouping Worksheets	255	(3)
Entering Formulas in a Worksheet Group	255	(1)
Formatting a Worksheet Group	256	(2)
Referencing Cells and Ranges in Other Worksheets	258	(2)
Consolidating Data from Multiple Worksheets Using a 3-D Reference	260	(5)
Copying Information Across Worksheets	261	(1)
Summing Values Across Worksheets	262	(3)
Printing Worksheet Groups	265	(1)
Session 6.1 Quick Check	266	(1)
Session 6.2	266	(1)
Using Templates	266	(1)
Creating a Workbook Based on a Template	267	(2)
Creating and Saving a Custom Workbook Template	269	(6)
Using the Templates Folder	271	(1)
Using the LBC Cash Flow Template	272	(1)
Editing a Template	273	(1)
Changing the Default Workbook Template	273	(1)
Organizing Folders and Files	274	(1)
Session 6.2 Quick Check	275	(1)
Session 6.3	275	(1)
Using Data from Multiple Workbooks	275	(3)
Managing Multiple Workbooks	277	(1)
Working with Linked Workbooks	278	(6)
Editing Workbook References	279	(4)
Working with a List of Links	283	(1)
Using Lookup Tables	284	(5)
Using Lookup Functions	286	(3)
Creating an Excel Workspace	289	(2)
Session 6.3 Quick Check	291	(1)
Tutorial Summary	291	(1)
Key Terms	291	(1)
Review Assignments	292	(1)
Case Problems	293	(6)
Internet Assignments	299	(1)
SAM Assessment and Training	299	(1)
Quick Check Answers	299	(2)
Working with Excel's Editing and Web Tools	301	(54)
Collaborating on a Workbook and Web Page	301	(1)
Session 7.1	302	(1)
Splitting a Worksheet into Panes	302	(1)
Auditing Formulas	303	(5)
Tracing Precedent Cells	305	(1)
Tracing Dependent Cells	306	(2)
Tracing and Fixing Errors	308	(6)
Using the Watch Window	314	(1)
Evaluating a Complex Formula	314	(1)
Session 7.1 Quick Check	314	(1)
Session 7.2	315	(1)
Adding Comments to a Workbook	315	(2)
Creating a Shared Workbook	317	(4)
Sharing a Workbook	317	(2)
Resolving Conflicts	319	(2)
Tracking Changes	321	(7)
Reviewing and Deleting Comments	321	(2)
Reviewing Changes	323	(3)
Accepting and Rejecting Changes	326	(2)
Mailing a Workbook	328	(2)
Mailing a Workbook for Review	329	(1)
Routing a Workbook	330	(1)
Merging Workbooks	330	(2)
Online Meetings and Web Discussions	332	(1)
Session 7.2 Quick Check	332	(1)
Session 7.3	333	(1)
Working with the Web	333	(2)
Introducing the World Wide Web	333	(1)
Understanding Web Pages, Browsers, and HTML	333	(1)
Creating Web Pages in Excel	334	(1)
Publishing a Noninteractive Workbook on a Web Page	335	(5)
Choosing Between a Noninteractive and Interactive Web Page	336	(1)
Setting the Page Title	337	(1)
Setting the Publishing Options	338	(2)
Creating and Using Hyperlinks	340	(3)
Inserting a Hyperlink	340	(1)
Editing and Using a Hyperlink	341	(2)
Setting Excel's Web Options	343	(1)
Saving a Workbook as an XML File	344	(1)
Session 7.3 Quick Check	344	(1)
Tutorial Summary	345	(1)
Key Terms	345	(1)
Review Assignments	345	(1)
Case Problems	346	(6)
Internet Assignments	352	(1)
SAM Assessment and Training	352	(1)
Quick Check Answers	352	(3)
Developing an Excel Application	355
Creating a Stock Reporter	355	(1)
Session 8.1	356	(1)
Planning an Excel Application	356	(1)
Validating Data Entry	357	(7)
Specifying a Data Type and Acceptable Values	357	(3)
Specifying an Input Message	360	(1)
Specifying an Error Alert Style and Message	361	(2)
Circling Invalid Data	363	(1)
Protecting a Worksheet and Workbook	364	(4)
Locking and Unlocking Cells	364	(1)
Protecting a Worksheet	365	(2)
Protecting a Workbook	367	(1)
Unprotecting a Worksheet	367	(1)
Session 8.1 Quick Check	368	(1)
Session 8.2	368	(1)
Naming Cells and Ranges	368	(9)
Creating a Name	369	(4)
Replacing Cell References with Names	373	(2)
Changing a Name Definition	375	(2)
Session 8.2 Quick Check	377	(1)
Session 8.3	377	(1)
Working with Macros	377	(1)
Protecting Yourself Against Macro Viruses	378	(1)
Working with Digital Certificates	378	(1)
Setting the Security Level	378	(1)
Recording a Macro	379	(6)
Running a Macro	385	(4)
Fixing Macro Errors	386	(1)
Creating the NewData Macro	386	(3)
Working with the Visual Basic Editor	389	(4)
Understanding the Structure of Macros	390	(1)
Writing a Macro Command	391	(1)
Printing the Macro Code	392	(1)
Creating Macro Buttons	393	(4)
Session 8.3 Quick Check	397	(1)
Tutorial Summary	397	(1)
Key Terms	397	(1)
Review Assignments	397	(2)
Case Problems	399	(8)
Internet Assignments	407	(1)
SAM Assessment and Training	407	(1)
Quick Check Answers	408
Appendix A Working with Logical Functions, Database Functions, and Advanced Filtering	1	(1)
Reviewing Employee Data	1	(1)
Working with Excel's Logical Functions	2	(7)
Using the IF Function	2	(3)
Using the and Function	5	(2)
Creating Nested IF Functions	7	(2)
Calculating Conditional Counts and Sums	9	(2)
Using the Countif Function	9	(1)
Using the SUMIF Function	10	(1)
Advanced Filtering	11	(3)
Setting Advanced Filtering Criteria	11	(2)
Copying Filtered Records to a New Location	13	(1)
Using Database Functions to Summarize an Excel List	14	(3)
Appendix Summary	17	(1)
Key Terms	17	(1)
Review Assignments	17	(1)
Case Problems	18
Appendix B Integrating Excel with Other Windows Programs	1	(1)
Creating Integrated Documents	1	(1)
Methods of Integration	2	(1)
Copying and Pasting Data	2	(1)
Object Linking and Embedding	3	(1)
Linking an Excel Worksheet to a Word Document	3	(3)
Updating a Linked Object	6	(1)
Embedding an Object	7	(1)
Modifying an Embedded Object	8	(1)
Appendix Summary	9	(1)
Key Terms	10	(1)
Review Assignments	10	(1)
Case Problems	11
Glossary/Index	1	(12)
Task Reference	13	(9)
Microsoft Office Specialist Certification Grid	22