Excel Formulas & Functions For Dummies, 6th Edition

Excel Formulas & Functions For Dummies, 6th Edition

by Ken Bluttman
Released December 2021
Publisher(s): For Dummies
ISBN: 9781119839118

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

Unlock the power of Excel with a step-by-step roadmap to its formulas and functions

There's a Swiss Army knife in your digital toolbox that can multiply your productivity and make you the smartest guy or gal in almost any room. It's called Microsoft Excel.

If you're like most people, you've barely scratched the surface of what this powerful tool's hundreds of built-in functions can do. But with a little help from Excel Formulas & Functions For Dummies, you'll soon be organizing, analyzing, and interpreting data like a pro.

For those who don't know the difference between a spreadsheet and a bedsheet, the book gets you up to speed with formula and function basics first. But you can also skip ahead to the fancy stuff and learn about working with probabilities, significance tests, and lookup functions.

This easy-to-use Excel formulas and functions survival guide shows you how to:

  • Work with financial functions like PMT, PPMT, NPER, RATE, and PV
  • Calculate mean, median, mode, standard deviation, and many more statistical functions
  • Troubleshoot formulas for common errors and validate your data to avoid mistakes
  • Work with dates, times, logic operators, conditions, and basic and advanced mathematical functions

You don't need a degree in data science or advanced mathematics to take advantage of the full functionality and flexibility of Microsoft Excel. Let Excel Formulas & Functions For Dummies show you how to transform this unassuming program into the most useful tool in your toolbox.

