Introduction                                       1  (1)
      What's in the Book                           2  (1)
      This Book's Special Features                 2  (5)
  I. MASTERING EXCEL RANGES AND FORMULAS
    Getting the Most Out of Ranges                 7  (30)
      Advanced Range-Selection Techniques          7  (8)
        Mouse Range-Selection Tricks               8  (1)
        Keyboard Range-Selection Tricks            9  (1)
        Working with 3D Ranges                     9  (1)
        Selecting a Range Using Go To              10 (1)
        Using the Go To Special Dialog Box         11 (4)
      Data Entry in a Range                        15 (1)
      Filling a Range                              16 (1)
      Using the Fill Handle                        16 (3)
        Using AutoFill to Create Text and          16 (2)
        Numeric Series
        Creating a Custom AutoFill List            18 (1)
        Filling a Range                            19 (1)
      Creating a Series                            19 (1)
      Advanced Range Copying                       20 (3)
        Copying Selected Cell Attributes           20 (2)
        Combining the Source and Destination       22 (1)
        Arithmetically
        Transposing Rows and Columns               23 (1)
      Clearing a Range                             23 (1)
      Applying Conditional Formatting to a Range   24 (13)
        Creating Highlight Cells Rules             24 (2)
        Creating Top/Bottom Rules                  26 (2)
        Adding Data Bars                           28 (3)
        Adding Color Scales                        31 (2)
        Adding Icon Sets                           33 (2)
        From Here                                  35 (2)
    Using Range Names                              37 (18)
      Defining a Range Name                        38 (7)
        Working with the Name Box                  38 (1)
        Using the New Name Dialog Box              39 (2)
        Changing the Scope to Define               41 (1)
        Sheet-Level Names
        Using Worksheet Text to Define Names       41 (3)
        Naming Constants                           44 (1)
      Working with Range Names                     45 (10)
        Referring to a Range Name                  46 (1)
        Working with Name AutoComplete             47 (1)
        Navigating Using Range Names               48 (1)
        Pasting a List of Range Names in a         48 (1)
        Worksheet
        Displaying the Name Manager                48 (1)
        Filtering Names                            49 (1)
        Editing a Range Name's Coordinates         49 (1)
        Adjusting Range Name Coordinates           50 (1)
        Automatically
        Changing a Range Name                      51 (1)
        Deleting a Range Name                      52 (1)
        Using Names with the Intersection          52 (1)
        Operator
        From Here                                  53 (2)
    Building Basic Formulas                        55 (34)
      Understanding Formula Basics                 55 (4)
        Formula Limits in Excel 2007               56 (1)
        Entering and Editing Formulas              56 (1)
        Using Arithmetic Formulas                  57 (1)
        Using Comparison Formulas                  58 (1)
        Using Text Formulas                        59 (1)
        Using Reference Formulas                   59 (1)
      Understanding Operator Precedence            59 (3)
        The Order of Precedence                    60 (1)
        Controlling the Order of Precedence        60 (2)
      Controlling Worksheet Calculation            62 (2)
      Copying and Moving Formulas                  64 (3)
        Understanding Relative Reference Format    65 (1)
        Understanding Absolute Reference Format    66 (1)
        Copying a Formula Without Adjusting        66 (1)
        Relative References
      Displaying Worksheet Formulas                67 (1)
      Converting a Formula to a Value              67 (1)
      Working with Range Names in Formulas         68 (4)
        Pasting a Name into a Formula              68 (1)
        Applying Names to Formulas                 69 (3)
        Naming Formulas                            72 (1)
      Working with Links in Formulas               72 (3)
        Understanding External References          73 (1)
        Updating Links                             74 (1)
        Changing the Link Source                   75 (1)
      Formatting Numbers, Dates, and Times         75 (14)
        Numeric Display Formats                    76 (7)
        Date and Time Display Formats              83 (3)
        Deleting Custom Formats                    86 (1)
        From Here                                  87 (2)
    Creating Advanced Formulas                     89 (24)
      Working with Arrays                          89 (6)
        Using Array Formulas                       90 (3)
        Using Array Constants                      93 (1)
        Functions That Use or Return Arrays        94 (1)
      Using Iteration and Circular References      95 (2)
      Consolidating Multisheet Data                97 (5)
        Consolidating by Position                  97 (4)
        Consolidating by Category                  101(1)
      Applying Data-Validation Rules to Cells      102(3)
      Using Dialog Box Controls on a Worksheet     105(8)
        Using the Form Controls                    105(1)
        Adding a Control to a Worksheet            106(1)
        Linking a Control to a Cell Value          106(1)
        Understanding the Worksheet Controls       107(4)
        From Here                                  111(2)
    Troubleshooting Formulas                       113(20)
      Understanding Excel's Error Values           114(4)
        #DIV/0!                                    114(1)
        #N/A                                       115(1)
        #NAME?                                     115(1)
        Avoiding #Name? Errors When Deleting       116(1)
        Range Names
        #Null!                                     117(1)
        #Num!                                      117(1)
        #Ref!                                      117(1)
        #Value!                                    118(1)
      Fixing Other Formula Errors                  118(3)
        Missing or Mismatched Parentheses          118(1)
        Erroneous Formula Results                  119(1)
        Fixing Circular References                 120(1)
      Handling Formula Errors with IFERROR()       121(1)
      Using the Formula Error Checker              122(4)
        Choosing an Error Action                   123(1)
        Setting Error Checker Options              123(3)
      Auditing a Worksheet                         126(7)
        Understanding Auditing                     126(1)
        Tracing Cell Precedents                    127(1)
        Tracing Cell Dependents                    127(1)
        Tracing Cell Errors                        127(1)
        Removing Tracer Arrows                     128(1)
        Evaluating Formulas                        128(1)
        Watching Cell Values                       129(1)
        From Here                                  129(4)
  II. HARNESSING THE POWER OF FUNCTIONS
    Understanding Functions                        133(10)
      About Excel's Functions                      134(1)
      The Structure of a Function                  134(2)
      Typing a Function into a Formula             136(2)
      Using the Insert Function Feature            138(2)
      Loading the Analysis ToolPak                 140(3)
        From Here                                  141(2)
    Working with Text Functions                    143(24)
      Excel's Text Functions                       144(1)
      Working with Characters and Codes            145(4)
        The Char() Function                        145(3)
        The Code() Function                        148(1)
      Converting Text                              149(1)
        The Lower() Function                       149(1)
        The Upper() Function                       149(1)
        The Proper() Function                      149(1)
      Formatting Text                              150(2)
        The Dollar() Function                      150(1)
        The Fixed() Function                       151(1)
        The Text() Function                        151(1)
        Displaying When a Workbook Was Last        151(1)
        Updated
      Manipulating Text                            152(1)
      Removing Unwanted Characters from a String   152(3)
        The Trim() Function                        152(1)
        The Clean() Function                       153(1)
        The Rept() Function: Repeating a           153(1)
        Character
        Padding a Cell                             154(1)
        Building Text Charts                       154(1)
      Extracting a Substring                       155(3)
        The Left() Function                        156(1)
        The Right() Function                       156(1)
        The Mid() Function                         156(1)
        Converting Text to Sentence Case           156(1)
        A Date-Conversion Formula                  157(1)
      Generating Account Numbers                   158(1)
      Searching for Substrings                     158(4)
        The Find() and Search() Functions          158(1)
        Extracting a First Name or Last Name       159(1)
        Extracting First Name, Last Name, and      160(1)
        Middle Initial
        Determining the Column Letter              161(1)
      Substituting One Substring for Another       162(3)
        The Replace() Function                     162(1)
        The Substitute() Function                  163(1)
        Removing a Character from a String         163(1)
        Removing Two Different Characters from     164(1)
        a String
        Removing Line Feeds                        164(1)
      Generating Account Numbers, Part 2           165(2)
        From Here                                  165(2)
    Working with Logical and Information           167(28)
    Functions
      Adding Intelligence with Logical Functions   167(15)
        Using the IF() Function                    168(3)
        Performing Multiple Logical Tests          171(5)
        Combining Logical Functions with Arrays    176(6)
      Building an Accounts Receivable Aging        182(2)
      Worksheet
        Calculating a Smarter Due Date             182(1)
        Aging Overdue Invoices                     183(1)
      Getting Data with Information Functions      184(11)
        The Cell() Function                        186(2)
        The Error.Type() Function                  188(1)
        The Info() Function                        189(2)
        The IS Functions                           191(2)
        From Here                                  193(2)
    Working with Lookup Functions                  195(18)
      Understanding Lookup Tables                  196(1)
      The Choose() Function                        197(3)
        Determining the Name of the Day of the     198(1)
        Week
        Determining the Month of the Fiscal Year   198(1)
        Calculating Weighted Questionnaire         199(1)
        Results
        Integrating Choose() and Worksheet         200(1)
        Option Buttons
      Looking Up Values in Tables                  200(13)
        The Vlookup() Function                     201(1)
        The Hlookup() Function                     202(1)
        Returning a Customer Discount Rate with    202(1)
        a Range Lookup
        Returning a Tax Rate with a Range Lookup   203(1)
        Finding Exact Matches                      204(2)
        Advanced Lookup Operations                 206(5)
        From Here                                  211(2)
    Working with Date and Time Functions           213(30)
      How Excel Deals with Dates and Times         213(3)
        Entering Dates and Times                   214(1)
        Excel and Two-Digit Years                  215(1)
      Using Excel's Date Functions                 216(17)
        Returning a Date                           218(1)
        Returning Parts of a Date                  219(10)
        Calculating the Difference Between Two     229(4)
        Dates
      Using Excel's Time Functions                 233(5)
        Returning a Time                           234(1)
        Returning Parts of a Time                  235(2)
        Calculating the Difference Between Two     237(1)
        Times
      Building an Employee Time Sheet              238(5)
        From Here                                  241(2)
    Working with Math Functions                    243(20)
      Understanding Excel's Rounding Functions     247(6)
        The Round() Function                       247(1)
        The Mround() Function                      248(1)
        The Rounddown() and Roundup() Functions    248(1)
        The Ceiling() and Floor() Functions        249(1)
        Determining the Fiscal Quarter in Which    249(1)
        a Date Falls
        Calculating Easter Dates                   250(1)
        The Even() and Odd() Functions             250(1)
        The Int() and Trunc() Functions            251(1)
        Using Rounding to Prevent Calculation      251(1)
        Errors
        Setting Price Points                       252(1)
      Rounding Billable Time                       253(1)
      Summing Values                               253(2)
        The Sum() Function                         253(1)
        Calculating Cumulative Totals              254(1)
        Summing Only the Positive or Negative      255(1)
        Values in a Range
      The Mod() Function                           255(4)
        A Better Formula for Time Differences      256(1)
        Summing Every nth Row                      256(1)
        Determining Whether a Year Is a Leap       257(1)
        Year
        Creating Ledger Shading                    257(2)
      Generating Random Numbers                    259(4)
        The Rand() Function                        259(2)
        The Randbetween() Function                 261(1)
        From Here                                  262(1)
    Working with Statistical Functions             263(34)
      Understanding Descriptive Statistics         265(1)
      Counting Items with the Count() Function     266(1)
      Calculating Averages                         267(2)
        The Average() Function                     267(1)
        The Median() Function                      267(1)
        The Mode() Function                        268(1)
        Calculating the Weighted Mean              268(1)
      Calculating Extreme Values                   269(3)
        The Max() and Min() Functions              269(1)
        The Large() and Small() Functions          270(1)
        Performing Calculations on the Top k       271(1)
        Values
        Performing Calculations on the Bottom k    271(1)
        Values
      Calculating Measures of Variation            272(3)
        Calculating the Range                      272(1)
        Calculating the Variance with the Var()    272(1)
        Function
        Calculating the Standard Deviation with    273(2)
        the Stdevp() and STDEV() Functions
      Working with Frequency Distributions         275(5)
        The Frequency() Function                   275(1)
        Understanding the Normal Distribution      276(2)
        and the Normdist() Function
        The Shape of the Curve I: The Skew()       278(1)
        Function
        The Shape of the Curve II: The Kurt()      279(1)
        Function
      Using the Analysis ToolPak Statistical       280(17)
      Tools
        Using the Descriptive Statistics Tool      283(2)
        Determining the Correlation Between Data   285(2)
        Working with Histograms                    287(2)
        Using the Random Number Generation Tool    289(3)
        Working with Rank and Percentile           292(2)
        From Here                                  294(3)
  III. BUILDING BUSINESS MODELS
    Analyzing Data with Tables                     297(34)
      Converting a Range to a Table                299(1)
      Basic Table Operations                       300(1)
      Sorting a Table                              301(5)
        Sorting a Table in Natural Order           303(1)
        Sorting on Part of a Field                 304(1)
        Sorting Without Articles                   305(1)
      Filtering Table Data                         306(10)
        Using Filter Lists to Filter a Table       306(4)
        Using Complex Criteria to Filter a Table   310(3)
        Entering Computed Criteria                 313(2)
        Copying Filtered Data to a Different       315(1)
        Range
      Referencing Tables in Formulas               316(4)
        Using Table Specifiers                     316(2)
        Entering Table Formulas                    318(2)
      Excel's Table Functions                      320(11)
        About Table Functions                      320(1)
        Table Functions That Don't Require a       320(3)
        Criteria Range
        Table Functions That Accept Multiple       323(2)
        Criteria
        Table Functions That Require a Criteria    325(4)
        Range
        Applying Statistical Table Functions to    329(1)
        a Defects Database
        From Here                                  330(1)
    Analyzing Data with PivotTables                331(30)
      What Are PivotTables?                        331(4)
        How PivotTables Work                       332(2)
        Some PivotTable Terms                      334(1)
      Building PivotTables                         335(5)
        Building a PivotTable from a Table or      335(3)
        Range
        Building a PivotTable from an External     338(1)
        Database
        Working with and Customizing a             339(1)
        PivotTable
      Working with PivotTable Subtotals            340(1)
        Hiding PivotTable Grand Totals             341(1)
        Hiding PivotTable Subtotals                341(1)
        Customizing the Subtotal Calculation       341(1)
      Changing the Data Field Summary              341(9)
      Calculation
        Using a Difference Summary Calculation     342(2)
        Using a Percentage Summary Calculation     344(3)
        Using a Running Total Summary              347(1)
        Calculation
        Using an Index Summary Calculation         348(2)
      Creating Custom PivotTable Calculations      350(5)
        Creating a Calculated Field                352(1)
        Creating a Calculated Item                 353(2)
      Budgeting with Calculated Items              355(2)
      Using PivotTable Results in a Worksheet      357(4)
      Formula
        From Here                                  359(2)
    Using Excel's Business-Modeling Tools          361(24)
      Using What-If Analysis                       361(6)
        Setting Up a One-Input Data Table          362(2)
        Adding More Formulas to the Input Table    364(1)
        Setting Up a Two-Input Table               365(2)
        Editing a Data Table                       367(1)
      Working with Goal Seek                       367(7)
        How Does Goal Seek Work?                   367(1)
        Running Goal Seek                          368(2)
        Optimizing Product Margin                  370(1)
        A Note About Goal Seek's Approximations    371(1)
        Performing a Break-Even Analysis           372(1)
        Solving Algebraic Equations                373(1)
      Working with Scenarios                       374(11)
        Understanding Scenarios                    375(1)
        Setting Up Your Worksheet for Scenarios    376(1)
        Adding a Scenario                          376(2)
        Displaying a Scenario                      378(1)
        Editing a Scenario                         379(1)
        Merging Scenarios                          379(1)
        Generating a Summary Report                380(2)
        Deleting a Scenario                        382(1)
        From Here                                  382(3)
    Using Regression to Track Trends and Make      385(42)
    Forecasts
      Choosing a Regression Method                 386(1)
      Using Simple Regression on Linear Data       386(14)
        Analyzing Trends Using Best-Fit Lines      387(8)
        Making Forecasts                           395(5)
      Trend Analysis and Forecasting for a         400(9)
      Seasonal Sales Model
      Using Simple Regression on Nonlinear Data    409(14)
        Working with an Exponential Trend          409(6)
        Working with a Logarithmic Trend           415(2)
        Working with a Power Trend                 417(3)
        Using Polynomial Regression Analysis       420(3)
      Using Multiple Regression Analysis           423(4)
        From Here                                  426(1)
    Solving Complex Problems with Solver           427(22)
      Some Background on Solver                    427(2)
        The Advantages of Solver                   428(1)
        When Do You Use Solver?                    428(1)
      Loading Solver                               429(1)
      Using Solver                                 429(3)
      Adding Constraints                           432(2)
      Saving a Solution as a Scenario              434(1)
      Setting Other Solver Options                 434(4)
        Controlling Solver                         435(1)
        Selecting the Method Solver Uses           436(1)
        Working with Solver Models                 437(1)
      Making Sense of Solver's Messages            438(1)
      Solving the Transportation Problem           439(2)
      Displaying Solver's Reports                  441(8)
        The Answer Report                          442(1)
        The Sensitivity Report                     443(2)
        The Limits Report                          445(1)
        From Here                                  445(4)
  IV. BUILDING FINANCIAL FORMULAS
    Building Loan Formulas                         449(20)
      Understanding the Time Value of Money        449(1)
      Calculating the Loan Payment                 450(6)
        Loan Payment Analysis                      451(1)
        Working with a Balloon Loan                452(1)
        Calculating Interest Costs, Part I         453(1)
        Calculating the Principal and Interest     453(1)
        Calculating Interest Costs, Part 2         454(1)
        Calculating Cumulative Principal and       455(1)
        Interest
      Building a Loan Amortization Schedule        456(3)
        Building a Fixed-Rate Amortization         457(1)
        Schedule
        Building a Dynamic Amortization Schedule   458(1)
      Calculating the Term of the Loan             459(2)
      Calculating the Interest Rate Required       461(1)
      for a Loan
      Calculating How Much You Can Borrow          462(1)
      Working with Mortgages                       463(6)
        Building a Variable-Rate Mortgage          464(1)
        Amortization Schedule
        Allowing for Mortgage Principal Paydowns   465(2)
        From Here                                  467(2)
    Building Investment Formulas                   469(14)
      Working with Interest Rates                  469(3)
        Understanding Compound Interest            470(1)
        Nominal Versus Effective Interest          470(1)
        Converting Between the Nominal Rate and    471(1)
        the Effective Rate
      Calculating the Future Value                 472(2)
        The Future Value of a Lump Sum             473(1)
        The Future Value of a Series of Deposits   473(1)
        The Future Value of a Lump Sum Plus        474(1)
        Deposits
      Working Toward an Investment Goal            474(5)
        Calculating the Required Interest Rate     474(1)
        Calculating the Required Number of         475(1)
        Periods
        Calculating the Required Regular Deposit   476(1)
        Calculating the Required Initial Deposit   477(1)
        Calculating the Future Value with          478(1)
        Varying Interest Rates
      Building an Investment Schedule              479(4)
        From Here                                  481(2)
    Building Discount Formulas                     483(22)
      Calculating the Present Value                484(4)
        Taking Inflation into Account              484(1)
        Calculating Present Value Using PV()       485(1)
        Income Investing Versus Purchasing a       486(1)
        Rental Property
        Buying Versus Leasing                      487(1)
      Discounting Cash Flows                       488(5)
        Calculating the Net Present Value          489(1)
        Calculating Net Present Value Using        490(1)
        NPV()
        Net Present Value with Varying Cash        491(1)
        Flows
        Net Present Value with Nonperiodic Cash    492(1)
        Flows
      Calculating the Payback Period               493(3)
        Simple Undiscounted Payback Period         494(1)
        Exact Undiscounted Payback Point           495(1)
        Discounted Payback Period                  496(1)
      Calculating the Internal Rate of Return      496(3)
        Using the IRR() Function                   497(1)
        Calculating the Internal Rate of Return    498(1)
        for Nonperiodic Cash Flows
        Calculating Multiple Internal Rates of     498(1)
        Return
      Publishing a Book                            499(6)
        From Here                                  503(2)
Index                                              505