Excel Formular Simple Guide

Ready-to-Use Excel Formulas

Chapter 1: Introducing Excel Formulas
Chapter 2: Common Mathematical Operations
Chapter 3: Manipulating Text with Formulas
Chapter 4: Working with Dates and Times
Chapter 5: Performing Conditional Analysis
Chapter 6: Using Lookup Formulas
Chapter 7: Common Business and Financial Formulas
Chapter 8: Common Statistical Analysis
Chapter 9: Using Formulas with Conditional Formatting

Table Of Content

Chapter 1: Introducing Excel Formulas
Creating and Editing Excel Formulas
Methods for entering formulas
Editing a formula

Using Formula Operators
Understanding the order of operator precedence
Using nested parentheses

Relative versus Absolute Cell References

Using External Cell References

Formula Calculation Modes

Leveraging Excel Functions
Why to use Excel functions
Understanding function arguments
Getting Help from the Insert Function Wizard
Understanding Formula Errors

Using Named Ranges in Formulas
Creating a named range
Working with the Name Box

Chapter 2: Common Mathematical Operations
Formula 1: Calculating Percent of Goal
How it works
Alternative: Using a common goal

Formula 2: Calculating Percent Variance
How it works
Alternative: Simplified percent variance calculation

Formula 3: Calculating Percent Variance with Negative Values
How it works

Formula 4: Calculating a Percent Distribution
How it works
Alternative: Percent distribution without a dedicated Total cell

Formula 5: Calculating a Running Total
How it works

Formula 6: Applying a Percent Increase or Decrease to Values
How it works

Formula 7: Dealing with Divide-by-Zero Errors
How it works

Formula 8: Basic Rounding of Numbers
How it works

Formula 9: Rounding to the Nearest Penny
How it works

Formula 10: Rounding to Significant Digits
How it works

Formula 11: Counting Values in a Range
How it works

Formula 12: Creating a Conversion Table
How it works

Chapter 3: Manipulating Text with Formulas
Formula 13: Joining Text Strings
How it works

Formula 14: Setting Text to Sentence Case
How it works

Formula 15: Removing Spaces from a Text String
How it works

Formula 16: Extract Parts of a Text String
How it works

Formula 17: Finding a Particular Character in a Text String
How it works
Alternative: Finding the second instance of a character

Formula 18: Substituting Text Strings
How it works

Formula 19: Counting Specific Characters in a Cell
How it works

Formula 20: Adding a Line Break within a Formula
How it works

Formula 21: Cleaning Strange Characters from Text Fields
How it works

Formula 22: Padding Numbers with Zeros
How it works

Formula 23: Formatting the Numbers in a Text String
How it works
Alternative: Using the DOLLAR function


Chapter 4: Working with Dates and Times
Formula 24: Getting the Current Date and Time
How it works

Formula 25: Calculating Age
How it works

Formula 26: Calculating the Number of Days between Two Dates
How it works

Formula 27: Calculating the Number of Workdays between Two Dates
How it works
Alternative: Using NETWORKDAYS.INTL

Formula 28: Generate a List of Business Days Excluding Holidays
How it works

Formula 29: Extracting Parts of a Date
How it works

Formula 30: Calculating the Number of Years and Months between Dates
How it works

Formula 31: Converting Dates to Julian Date Formats
How it works

Formula 32: Calculating the Percent of Year Completed and Remaining
How it works