Introduction to Excel

Cover......Page 1
Contents......Page 6
 1.1 Introduction to Worksheets......Page 14
 1.3 Typographic Conventions Used in This Book......Page 15
 1.4 Understanding the Excel 2007 Screen......Page 16
  1.4.1 Title Bar......Page 17
  1.4.2 Ribbon......Page 19
  1.4.3 Formula Bar......Page 20
  1.4.5 Sheet Tabs......Page 22
 1.5 Getting Help......Page 23
  1.5.2 Searching the Help System......Page 24
  1.6.2 Opening an Existing Workbook......Page 25
  1.6.3 Creating a New Worksheet......Page 28
  1.6.4 Introduction to Templates......Page 29
  1.6.5 Opening Workbooks with Macros......Page 31
  1.6.6 Saving Documents......Page 34
  1.6.7 The AutoRecover Feature......Page 35
  1.6.8 Naming Documents......Page 37
 1.7 Moving Around a Worksheet......Page 38
  1.7.1 Movement by Using the Keyboard......Page 39
  1.7.3 Movement by Using the Go To Dialog Box......Page 40
  1.8.2 Selection by Using the Mouse......Page 42
  1.8.3 Selection by Using the Keyboard......Page 43
  1.9.1 Cutting a Region......Page 44
  1.9.3 Copying a Region......Page 45
  1.10.1 Deleting Cells......Page 46
 1.11 Shortcut Keys......Page 47
  1.12.2 Checking Spelling......Page 48
  1.12.3 The AutoCorrect Feature......Page 49
  1.13.2 Previewing a Worksheet......Page 50
  1.13.3 Printing a Worksheet......Page 52
 2.1 Introduction to Entering and Formatting Data......Page 58
  2.2.1 Entering Numeric Data......Page 59
  2.2.2 Entering Text Data......Page 65
  2.2.3 Entering Date and Time Data......Page 67
  2.3.1 Using the Fill Handle with the Left Mouse Button to Copy Cells......Page 69
  2.3.2 Using the Fill Handle with the Left Mouse Button—More Copy Options......Page 70
  2.3.4 Using the Fill Handle with the Right Mouse Button for Additional Options......Page 71
  2.4.1 Changing the Appearance of Cells......Page 74
  2.4.2 Changing the Appearance of Columns and Rows......Page 89
  2.5.3 Changing the Color of the Worksheet Tab......Page 92
  2.5.5 Locking a Worksheet......Page 93
 2.6 Formatting a Data Set as an Excel Table......Page 95
  2.6.1 Creating an Excel Table......Page 96
  2.6.3 Filtering Data in Excel Tables......Page 98
  2.6.4 Using a Total Row with Excel Tables......Page 100
 2.7 Conditional Formatting......Page 104
 3.2 Referencing Cells and Cell Ranges......Page 118
  3.2.1 Cell References......Page 119
  3.2.2 Referencing a Range of Cells......Page 120
  3.2.3 Naming a Cell or Range of Cells......Page 121
  3.3.1 Formula Syntax......Page 122
  3.3.2 Arithmetic Operators and Operator Precedence......Page 123
 3.4 Using Excel’s Built-in Functions......Page 125
  3.4.1 Examples of Statistical Functions......Page 129
  3.4.2 Examples of Trigonometric Functions......Page 132
  3.4.3 Example of Matrix Operations......Page 135
  3.4.4 Examples of Financial Functions......Page 138
 3.5 Absolute and Relative Cell References......Page 141
 3.7 Debugging Excel Worksheets......Page 146
  3.7.1 Highlighting Formulas......Page 147
  3.7.2 Tracing Dependents......Page 148
  3.7.3 Tracing Precedents......Page 149
  3.8.1 Recording a Macro......Page 152
  3.8.3 Editing a Macro......Page 156
 4.2 Creating a Basic Chart......Page 172
  4.2.1 Creating a Basic XY Scatter Chart......Page 173
 4.3 Selecting a Chart Layout......Page 175
 4.4 Formatting Chart Features......Page 179
  4.4.1 Editing an Existing Chart Feature......Page 181
 4.5 Creating a Basic Line Chart......Page 184
  4.6.1 Previewing Charts......Page 194
  4.6.2 Printing Charts......Page 196
  4.7.1 Adding Data to an Existing Series......Page 197
  4.7.2 Adding a Data Series to an XY Scatter Chart......Page 201
  4.8.1 Adding a Trendline to a Chart......Page 206
  4.8.2 Adding Error Bars to a Chart......Page 208
  4.8.3 Using a Log Scale Axis......Page 209
  4.8.4 Using Secondary Axes......Page 210
 5.1 Introduction......Page 221
  5.2.1 Activating the Analysis ToolPak......Page 222
 5.3 Creating a Histogram......Page 224
 5.4 Providing Descriptive Statistics......Page 233
 5.5 Computing a Correlation......Page 238
 5.6 Performing a Linear Regression......Page 241
  5.7.1 Trend Analysis with Data Series......Page 245
  5.7.2 Trend Analysis: Linear Extension......Page 246
  5.7.3 Trend Analysis: Linear Replacement......Page 248
  5.7.5 Trend-Analysis Functions......Page 249
  5.7.6 Trend Analysis for Charts......Page 253
 5.8 Using the Goal Seek Tool......Page 258
  5.9.1 Activating the Excel Solver......Page 260
  5.9.2 Using the Solver......Page 262
  5.9.3 Setting Up an Optimization Problem in Excel......Page 263
  5.9.4 Linear Optimization Example......Page 264
  5.9.5 Nonlinear Optimization Example......Page 268
  6.1.1 Database Terminology......Page 280
 6.2 Creating Databases......Page 281
 6.3 Entering Data......Page 282
 6.4 Sorting a Database......Page 285
  6.5.1 Using the AutoFilter......Page 287
  6.5.2 Using the Custom AutoFilter......Page 289
  6.5.3 Using Wild-Card Characters......Page 291
  6.5.6 Logic between Rows......Page 293
  7.1.1 Microsoft Excel and Collaboration......Page 301
  7.2.1 Highlighting Changes......Page 302
  7.2.2 Creating an Identity......Page 303
  7.2.3 Incorporating or Rejecting Revisions......Page 305
 7.4 Maintaining Shared Workbooks......Page 306
  7.4.2 Keeping a Change History......Page 307
  7.4.4 Personal Views......Page 308
  7.4.5 Merging Workbooks......Page 309
  7.5.1 Open Protection......Page 310
  7.5.2 Modify or Write Protection......Page 311
  7.5.3 Sheet Protection......Page 312
  7.6.1 Importing Data Using the File Open Option......Page 315
  7.6.2 Importing Text Data by Using the Text Import Wizard......Page 316
 8.1 Engineering and the Internet......Page 331
 8.3 Creating Hyperlinks in a Worksheet......Page 332
  8.3.3 Links within Your Excel Workbook......Page 333
  8.5.1 Copying and Pasting Web Data into Excel......Page 336
  8.5.2 Downloading Excel Files from the Web......Page 339
  8.5.3 Using a Web Query to Retrieve Web Data......Page 340
 8.6 Saving an Excel Workbook as a Web Page......Page 343
APPENDIX A: COMMONLY USED FUNCTIONS......Page 348
 B......Page 350
 C......Page 351
 D......Page 352
 F......Page 353
 I......Page 354
 N......Page 355
 R......Page 356
 T......Page 357
 W......Page 358
 Y......Page 359