Table of contents

  1. Cover
  2. Title Page
  3. Copyright
  4. Introduction
    1. About This Book
    2. Foolish Assumptions
    3. Icons Used in This Book
    4. Beyond the Book
    5. Where to Go from Here
  5. Part 1: Getting Started with Excel Formulas and Functions
    1. Chapter 1: Tapping Into Formula and Function Fundamentals
      1. Working with Excel Fundamentals
      2. Gaining the Upper Hand on Formulas
      3. Using Functions in Formulas
    2. Chapter 2: Saving Time with Function Tools
      1. Getting Familiar with the Insert Function Dialog Box
      2. Finding the Correct Function
      3. Entering Functions Using the Insert Function Dialog Box
      4. Directly Entering Formulas and Functions
    3. Chapter 3: Saying “Array!” for Formulas and Functions
      1. Discovering Arrays
      2. Using Arrays in Formulas
      3. Working with Functions That Return Arrays
    4. Chapter 4: Fixing Formula Boo-Boos
      1. Catching Errors As You Enter Them
      2. Auditing Formulas
      3. Watching the Watch Window
      4. Evaluating and Checking Errors
      5. Making an Error Behave the Way You Want
  6. Part 2: Doing the Math
    1. Chapter 5: Calculating Loan Payments and Interest Rates
      1. Understanding How Excel Handles Money
      2. Figuring Loan Calculations
    2. Chapter 6: Appreciating What You’ll Get, Depreciating What You’ve Got
      1. Looking into the Future
      2. Depreciating the Finer Things in Life
      3. Measuring Your Internals
    3. Chapter 7: Using Basic Math Functions
      1. Adding It All Together with the SUM Function
      2. Rounding Out Your Knowledge
      3. Leaving All Decimals Behind with INT
      4. Leaving Some Decimals Behind with TRUNC
      5. Looking for a Sign
      6. Ignoring Signs
    4. Chapter 8: Advancing Your Math
      1. Using PI to Calculate Circumference and Diameter
      2. Generating and Using Random Numbers
      3. Ordering Items
      4. Combining
      5. Raising Numbers to New Heights
      6. Multiplying Multiple Numbers
      7. Using What Remains with the MOD Function
      8. Summing Things Up
      9. Getting an Angle on Trigonometry
  7. Part 3: Solving with Statistics
    1. Chapter 9: Throwing Statistics a Curve
      1. Getting Stuck in the Middle with AVERAGE, MEDIAN, and MODE
      2. Deviating from the Middle
      3. Analyzing Data with Percentiles and Bins
      4. Going for the Count
    2. Chapter 10: Using Significance Tests
      1. Testing to the T
      2. Comparing Results with an Estimate
    3. Chapter 11: Rolling the Dice on Predictions and Probability
      1. Modeling
      2. Getting It Straight: Using SLOPE and INTERCEPT to Describe Linear Data
      3. What’s Ahead: Using FORECAST, TREND, and GROWTH to Make Predictions
      4. Using NORM.DIST and POISSON.DIST to Determine Probabilities
  8. Part 4: Dancing with Data
    1. Chapter 12: Dressing Up for Date Functions
      1. Understanding How Excel Handles Dates
      2. Formatting Dates
      3. Making a Date with DATE
      4. Breaking a Date with DAY, MONTH, and YEAR
      5. Converting a Date from Text
      6. Finding Out What TODAY Is
      7. Determining the Day of the Week
      8. Working with Workdays
      9. Calculating Time Between Two Dates with the DATEDIF Function
    2. Chapter 13: Keeping Well-Timed Functions
      1. Understanding How Excel Handles Time
      2. Formatting Time
      3. Keeping TIME
      4. Converting Text to Time with TIMEVALUE
      5. Deconstructing Time with HOUR, MINUTE, and SECOND
      6. Finding the Time NOW
      7. Calculating Elapsed Time Over Days
    3. Chapter 14: Using Lookup, Logical, and Reference Functions
      1. Testing on One Condition
      2. Choosing the Right Value
      3. Let’s Be Logical
      4. Finding Where the Data Is
      5. Looking It Up
    4. Chapter 15: Digging Up the Facts
      1. Getting Informed with the CELL Function
      2. Getting Information About Excel and Your Computer System
      3. Finding What IS and What IS Not
      4. Getting to Know Your Type
    5. Chapter 16: Writing Home about Text Functions
      1. Breaking Apart Text
      2. Putting Text Together with CONCATENATE
      3. Changing Text
      4. Comparing, Finding, and Measuring Text
    6. Chapter 17: Playing Records with Database Functions
      1. Putting Your Data into a Database Structure
      2. Working with Database Functions
      3. Fine-Tuning Criteria with AND and OR
      4. Adding Only What Matters with DSUM
      5. Going for the Middle with DAVERAGE
      6. Counting Only What Matters with DCOUNT
      7. Finding Highest and Lowest with DMIN and DMAX
      8. Finding Duplicate Values with DGET
      9. Being Productive with DPRODUCT
  9. Part 5: The Part of Tens
    1. Chapter 18: Ten Tips for Working with Formulas
      1. Master Operator Precedence
      2. Display Formulas
      3. Fix Formulas
      4. Use Absolute References
      5. Turn Calc On/Turn Calc Off
      6. Use Named Areas
      7. Use Formula Auditing
      8. Use Conditional Formatting
      9. Use Data Validation
      10. Create Your Own Functions
    2. Chapter 19: Ten Ways to Get Fancy with Excel
      1. Calculating Data from Multiple Sheets
      2. Getting Data from the Internet
      3. Determining the Needed Number
      4. Removing Duplicates
      5. Getting to the Last Row of Your Data
      6. Freezing Panes
      7. Splitting a Worksheet
      8. Filling Cells
      9. Adding Notes to Cells
      10. Getting More Information about a Workbook or Worksheet
    3. Chapter 20: Ten Really Cool Functions
      1. Work with Hexadecimal, Octal, Decimal, and Binary Numbers
      2. Convert Units of Measurement
      3. Find the Greatest Common Divisor and the Least Common Multiple
      4. Easily Generate a Random Number
      5. Convert to Roman Numerals
      6. Factor in a Factorial
      7. Determine Part of a Year with YEARFRAC
      8. Find the Data TYPE
      9. Find the LENgth of Your Text
      10. Just in CASE
  10. Index
  11. About the Author
  12. Advertisement Page
  13. Connect with Dummies
  14. End User License Agreement

Product information

  • Title: Excel Formulas & Functions For Dummies, 6th Edition
  • Author(s): Ken Bluttman
  • Release date: December 2021
  • Publisher(s): For Dummies
  • ISBN: 9781119839118