Michael Alexander is a Microsoft Certified Application Developer and author of several books on Microsoft Access and Excel. He runs a free tutorial site at [...].

Dick Kusleika develops Access- and Excel-based solutions, and conducts Office training seminars in the U.S. and Australia. He writes the popular blog [...].

John Walkenbach is a renowned authority on Excel. He has written hundreds of articles, thirty-plus books, created the award-winning Power Utility Pak, and developed the popular [...].

Introduction xxvii

Part I: Understanding Formula Basics

Chapter 1: The Excel User Interface in a Nutshell 3

The Workings of Workbooks 3

Worksheets 4

Chart sheets 5

Macro sheets and dialog sheets 5

The Excel User Interface 5

The Ribbon 6

Backstage View 7

Shortcut menus and the mini toolbar 7

Dialog boxes 7

Customizing the UI 8

Task panes 9

Customizing onscreen display 9

Numeric formatting 9

Stylistic formatting 9

Protection Options 10

Securing access to the entire workbook 10

Limiting access to specific worksheet ranges 13

Protecting the workbook structure 16

Chapter 2: Basic Facts About Formulas 19

Entering and Editing Formulas 19

Formula elements 20

Entering a formula 20

Pasting names 22

Spaces and line breaks 22

Formula limits 23

Sample formulas 23

Editing formulas 24

Using Operators in Formulas 25

Reference operators 25

Sample formulas that use operators 26

Operator precedence 27

Nested parentheses 29

Calculating Formulas 30

Cell and Range References 30

Creating an absolute or a mixed reference 31

Referencing other sheets or workbooks 33

Copying or Moving Formulas 35

Making an Exact Copy of a Formula 36

Converting Formulas to Values 37

Hiding Formulas 39

Errors in Formulas 40

Dealing with Circular References 41

Goal Seeking 42

A goal seeking example 42

More about goal seeking 43

Chapter 3: Working with Names 45

What's in a Name? 45

A Name's Scope 46

Referencing names 47

Referencing names from another workbook 48

Conflicting names 48

The Name Manager 48

Creating names 49

Editing names 50

Deleting names 50

Shortcuts for Creating Cell and Range Names 50

The New Name dialog box 51

Creating names using the Name box 52

Creating names from text in cells 52

Naming entire rows and columns 54

Names created by Excel 55

Creating Multisheet Names 55

Working with Range and Cell Names 57

Creating a list of names 58

Using names in formulas 59

Using the intersection operators with names 59

Using the range operator with names 61

Referencing a single cell in a multicell named range 61

Applying names to existing formulas 62

Applying names automatically when creating a formula 63

Unapplying names 63

Names with errors 64

Viewing named ranges 64

Using names in charts 64

How Excel Maintains Cell and Range Names 65

Inserting a row or column 65

Deleting a row or a column 65

Cutting and pasting 65

Potential Problems with Names 66

Name problems when copying sheets 66

Name problems when deleting sheets 66

The Secret to Understanding Names 68

Naming constants 68

Naming text constants 69

Using worksheet functions in named formulas 70

Using cell and range references in named formulas 71

Using named formulas with relative references 72

Advanced Techniques That Use Names 75

Using the INDIRECT function with a named range 75

Using arrays in named formulas 77

Creating a dynamic named formula 78

Using an XLM macro in a named formula 80

Part II: Leveraging Excel Functions

Chapter 4: Introducing Worksheet Functions 85

What Is a Function? 85

Simplify your formulas 86

Perform otherwise impossible calculations 86

Speed up editing tasks 86

Provide decision-making capability 87

More about functions 87

Function Argument Types 88

Names as arguments 89

Full-column or full-row as arguments 89

Literal values as arguments 90

Expressions as arguments 90

Other functions as arguments 91

Arrays as arguments 91

Ways to Enter a Function into a Formula 91

Entering a function manually 91

Using the Function Library commands 93

Using the Insert Function dialog box 94

More tips for entering functions 96

Chapter 5: Manipulating Text 99

A Few Words About Text 99

How many characters in a cell? 99

Numbers as text 99

