Embedded Files
Date Difference, Calculate
Date Difference, Calculate
Calculate the period between two dates in two separate cells:
=datedif(CELL, CELL, "y")
Time Difference, Calculate
Time Difference, Calculate
Two methods:
=(ABS(D180-C180)*24)
=text(d180-c180, "h")
If/then
If/then
Compares logic against another cell, then permits additional action
General Formula is =if(CELL = "LOGIC" then "Enter/Action" otherwise "Alternate Entry/Action")
=if(A1="Heidelberg", "Germany", "Not in Germany")
If/Then...If cell not empty, then execute action
If/Then...If cell not empty, then execute action
Text goes here
Current Date Shortcut
Current Date Shortcut
Control & ;
countifs
countifs
=countifs(RANGE 1, Criteria 1, Range 2, Criteria 2, ...)
Count between two values
Count between two values
=countifs(LIST:LIST, ">=CRITERIA", "<=CRITERIA")
Headcount Formula
Headcount Formula
=sumproduct(1/countif(Range Start, Range End))
Rank & Sort
Rank & Sort
=rank(XYZ) See this video.
=sort(XYZ) See this video.
If cell is blank
If cell is blank
=if(A1<>"", execute if true, execute if false)
Remove or Replace Characters in Text String
Remove or Replace Characters in Text String
=replace(old, start number, number characters, next text) or =replace(a1, 1, 1, "") or =replace(a1, 1, 1, "Account")
=replace(old text, LEN(old text), 1, ""). The =len(old text) function calculates the total number of characters in the old text cell.
Highlight Row Based on Value in Cell(s)
Highlight Row Based on Value in Cell(s)
Highlight entire table
Conditional Formatting
Highlight by Formula
=$XY= insert logic here
Example =$E1="Boston" will highlight rows that contain Boston in column E
Dashboards
Advanced VLookup Ideas
General
Unique Function
Advanced Pivot Table Techniques
Advanced VLookup Functions
Video Hints
Gothic A1 Bold 18
Gothic A1 Bold 18
Gothic A1 Medium 12
Page updated
Google Sites
Report abuse