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