My Excel Cheat Sheet

Issue	Solution	Formula
LEFT OUTER JOIN two Excel table	Use VLOOKUP Note: If two data has combination key (e.g. one column for date and one column for time), we need to merge the two column into one column for easy index handling. Need to have unique key for both tables.	Syntax: VLOOKUP(Key, SecondTableRange, ReturnDataColumnNumber,FALSE) Sample: =VLOOKUP($A3, $F$3:$J$17508, 4, FALSE)
Find Duplicate Record	Use COUNTIF() We may count the number of the same values in a range.  If it is more than one, than it is duplicated.  We can then use Filter function to filter them out.	Syntax: =COUNTIF(Range,valueToFind) Sample: =COUNTIF(G3:G9,G3)
Convert DateTime data into text	use TEXT()	e.g. =TEXT(A3,"yyyy-mm-dd-HH-MM")
Add one minute to a DateTime value	Use "+"/"-" operator. The "+/"-" operator adds/subtracts one day for numeric value one. If we want to add less than one day we can: hour = 1/24 minute = 1/24/60 second = 1/24/60/60	e.g. (add one hour) =A1 + 1/24
Add one month/year	Use YEAR(), MONTH() and DAY() function to extract the date component to numeric value.  After addition, then use DATE() function to convert them back to a single DateTime value.	e.g. (one month) =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) e.g. (one year) =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))
Count Unique value pairs (e.g. Customer + Product	Add a new column and use the right formula.  The worksheet must be sorted before use.	=IF(COUNTIFS(B$1:B2,B2,E$1:E2,E2)=1,1,0)
Count unique value (single column only)	Add a new column and use the right.  The worksheet must be sorted before use.	=IF(COUNTIF($B$2:$B2,B2)>1,0,1)
Worksheet name		=MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,255)
Eliminate NA error		=IFERROR(your formula, 0)
Find a value in an array,return relative position	use INDEX and MATCH instead of VLOOKUP	=MATCH($A$1,  $A$3:$A$58)
Return a value based on location	use INDEX and MATCH	=INDEX($C$3:$C$59,  MATCH($A$1,  $A$3:$A$58))

• Vertex42.com Excel Templates
• Contextures Excel Tips