Description
Book SynopsisLeverage the full power of Excel formulas Excel 2016 Formulas is fully updated to cover all of the tips, tricks, and techniques you need to maximize the power of Excel 2016 through the use of formulas.
Table of ContentsIntroduction 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 value within a range 213
Looking up a value by using the closest match 214
Looking up a value using linear interpolation 215
Chapter 9: Working with Tables and Lists 219
Tables and Terminology 219
A list example 220
A table example 220
Working with Tables 222
Creating a table 222
Changing the look of a table 223
Navigating and selecting in a table 224
Adding new rows or columns 225
Deleting rows or columns 226
Moving a table 226
Removing duplicate rows from a table 227
Sorting and filtering a table 228
Working with the Total row 233
Using formulas within a table 235
Referencing data in a table 237
Converting a table to a list 241
Using Advanced Filtering 242
Setting up a criteria range 242
Applying an advanced filter 243
Clearing an advanced filter 245
Specifying Advanced Filter Criteria 245
Specifying a single criterion 245
Specifying multiple criteria 247
Specifying computed criteria 249
Using Database Functions 250
Inserting Subtotals 252
Chapter 10: Miscellaneous Calculations 257
Unit Conversions 257
Rounding Numbers 261
Basic rounding formulas 262
Rounding to the nearest multiple 263
Rounding currency values 263
Working with fractional dollars 264
Using the INT and TRUNC functions 265
Rounding to an even or odd integer 266
Rounding to n significant digits 267
Solving Right Triangles 267
Area, Surface, Circumference, and Volume Calculations 270
Calculating the area and perimeter of a square 270
Calculating the area and perimeter of a rectangle 270
Calculating the area and perimeter of a circle 270
Calculating the area of a trapezoid 271
Calculating the area of a triangle 271
Calculating the surface and volume of a sphere 271
Calculating the surface and volume of a cube 271
Calculating the surface and volume of a rectangular solid 272
Calculating the surface and volume of a cone 272
Calculating the volume of a cylinder 272
Calculating the volume of a pyramid 273
Solving Simultaneous Equations 273
Working with Normal Distributions 274
Part III: Financial Formulas
Chapter 11: Borrowing and Investing Formulas 279
The Time Value of Money 279
Loan Calculations 280
Worksheet functions for calculating loan information 281
A loan calculation example 284
Credit card payments 285
Creating a loan amortization schedule 287
Calculating a loan with irregular payments 288
Investment Calculations 290
Future value of a single deposit 290
Present value of a series of payments 296
Future value of a series of deposits 296
Chapter 12: Discounting and Depreciation Formulas 299
Using the NPV Function 299
Definition of NPV 300
NPV function examples 301
Using the IRR Function 306
Rate of return 307
Geometric growth rates 308
Checking results 309
Irregular Cash Flows 310
Net present value 310
Internal rate of return 311
Depreciation Calculations 312
Chapter 13: Financial Schedules 317
Creating Financial Schedules 317
Creating Amortization Schedules 318
A simple amortization schedule 318
A dynamic amortization schedule 320
Credit card calculations 323
Summarizing Loan Options Using a Data Table 325
Creating a one-way data table 325
Creating a two-way data table 327
Financial Statements and Ratios 329
Basic financial statements 329
Ratio analysis 333
Creating Indices 337
Part IV: Array Formulas
Chapter 14: Introducing Arrays 341
Introducing Array Formulas 341
A multicell array formula 342
A single‐cell array formula 343
Creating an array constant 344
Array constant elements 345
Understanding the Dimensions of an Array 346
One‐dimensional horizontal arrays 346
One‐dimensional vertical arrays 347
Two‐dimensional arrays 347
Naming Array Constants 349
Working with Array Formulas 350
Entering an array formula 350
Selecting an array formula range 350
Editing an array formula 351
Expanding or contracting a multicell array formula 352
Using Multicell Array Formulas 353
Creating an array from values in a range 353
Creating an array constant from values in a range 353
Performing operations on an array 354
Using functions with an array 355
Transposing an array 355
Generating an array of consecutive integers 357
Using Single‐Cell Array Formulas 358
Counting characters in a range 358
Summing the three smallest values in a range 359
Counting text cells in a range 360
Eliminating intermediate formulas 362
Using an array in lieu of a range reference 364
Chapter 15: Performing Magic with Array Formulas 365
Working with Single‐Cell Array Formulas 365
Summing a range that contains errors 366
Counting the number of error values in a range 367
Summing the n largest values in a range 368
Computing an average that excludes zeros 368
Determining whether a particular value appears in a range 369
Counting the number of differences in two ranges 371
Returning the location of the maximum value in a range 372
Finding the row of a value’s nth occurrence in a range 373
Returning the longest text in a range 373
Determining whether a range contains valid values 374
Summing the digits of an integer 375
Summing rounded values 377
Summing every nth value in a range 377
Removing nonnumeric characters from a string 379
Determining the closest value in a range 380
Returning the last value in a column 380
Returning the last value in a row 381
Working with Multicell Array Formulas 382
Returning only positive values from a range 382
Returning nonblank cells from a range 384
Reversing the order of cells in a range 384
Sorting a range of values dynamically 385
Returning a list of unique items in a range 386
Displaying a calendar in a range 387
Part V: Miscellaneous Formula Techniques
Chapter 16: Importing and Cleaning Data 393
A Few Words About Data 393
Importing Data 394
Importing from a file 394
Importing a text file into a specified range 396
Copying and pasting data 398
Data Cleanup Techniques 398
Removing duplicate rows 398
Identifying duplicate rows 400
Splitting text 401
Changing the case of text 407
Removing extra spaces 408
Removing strange characters 409
Converting values 409
Classifying values 410
Joining columns 411
Rearranging columns 412
Randomizing the rows 412
Matching text in a list 413
Change vertical data to horizontal data 414
Filling gaps in an imported report 417
Spelling checking 418
Replacing or removing text in cells 419
Adding text to cells 420
Fixing trailing minus signs 420
A Data Cleaning Checklist 421
Exporting Data 422
Exporting to a text file 422
Exporting to other file formats 423
Chapter 17: Charting Techniques 425
Understanding the SERIES Formula 425
Using names in a SERIES formula 427
Unlinking a chart series from its data range 428
Creating Links to Cells 429
Adding a chart title link 429
Adding axis title links 430
Adding text links 430
Adding a linked picture to a chart 430
Chart Examples 431
Single data point charts 431
Displaying conditional colors in a column chart 433
Creating a comparative histogram 434
Creating a Gantt chart 435
Creating a box plot 438
Plotting every nth data point 439
Identifying maximum and minimum values in a chart 441
Creating a Timeline 442
Plotting mathematical functions 443
Plotting a circle 448
Creating a clock chart 450
Creating awesome designs 452
Working with Trendlines 453
Linear trendlines 454
Working with nonlinear trendlines 460
Summary of trendline equations 461
Creating Interactive Charts 462
Selecting a series from a drop‐down list 462
Plotting the last n data points 463
Choosing a start date and number of points 464
Displaying population data 465
Displaying weather data 465
Chapter 18: Pivot Tables 469
About Pivot Tables 469
A Pivot Table Example 470
Data Appropriate for a Pivot Table 472
Creating a Pivot Table Automatically 475
Creating a Pivot Table Manually 477
Specifying the data 477
Specifying the location for the pivot table 478
Laying out the pivot table 480
Formatting the pivot table 481
Modifying the pivot table 483
More Pivot Table Examples 485
Question 1 485
Question 2 486
Question 3 487
Question 4 487
Question 5 488
Question 6 489
Question 7 490
Grouping Pivot Table Items 491
A manual grouping example 491
Viewing grouped data 493
Automatic grouping examples 494
Creating a Frequency Distribution 498
Creating a Calculated Field or Calculated Item 499
Creating a calculated field 501
Inserting a calculated item 503
Filtering Pivot Tables with Slicers 506
Filtering Pivot Tables with a Timeline 507
Referencing Cells Within a Pivot Table 508
Another Pivot Table Example 510
Using the Data Model 513
Creating Pivot Charts 516
A pivot chart example 517
More about pivot charts 519
Chapter 19: Conditional Formatting 521
About Conditional Formatting 521
Specifying Conditional Formatting 523
Formatting types you can apply 523
Making your own rules 524
Conditional Formats That Use Graphics 525
Using data bars 525
Using color scales 527
Using icon sets 530
Creating Formula-Based Rules 533
Understanding relative and absolute references 534
Conditional formatting formula examples 536
Working with Conditional Formats 543
Managing rules 544
Copying cells that contain conditional formatting 544
Deleting conditional formatting 545
Locating cells that contain conditional formatting 545
Chapter 20: Using Data Validation 547
About Data Validation 547
Specifying Validation Criteria 548
Types of Validation Criteria You Can Apply 549
Creating a Drop‐Down List 551
Using Formulas for Data Validation Rules 552
Understanding Cell References 552
Data Validation Formula Examples 554
Accepting text only 554
Accepting a larger value than the previous cell 554
Accepting nonduplicate entries only 554
Accepting text that begins with a specific character 555
Accepting dates by the day of the week 556
Accepting only values that don’t exceed a total 556
Creating a dependent list 557
Using Structured Table Referencing 558
Chapter 21: Creating Megaformulas 561
What Is a Megaformula? 561
Creating a Megaformula: A Simple Example 562
Megaformula Examples 564
Using a megaformula to remove middle names 564
Using a megaformula to return a string’s last space character position 569
Using a megaformula to determine the validity of a credit card number 573
Using Intermediate Named Formulas 578
Generating random names 579
The Pros and Cons of Megaformulas 580
Chapter 22: Tools and Methods for Debugging Formulas 581
Formula Debugging? 581
Formula Problems and Solutions 582
Mismatched parentheses 583
Cells are filled with hash marks 584
Blank cells are not blank 584
Extra space characters 585
Formulas returning an error 585
Absolute/relative reference problems 590
Operator precedence problems 591
Formulas are not calculated 592
Actual versus displayed values 592
Floating‐point number errors 593
Phantom link errors 594
Logical value errors 595
Circular reference errors 596
Excel’s Auditing Tools 596
Identifying cells of a particular type 596
Viewing formulas 597
Tracing cell relationships 598
Tracing error values 600
Fixing circular reference errors 600
Using background error checking 600
Using Excel’s Formula Evaluator 603
Part VI: Developing Custom Worksheet Functions
Chapter 23: Introducing VBA 607
Fundamental Macro Concepts 607
Activating the Developer tab 608
Recording a macro 608
Understanding macro-enabled extensions 611
Macro security in Excel 611
Trusted locations 611
Storing macros in your Personal Macro Workbook 612
Assigning a macro to a button and other form controls 612
Placing a macro on the Quick Access toolbar 614
Working in the Visual Basic Editor 615
Understanding VBE components 615
Working with the Project window 616
Working with a code window 619
Customizing the VBA environment 622
Chapter 24: VBA Programming Concepts 627
A Brief Overview of the Excel Object Model 627
Understanding objects 628
Understanding collections 628
Understanding properties 629
Understanding methods 629
A brief look at variables 630
Error handling 633
Using code comments 636
An Introductory Example Function Procedure 636
Using Built-In VBA Functions 638
Controlling Execution 640
The If-Then construct 640
The Select Case construct 642
Looping blocks of instructions 643
Using Ranges 648
The For Each-Next construct 648
Referencing a range 649
Some useful properties of ranges 651
The Set keyword 655
The Intersect function 655
The Union function 656
The UsedRange property 656
Chapter 25: Function Procedure Basics 659
Why Create Custom Functions? 659
An Introductory VBA Function Example 660
About Function Procedures 662
Declaring a function 662
Choosing a name for your function 663
Using functions in formulas 664
Using function arguments 665
Using the Insert Function Dialog Box 665
Adding a function description 666
Specifying a function category 667
Adding argument descriptions 669
Testing and Debugging Your Functions 670
Using the VBA MsgBox statement 671
Using Debug.Print statements in your code 673
Calling the function from a Sub procedure 673
Setting a breakpoint in the function 676
Creating Add-Ins for Functions 676
Chapter 26: VBA Custom Function Examples 679
Simple Functions 679
Is the cell hidden? 680
Returning a worksheet name 680
Returning a workbook name 681
Returning the application’s name 681
Returning Excel’s version number 682
Returning cell formatting information 682
Determining a Cell’s Data Type 684
A Multifunctional Function 685
Generating Random Numbers 688
Generating random numbers that don’t change 688
Selecting a cell at random 690
Calculating Sales Commissions 691
A function for a simple commission structure 691
A function for a more complex commission structure 692
Text Manipulation Functions 693
Reversing a string 694
Scrambling text 694
Returning an acronym 695
Does the text match a pattern? 695
Does a cell contain a particular word? 696
Does a cell contain text? 698
Extracting the nth element from a string 698
Spelling out a number 699
Counting Functions 700
Counting pattern-matched cells 700
Counting sheets in a workbook 700
Counting words in a range 701
Date Functions 701
Calculating the next Monday 702
Calculating the next day of the week 702
Which week of the month? 703
Working with dates before 1900 703
Returning the Last Nonempty Cell in a Column or Row 704
The LASTINCOLUMN function 705
The LASTINROW function 705
Multisheet Functions 706
Returning the maximum value across all worksheets 706
The SHEETOFFSET function 708
Advanced Function Techniques 709
Returning an error value 709
Returning an array from a function 710
Returning an array of nonduplicated random integers 712
Randomizing a range 714
Using optional arguments 716
Using an indefinite number of arguments 717
Part VII: Appendixes
Appendix A: Excel Function Reference 725
Appendix B: Using Custom Number Formats 743
Index 763
