Microsoft Excel Formulas and Functions (Office 2021 and Microsoft 365)

Microsoft Excel Formulas and Functions (Office 2021 and Microsoft 365)

by Paul McFedries
Released March 2022
Publisher(s): Microsoft Press
ISBN: 9780137559268

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Expert Paul McFedries helps you master key Excel tools for building more powerful spreadsheets.

Use Excel core features to build spreadsheets that solve business problems and deliver reliable answers. Drawing on his unsurpassed experience, Paul McFedries helps you make the most of formulas and functions, including the latest improvements to arrays, formula error handling, and statistics. McFedries’ step-by-step projects walk you through handling key tasks, from building timesheets to projecting cash flow and aging receivables. His practical examples and clear instructions demystify intermediate- to advanced-level formula construction, and help you leverage Excel’s most useful functions in your everyday work. Becoming an Excel expert has never been easier!

By reading this book, you will:

  • Improve business analyses by adding intelligence and knowledge to your models

  • Replace cumbersome formulas with convenient predefined functions

  • Radically simplify complex calculations with Office 365’s new dynamic arrays

  • Use conditional formatting to reveal anomalies, problems, or opportunities

  • Calculate loan payments, interest costs, terms, and amortization schedules

  • Project the future value of investments, and plan to achieve investment goals

  • Master essential discounting and cash-flow analysis tools, including net present value and internal rate of return

  • Sort, filter, and analyze tabular data, from customers to inventory

  • Easily analyze huge data sets with PivotTable calculations

About This Book

  • For everyone who wants to get more done with Microsoft Excel in less time

  • For business and financial professionals, entrepreneurs, students, and others who need to efficiently manage and analyze data

Table of contents

  1. Cover Page
  2. Title Page
  3. Copyright Page
  4. Pearson’s Commitment to Diversity, Equity, and Inclusion
  5. Contents at a Glance
  6. Contents
  7. Acknowledgments
  8. About the author
  9. Introduction
    1. What’s in the book
    2. This book’s special features
    3. About the companion content
    4. Support and feedback
  10. Part 1. Mastering Excel Formulas
    1. Chapter 1. Building basic formulas
      1. Understanding formula basics
      2. Understanding operator precedence
      3. Controlling worksheet calculation
      4. Copying and moving formulas
      5. Displaying worksheet formulas
      6. Converting a formula to a value
      7. Working with range names in formulas
      8. Working with links in formulas
    2. Chapter 2. Creating advanced formulas
      1. Working with arrays
      2. Using iteration and circular references
      3. Consolidating multisheet data
      4. Applying data validation rules to cells
      5. Using dialog box controls on a worksheet
    3. Chapter 3. Troubleshooting formulas
      1. Understanding Excel’s error values
      2. Fixing other formula errors
      3. Handling formula errors with IFERROR
      4. Using the formula error checker
      5. Auditing a worksheet
  11. Part 2. Harnessing the Power of Functions
    1. Chapter 4. Understanding functions
      1. About Excel’s functions
      2. The structure of a function
      3. Typing a function into a formula
      4. Using the Insert Function feature
      5. Loading the Analysis ToolPak
    2. Chapter 5. Working with text functions
      1. Excel’s text functions
      2. Working with characters and codes
      3. Converting text
      4. Formatting text
      5. Manipulating text
      6. Searching for substrings
      7. Substituting one substring for another
    3. Chapter 6. Working with logical and information functions
      1. Adding intelligence with logical functions
      2. Getting data with information functions
    4. Chapter 7. Working with lookup functions
      1. Taking a look at Excel’s lookup functions
      2. Understanding lookup tables
      3. The CHOOSE function
      4. Looking up values in ranges or tables
      5. Modern lookups with XLOOKUP
    5. Chapter 8. Working with date and time functions
      1. How Excel deals with dates and times
      2. Using Excel’s time functions
    6. Chapter 9. Working with math functions
      1. Excel’s math and trig functions
      2. Understanding Excel’s rounding functions
      3. Summing values
      4. The MOD function
      5. Generating random numbers
      6. Programming with Excel’s formula language
  12. Part 3. Building Business Formulas
    1. Chapter 10. Implementing basic business formulas
      1. Pricing formulas
      2. Financial formulas
      3. Inventory formulas
      4. Liquidity formulas
    2. Chapter 11. Building descriptive statistical formulas
      1. Understanding descriptive statistics
      2. Counting items
      3. Calculating averages
      4. Calculating extreme values
      5. Working with rank and percentile
      6. Calculating measures of variation
      7. Working with frequency distributions
    3. Chapter 12. Building inferential statistical formulas
      1. Understanding inferential statistics
      2. Sampling data
      3. Determining whether two variables are related
      4. Working with probability distributions
      5. Determining confidence intervals
      6. Hypothesis testing
    4. Chapter 13. Applying regression to track trends and make forecasts
      1. Choosing a regression method
      2. Using simple regression on linear data
      3. Using simple regression on nonlinear data
      4. Using multiple regression analysis
    5. Chapter 14. Building loan formulas
      1. Understanding the time value of money
      2. Calculating a loan payment
      3. Building a loan amortization schedule
      4. Calculating the term of a loan
      5. Calculating the interest rate required for a loan
      6. Calculating how much you can borrow
    6. Chapter 15. Working with investment formulas
      1. Working with interest rates
      2. Calculating the future value
      3. Working toward an investment goal
    7. Chapter 16. Building discount formulas
      1. Calculating the present value
      2. Discounting cash flows
      3. Calculating the payback period
      4. Calculating the internal rate of return
  13. Part 4. Building Business Models
    1. Chapter 17. Analyzing data with tables
      1. Sorting a table
      2. Filtering table data
      3. Referencing tables in formulas
      4. Excel’s table functions
    2. Chapter 18. Analyzing data with PivotTables
      1. Working with PivotTable subtotals
      2. Changing the value field summary calculation
      3. Creating custom PivotTable calculations
      4. Using PivotTable results in a worksheet formula
    3. Chapter 19. Using Excel’s business modeling tools
      1. Using what-if analysis
      2. Working with Goal Seek
      3. Working with scenarios
    4. Chapter 20. Solving complex problems with Solver
      1. Some background on Solver
      2. Loading Solver
      3. Using Solver
      4. Adding constraints
      5. Saving a solution as a scenario
      6. Setting other Solver options
      7. Making sense of Solver’s messages
      8. Displaying Solver’s reports
  14. Index
  15. Code Snippets

Product information

  • Title: Microsoft Excel Formulas and Functions (Office 2021 and Microsoft 365)
  • Author(s): Paul McFedries
  • Release date: March 2022
  • Publisher(s): Microsoft Press
  • ISBN: 9780137559268