Text Functions 101

Determining whether a cell contains text 101

Working with character codes 102

Determining whether two strings are identical 105

Joining two or more cells 105

Displaying formatted values as text 106

Displaying formatted currency values as text 108

Removing excess spaces and nonprinting characters 108

Counting characters in a string 109

Repeating a character or string 109

Creating a text histogram 110

Padding a number 111

Changing the case of text 112

Extracting characters from a string 113

Replacing text with other text 113

Finding and searching within a string 114

Searching and replacing within a string 115

Advanced Text Formulas 115

Counting specific characters in a cell 116

Counting the occurrences of a substring in a cell 116

Removing trailing minus signs 116

Expressing a number as an ordinal 117

Determining a column letter for a column number 118

Extracting a filename from a path specification 118

Extracting the first word of a string 119

Extracting the last word of a string 119

Extracting all but the first word of a string 120

Extracting first names, middle names, and last names 120

Removing titles from names 122

Counting the number of words in a cell 122

Chapter 6: Working with Dates and Times 125

How Excel Handles Dates and Times 125

Understanding date serial numbers 126

Entering dates 127

Understanding time serial numbers 129

Entering times 130

Formatting dates and times 131

Problems with dates 133

Date-Related Functions 134

Displaying the current date 135

Displaying any date with a function 136

Generating a series of dates 137

Converting a nondate string to a date 138

Calculating the number of days between two dates 139

Calculating the number of work days between two dates 139

Offsetting a date using only work days 141

Calculating the number of years between two dates 141

Calculating a person's age 142

Determining the day of the year 143

Determining the day of the week 144

Determining the week of the year 144

Determining the date of the most recent Sunday 144

Determining the first day of the week after a date 145

Determining the nth occurrence of a day of the week in a month 145

Counting the occurrences of a day of the week 146

Expressing a date as an ordinal number 147

Calculating dates of holidays 147

Determining the last day of a month 150

Determining whether a year is a leap year 151

Determining a date's quarter 151

Converting a year to roman numerals 151

Time-Related Functions 152

Displaying the current time 152

Displaying any time using a function 153

Calculating the difference between two times 154

Summing times that exceed 24 hours 155

Converting from military time 157

Converting decimal hours, minutes, or seconds to a time 158

Adding hours, minutes, or seconds to a time 158

Converting between time zones 159

Rounding time values 160

Calculating Durations 161

Chapter 7: Counting and Summing Techniques 163

Counting and Summing Worksheet Cells 163

Other Counting Methods 165

Basic Counting Formulas 165

Counting the total number of cells 166

Counting blank cells 166

Counting nonblank cells 167

Counting numeric cells 167

Counting text cells 168

Counting nontext cells 168

Counting logical values 168

Counting error values in a range 168

Advanced Counting Formulas 169

Counting cells with the COUNTIF function 169

Counting cells that meet multiple criteria 170

Counting the most frequently occurring entry 173

Counting the occurrences of specific text 174

Counting the number of unique values 176

Creating a frequency distribution 178

Summing Formulas 184

Summing all cells in a range 184

Summing a range that contains errors 185

Computing a cumulative sum 186

Summing the "top n" values 187

Conditional Sums Using a Single Criterion 188

Summing only negative values 189

Summing values based on a different range 190

Summing values based on a text comparison 190

Summing values based on a date comparison 190

Conditional Sums Using Multiple Criteria 191

Using And criteria 191

Using Or criteria 192

Using And and Or criteria 193

Chapter 8: Using Lookup Functions 195

What Is a Lookup Formula? 195

Functions Relevant to Lookups 196

Basic Lookup Formulas 198

The VLOOKUP function 198

The HLOOKUP function 200

The LOOKUP function 201

Combining the MATCH and INDEX functions 202

Specialized Lookup Formulas 203

Looking up an exact value 204

Looking up a value to the left 206

Performing a case-sensitive lookup 207

Choosing among multiple lookup tables 207

Determining letter grades for test scores 208

Calculating a grade point average 209

Performing a two-way lookup 211

Performing a two-column lookup 212

Determining the address of...