Introduction to Excel

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