- ISBN: 9780133083637 | 0133083632
- Cover: Paperback
- Copyright: 9/14/2012
A highly visual, step-by-step approach to solving engineering problems with Excel. Specifically targeted at first-year engineering students, this text seeks to teach the basic Excel skills that undergraduates will use in the first few years of engineering courses. This book was written with the understanding that students get frustrated by multi-step procedures that illustrate only the final outcome. Ron Larsen, in his hallmark approach, provides screen images for each and every step allowing students to easily follow along as they try to perform each task.
About This Book ix
1 • Microsoft Excel Basics 1
1.1 Introduction to Worksheets 2
1.2 How to Use This Book 2
1.3 Typographic Conventions Used in This Book 3
1.4 Understanding the Excel 2010 Screen 4
1.4.1 Title Bar 5
1.4.2 Ribbon 6
1.4.3 Formula Bar 8
1.4.4 Work Area 9
1.4.5 Sheet Tabs 10
1.4.6 Status Bar 10
1.5 Getting Help 10
1.5.1 Browsing the Help Topic List 11
1.5.2 Searching the Help System 12
1.6 Creating and Saving Worksheets and Workbooks 12
1.6.1 Creating a New Workbook 12
1.6.2 Opening an Existing Workbook 13
1.6.3 Creating a New Worksheet 14
1.6.4 Introduction to Templates 15
1.6.5 Opening Workbooks with Macros 18
1.6.6 Saving Documents 18
1.6.7 The AutoRecover Feature 21
1.6.8 Naming Documents 22
1.7 Moving Around a Worksheet 23
1.7.1 Movement by Using the Keyboard 24
1.7.2 Movement by Using the Mouse 25
1.7.3 Movement by Using the Go To Dialog Box 25
1.8 Selecting a Region 27
1.8.1 Selection by Using Cell References 27
1.8.2 Selection by Using the Mouse 27
1.8.3 Selection by Using the Keyboard 28
1.9 Cutting, Moving, Copying, and Pasting 29
1.9.1 Cutting a Region 30
1.9.2 Moving a Region (Cut and Paste) 30
1.9.3 Copying a Region 31
1.10 Inserting and Deleting Cells 31
1.10.1 Deleting Cells 31
1.10.2 Clearing Cells 32
1.10.3 Inserting Cells 32
1.11 Shortcut Keys 32
1.12 Finding and Correcting Mistakes 33
1.12.1 Undoing Mistakes 33
1.12.2 Checking Spelling 34
1.12.3 The AutoCorrect Feature 35
1.13 Printing 36
1.13.1 Setting the Print Area 36
1.13.2 Previewing a Worksheet 36
1.13.3 Printing a Worksheet 38
2 • Entering and Formatting Data 44
2.1 Introduction to Entering and Formatting Data 44
2.2 Entering Data 45
2.2.1 Entering Numeric Data 45
2.2.2 Entering Text Data 50
2.2.3 Entering Date and Time Data 52
2.3 Using the Fill Handle 54
2.3.1 Using the Fill Handle with the Left Mouse Button to Copy Cells 55
2.3.2 Using the Fill Handle with the Left Mouse Button–More Copy Options 55
2.3.3 Creating a Linear Series with a Non-Unity Increment 56
2.3.4 Using the Fill Handle with the Right Mouse Button for Additional Options 56
2.4 Formatting for Appearance 58
2.4.1 Changing the Appearance of Cells 58
2.4.2 Changing the Appearance of Columns and Rows 72
2.5 Working with Worksheets 74
2.5.1 Inserting New Worksheets into a Workbook 75
2.5.2 Renaming a Worksheet 75
2.5.3 Changing the Color of the Worksheet Tab 75
2.5.4 Hiding a Worksheet 75
2.5.5 Locking a Worksheet 76
2.6 Formatting a Data Set as an Excel Table 78
2.6.1 Creating an Excel Table 78
2.6.2 Sorting Data in Excel Tables 80
2.6.3 Filtering Data in Excel Tables 81
2.6.4 Using a Total Row with Excel Tables 82
2.7 Conditional Formatting 85
3 • Formulas and Functions 99
3.1 Introduction 99
3.2 Referencing Cells and Cell Ranges 100
3.2.1 Cell References 100
3.2.2 Referencing a Range of Cells 101
3.2.3 Naming a Cell or Range of Cells 102
3.3 Creating and Using Formulas 103
3.3.1 Formula Syntax 103
3.3.2 Arithmetic Operators and Operator Precedence 104
3.4 Using Excel’s Built-In Functions 106
3.4.1 Examples of Statistical Functions 110
3.4.2 Examples of Trigonometric Functions 113
3.4.3 Examples of Matrix Operations 115
3.4.4 Examples of Financial Functions 118
3.5 Absolute and Relative Cell References 120
3.6 Excel Error Messages 124
3.7 Debugging Excel Worksheets 124
3.7.1 Highlighting Formulas 125
3.7.2 Tracing Dependents 127
3.7.3 Tracing Precedents 127
3.8 Using Macros to Automate Computations 129
3.8.1 Recording a Macro 130
3.8.2 Running a Macro 134
3.8.3 Editing a Macro 134
4 • Working with Charts 149
4.1 Introduction 149
4.2 Creating a Basic XY Scatter Chart 150
4.2.1 Creating a Basic XY Scatter Chart 151
4.3 Selecting a Chart Layout 152
4.4 Formatting Chart Features 155
4.4.1 Editing an Existing Chart Feature 157
4.5 Creating a Basic Line Chart 160
4.5.1 Changing Chart Types 168
4.6 Previewing and Printing Charts 169
4.6.1 Previewing Charts 169
4.6.2 Printing Charts 170
4.7 Adding Data to Charts 171
4.7.1 Adding Data to an Existing Series 171
4.7.2 Adding a Data Series to an XY Scatter Chart 174
4.8 Charting Features Useful to Engineers 178
4.8.1 Adding a Trendline to a Chart 179
4.8.2 Adding Error Bars to a Chart 182
4.8.3 Using a Log Scale Axis 183
4.8.4 Using Secondary Axes 184
5 • Performing Data Analysis 193
5.1 Introduction 193
5.2 Using the Analysis ToolPak 194
5.2.1 Activating the Analysis ToolPak 194
5.2.2 Opening the Data Analysis Dialog Box 196
5.3 Creating a Histogram 197
5.4 Providing Descriptive Statistics 204
5.5 Computing a Correlation 209
5.6 Performing a Linear Regression 211
5.7 Trend Analysis 215
5.7.1 Trend Analysis with Data Series 215
5.7.2 Trend Analysis: Linear Extension 217
5.7.3 Trend Analysis: Linear Replacement 218
5.7.4 Trend Analysis: Exponential Replacement 219
5.7.5 Trend Analysis Functions 219
5.7.6 Trend Analysis for Charts 222
5.8 Using the Goal Seek Tool 226
5.9 Using Solver for Optimization Problems 228
5.9.1 Activating the Excel Solver 229
5.9.2 Using the Solver 230
5.9.3 Setting Up an Optimization Problem in Excel 232
5.9.4 Linear Optimization Example 232
5.9.5 Non-Linear Optimization Example 236
6 • Database Management within Excel 249
6.1 Introduction 249
6.1.1 Database Terminology 250
6.2 Creating Databases 251
6.3 Entering Data 251
6.4 Sorting a Database 254
6.5 Filtering Data 256
6.5.1 Using the AutoFilter 256
6.5.2 Using the Custom AutoFilter 258
6.5.3 Using Wild-Card Characters 261
6.5.4 Using the Advanced Filter 262
6.5.5 Logic Within Rows 263
6.5.6 Logic Between Rows 263
7 • Collaborating with Other Engineers 270
7.1 The Collaborative Design Process 270
7.1.1 Microsoft Excel and Collaboration 271
7.2 Tracking Changes 271
7.2.1 Highlighting Changes 271
7.2.2 Creating an Identity 272
7.2.3 Incorporating or Rejecting Revisions 274
7.3 Adding Comments to a Document 274
7.4 Maintaining Shared Workbooks 275
7.4.1 Sharing a Workbook 275
7.4.2 Keeping a Change History 276
7.4.3 Managing Conflicts 277
7.4.4 Personal Views 277
7.4.5 Merging Workbooks 277
7.4.6 Restrictions for Shared Workbooks 278
7.5 Password Protection for Workbooks 278
7.5.1 Open Protection 278
7.5.2 Modify or Write Protection 280
7.5.3 Sheet Protection 281
7.6 Importing and Exporting Data from External File Formats 283
7.6.1 Importing by Using the File Open Option 284
7.6.2 Importing Text Data by Using the Text Import Wizard 285
8 • Excel and the World Wide Web 300
8.1 Engineering and the Internet 300
8.2 Accessing the World Wide Web from Within Excel 301
8.3 Creating Hyperlinks in a Worksheet 302
8.3.1 Typing a Web Address in a Cell 302
8.3.2 Using the Insert Hyperlink Dialog Box 302
8.3.3 Links Within Your Excel Workbook 303
8.4 Using Websites Related to Excel 305
8.5 Using Web Data in Excel 305
8.5.1 Copying and Pasting Web Data into Excel 306
8.5.2 Downloading Excel Files from the Web 308
8.5.3 Using a Web Query to Retrieve Web Data 309
8.6 Saving an Excel Workbook as a Web Page 312
Appendix A 317
Index 321
1 • Microsoft Excel Basics 1
1.1 Introduction to Worksheets 2
1.2 How to Use This Book 2
1.3 Typographic Conventions Used in This Book 3
1.4 Understanding the Excel 2010 Screen 4
1.4.1 Title Bar 5
1.4.2 Ribbon 6
1.4.3 Formula Bar 8
1.4.4 Work Area 9
1.4.5 Sheet Tabs 10
1.4.6 Status Bar 10
1.5 Getting Help 10
1.5.1 Browsing the Help Topic List 11
1.5.2 Searching the Help System 12
1.6 Creating and Saving Worksheets and Workbooks 12
1.6.1 Creating a New Workbook 12
1.6.2 Opening an Existing Workbook 13
1.6.3 Creating a New Worksheet 14
1.6.4 Introduction to Templates 15
1.6.5 Opening Workbooks with Macros 18
1.6.6 Saving Documents 18
1.6.7 The AutoRecover Feature 21
1.6.8 Naming Documents 22
1.7 Moving Around a Worksheet 23
1.7.1 Movement by Using the Keyboard 24
1.7.2 Movement by Using the Mouse 25
1.7.3 Movement by Using the Go To Dialog Box 25
1.8 Selecting a Region 27
1.8.1 Selection by Using Cell References 27
1.8.2 Selection by Using the Mouse 27
1.8.3 Selection by Using the Keyboard 28
1.9 Cutting, Moving, Copying, and Pasting 29
1.9.1 Cutting a Region 30
1.9.2 Moving a Region (Cut and Paste) 30
1.9.3 Copying a Region 31
1.10 Inserting and Deleting Cells 31
1.10.1 Deleting Cells 31
1.10.2 Clearing Cells 32
1.10.3 Inserting Cells 32
1.11 Shortcut Keys 32
1.12 Finding and Correcting Mistakes 33
1.12.1 Undoing Mistakes 33
1.12.2 Checking Spelling 34
1.12.3 The AutoCorrect Feature 35
1.13 Printing 36
1.13.1 Setting the Print Area 36
1.13.2 Previewing a Worksheet 36
1.13.3 Printing a Worksheet 38
2 • Entering and Formatting Data 44
2.1 Introduction to Entering and Formatting Data 44
2.2 Entering Data 45
2.2.1 Entering Numeric Data 45
2.2.2 Entering Text Data 50
2.2.3 Entering Date and Time Data 52
2.3 Using the Fill Handle 54
2.3.1 Using the Fill Handle with the Left Mouse Button to Copy Cells 55
2.3.2 Using the Fill Handle with the Left Mouse Button–More Copy Options 55
2.3.3 Creating a Linear Series with a Non-Unity Increment 56
2.3.4 Using the Fill Handle with the Right Mouse Button for Additional Options 56
2.4 Formatting for Appearance 58
2.4.1 Changing the Appearance of Cells 58
2.4.2 Changing the Appearance of Columns and Rows 72
2.5 Working with Worksheets 74
2.5.1 Inserting New Worksheets into a Workbook 75
2.5.2 Renaming a Worksheet 75
2.5.3 Changing the Color of the Worksheet Tab 75
2.5.4 Hiding a Worksheet 75
2.5.5 Locking a Worksheet 76
2.6 Formatting a Data Set as an Excel Table 78
2.6.1 Creating an Excel Table 78
2.6.2 Sorting Data in Excel Tables 80
2.6.3 Filtering Data in Excel Tables 81
2.6.4 Using a Total Row with Excel Tables 82
2.7 Conditional Formatting 85
3 • Formulas and Functions 99
3.1 Introduction 99
3.2 Referencing Cells and Cell Ranges 100
3.2.1 Cell References 100
3.2.2 Referencing a Range of Cells 101
3.2.3 Naming a Cell or Range of Cells 102
3.3 Creating and Using Formulas 103
3.3.1 Formula Syntax 103
3.3.2 Arithmetic Operators and Operator Precedence 104
3.4 Using Excel’s Built-In Functions 106
3.4.1 Examples of Statistical Functions 110
3.4.2 Examples of Trigonometric Functions 113
3.4.3 Examples of Matrix Operations 115
3.4.4 Examples of Financial Functions 118
3.5 Absolute and Relative Cell References 120
3.6 Excel Error Messages 124
3.7 Debugging Excel Worksheets 124
3.7.1 Highlighting Formulas 125
3.7.2 Tracing Dependents 127
3.7.3 Tracing Precedents 127
3.8 Using Macros to Automate Computations 129
3.8.1 Recording a Macro 130
3.8.2 Running a Macro 134
3.8.3 Editing a Macro 134
4 • Working with Charts 149
4.1 Introduction 149
4.2 Creating a Basic XY Scatter Chart 150
4.2.1 Creating a Basic XY Scatter Chart 151
4.3 Selecting a Chart Layout 152
4.4 Formatting Chart Features 155
4.4.1 Editing an Existing Chart Feature 157
4.5 Creating a Basic Line Chart 160
4.5.1 Changing Chart Types 168
4.6 Previewing and Printing Charts 169
4.6.1 Previewing Charts 169
4.6.2 Printing Charts 170
4.7 Adding Data to Charts 171
4.7.1 Adding Data to an Existing Series 171
4.7.2 Adding a Data Series to an XY Scatter Chart 174
4.8 Charting Features Useful to Engineers 178
4.8.1 Adding a Trendline to a Chart 179
4.8.2 Adding Error Bars to a Chart 182
4.8.3 Using a Log Scale Axis 183
4.8.4 Using Secondary Axes 184
5 • Performing Data Analysis 193
5.1 Introduction 193
5.2 Using the Analysis ToolPak 194
5.2.1 Activating the Analysis ToolPak 194
5.2.2 Opening the Data Analysis Dialog Box 196
5.3 Creating a Histogram 197
5.4 Providing Descriptive Statistics 204
5.5 Computing a Correlation 209
5.6 Performing a Linear Regression 211
5.7 Trend Analysis 215
5.7.1 Trend Analysis with Data Series 215
5.7.2 Trend Analysis: Linear Extension 217
5.7.3 Trend Analysis: Linear Replacement 218
5.7.4 Trend Analysis: Exponential Replacement 219
5.7.5 Trend Analysis Functions 219
5.7.6 Trend Analysis for Charts 222
5.8 Using the Goal Seek Tool 226
5.9 Using Solver for Optimization Problems 228
5.9.1 Activating the Excel Solver 229
5.9.2 Using the Solver 230
5.9.3 Setting Up an Optimization Problem in Excel 232
5.9.4 Linear Optimization Example 232
5.9.5 Non-Linear Optimization Example 236
6 • Database Management within Excel 249
6.1 Introduction 249
6.1.1 Database Terminology 250
6.2 Creating Databases 251
6.3 Entering Data 251
6.4 Sorting a Database 254
6.5 Filtering Data 256
6.5.1 Using the AutoFilter 256
6.5.2 Using the Custom AutoFilter 258
6.5.3 Using Wild-Card Characters 261
6.5.4 Using the Advanced Filter 262
6.5.5 Logic Within Rows 263
6.5.6 Logic Between Rows 263
7 • Collaborating with Other Engineers 270
7.1 The Collaborative Design Process 270
7.1.1 Microsoft Excel and Collaboration 271
7.2 Tracking Changes 271
7.2.1 Highlighting Changes 271
7.2.2 Creating an Identity 272
7.2.3 Incorporating or Rejecting Revisions 274
7.3 Adding Comments to a Document 274
7.4 Maintaining Shared Workbooks 275
7.4.1 Sharing a Workbook 275
7.4.2 Keeping a Change History 276
7.4.3 Managing Conflicts 277
7.4.4 Personal Views 277
7.4.5 Merging Workbooks 277
7.4.6 Restrictions for Shared Workbooks 278
7.5 Password Protection for Workbooks 278
7.5.1 Open Protection 278
7.5.2 Modify or Write Protection 280
7.5.3 Sheet Protection 281
7.6 Importing and Exporting Data from External File Formats 283
7.6.1 Importing by Using the File Open Option 284
7.6.2 Importing Text Data by Using the Text Import Wizard 285
8 • Excel and the World Wide Web 300
8.1 Engineering and the Internet 300
8.2 Accessing the World Wide Web from Within Excel 301
8.3 Creating Hyperlinks in a Worksheet 302
8.3.1 Typing a Web Address in a Cell 302
8.3.2 Using the Insert Hyperlink Dialog Box 302
8.3.3 Links Within Your Excel Workbook 303
8.4 Using Websites Related to Excel 305
8.5 Using Web Data in Excel 305
8.5.1 Copying and Pasting Web Data into Excel 306
8.5.2 Downloading Excel Files from the Web 308
8.5.3 Using a Web Query to Retrieve Web Data 309
8.6 Saving an Excel Workbook as a Web Page 312
Appendix A 317
Index 321
What is included with this book?
The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.
The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.
Please wait while the item is added to your bag...
