Hjem

Introduction 1
PART I
: MASTERING EXCEL RANGES AND FORMULAS
Chapter 1 Getting the Most Out of Ranges 5 Advanced Range-Selection Techniques 5 Mouse Range-Selection Tricks 6 Keyboard Range-Selection Tricks 7 Working with 3D Ranges 7 Selecting a Range Using Go To 8 Using the Go To Special Dialog Box 9 Data Entry in a Range 14 Filling a Range 14 Using the Fill Handle 15 Flash-Filling a Range 18 Creating a Series 20 Advanced Range Copying and Pasting 21 Pasting Selected Cell Attributes 22 Combining Two Ranges Arithmetically 23 Transposing Rows and Columns 24 Clearing a Range 25 Applying Conditional Formatting to a Range 25 Creating Highlight Cells Rules 26 Creating Top/Bottom Rules 27 Adding Data Bars 29 Adding Color Scales 32 Adding Icon Sets 33
Chapter 2 Using Range Names 37 Defining a Range Name 38 Working with the Name Box 39 Using the New Name Dialog Box 40 Changing the Scope to Define Sheet-Level Names 41 Using Worksheet Text to Define Names 41 Naming Constants 44 Working with Range Names 45 Referring to a Range Name 45 Working with AutoComplete for Range Names 47 Navigating Using Range Names 47 Pasting a List of Range Names in a Worksheet 48 Displaying the Name Manager 48 Filtering Names 48 Editing a Range Name''s Coordinates 49 Adjusting Range Name Coordinates Automatically 49 Changing a Range Name 51 Deleting a Range Name 51 Using Names with the Intersection Operator 51
Chapter 3 Building Basic Formulas 53 Understanding Formula Basics 53 Formula Limits in Excel 2016 54 Entering and Editing Formulas 54 Using Arithmetic Formulas 55 Using Comparison Formulas 56 Using Text Formulas 57 Using Reference Formulas 57 Understanding Operator Precedence 57 The Order of Precedence 58 Controlling the Order of Precedence 58 Controlling Worksheet Calculation 60 Copying and Moving Formulas 62 Understanding Relative Reference Format 62 Understanding Absolute Reference Format 64 Copying a Formula Without Adjusting Relative References 65 Displaying Worksheet Formulas 65 Displaying All Worksheet Formulas 65 Displaying a Cell''s Formula by Using FORMULATEXT() 65 Converting a Formula to a Value 66 Working with Range Names in Formulas 67 Pasting a Name into a Formula 67 Applying Names to Formulas 68 Naming Formulas 70 Working with Links in Formulas 71 Understanding External References 72 Updating Links 73 Changing the Link Source 73 Formatting Numbers, Dates, and Times 74 Numeric Display Formats 74 Date and Time Display Formats 83 Deleting Custom Formats 85
Chapter 4 Creating Advanced Formulas 87 Working with Arrays 87 Using Array Formulas 88 Using Array Constants 91 Functions That Use or Return Arrays 91 Using Iteration and Circular References 93 Consolidating Multisheet Data 95 Consolidating by Position 95 Consolidating by Category 98 Applying Data-Validation Rules to Cells 100 Using Dialog Box Controls on a Worksheet 103 Displaying the Developer Tab 103 Using the Form Controls 103 Adding a Control to a Worksheet 104 Linking a Control to a Cell Value 104 Understanding the Worksheet Controls 105
Chapter 5 Troubleshooting Formulas 111 Understanding Excel''s Error Values 112 #DIV/0! 112 #N/A 113 #NAME? 113 Case Study: Avoiding #NAME? Errors When Deleting Range Names 114 #NULL! 115 #NUM! 115 #REF! 115 #VALUE! 115 Fixing Other Formula Errors 116 Missing or Mismatched Parentheses 116 Erroneous Formula Results 117 Fixing Circular References 118 Handling Formula Errors with IFERROR() 118 Using the Formula Error Checker 119 Choosing an Error Action 120 Setting Error Checker Options 121 Auditing a Worksheet 123 Understanding Auditing 124 Tracing Cell Precedents 125 Tracing Cell Dependents 125 Tracing Cell Errors 125 Removing Tracer Arrows 125 Evaluating Formulas 126 Watching Cell Values 126
PART II
: HARNESSING THE POWER OF FUNCTIONS
Chapter 6 Understanding Functions 129 About Excel''s Functions 130 The Structure of a Function 130 Typing a Function into a Formula 132 Using the Insert Function Feature 134 Loading the Analysis ToolPak 136
Chapter 7 Working with Text Functions 139 Excel''s Text Functions 139 Working with Characters and Codes 141 The CHAR() Function 141 The CODE() Function 144 Converting Text 144 The LOWER() Function 145 The UPPER() Function 145 The PROPER() Function