Introduction to Excel

1

Microsoft Excel Basics

1.1 Introduction to Worksheets

1.2 How to Use This Book

1.3 Typographic Conventions Used in This Book

1.3.1 Selection with the Mouse

1.3.2 Multiple Selections

1.3.3 Multiple Keystrokes

1.3.4 Literal Expressions

1.3.5 Key Terms

1.4 Understanding the Excel 2003 Screen

1.4.1 Title Bar

1.4.2 Menu Bar

1.4.3 Toolbars

1.4.4 Customizing Toolbars

1.4.5 Formula Bar

1.4.6 Task Pane

1.4.7 Workbook Window

1.4.8 Sheet Tabs

1.4.9 Scrollbars

1.4.10 Status Bar

1.5 Getting Help

1.5.1 Choosing from the Table of Contents

1.5.2 Searching the Help Section

1.5.3 Using the Office Assistant

1.5.4 Learning from the Tip of the Day

1.5.5 Accessing Help from the World Wide Web

1.6 Manipulating Windows

1.6.1 Minimize Control Button

1.6.2 Restore/Maximize

1.6.3 Close Control Button

1.7 Creating and Saving Worksheets and Workbooks

1.7.1 Creating a New Workbook

1.7.2 Opening an Existing Workbook

1.7.3 Creating a New Worksheet

1.7.4 Introduction to Templates

1.7.5 Opening Workbooks with Macros

1.7.6 Saving Documents

1.7.7 The AutoRecover Feature

1.7.8 Naming Documents

1.8 Moving around a Worksheet

1.8.1 Movement by Using the Keyboard

1.8.2 Movement by Using the Mouse

1.8.3 Movement by Using the Go To Dialog Box

1.9 Selecting a Region.1.9.1 Selection by Using Cell References

1.9.2 Selection by Using the Mouse

1.9.3 Selection by Using the Keyboard

1.10 Cutting, Moving, Copying, and Pasting

1.10.1 Cutting a Region

1.10.2 Moving a Region

1.10.3 Copying a Region

1.11 Inserting and Deleting Cells

1.11.1 Deleting Cells

1.11.2 Clearing Cells

1.11.3 Inserting Cells

1.12 Shortcut Keys

1.13 Finding and Correcting Mistakes

1.13.1 Undoing Mistakes

1.13.2 Checking Spelling

1.13.3 The AutoCorrect Feature

1.14 Printing

1.14.1 Setting the Print Area

1.14.2 Previewing a Worksheet

1.14.3 Printing a Worksheet

2

Entering and Formatting Data

2.1 Introduction to Entering and Formatting Data

2.2 Entering Data

2.2.1 Entering Numeric Data

2.2.2 Entering Text Data

2.2.3 Entering Date and Time Data

2.3 Using Fill Handles

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

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

2.3.3. Creating a Linear Series with a Non-Unity Increment

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

2.4 Formatting for Appearance

2.4.1 Changing the Appearance of Cells

2.4.2 Changing the Appearance of Columns and Rows

2.4.3 Changing the Appearance of Entire Worksheets

2.5 Formatting Tables

2.5.1 Table Autoformats

2.5.2 Sorting Tables

2.6 Conditional Formatting

3

Formulas and Functions

3.1 Introduction

3.2 Referencing Cells and Cell Ranges

3.2.1 Cell References

3.2.2 Referencing a Range of Cells

3.2.3 Naming a Cell or Range of Cells

3.3 Creating and Using Formulas.3.3.1 Formula Syntax

3.3.2 Arithmetic Operators and Operator Precedence

3.4 Using Excel’s Built-In Functions

3.4.1 Examples of Statistical Functions

3.4.2 Examples of Trigonomic Functions

3.4.3 Examples of Matrix Operations

3.4.4 Examples of Financial Functions

3.5 Absolute and Relative Cell References

3.6 Error Messages

3.7 Debugging Excel Worksheets

3.7.1 Debugging by Using Cell Selection

3.7.2 Debugging by Using Tracing

3.8 Using Macros to Automate Computations

3.8.1 Recording a Macro

3.8.2 Executing a Macro

3.8.3 Editing a Macro

4

Working with Charts

4.1 Introduction

4.2 Using the Chart Wizard to Create a Chart

4.2.1 Creating an XY Scatter Plot

4.2.2 Creating a Line Chart

4.3 Creating a Chart by Using a Shortcut Key

4.4 Formatting Charts

4.4.1 Formatting Markers and Lines

4.4.2 Chart Titles and Labels

4.4.3 Formatting the Plot Area

4.4.4 Formatting Axes

4.4.5 Formatting the Legend

4.4.6 Formatting Gridlines

4.5 Previewing and Printing Charts

4.6 Adding Data to Charts

4.6.1 Adding Data to an Existing Series

4.6.2 Adding a Data Series to an XY Scatter Chart

4.7 Charting Features Useful to Engineers

4.7.1 Adding a Trendline to a Chart

4.7.2 Adding Error Bars to a Chart

4.7.3 Using a Log Scale Axis

4.7.4 Using a Secondary Axis

5

Performing Data Analysis

5.1 Introduction

5.2 Using the Analysis ToolPak

5.3 Creating a Histogram

5.4 Providing Descriptive Statistics

5.5 Computing a Correlation

5.6 Performing a Linear Regression

5.7 Trend Analysis.5.7.1 Trend Analysis with Data Series

5.7.2 Trend-Analysis Functions

5.7.3 Trend Analysis for Charts

5.8 Using the Goal Seeker

5.9 Using Solver for Optimization Problems

5.9.1 Introduction to Microsoft Excel Solver

5.9.2 Setting Up an Optimization Problem in Excel

5.9.3 Linear Optimization Example

5.9.4 Nonlinear Optimization Example

6

Database Management within Excel

6.1 Introduction

6.1.1 Database Terminology

6.2 Creating Databases

6.3 Entering Data

6.4 Sorting a Database

6.5 Searching and Filtering

6.5.1 Filtering with the Data Entry Form

6.5.2 Using the AutoFilter Function

6.5.3 Using the Custom AutoFilter

6.5.4 Using Wild-card Characters

6.5.5 Using the Advanced Filter Function

6.5.6 Logic within Rows

6.5.7 Logic between Rows

7

Collaborating with Other Engineers

7.1 The Collaborative Design Process

7.1.1 Microsoft Excel and Collaboration

7.2 Tracking Changes

7.2.1 Highlighting Changes

7.2.2 Creating an Identity

7.2.3 Incorporating or Rejecting Revisions

7.3 Adding Comments to a Document

7.4 Maintaining Shared Workbooks

7.4.1 Sharing a Workbook

7.4.2 Keeping a Change History

7.4.3 Timed Updates

7.4.4 Managing Conflicts

7.4.5 Personal Views

7.4.6 Merging Workbooks

7.4.7 Viewing the History of Changes

7.4.8 Restrictions for Shared Workbooks

7.5 Password Protection

7.5.1 Open Protection

7.5.2 Write Protection

7.5.3 Sheet Protection

7.6 Importing and Exporting Data from External File Formats.7.6.1 Importing by Using the File Open Option

7.6.2 Importing Text Data by Using the Text Import Wizard

8

Excel and the World Wide Web

8.1 Engineering and the Internet

8.2 Accessing the World Wide Web from within Excel

8.3 Web Sites Related to Microsoft Excel

8.4 Retrieving Data by Using a Web Query

8.5 Accessing Excel Files on the Web

8.6 Creating Hyperlinks within a Worksheet

8.7 Converting a Worksheet to a Web Page

Appendix A

Index