Introduction to Excel

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