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 a val.