Excel Formula and Function Tips - All Tips (132 tips)
- Separating Positive and Negative numbers and add Running Balance column
- Reversing The Order Of Values In A List
- Rounding Numbers that Meet Specified Criteria
- Ranking Numbers in a Dynamic List
- Counting the number of cells containing a substring
- Sum Total Sales Except for One Specified Item
- Dividing an Amount into Equal Payments While Avoiding Division / Rounding Errors
- Counting values meeting multiple criteria in different columns.
- Counting the Number of Values between Two Specified Values in a List
- Counting Rows that Match Specific Criteria for Each Column
- Retrieving Unique Values From A List
- Retrieving Every nth Value In a Range
- Finding Minimum / Maximum Values
- Avoiding Errors When Counting Date Values That Meet Specified Criteria
- Preventing a Vlookup Function From Returning an Error When an Exact Match Is Not Found
- Simplifying Formulas by Reducing the Complexity of IF Functions
- Calculating the Average of Every Nth Value
- Counting the Number of Values Between Upper and Lower Limits
- Finding the Largest Value from Two Different Lists, Subject to Specified Criteria
- Indicators for the maximal and minimal values
- Creating Formulas that Only Return Results from Non-Blank Cells
- Calculating the Average for Numbers Meeting Specified Criteria
- Checking For Duplicate Values Within A Range
- Retrieving an Item from a List that Meets Multiple Criteria
- Performing calculations based on multiple criteria, using SUMPRODUCT function
- Retrieving the Value of the First Nonblank Cell in a List
- Retrieving From List By Position
- Calculating an Average, Only Including Prices on Which There Was no Discount
- Nesting OR and AND Functions to Meet Multiple Criteria
- Ranking Salespeople According To Sales Figure
- Retrieving the Closest Larger and Closest Smaller Values from a List When There Is No Exact Match
- Finding the Largest Number among Those Meeting Specified Criteria
- Counting Rows of Even Numbers in Two Parallel Ranges
- Disregarding blank cells when performing calculations on a range.
- Ignoring Blank Cells Containing Invisible Spaces when Using Array Formulas
- Extracting a substring
- Summing How Many Points Scored by a Specified Team in Specified Games
- Counting the Number of Players According to Their Score in a Particular Quarter
- Reversing Strings
- Correcting Formatting Errors
- Rounding the Calculation of Retail Price and Sales Tax
- Counting the Number of Students who Answered "A" to the Second Question
- Checking For Sequences Within A Sorted List
- Retrieving a value from a reference grid according to index values
- Finding a substring within a string
- Extracting last Word
- Validating Data To Be Unique In A Range
- Random Selection from a List
- Avoiding #DIV/0! Errors when Dividing by Zero
- Ignoring #DIV/0! Errors when Performing Calculations
- Eliminating Errors Resulting from If Functions with Multiple Conditions
- Avoiding Errors when Counting Date Values that Meet Specified Criteria
- Avoiding Errors when Subtracting Times
- Preventing a VLOOKUP Function from Returning an Error when an Exact Match is Not Found
- Avoiding the Occurrence of Unexpected VLOOKUP #N/A Errors
- Using a VLOOKUP Formula to Check If a Value Exists
- Checking If Matching Numbers Exist in Different Ranges
- Sorting Numbers in Ascending or Descending Order
- Sorting Numbers Based on Their Frequency within a List
- Reversing the Order of Values in a List
- Shuffling a List of Values
- Retrieving Unique Values from a List
- Retrieving List Values that Do Not Appear in a Second List
- Retrieving Values that are Common to Two Lists
- Creating a List of All Non-Blank Cells in a Column
- Retrieving Values from a List that are Greater than a Specified Number
- Checking for Sequences within a Sorted List
- Checking for Duplicate Values within a Range
- Identifying Duplicate Rows within a Range
- Determining and Indicating the Number of Times Each Value Has Been Entered in a List
- Transposing an Entire Range of Data into a Single Column
- Transposing a Column into Successive Rows of a Specified Length
- Transposing a Range in Ascending Order
- Transposing Values from Columns into Rows, and Vice Versa
- Calculating Average Annual Growth
- Calculating the Average Growth of a Child
- Finding Specified Items in a List and Averaging their Associated Values
- Excluding Exceptional Values when Calculating an Average
- Averaging Values that Correspond with the X Largest Values in a Dynamic Range
- Calculating the Average of a Range of Numbers, Excluding the Minimal Value
- Calculating the Lowest Common Multiple (LCM)
- Returning the Nth Largest / Smallest Values in a Range
- Rounding Up / Down to the Nearest Multiple of a Specific Number
- Rounding a Value to Make It Divisible by a Specified Number
- Ranking a List of Numbers
- Calculating Commissions Based on Sales Rank
- Selecting a Set of Random Numbers, Avoiding Duplicates
- Adjusting Values Returned by the RAND Function
- Randomly Selecting a Value from a Range
- Calculating Net Sale Price According to Text Criteria
- Calculating the Proportion of Characters from One String that Appears in another String
- Calculating the Remaining Credit after Each Purchase
- Calculating Total Annual Payment Based on Cumulative Monthly Payments
- Placing the Previous Payment Date beside Each Pay Date in a List
- Restricting the Automatic Recalculation of Volatile Functions
- Calculating the Percentage of Cells in a Range that Contain a Specified String
- Calculating the Absolute Difference between Each List Value and the Minimum Value in the List
- Determining Divisibility
- Multiplying Values from Two Matrixes that Occupy Corresponding Addresses
- Converting Decimal Fractions to Fractions of a Specified Number
- Simplifying Formulas by Defining Names for Often-Repeated Parts
- Converting Units
- Calculating Total Income Tax
- Inserting/Editing Formulas in Excel 2007
- Entering Formulas in a Quicker Way by Shortening Sheet Names in Excel 2007
- Nesting Formulas in Excel 2007
- Changing an Absolute Reference to a Relative Reference or Vice Versa in Excel 2007
- Copying a Formula from a Cell While Keeping the Reference in Excel 2007
- Copying Formulas from a Range of Cells without Changing the Absolute or Relative References in Excel 2007
- Copying Formulas and Pasting Them in a Transposed Direction without Changing the Relative References in Excel 2007
- Pasting Values in Excel 2007
- Displaying Formula Syntax in Excel 2007
- Printing Formula Syntax in Excel 2007
- Displaying Formulas and Values of the Same Cells in Excel 2007
- Selecting Cells That Contain Formulas in Excel 2007
- Stepping into a Formula in Excel 2007
- Moving Between Precedent and Dependent Cells in Excel 2007
- Circular References in Excel 2007
- Iteration in Excel 2007
- Selecting Cells That Contain Errors in Formulas in Excel 2007
- Tracing Errors in Formula Results in Excel 2007
- Range Name Syntax in Excel 2007
- Defining a Range Name in Excel 2007
- Deleting a Range Name in Excel 2007
- Deleting a Name when the Reference containing an Error or an Orphan Name in Excel 2007
- Automatically Defining Names for Ranges in a List in Excel 2007
- Use Name in a Formula in Excel 2007
- Saving a Frequently Used Formula/Numeric Value in the Name, Refers to Box in Excel 2007
- Automatically Updating a Range Name Reference in Excel 2007
- IFERROR Function - New Function in Excel 2007
- New AVERAGE Functions in Excel 2007
- AVERAGEIFS, SUMIFS and COUNTIFS Functions New functions in Excel 2007
»Click here to submit a tip to Excel Formula and Function tips
