Cover Image

Advanced Excel Formulas : Unleashing Brilliance with Excel Formulas /

Enhance and upgrade your Excel knowledge with this comprehensive guide to formulas in Excel. Over 150 of the most useful Excel functions are covered with numerous practical examples of their use. This book is fully updated and includes examples of the most recently released functions in 2022. It is...

Full description

Bibliographic Details
Main Author: Murray, Alan
Format: eBook
Language:English
Published: Berkeley, CA : Apress L.P., 2022.
Subjects:
Microsoft Excel (Computer file)
Electronic spreadsheets > Computer programs.
Online Access:Connect to the full text of this electronic book
Table of Contents:
  • Intro
  • Table of Contents
  • About the Author
  • About the Technical Reviewer
  • Acknowledgments
  • Introduction
  • Chapter 1: Excel Formulas: A Quick Primer
  • Anatomy of an Excel Formula
  • Breakdown of a Function
  • Table of Formula Characters
  • Order of Calculation
  • Order of Range Operations
  • Order of Mathematical Operations
  • The Function Wizard
  • Cell Referencing
  • Absolute References
  • Relative References Explained
  • Making a Reference Absolute
  • Mixed References
  • Sheet References
  • Workbook References
  • Calculating Percentages
  • Percentage of a Value
  • Percentage of a Total
  • Increase a Value by a Percentage
  • Decrease a Value by a Percentage
  • Calculate Percentage Change
  • When Formulas Go Wrong
  • Formula Errors
  • Automatic Error Checking
  • Excel Formula Is Not Calculating
  • Formula Calculations Are Set to Manual
  • Show Formulas Is Switched On
  • Formula Is Stored As Text
  • Cell Formatted As Text
  • Summary
  • Chapter 2: Logical Functions
  • What Is TRUE and FALSE?
  • The IF Function
  • Example 1: Testing Numeric Values
  • Example 2: Testing Text Values
  • Example 3: Exact Text Match
  • Example 4: Partial Text Match
  • Example 5: Testing Date Values
  • Example 6: Testing If a Cell Is Empty or Blank
  • Example 7: Return a Symbol or Emoji
  • Nested IF Functions
  • IFS Function
  • AND, OR, XOR, and NOT Functions
  • AND Function
  • OR Function
  • XOR Function
  • NOT Function
  • The SWITCH Function
  • Example 1: Match Against a List of Text Values
  • Example 2: Evaluate to TRUE
  • Example 3: Match a Formula Result to the List of Values
  • Example 4: Pick Formula from a Drop-Down List
  • Handling Errors
  • IFERROR Function
  • IFNA Function
  • Logical Functions with Excel Features
  • Format Values That Meet a Goal
  • Conditional Formatting Rule with Multiple Conditions
  • Data Validation Rule Based on Another Cell Value
  • Conditional Formatting with Charts
  • Dynamic Chart Data with SWITCH
  • Summary
  • Chapter 3: Defined Names
  • Named Ranges
  • Why Use Named Ranges?
  • Define a Named Range with the Name Box
  • Using Named Ranges in Formulas
  • Rules for Defining Names
  • Tips for Defining Names
  • Named Ranges with Multiple Cells
  • Named Range for a Drop-Down List
  • Define a Dynamic Named Range
  • Define Names with Create from Selection
  • Scope of a Defined Name
  • Define a Named Constant
  • Finding Named Ranges
  • Relative Named Ranges
  • Named Formulas
  • Managing Names
  • Edit a Name
  • Filtering Names
  • Print a List of the Names
  • Apply Names to Existing Formulas
  • Summary
  • Chapter 4: You Need to Start Using Tables
  • Why Use Tables?
  • Format a Range As a Table
  • Creating a Table
  • Changing or Removing Table Styles
  • Naming the Table
  • Working with Tables
  • Table References in a Formula
  • The Magic of @
  • Referencing Table Elements
  • Make Table Column and Cell References Absolute
  • Tables with Other Excel Features
  • Dynamic Lists
  • Dynamic Charts
  • Summary