Introduction to Excel

KEY BENEFIT: A highly visual, step-by-step approach to solving engineering problems with Excel. Ron Larsen, in his hallmark approach, provides screen images for each and every each step allowing readers to easily follow along as they try to perform each task.KEY TOPICS: Microsoft Excel Basics; Entering and Formatting Data; Formulas and Functions; Working with Charts; Performing Data Analysis; Database Management within Excel; Collaborating with Other Engineers; Excel and the World Wide Web.MARKET: For engineers interested in using Excel to solve engineering problems.

Contents

 

1 • MICROSOFT EXCEL BASICS 1

1.1 Introduction to Worksheets 1

1.2 How to Use This Book 2

1.3 Typographic Conventions Used in This Book 2

1.4 Understanding the Excel 2007 Screen 3

1.4.1 Title Bar 4

1.4.2 Ribbon 6

1.4.3 Formula Bar 7

1.4.4 Work Area 9

1.4.5 Sheet Tabs 9

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 11

1.6 Creating and Saving Worksheets and Workbooks 12

1.6.1 Creating a New Workbook 12

1.6.2 Opening an Existing Workbook 12

1.6.3 Creating a New Worksheet 15

1.6.4 Introduction to Templates 16

1.6.5 Opening Workbooks with Macros 18

1.6.6 Saving Documents 21

1.6.7 The AutoRecover Feature 22

1.6.8 Naming Documents 24

1.7 Moving Around a Worksheet 25

1.7.1 Movement by Using the Keyboard 26

1.7.2 Movement by Using the Mouse 27

1.7.3 Movement by Using the Go To Dialog Box 27

1.8 Selecting a Region 29

1.8.1 Selection by Using Cell References 29

1.8.2 Selection by Using the Mouse 29

1.8.3 Selection by Using the Keyboard 30

1.9 Cutting, Moving, Copying, and Pasting 31

1.9.1 Cutting a Region 31

1.9.2 Moving a Region (Copy and Paste) 32

1.9.3 Copying a Region 32

1.10 Inserting and Deleting Cells 33

1.10.1 Deleting Cells 33

1.10.2 Clearing Cells 34

1.10.3 Inserting Cells 34

1.11 Shortcut Keys 34

1.12 Finding and Correcting Mistakes 35

1.12.1 Undoing Mistakes 35

1.12.2 Checking Spelling 35

1.12.3 The AutoCorrect Feature 36

1.13 Printing 37

1.13.1 Setting the Print Area 37

1.13.2 Previewing a Worksheet 37

1.13.3 Printing a Worksheet 39

 

2 • ENTERING AND FORMATTING DATA 45

2.1 Introduction to Entering and Formatting Data 45

2.2 Entering Data 46

2.2.1 Entering Numeric Data 46

2.2.2 Entering Text Data 52

2.2.3 Entering Date and Time Data 54

2.3 Using the Fill Handle 56

2.3.1 Using the Fill Handle with the Left Mouse Button to Copy Cells 56

2.3.2 Using the Fill Handle with the Left Mouse Button–More Copy

Options 57

2.3.3 Creating a Linear Series with a Nonunity Increment 58

2.3.4 Using the Fill Handle with the Right Mouse Button for Additional Options 58

2.4 Formatting for Appearance 61

2.4.1 Changing the Appearance of Cells 61

2.4.2 Changing the Appearance of Columns and Rows 76

2.5 Working with Worksheets 79

2.5.1 Insert New Worksheets into a Workbook 79

2.5.2 Renaming a Worksheet 79

2.5.3 Changing the Color of the Worksheet Tab 79

2.5.4 Hiding a Worksheet 80

2.5.5 Locking a Worksheet 80

2.6 Formatting a Data Set as an Excel Table 82

2.6.1 Creating an Excel Table 83

2.6.2 Sorting Data in Excel Tables 85

2.6.3 Filtering Data in Excel Tables 85

2.6.4 Using a Total Row with Excel Tables 87

2.7 Conditional Formatting 91

 

3 • FORMULAS AND FUNCTIONS 105

3.1 Introduction 105

3.2 Referencing Cells and Cell Ranges 105

3.2.1 Cell References 106

3.2.2 Referencing a Range of Cells 107

3.2.3 Naming a Cell or Range of Cells 108

3.3 Creating and Using Formulas 109

3.3.1 Formula Syntax 109

3.3.2 Arithmetic Operators and Operator Precedence 110

3.4 Using Excel’s Built-in Functions 112

3.4.1 Examples of Statistical Functions 116

3.4.2 Examples of Trigonometric Functions 119

3.4.3 Example of Matrix Operations 122

3.4.4 Examples of Financial Functions 125

3.5 Absolute and Relative Cell References 128

3.6 Excel Error Messages 133

3.7 Debugging Excel Worksheets 133

