Cover image for Excel 2016 Formulas

Excel 2016 Formulas

Author Richard Kusleika , Michael Alexander
Release Date: 2016/01/01
ISBN: 9781119067863
Topic:
0%
45
Chapters
0-1
Hours read
0k
Total Words
    Start Reading Now    
       Add to Wishlist       
View table of contents

Book Description

Leverage 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. This comprehensive book explains how to create financial formulas, release the power of array formulas, develop custom worksheet functions with VBA, debug formulas, and much more. Whether you're a beginner, a power user, or somewhere in between this is your essential go-to for the latest on Excel formulas.

When conducting simple math or building highly complicated spreadsheets that require formulas up to the task, leveraging the right formula can heighten the accuracy and efficiency of your work, and can improve the speed with which you compile and analyze data. Understanding which formulas to use and knowing how to create a formula when you need to are essential.

  • Access tips, tricks, and techniques that have been fully updated to reflect the latest capabilities of Microsoft Excel
  • Create and use formulas that have the power to transform your Excel experience
  • Leverage supplemental material online, including sample files, templates, and worksheets from the book

Table of Contents

  1. Introduction
    1. What You Need to Know
    2. What You Need to Have
    3. Conventions in This Book
    4. How This Book Is Organized
    5. About This Book’s Website
    6. About the Power Utility Pak Offer
  2. Part I: Understanding Formula Basics
    1. Chapter 1: The Excel User Interface in a Nutshell
      1. The Workings of Workbooks
      2. The Excel User Interface
      3. Protection Options
    2. Chapter 2: Basic Facts About Formulas
      1. Entering and Editing Formulas
      2. Using Operators in Formulas
      3. Calculating Formulas
      4. Cell and Range References
      5. Copying or Moving Formulas
      6. Making an Exact Copy of a Formula
      7. Converting Formulas to Values
      8. Hiding Formulas
      9. Errors in Formulas
      10. Dealing with Circular References
      11. Goal Seeking
    3. Chapter 3: Working with Names
      1. What’s in a Name?
      2. A Name’s Scope
      3. The Name Manager
      4. Shortcuts for Creating Cell and Range Names
      5. Creating Multisheet Names
      6. Working with Range and Cell Names
      7. How Excel Maintains Cell and Range Names
      8. Potential Problems with Names
      9. The Secret to Understanding Names
      10. Advanced Techniques That Use Names
  3. Part II: Leveraging Excel Functions
    1. Chapter 4: Introducing Worksheet Functions
      1. What Is a Function?
      2. Function Argument Types
      3. Ways to Enter a Function into a Formula
    2. Chapter 5: Manipulating Text
      1. A Few Words About Text
      2. Text Functions
      3. Advanced Text Formulas
    3. Chapter 6: Working with Dates and Times
      1. How Excel Handles Dates and Times
      2. Date-Related Functions
      3. Time-Related Functions
    4. Chapter 7: Counting and Summing Techniques
      1. Counting and Summing Worksheet Cells
      2. Other Counting Methods
      3. Basic Counting Formulas
      4. Advanced Counting Formulas
      5. Summing Formulas
      6. Conditional Sums Using a Single Criterion
      7. Conditional Sums Using Multiple Criteria
    5. Chapter 8: Using Lookup Functions
      1. What Is a Lookup Formula?
      2. Functions Relevant to Lookups
      3. Basic Lookup Formulas
      4. Specialized Lookup Formulas
    6. Chapter 9: Working with Tables and Lists
      1. Tables and Terminology
      2. Working with Tables
      3. Using Advanced Filtering
      4. Specifying Advanced Filter Criteria
      5. Using Database Functions
      6. Inserting Subtotals
    7. Chapter 10: Miscellaneous Calculations
      1. Unit Conversions
      2. Rounding Numbers
      3. Solving Right Triangles
      4. Area, Surface, Circumference, and Volume Calculations
      5. Solving Simultaneous Equations
      6. Working with Normal Distributions
  4. Part III: Financial Formulas
    1. Chapter 11: Borrowing and Investing Formulas
      1. The Time Value of Money
      2. Loan Calculations
      3. Investment Calculations
    2. Chapter 12: Discounting and Depreciation Formulas
      1. Using the NPV Function
      2. Using the IRR Function
      3. Irregular Cash Flows
      4. Depreciation Calculations
    3. Chapter 13: Financial Schedules
      1. Creating Financial Schedules
      2. Creating Amortization Schedules
      3. Summarizing Loan Options Using a Data Table
      4. Financial Statements and Ratios
      5. Creating Indices
  5. Part IV: Array Formulas
    1. Chapter 14: Introducing Arrays
      1. Introducing Array Formulas
      2. Understanding the Dimensions of an Array
      3. Naming Array Constants
      4. Working with Array Formulas
      5. Using Multicell Array Formulas
      6. Using Single-Cell Array Formulas
    2. Chapter 15: Performing Magic with Array Formulas
      1. Working with Single-Cell Array Formulas
      2. Working with Multicell Array Formulas
  6. Part V: Miscellaneous Formula Techniques
    1. Chapter 16: Importing and Cleaning Data
      1. A Few Words About Data
      2. Importing Data
      3. Data Cleanup Techniques
      4. A Data Cleaning Checklist
      5. Exporting Data
    2. Chapter 17: Charting Techniques
      1. Understanding the SERIES Formula
      2. Creating Links to Cells
      3. Chart Examples
      4. Creating a Timeline
      5. Working with Trendlines
      6. Creating Interactive Charts
    3. Chapter 18: Pivot Tables
      1. About Pivot Tables
      2. A Pivot Table Example
      3. Data Appropriate for a Pivot Table
      4. Creating a Pivot Table Automatically
      5. Creating a Pivot Table Manually
      6. More Pivot Table Examples
      7. Grouping Pivot Table Items
      8. Creating a Frequency Distribution
      9. Creating a Calculated Field or Calculated Item
      10. Filtering Pivot Tables with Slicers
      11. Filtering Pivot Tables with a Timeline
      12. Referencing Cells Within a Pivot Table
      13. Another Pivot Table Example
      14. Using the Data Model
      15. Creating Pivot Charts
    4. Chapter 19: Conditional Formatting
      1. About Conditional Formatting
      2. Specifying Conditional Formatting
      3. Conditional Formats That Use Graphics
      4. Creating Formula-Based Rules
      5. Working with Conditional Formats
    5. Chapter 20: Using Data Validation
      1. About Data Validation
      2. Specifying Validation Criteria
      3. Types of Validation Criteria You Can Apply
      4. Creating a Drop-Down List
      5. Using Formulas for Data Validation Rules
      6. Understanding Cell References
      7. Data Validation Formula Examples
    6. Chapter 21: Creating Megaformulas
      1. What Is a Megaformula?
      2. Creating a Megaformula: A Simple Example
      3. Megaformula Examples
      4. The Pros and Cons of Megaformulas
    7. Chapter 22: Tools and Methods for Debugging Formulas
      1. Formula Debugging?
      2. Formula Problems and Solutions
      3. Excel’s Auditing Tools
  7. Part VI: Developing Custom Worksheet Functions
    1. Chapter 23: Introducing VBA
      1. Fundamental Macro Concepts
      2. Working in the Visual Basic Editor
    2. Chapter 24: VBA Programming Concepts
      1. A Brief Overview of the Excel Object Model
      2. An Introductory Example Function Procedure
      3. Using Built-In VBA Functions
      4. Controlling Execution
      5. Using Ranges
    3. Chapter 25: Function Procedure Basics
      1. Why Create Custom Functions?
      2. An Introductory VBA Function Example
      3. About Function Procedures
      4. Using the Insert Function Dialog Box
      5. Testing and Debugging Your Functions
      6. Creating Add-Ins for Functions
    4. Chapter 26: VBA Custom Function Examples
      1. Simple Functions
      2. Determining a Cell’s Data Type
      3. A Multifunctional Function
      4. Generating Random Numbers
      5. Calculating Sales Commissions
      6. Text Manipulation Functions
      7. Counting Functions
      8. Date Functions
      9. Returning the Last Nonempty Cell in a Column or Row
      10. Multisheet Functions
      11. Advanced Function Techniques
  8. Part VII: Appendixes
    1. Appendix A: Excel Function Reference
    2. Appendix B: Using Custom Number Formats
      1. About Number Formatting
      2. Creating a Custom Number Format
      3. Custom Number Format Examples
  9. Advert
  10. EULA
3.128.255.174