- Browse
- » Advanced Excel Formulas: Unleashing Brilliance with Excel Formulas
Advanced Excel Formulas: Unleashing Brilliance with Excel Formulas
Author
Publisher
Apress L.P
Publication Date
2022
Language
English
Description
Loading Description...
More Details
ISBN
9781484271254
Table of Contents
From the eBook
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
Excerpt
Loading Excerpt...
Author Notes
Loading Author Notes...
Reviews from GoodReads
Loading GoodReads Reviews.
Staff View
Loading Staff View.