3.7.1 Highlighting Formulas 134

3.7.2 Tracing Dependents 135

3.7.3 Tracing Precedents 136

3.8 Using Macros to Automate Computations 139

3.8.1 Recording a Macro 139

3.8.2 Running a Macro 143

3.8.3 Editing a Macro 143

 

4 • WORKING WITH CHARTS 159

4.1 Introduction 159

4.2 Creating a Basic Chart 159

4.2.1 Creating a Basic XY Scatter Chart 160

4.3 Selecting a Chart Layout 162

4.4 Formatting Chart Features 166

4.4.1 Editing an Existing Chart Feature 168

4.5 Creating a Basic Line Chart 171

4.5.1 Changing Chart Types 181

4.6 Previewing and Printing Charts 181

4.6.1 Previewing Charts 181

4.6.2 Printing Charts 183

4.7 Adding Data to Charts 184

4.7.1 Adding Data to an Existing Series 184

4.7.2 Adding a Data Series to an XY Scatter Chart 188

4.8 Charting Features that are Useful to Engineers 193

4.8.1 Adding a Trendline to a Chart 193

4.8.2 Adding Error Bars to a Chart 195

4.8.3 Using a Log Scale Axis 196

4.8.4 Using Secondary Axes 197

 

5 • PERFORMING DATA ANALYSIS 208

5.1 Introduction 208

5.2 Using the Analysis Toolpak 209

5.2.1 Activating the Analysis ToolPak 209

5.2.2 Opening the Data Analysis Dialog Box 211

5.3 Creating a Histogram 211

5.4 Providing Descriptive Statistics 220

5.5 Computing a Correlation 225

5.6 Performing a Linear Regression 228

5.7 Trend Analysis 232

5.7.1 Trend Analysis with Data Series 232

5.7.2 Trend Analysis: Linear Extension 233

5.7.3 Trend Analysis: Linear Replacement 235

5.7.4 Trend Analysis: Exponential Replacement 236

5.7.5 Trend-Analysis Functions 236

5.7.6 Trend Analysis for Charts 240

5.8 Using the Goal Seek Tool 245

5.9 Using Solver for Optimization Problems 247

5.9.1 Activating the Excel Solver 247

5.9.2 Using the Solver 249

5.9.3 Setting Up an Optimization Problem in Excel 250

5.9.4 Linear Optimization Example 251

5.9.5 Nonlinear Optimization Example 255

 

6 • DATABASE MANAGEMENT WITHIN EXCEL 267

6.1 Introduction 267

6.1.1 Database Terminology 267

6.2 Creating Databases 268

6.3 Entering Data 269

6.4 Sorting a Database 272

6.5 Filtering Data 274

6.5.1 Using the AutoFilter 274

6.5.2 Using the Custom AutoFilter 276

6.5.3 Using Wild-Card Characters 278

6.5.4 Using the Advanced Filter 280

6.5.5 Logic within Rows 280

6.5.6 Logic between Rows 280

 

7 • COLLABORATING WITH OTHER ENGINEERS 288

7.1 The Collaborative Design Process 288

7.1.1 Microsoft Excel and Collaboration 288

7.2 Tracking Changes 289

7.2.1 Highlighting Changes 289

7.2.2 Creating an Identity 290

7.2.3 Incorporating or Rejecting Revisions 292

7.3 Adding Comments to a Document 293

7.4 Maintaining Shared Workbooks 293

7.4.1 Sharing a Workbook 294

7.4.2 Keeping a Change History 294

7.4.3 Managing Conflicts 295

7.4.4 Personal Views 295

7.4.5 Merging Workbooks 296

7.4.6 Restrictions for Shared Workbooks 297

7.5 Password Protection 297

7.5.1 Open Protection 297

7.5.2 Modify or Write Protection 298

7.5.3 Sheet Protection 299

7.6 Importing and Exporting Data From External File Formats 302

7.6.1 Importing Data Using the File Open Option 302

7.6.2 Importing Text Data by Using the Text Import Wizard 303

 

8 • EXCEL AND THE WORLD WIDE WEB 318

8.1 Engineering and the Internet 318

8.2 Accessing the World Wide Web from within Excel 319

8.3 Creating Hyperlinks in a Worksheet 319

8.3.1 Typing a Web Address in a Cell 320

8.3.2 Using the Insert Hyperlink Dialog Box 320

8.3.3 Links within Your Excel Workbook 320

8.4 Using Websites Related to Excel 323

8.5 Using Web Data in Excel 323

8.5.1 Copying and Pasting Web Data into Excel 323

8.5.2 Downloading Excel Files from the Web 326

8.5.3 Using a Web Query to Retrieve Web Data 327

8.6 Saving an Excel Workbook as a Web Page 330

 

APPENDIX A • COMMONLY USED FUNCTIONS 335

INDEX 337