| Acknowledgments |
xxiv |
| Part I Getting Started with Microsoft Excel 2000 |
1 |
| Chapter 1 Getting Your Bearings |
3 |
| Starting Microsoft Excel 2000 |
4 |
| A Tour of the Excel Workspace |
5 |
| The Workbook Window |
5 |
| The Menu Bar |
13 |
| Shortcut Menus |
17 |
| The Formula Bar |
19 |
| The Status Bar |
20 |
| The Pointer |
21 |
| Exiting Microsoft Excel 2000 |
22 |
| Chapter 2 Getting Help |
23 |
| Meet the Assistant |
24 |
| Firing the Assistant |
27 |
| Getting Answers to Unasked Questions |
27 |
| Selecting Office Assistant Options |
28 |
| Using the Help System |
31 |
| Navigating through the Help System |
31 |
| Getting Help on the World Wide Web |
33 |
| Help for Lotus 1-2-3 Users |
33 |
| Getting Context-Sensitive Help |
34 |
| Identifying Toolbar Buttons with ScreenTips |
35 |
| Chapter 3 Toolbars and Menus |
37 |
| Introducing Toolbars |
38 |
| "Rafting" Toolbars |
38 |
| Adding More Toolbars to Your Screen |
40 |
| Positioning Toolbars and Menu Bars on Your Screen |
41 |
| Customizing Toolbars and Menus |
43 |
| Removing and Adding Buttons and Commands |
44 |
| Creating New Toolbars |
53 |
| Deleting Custom Toolbars |
54 |
| Creating New Menus |
54 |
| Saving Toolbar and Menu Changes |
55 |
| Changing the Look of Buttons |
56 |
| Creating a Custom Button |
57 |
| Copying Button Faces |
58 |
| Editing and Button Size |
58 |
| Attaching a Toolbar to a Workbook |
58 |
| Chapter 4 Managing Files |
61 |
| Working with Files |
62 |
| Creating a New File |
62 |
| Creating a New File Based on an Existing File |
63 |
| Saving Files |
65 |
| Saving the Entire Workspace |
76 |
| Opening Files |
77 |
| Searching for Files |
83 |
| Adding Additional Search Criteria |
84 |
| Importing and Exporting Files |
86 |
| Using the Open and Save As Commands to Import Files |
86 |
| Importing and Exporting Text Files |
87 |
| Exporting Files to Earlier Versions of Excel |
88 |
| Sharing Data with Microsoft Excel for the Macintosh |
88 |
| Part II Worksheets |
91 |
| Chapter 5 Worksheet Basics |
93 |
| Moving Around the Worksheet |
94 |
| Selecting with the Mouse |
94 |
| Selecting with the Keyboard |
100 |
| Entering Data |
103 |
| Simple Numeric and Text Values |
103 |
| Entering Numeric Values |
103 |
| Entering Text Values |
106 |
| Making Entries in Ranges |
108 |
| Correcting Errors in Entries |
109 |
| Protecting Your Data |
111 |
| Unlocking Individual Cells |
112 |
| Hiding Cells and Sheets |
113 |
| Entering Passwords |
114 |
| Removing Protection |
114 |
| Chapter 6 Building Formulas |
115 |
| Creating Formulas |
116 |
| Precedence of Operators |
116 |
| Matching Parentheses |
117 |
| Using Cell References in Formulas |
117 |
| Editing Formulas |
123 |
| Using Numeric Text in Formulas |
124 |
| Text Values |
125 |
| Using Functions: A Preview |
126 |
| Formula Bar Formatting |
129 |
| Creating Formulas Using Plain Language |
129 |
| Naming Cells and Ranges |
133 |
| Using Names in Formulas |
133 |
| Defining Names with the Name Box |
133 |
| Defining Names with the Name Command |
134 |
| Rules for Naming Cells and Ranges |
138 |
| Editing Names |
138 |
| Creating Names from Text Cells |
138 |
| Naming Constants and Formulas |
139 |
| Three-Dimensional Names |
140 |
| Pasting Names into Formulas |
141 |
| Applying Names to Formulas |
142 |
| Using Range Names with the Go To Command |
143 |
| Understanding Error Values |
144 |
| Working with Arrays |
144 |
| Introducing Arrays |
145 |
| Two-Dimensional Arrays |
146 |
| Array Formula Rules |
147 |
| Using Array Constants |
147 |
| Array Expansion |
148 |
| Calculating the Worksheet |
149 |
| Manual Recalculation |
149 |
| Copying Formulas and Pasting Values |
151 |
| Circular References |
151 |
| Intentional Circular References |
153 |
| Precision of Numeric Values |
154 |
| Chapter 7 Formatting a Worksheet |
155 |
| Assigning and Removing Formats |
157 |
| Formatting with Toolbars |
158 |
| Formatting Individual Characters |
159 |
| Using AutoFormat |
159 |
| Formatting Numbers and Text |
163 |
| The General Format |
164 |
| The Number Formats |
165 |
| The Currency Formats |
165 |
| The Accounting Formats |
166 |
| The Percentage Formats |
167 |
| The Fraction Formats |
167 |
| The Scientific (Exponential) Formats |
168 |
| The Text Format |
169 |
| The Special Formats |
169 |
| Creating Custom Numeric Formats |
170 |
| Aligning Cell Contents |
179 |
| The Horizontal Text Alignment Options |
179 |
| Centering Text across Columns |
180 |
| The Fill Option |
180 |
| The Justify and Wrap Text Options |
182 |
| The Vertical Text Alignment Options |
183 |
| The Orientation Options |
185 |
| The Shrink-To-Fit Option |
185 |
| Text-Alignment Prefix Characters |
186 |
| Formatting Fonts |
186 |
| Selecting a Typeface and a Size |
188 |
| Selecting Font Styles and Effects |
188 |
| Accounting Underlines |
189 |
| Selecting a Color |
189 |
| Adding Custom Borders |
190 |
| Selecting a Line Style |
190 |
| Selecting Line Color |
191 |
| Selecting Border Placement |
191 |
| Applying Two or More Borders at Once |
191 |
| Changing or Removing Borders |
192 |
| Applying Border Formats with the Borders Button |
193 |
| Adding Colors and Patterns |
195 |
| Using Shading |
196 |
| Applying Conditional Formatting |
198 |
| Merging Cells |
200 |
| Merging and Unmerging Cells |
201 |
| Controlling Column Width |
203 |
| Using the Mouse to Change Column Widths |
203 |
| Using the Column Commands |
205 |
| Controlling Row Height |
206 |
| Using the Mouse to Change Row Heights |
207 |
| Using the Row Commands |
207 |
| How Wrapping and Justifying Text Affect Row Height |
208 |
| Formatting with Styles |
208 |
| Applying a Style |
209 |
| Defining a Style |
210 |
| Modifying a Style |
212 |
| Overriding a Style with Direct Formatting Changes |
213 |
| Merging Styles from Different Workbooks |
213 |
| Deleting a Style |
214 |
| Using Template Files |
214 |
| Modifying a Template File |
217 |
| Controlling Display Options |
217 |
| Controlling the Display of the Formula Bar and Status Bar |
218 |
| Changing the Standard Font |
221 |
| Changing the Available Colors |
222 |
| Outlining Your Worksheets |
225 |
| Creating an Outline |
227 |
| Collapsing and Expanding Outline Levels |
229 |
| Displaying a Specific Outline Level |
231 |
| Selecting Only Visible Cells |
231 |
| Ungrouping and Grouping Columns and Rows |
232 |
| Removing an Outline |
232 |
| Chapter 8 Editing a Worksheet |
233 |
| Setting Workspace Options for Editing |
234 |
| The Move Selection After Enter Option |
234 |
| The Fixed Decimal Option |
235 |
| The Provide Feedback With Animation Option |
235 |
| Shortcuts for Entering and Editing |
236 |
| Moving and Copying with the Mouse |
238 |
| Inserting, Deleting, and Clearing Cellswith the Mouse |
239 |
| Filling and Creating Series with the Mouse |
241 |
| Editing Directly in Cells |
247 |
| Undoing, Redoing, and Repeating PreviousActions |
247 |
| Actions That Don't Affect Undo |
248 |
| Redoing What You've Undone |
249 |
| Repeating Your Last Action |
249 |
| Using Editing Commands |
250 |
| Clearing Cell Contents and Formats |
250 |
| Deleting Cells, Columns, and Rows |
251 |
| Inserting Columns and Rows |
254 |
| Cutting and Pasting |
256 |
| Copying and Pasting |
261 |
| Using Cut, Copy, Paste, and Clear inthe Formula Bar |
267 |
| Selective Pasting |
268 |
| Filling Cell Ranges with Data |
274 |
| Finding and Replacing Data |
278 |
| Using Find |
278 |
| Using Replace |
282 |
| Getting the Words Right |
283 |
| Fix Typing Errors on the Fly |
283 |
| Let Excel Help with the Typing |
285 |
| Check Your Spelling |
287 |
| Auditing and Documenting a Worksheet |
288 |
| Tracing Cell References |
288 |
| Adding Comments to Cells |
292 |
| Using the Go To Special Dialog Box |
294 |
| Chapter 9 Worksheets and Windows |
299 |
| Working with Workbook Windows |
300 |
| Managing Multiple Workbooks |
300 |
| Opening Multiple Windows for the SameWorkbook |
306 |
| Hiding and Protecting Workbooks |
308 |
| Working with Sheets |
311 |
| Sheet Basics |
311 |
| Moving and Copying Sheets in aWorkbook |
314 |
| Moving and Copying Sheets BetweenWorkbooks |
316 |
| Splitting Sheets into Panes |
317 |
| Zooming Worksheets |
322 |
| Custom Views |
324 |
| Editing Groups of Sheets Simultaneously |
326 |
| Filling a Group |
327 |
| Working with Linked Workbooks |
329 |
| Saving Linked Workbooks |
330 |
| Updating Links Without OpeningSupporting Workbooks |
333 |
| Redirecting Links |
333 |
| Linking Microsoft Excel 2000 to AnotherApplication |
334 |
| Copying, Cutting, and Pasting inLinked Workbooks |
334 |
| Severing Links Between Workbooks |
336 |
| Consolidating Worksheets |
337 |
| Consolidating by Position |
338 |
| Consolidating by Category |
341 |
| Creating Links to the Source Worksheets |
343 |
| Chapter 10 Graphics |
345 |
| Creating Graphic Objects |
346 |
| Drawing Constrained Objects |
348 |
| Types of Graphic Objects |
349 |
| Drawing Freehand Lines and Polygons |
350 |
| Working with Text Boxes |
353 |
| Working with AutoShapes |
355 |
| Creating WordArt |
358 |
| Selecting and Grouping Objects |
360 |
| Formatting Objects |
362 |
| Positioning Objects |
369 |
| Tools to Help You Position Objects onthe Worksheet |
371 |
| Cutting, Copying, and Sorting Objectswith Cells |
373 |
| Controlling the Display of Objects |
374 |
| Protecting Objects |
375 |
| Taking Pictures of Your Worksheets |
376 |
| Using the Camera Button |
376 |
| Using the Copy Picture Command |
377 |
| Using Graphics from Other Programs |
379 |
| The Picture Command |
379 |
| The Object Command |
380 |
| Using Clip Art |
382 |
| Importing Clips |
385 |
| Organizing Clips |
385 |
| Restoring Defaults |
387 |
| Chapter 11 Printing and Presenting |
389 |
| Specifying What to Print |
390 |
| Defining an Area to Print |
391 |
| Printing Multiple Copies |
391 |
| Controlling the Appearance of Your Pages |
392 |
| Printing Wide or Tall |
392 |
| Setting a Reduction Ratio |
392 |
| Specifying Paper Size and Print Quality |
393 |
| The First Page Number Setting |
393 |
| Setting Margins |
393 |
| Centering Your Work on the Page |
393 |
| Creating a Header and Footer |
394 |
| Setting Print Titles |
397 |
| Printing Gridlines |
398 |
| Translating Screen Colors to Black andWhite |
398 |
| Draft Quality |
398 |
| Printing Row and Column Headings |
398 |
| Printing Comments |
399 |
| Setting the Printing Order of Large PrintRanges |
399 |
| Setting Printer Driver Options |
399 |
| Adjusting Page Breaks with Page Break Preview |
400 |
| Removing Manual Page Breaks |
401 |
| Using Print Preview |
402 |
| Zooming In or Out |
403 |
| Adjusting Margins and Column Widths |
404 |
| Using the Report Manager |
404 |
| Defining the Report |
405 |
| Part III Analyzing Data |
409 |
| Chapter 12 Common Worksheet Functions |
411 |
| Getting More Help with Worksheet Functions |
412 |
| The Power of Functions |
413 |
| The Syntax of Functions |
414 |
| Using Arguments |
414 |
| Types of Arguments |
415 |
| Entering Functions in a Worksheet |
417 |
| Using the Insert Function Command |
417 |
| Inserting References |
419 |
| Selected Mathematical Functions |
419 |
| Logarithmic Functions |
427 |
| Trigonometric Functions |
428 |
| Engineering Functions |
430 |
| Text Functions |
430 |
| Logical Functions |
439 |
| Conditional Tests |
439 |
| Lookup and Reference Functions |
443 |
| Chapter 13 Dates and Times |
459 |
| How Excel Records Dates and Times |
460 |
| Entering Dates and Times |
461 |
| Entering a Series of Dates |
463 |
| Formatting Dates and Times |
466 |
| Combining Date and Time Formats |
468 |
| Creating Your Own Date and Time Formats |
469 |
| Using Date and Time Arithmetic |
471 |
| Date and Time Functions |
472 |
| Chapter 14 Financial Analysis |
479 |
| Functions for Calculating Investments |
480 |
| The PV Function |
480 |
| The NPV Function |
482 |
| The FV Function |
483 |
| The PMT Function |
483 |
| The IPMT Function |
484 |
| The PPMT Function |
484 |
| The NPER Function |
485 |
| Functions for Calculating the Rate of Return |
486 |
| The RATE Function |
486 |
| The IRR Function |
487 |
| The MIRR Function |
488 |
| Functions for Calculating Depreciation |
488 |
| The SLN Function |
488 |
| The DDB and DB Functions |
489 |
| The VDB Function |
490 |
| The SYD Function |
491 |
| Functions for Analyzing Securities |
491 |
| The DOLLARDE and DOLLARFR Functions |
493 |
| The ACCRINT and ACCRINTM Functions |
493 |
| The INTRATE and RECEIVED Functions |
494 |
| The PRICE, PRICEDISC, and PRICEMATFunctions |
495 |
| The DISC Function |
495 |
| The YIELD, YIELDDISC, and YIELDMATFunctions |
496 |
| The TBILLEQ, TBILLPRICE, andTBILLYIELD Functions |
497 |
| The COUPDAYBS, COUPDAYS,COUPDAYSNC, COUPNCD, COUPNUM,and COUPPCD Functions |
497 |
| The DURATION and MDURATIONFunctions |
499 |
| The ODDFPRICE, ODDFYIELD, ODDLPRICE, and ODDLYIELD Functions |
500 |
| Chapter 15 Statistical Analysis |
501 |
| Accessing the Analysis ToolPak |
502 |
| Analyzing Distributions of Data |
503 |
| Built-In Statistical Functions |
503 |
| Sample and Population Statistical Functions |
507 |
| The Descriptive Statistics Tool |
509 |
| The Histogram Tool |
512 |
| Functions That Analyze Rank andPercentile |
515 |
| The Rank and Percentile Tool |
518 |
| Generating Random Numbers |
519 |
| The Uniform Distribution Option |
520 |
| The Normal Distribution Option |
521 |
| The Bernoulli Distribution Option |
521 |
| The Binomial Distribution Option |
522 |
| The Poisson Distribution Option |
522 |
| The Discrete Distribution Option |
522 |
| The Patterned Distribution Option |
523 |
| Sampling a Population of Numbers |
523 |
| Calculating Moving Averages |
524 |
| Linear and Exponential Regression |
526 |
| Calculating Linear Regression Statistics |
527 |
| Calculating Exponential RegressionStatistics |
532 |
| Chapter 16 What-If Analysis |
535 |
| Data Tables |
536 |
| Data Tables Based on One Input Variable |
536 |
| Data Tables Based on Two Input Variables |
539 |
| Editing Tables |
541 |
| The Scenario Manager |
542 |
| Defining Scenarios |
544 |
| Using the Show Button to View Scenarios |
546 |
| Adding, Editing, and Deleting Scenarios |
547 |
| Merging Scenarios |
548 |
| Creating Scenario Reports |
552 |
| The Goal Seek Command |
556 |
| Precision and Multiple Solutions |
558 |
| The Solver |
559 |
| Stating the Objective |
561 |
| Specifying Variable Cells |
562 |
| Specifying Constraints |
562 |
| Saving and Reusing the Solver Parameters |
565 |
| Other Solver Options |
566 |
| Generating Reports |
568 |
| When the Solver Is Unable to Solve |
570 |
| Part IV Interfacing with the Rest of the World |
571 |
| Chapter 17 Integrating Applications with OLE |
573 |
| Linking vs. Embedding |
574 |
| Why and How to Embed |
575 |
| Why and How to Link |
578 |
| Automatic vs. Manual Links |
579 |
| Opening a File that Contains Links |
580 |
| Suspending Links |
580 |
| How Links Are Identified in Excel |
580 |
| Linking Text vs. Linking Objectsor Pictures |
581 |
| Activating a Link |
581 |
| Editing Links |
582 |
| Chapter 18 Sharing Files with Others |
583 |
| Sharing Files Using a Network |
584 |
| Saving and Retrieving Files on RemoteComputers |
584 |
| Retrieving Busy Files Automatically |
584 |
| Sharing Workbooks on a Network |
585 |
| Advanced Sharing Options |
588 |
| Tracking Changes |
590 |
| Reviewing Changes |
592 |
| Canceling the Shared Workbook Session |
593 |
| Combining Changes Made to Multiple Workbooks |
594 |
| Merging Workbooks |
594 |
| Mailing Workbooks Using Electronic Mail |
597 |
| Routing Workbooks to a Workgroup UsingElectronic Mail |
599 |
| Chapter 19 Hyperlinks and the Internet |
603 |
| Basic Internet Concepts |
604 |
| Using Hyperlinks |
607 |
| Internet Addressing |
607 |
| Creating a Hyperlink to a Web Page |
608 |
| Editing a Hyperlink |
610 |
| The Web Toolbar |
611 |
| Cruising with a Web Hyperlink |
611 |
| Copying a Link |
613 |
| More about Creating Hyperlinks |
614 |
| Making a Button or Drawing Objecta Hyperlink |
616 |
| Using the HYPERLINK Worksheet Function |
617 |
| Working with FTP Sites in Excel |
619 |
| Preparing for FTP Access |
620 |
| Opening a Workbook at an FTP Site |
621 |
| Downloading a Workbook to YourComputer |
622 |
| Using Web Folders |
627 |
| Chapter 20 Creating Web Pages with Excel |
629 |
| Converting an Entire Excel Workbookinto HTML |
630 |
| Converting Individual Excel Worksheetsinto HTML |
634 |
| Other Web Publishing Options |
635 |
| Changing the Title |
635 |
| Choosing Other Items to Publish |
636 |
| Adding Data to an Existing Web Page |
637 |
| Adding Interactivity |
637 |
| Spreadsheet Functionality |
638 |
| Chart Functionality |
641 |
| PivotTable Functionality |
644 |
| Part V Charts |
649 |
| Chapter 21 Basic Charting Techniques |
651 |
| Creating a New Chart |
652 |
| Step 1: Choosing a Chart Type |
652 |
| Step 2: Specifying the Data to Plot |
653 |
| Step 3: Choosing Chart Options |
656 |
| Step 4: Deciding Where to Put the Chart |
661 |
| Changing a Chart's Size and Position |
661 |
| Plotting Hidden Cells |
662 |
| How Excel Handles Missing Values |
663 |
| Changing the Default Chart Type |
664 |
| Printing Charts |
665 |
| Saving, Opening, and Protecting Charts |
665 |
| Chapter 22 Changing the Appearance of Your Charts |
667 |
| Working with the Chart Menu and Chart Toolbar |
668 |
| Selecting Chart Elements |
669 |
| Copying Formats from One Chart to Another |
670 |
| Adding a Customized Chart to the ChartWizard Gallery |
671 |
| Repositioning Chart Elements with the Mouse |
671 |
| Moving and Resizing the Plot Area |
672 |
| Formatting Text Elements |
672 |
| Changing the Font and Color |
673 |
| Changing Text Rotation |
675 |
| Changing the Alignment of Multiline Titles |
676 |
| Changing the Numeric Format Usedby Tick-Mark and Data Labels |
676 |
| Formatting the Borders and Backgroundsof Text Areas |
677 |
| Formatting and Scaling Axes |
677 |
| Specifying Line Style, Color, and Weight |
677 |
| Specifying the Position of Tick Marksand Tick-Mark Labels |
678 |
| Scaling Axes Manually |
679 |
| Displaying and Formatting Gridlines |
688 |
| Formatting Data Series and Markers |
689 |
| Assigning a Series to a SecondaryValue Axis |
689 |
| Using Two or More Chart Types in theSame Chart |
690 |
| Changing the Series Order |
691 |
| Toggling the Column/Row Orientation |
691 |
| Changing Colors, Patterns, Fills, andBorders for Markers |
692 |
| Adjusting Marker Spacing inTwo-Dimensional Column andBar Charts |
693 |
| Adjusting Marker Spacing inThree-Dimensional Charts |
694 |
| Adding Series Lines in Stacked Columnand Bar Charts |
695 |
| Changing Marker Shapes inThree-Dimensional Column andBar Charts |
695 |
| Smoothing the Lines in Line and XY(Scatter) Charts |
696 |
| Changing Line and Marker Styles in Line,XY (Scatter), and Radar Charts |
697 |
| Adding High-Low Lines and Up-DownBars in Line Charts |
697 |
| Adding Drop Lines in Area andLine Charts |
698 |
| Exploding Pie Slices and Doughnut Bites |
698 |
| Split and Formatting Options in Pie-Columnand Pie-Pie Charts |
699 |
| Changing the Angle of the First Pie Sliceor Doughnut Bite |
700 |
| Display Options for Bubble Charts |
700 |
| Formatting Background Areas |
701 |
| Formatting the Border |
702 |
| Filling an Area with a Solid Color |
702 |
| Filling an Area with a Color Gradient |
702 |
| Filling an Area with a Pattern |
703 |
| Filling an Area with a Texture or Picture |
704 |
| Changing Three-Dimensional Viewing Angles |
706 |
| Adjusting the Elevation |
708 |
| Changing the Rotation |
708 |
| Changing the Height |
709 |
| Changing the Perspective |
709 |
| Changing the Axis Angle and Scale |
709 |
| Chapter 23 Working with Chart Data |
711 |
| Adding Data |
712 |
| Removing Data |
715 |
| Replacing Data |
716 |
| Changing the Plot Order |
717 |
| Using Multilevel Categories |
718 |
| Adding Trendlines |
719 |
| Adding Error Bars |
720 |
| Dragging Chart Markers to Change Data |
721 |
| Chapter 24 Mapping Geographic Data |
723 |
| Preparing Your Data |
724 |
| Creating a Data Map |
725 |
| Single-Series Map Formats |
728 |
| Changing the Look of a Map |
730 |
| Adding New Layers to a Map |
733 |
| Adding Map Features |
735 |
| Refreshing the Map |
738 |
| Saving the Map Template |
738 |
| Multiple-Series Map Formats |
739 |
| Inserting Data into a Map |
742 |
| Creating Custom Pin Maps |
743 |
| Part VI Database and List Management |
745 |
| Chapter 25 Managing Information in Lists |
747 |
| Building and Maintaining a List |
748 |
| Using a Form to Add, Change, or DeleteRows |
749 |
| Sorting Lists and Other Ranges |
752 |
| A Simple Example |
752 |
| Sorting on More Than One Column |
754 |
| Sorting Only Part of a List |
756 |
| The Header Row Option |
756 |
| Sorting by Columns |
757 |
| Creating and Using Custom Sort Orders |
759 |
| Using Filters to Analyze a List |
760 |
| The AutoFilter Command |
760 |
| The Advanced Filter Command |
766 |
| Using Subtotals to Analyze a List |
777 |
| How Subtotal Formulas Are Built |
780 |
| Using Automatic Page Breaks |
781 |
| Applying More Than One AggregationFormula to a Column |
781 |
| Removing or Replacing Subtotals |
782 |
| Using Functions to Analyze a List |
782 |
| Using the COUNTIF and SUMIF Functions |
782 |
| Using the D Functions |
783 |
| Validating Data Entry |
785 |
| Specifying Data Type and AcceptableValues |
785 |
| Specifying an Input Message |
786 |
| Specifying Error Alert Style and Message |
786 |
| Chapter 26 Working with External Data |
787 |
| Getting Data from Text Files |
788 |
| The Import Text File Wizard |
789 |
| Working with the External Data Toolbar |
793 |
| Revisiting the Import Text File Wizard |
793 |
| Refreshing a Text Query |
794 |
| Refreshing All Queries at Once |
794 |
| Working with the Text Query'sProperty Sheet |
795 |
| Using Web Queries to Get Informationfrom the Internet |
797 |
| Executing an Existing Web Query |
798 |
| Refreshing a Web Query |
800 |
| Working with the Web Query'sProperty Sheet |
800 |
| Creating Your Own Web Queries |
801 |
| Using Microsoft Query to CreateDatabase Queries |
804 |
| When to Use Query, When to Use theQuery Wizard |
805 |
| Specifying a Data Source |
806 |
| Using the Query Wizard |
809 |
| Working Directly with Microsoft Query |
813 |
| Chapter 27 Using PivotTables |
835 |
| A Simple Example |
836 |
| Creating a PivotTable |
839 |
| Starting the PivotTable and PivotChartWizard |
839 |
| Step 1: Specifying the Type of Data Source |
840 |
| Step 2: Indicating the Location of YourSource Data |
840 |
| Step 3: Telling the Wizard Where toPut Your PivotTable |
842 |
| Laying Out the PivotTable |
843 |
| Changing the Layout of a PivotTable |
844 |
| Using the Page Axis |
844 |
| Selecting Items to Display on the Rowand Column Axes |
846 |
| Creating a PivotChart |
846 |
| Refreshing a PivotTable |
848 |
| Refreshing on File Open |
848 |
| Refreshing Linked PivotTables |
848 |
| Refreshing a PivotTable Based onExternal Data |
849 |
| Selecting Elements of a PivotTable |
849 |
| Turning Structured Selection On or Off |
850 |
| Selecting Labels Only or Data Only |
851 |
| Selecting an Entire PivotTable |
851 |
| Formatting a PivotTable |
851 |
| Using AutoFormat with PivotTables |
852 |
| Changing the Number Format for AllCells in a PivotTable |
852 |
| Changing the Way a PivotTableDisplays Empty Cells |
853 |
| Changing the Way a PivotTableDisplays Error Values |
853 |
| Merging Labels |
854 |
| Using Multiple Data Fields |
854 |
| Renaming Fields and Items |
856 |
| Sorting Items |
856 |
| Using AutoSort |
857 |
| Using Nonstandard Sorting Orders |
857 |
| Showing the Top or Bottom Items in a Field |
858 |
| Hiding and Showing Inner Field Items |
859 |
| Displaying the Details Behinda Data Value |
859 |
| Grouping and Ungrouping Data |
860 |
| Creating Ad Hoc Item Groupings |
860 |
| Grouping Numeric Items |
862 |
| Grouping Items in Date or Time Ranges |
862 |
| Removing Groups (Ungrouping) |
864 |
| Using Grand Totals and Subtotals |
864 |
| Grand Totals |
864 |
| Subtotals |
864 |
| Subtotals for Innermost Fields |
865 |
| Changing a PivotTable's Calculations |
866 |
| Using a Different Summary Function |
866 |
| Applying Multiple Summary Functionsto the Same Field |
867 |
| Using Custom Calculations |
867 |
| Using Calculated Fields and Items |
868 |
| Referencing PivotTable Datafrom Worksheet Cells |
871 |
| Using a PivotTable to Consolidate Ranges |
871 |
| Part VII Macros and Visual Basic |
877 |
| Chapter 28 Creating Macros |
879 |
| Recording Macros |
880 |
| Using the Macro Dialog Box |
883 |
| Behind the Scenes: The Visual BasicEnvironment |
883 |
| Getting Help on Visual Basic Keywords |
884 |
| Objects, Methods, and Properties |
885 |
| Collections of Objects |
888 |
| Naming Arguments to Methods |
890 |
| Adding Code to an Existing Macro |
891 |
| Using Absolute and Relative References |
894 |
| Macro Subroutines |
896 |
| Other Ways to Run Macros |
898 |
| Using the Personal Macro Workbook |
899 |
| Chapter 29 Custom Functions |
901 |
| Creating a Custom Function |
902 |
| Using Custom Functions |
905 |
| What's Happening |
906 |
| Custom Function Rules |
908 |
| Visual Basic Keywords Availableto Custom Functions |
909 |
| Documenting Macros and CustomFunctions |
909 |
| Designing Flexible Custom Functions |
911 |
| Chapter 30 A Sample Visual Basic Application |
915 |
| A Visual Basic Application |
916 |
| Loading a Workbook Automatically |
917 |
| Declaring Public Variables |
918 |
| The Main Sequence |
919 |
| Running a Macro Automatically |
920 |
| Waiting for an Event |
921 |
| Using a Custom Dialog Box to Loadand Save Options |
923 |
| Creating an Options Sheet |
924 |
| Creating a UserForm |
925 |
| Displaying the UserForm |
927 |
| A Minor Problem |
928 |
| UserForm Code |
929 |
| Downloading Data via the Internet |
931 |
| Working with Text Files |
932 |
| Formatting a Worksheet |
934 |
| Creating a Chart |
935 |
| Controlling Word from an Excel Macro |
938 |
| The GenWordReport Routine |
938 |
| Converting to an Add-In Application |
942 |
| Index |
945 |
