Download Advanced Excel Functions and Formulas for Data Analysis and more Schemes and Mind Maps MS Microsoft Excel skills in PDF only on Docsity!
# [ Excel Advanced Functions and Formulas ] CheatSheet
1. Advanced Lookup Functions ● Index-Match: =INDEX(return_range,MATCH(lookup_value,lookup_range,0)) ● Double Index-Match: =INDEX(return_range,MATCH(row_value,row_lookup,0),MATCH(col_value,c ol_lookup,0)) ● Multi-criteria Index-Match: =INDEX(return_range,MATCH(1,(criteria1=range1)(criteria2=range2), )) ● Offset-Match: =OFFSET(reference,MATCH(lookup_value,lookup_range,0)-1,0) ● Xlookup basic: =XLOOKUP(lookup_value,lookup_array,return_array) ● Xlookup with not found: =XLOOKUP(lookup_value,lookup_array,return_array,"Not Found") ● Xlookup exact match: =XLOOKUP(lookup_value,lookup_array,return_array,,-1) ● Xlookup wildcard: =XLOOKUP(""&lookup_value,lookup_array,return_array) ● Xlookup multiple returns: =XLOOKUP(lookup_value,lookup_array,{return_array1,return_array2}) ● Xlookup reverse lookup: =XLOOKUP(lookup_value,lookup_array,return_array,,,-1) 2. Array Formulas ● Sum array: =SUM(IF(criteria_range=criteria,sum_range)) ● Count array: =SUM(IF(criteria_range=criteria,1)) ● Average array: =AVERAGE(IF(criteria_range=criteria,average_range)) ● Multi-condition array: =SUM(IF((criteria1_range=criteria1)*(criteria2_range=criteria2),sum _range)) ● Unique values array: =UNIQUE(range) ● Sort array: =SORT(range,[sort_column],[sort_order],by_col) ● Filter array: =FILTER(range,criteria_range=criteria) ● Sequence generation: =SEQUENCE(rows,[columns],[start],[step]) ● Array spill: =range# ● Dynamic array expansion: =INDIRECT("A1:A"&COUNTA(A:A))
3. Text Functions ● Extract first word: =LEFT(A1,FIND(" ",A1&" ")-1) ● Extract last word: =RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) ● Extract nth word: =TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),((n-1)100)+1,100)) ● Case-sensitive find: =FIND(find_text,within_text) ● Case-insensitive find: =SEARCH(find_text,within_text) ● Replace multiple instances: =SUBSTITUTE(text,old_text,new_text,instance_num) ● Clean text: =CLEAN(TRIM(text)) ● Proper case with exceptions: =IF(ISNUMBER(SEARCH(" mc",LOWER(A1))),PROPER(A1),PROPER(SUBSTITUTE(A1," mc"," Mc"))) ● Remove special characters: =REGEXREPLACE(A1,"[^a-zA-Z0-9]","") ● Extract numbers from text: =--REGEXREPLACE(A1,"[^0-9.]","") 4. Date and Time Functions ● Last day of month: =EOMONTH(date,0) ● First day of month: =EOMONTH(date,-1)+ ● First day of next month: =EOMONTH(date,0)+ ● Next occurrence of day: =date+MOD(target_day-WEEKDAY(date),7) ● Working days calculation: =NETWORKDAYS(start_date,end_date,holidays) ● Add working days: =WORKDAY(start_date,num_days,holidays) ● Fiscal year start: =DATE(YEAR(date)+(MONTH(date)>fiscal_month),fiscal_month,1) ● Quarter from date: =ROUNDUP(MONTH(date)/3,0) ● Week number: =WEEKNUM(date,[return_type]) ● ISO week number: =ISOWEEKNUM(date) 5. Math and Statistical Functions ● Weighted average: =SUMPRODUCT(range,weights)/SUM(weights) ● Percentile calculation: =PERCENTILE.INC(range,k) ● Mode multiple values: =MODE.MULT(range) ● Median excluding zeros: =MEDIAN(IF(range<>0,range)) ● Moving average: =AVERAGE(OFFSET(cell,0,0,n,1)) ● Exponential moving average: =αcurrent+(1-α)*previous ● Standard deviation sample: =STDEV.S(range)
8. Database Functions ● DSUM with criteria: =DSUM(database,field,criteria) ● DCOUNT with criteria: =DCOUNT(database,field,criteria) ● DAVERAGE with criteria: =DAVERAGE(database,field,criteria) ● DMAX with criteria: =DMAX(database,field,criteria) ● DMIN with criteria: =DMIN(database,field,criteria) ● DGET unique value: =DGET(database,field,criteria) ● DVAR with criteria: =DVAR(database,field,criteria) ● DSTDEV with criteria: =DSTDEV(database,field,criteria) ● DPRODUCT with criteria: =DPRODUCT(database,field,criteria) ● Complex database query: =DSUM(database,field,IF(criteria_range=criteria,1,0)) 9. Text Parsing Functions ● Split email address: =LEFT(A1,FIND("@",A1)-1) ● Extract domain: =MID(A1,FIND("@",A1)+1,LEN(A1)) ● Extract between delimiters: =MID(A1,FIND("[",A1)+1,FIND("]",A1)-FIND("[",A1)-1) ● Phone number format: =TEXT(--REGEXREPLACE(A1,"[^0-9]",""),"(000) 000-0000") ● Clean special characters: =REGEXREPLACE(A1,"[^a-zA-Z0-9\s]","") ● Extract ZIP code: =REGEXEXTRACT(A1,"\d{5}(-\d{4})?") ● Parse CSV string: =FILTERXML(""&SUBSTITUTE(A1,",","")&"","//s[po sition()="&n&"]") ● Extract numbers only: =--REGEXREPLACE(A1,"[^0-9.]","") ● Parse name components: =TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",100)),100)) ● Format SSN: =TEXT(--REGEXREPLACE(A1,"[^0-9]",""),"000-00-0000") 10. Error Handling Functions ● Divide by zero handler: =IFERROR(A1/B1,0) ● Multiple error types: =IF(ISERROR(A1),IF(ISNA(A1),"N/A","Error"),"Valid") ● Error with custom message: =IFERROR(VLOOKUP(...),"Not Found") ● Nested error handling: =IFERROR(IFERROR(formula1,formula2),formula3) ● Check specific error: =IF(ISERR(A1),"Error",A1) ● Safe division: =IF(B1=0,0,A1/B1)
● Array error handling: =IFERROR(INDEX(range,MATCH(...)),"Not Found") ● NA error handling: =IFNA(VLOOKUP(...),"No Match") ● Error in calculation: =IFERROR(complex_calculation,"Check Input") ● Validation error: =IF(ISERROR(validation_check),"Invalid","Valid")
11. Dynamic Range Functions ● Dynamic named range: =OFFSET(start_cell,0,0,COUNTA(column),1) ● Dynamic chart range: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) ● Dynamic SUMIFS range: =SUMIFS(INDIRECT("Table1[Value]"),INDIRECT("Table1[Category]"),A1) ● Dynamic VLOOKUP range: =VLOOKUP(lookup_value,INDIRECT("A1:B"&COUNTA(A:A)),2,FALSE) ● Dynamic array expansion: =INDIRECT("A1:A"&ROWS(A1:INDEX(A:A,MATCH("",A:A)-1))) ● Dynamic table reference: =INDEX(table_name,SEQUENCE(ROWS(table_name)),SEQUENCE(1,COLUMNS(tab le_name))) ● Dynamic range intersection: =INDIRECT(ADDRESS(ROW(),COLUMN())&":"&ADDRESS(ROW()+n,COLUMN())) ● Dynamic range union: =CHOOSE({1,2},range1,range2) ● Dynamic cross-worksheet reference: =INDIRECT("'"&sheet_name&"'!"&cell_reference) ● Dynamic array formula: =FILTER(range,criteria_range=INDIRECT(criteria_cell)) 12. Advanced Conditional Functions ● Nested IFS: =IFS(A1>90,"A",A1>80,"B",A1>70,"C",A1>60,"D",TRUE,"F") ● Multiple condition IF: =IF(AND(A1>minimum,A1<maximum,NOT(ISBLANK(A1))),"Valid","Invalid") ● Array conditional: =IF(range>threshold,value1,value2) ● Conditional array formula: =SUM(IF(conditions,values)) ● Complex switch: =SWITCH(TRUE(),condition1,value1,condition2,value2,default) ● Conditional formatting formula: =AND($B2>0,$C2<$B2) ● Alternating row formula: =MOD(ROW(),2)= ● Traffic light conditions: =CHOOSE(MATCH(value,{0,70,90}),RED,YELLOW,GREEN) ● Grade calculation: =LOOKUP(score,{0,60,70,80,90},{F,D,C,B,A})
● R-squared value: =RSQ(y_range,x_range) ● Covariance: =COVARIANCE.P(array1,array2) ● Kurtosis: =KURT(range) ● Skewness: =SKEW(range)
16. Advanced Lookup Patterns ● Multi-condition lookup: =INDEX(return_range,AGGREGATE(15,6,(ROW(criteria_range)-ROW(criteri a_range)+1)(criteria1=value1)(criteria2=value2),1)) ● Fuzzy lookup: =INDEX(return_range,MATCH(MIN(ABS(lookup_range-lookup_value)),ABS(l ookup_range-lookup_value),0)) ● Range lookup with tolerance: =XLOOKUP(lookup_value,lookup_range,return_range,,-1,tolerance) ● Lookup last matching value: =LOOKUP(2,1/(criteria_range=criteria),return_range) ● Case-insensitive lookup: =XLOOKUP(LOWER(lookup_value),LOWER(lookup_range),return_range) ● Wildcard lookup: =XLOOKUP(""&lookup_value&"",lookup_range,return_range,,2) ● Lookup nearest value: =INDEX(return_range,MATCH(MIN(ABS(lookup_range-target)),ABS(lookup_ range-target),0)) ● Lookup with multiple return columns: =XLOOKUP(lookup_value,lookup_range,{return_range1,return_range2,ret urn_range3}) ● Bi-directional lookup: =IF(ISNA(MATCH(lookup_value,forward_range,0)),INDEX(reverse_range,M ATCH(lookup_value,backward_range,0)),INDEX(forward_range,MATCH(look up_value,forward_range,0))) ● Dynamic range lookup: =INDIRECT("Table1["&column_name&"]") 17. Data Cleaning Functions ● Remove extra spaces: =TRIM(CLEAN(text)) ● Standardize case: =PROPER(LOWER(text)) ● Remove non-printable characters: =SUBSTITUTE(SUBSTITUTE(text,CHAR(10),""),CHAR(13),"")
● Extract valid email: =IF(ISNUMBER(SEARCH("@",text)),IF(ISNUMBER(SEARCH(".",text,SEARCH(" @",text))),text,""),"")) ● Standardize phone numbers: =TEXT(--REGEXREPLACE(text,"[^0-9]",""),"[<=9]000-000-0000") ● Remove duplicate spaces: =REGEXREPLACE(text,"\s+", " ") ● Clean currency format: =TEXT(--REGEXREPLACE(text,"[^0-9.-]",""),"$#,##0.00") ● Standardize dates: =TEXT(--SUBSTITUTE(SUBSTITUTE(text,"-","/"),".","/"),"mm/dd/yyyy") ● Remove specific characters: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(text,"#",""),"@",""),"$","") ● Validate numeric values: =IF(ISNUMBER(--text),--text,"")
18. Business Intelligence Functions ● Rolling average calculation: =AVERAGE(OFFSET(reference,COUNTA(range)-periods+1,0,periods)) ● Year-over-year growth: =(current_value/LOOKUP(2,1/(period_column=previous_period),value_co lumn))- ● Market share calculation: =category_sales/SUM(total_sales_range) ● Customer lifetime value: =average_purchase_value*(1/(1-retention_rate)) ● Churn rate calculation: =lost_customers/AVERAGE(start_customers,end_customers) ● Revenue per customer: =total_revenue/COUNTA(UNIQUE(customer_range)) ● Conversion rate: =successful_conversions/total_attempts ● Inventory turnover: =cost_of_goods_sold/average_inventory ● Gross margin percentage: =(revenue-cost_of_goods_sold)/revenue ● Customer acquisition cost: =marketing_spend/new_customers 19. Report Automation Functions ● Dynamic report title: ="Report for "&TEXT(TODAY(),"mmmm yyyy") ● Auto-updating timestamp: =TEXT(NOW(),"dd-mmm-yyyy hh:mm:ss") ● Dynamic chart range: =OFFSET(start_cell,0,0,COUNTA(range),1) ● Running totals: =SUMIFS(amount_range,date_range,"<="&reference_date) ● Progressive totals: =SUM($A$1:A1) ● Automated rankings: =RANK.EQ(value,range,0) ● Dynamic categories: =UNIQUE(category_range)
22. Advanced Data Visualization Functions ● Conditional color scaling: =IF(value>threshold3,"Green",IF(value>threshold2,"Yellow",IF(value> threshold1,"Orange","Red"))) ● Dynamic chart labels: =text_label&": "&TEXT(value,"#,##0.00") ● Custom sparklines: =REPT("▁",IF(A1<ranges,1,IF(A1<range2,2,IF(A1<range3,3,4)))) ● Progress bar: =REPT("■",ROUND(percentage10,0))&REPT("□",10-ROUND(percentage10, )) ● Custom gauge chart: =CHOOSE(MATCH(value,{0,25,50,75,100},1),"○","◔","◑","◕","●") ● Heat map formula: =INDEX(color_array,MATCH(value,threshold_array,1)) ● Dynamic data bars: =REPT("|",ROUND(value/max_value20,0)) ● Custom KPI indicators: =IF(trend>0,"↑",IF(trend<0,"↓","→")) ● Radar chart values: =valueCOS(anglePI()/180) ● Bubble size calculation: =SQRT(value/PI())scale_factor 23. Advanced Error Prevention ● Division by zero prevention: =IF(denominator=0,0,numerator/denominator) ● Null concatenation handler: =IF(ISBLANK(text1)&ISBLANK(text2),"",CONCATENATE(IFERROR(text1,""), IFERROR(text2,""))) ● Multiple error handler: =IFERROR(IFERROR(formula1,formula2),formula3) ● Range error prevention: =IF(row_num>ROWS(range),"Out of Range",INDEX(range,row_num,1)) ● Type mismatch prevention: =IF(ISNUMBER(value),value,VALUE(CLEAN(value))) ● Empty array handler: =IF(ROWS(array)=0,"Empty",array) ● Circular reference prevention: =IF(INDIRECT("RC",FALSE)=prev_value,prev_value,new_calculation) ● Overflow prevention: =IF(result>MAX_VALUE,MAX_VALUE,result) ● Underflow prevention: =IF(result<MIN_VALUE,MIN_VALUE,result) ● Missing reference handler: =IFERROR(INDIRECT(reference_name),"Reference Missing")
24. Performance Optimization ● Array prelookup: =CHOOSE(MATCH(key,lookup_array,0),return_array) ● Efficient range reference: =INDEX(column,sequence) ● Optimized VLOOKUP: =INDEX(return_array,MATCH(lookup_value,lookup_array,0)) ● Calculation chain reduction: =IF(trigger_cell="Yes",complex_calculation,previous_result) ● Memory efficient array: =FILTER(large_range,criteria) ● Volatile function replacement: =ROW()+offset instead of =OFFSET() ● Structured references: =[@Column] instead of cell references ● Helper column utilization: =IF(helper_column=criteria,calculation,0) ● Array optimization: =SUMPRODUCT(--(conditions)) instead of {=SUM(IF())} ● Lookup table efficiency: =INDEX(sorted_array,MATCH(key,sorted_keys,1)) 25. Advanced Analytics Integration ● Simple linear regression: =SLOPE(y_range,x_range)x+INTERCEPT(y_range,x_range) ● Multiple regression: =SUMPRODUCT(coefficients,variables)+intercept ● Logistic regression: =1/(1+EXP(-SUMPRODUCT(coefficients,variables))) ● K-means distance: =SQRT(SUMXMY2(point1,point2)) ● Decision tree node: =IF(feature1<=threshold1,class1,IF(feature2<=threshold2,class2,clas s3)) ● Random forest voting: =MODE(predictions_array) ● Neural network activation: =1/(1+EXP(-weighted_sum)) ● Feature scaling: =(value-MIN(range))/(MAX(range)-MIN(range)) ● Principal component: =MMULT(eigenvector,TRANSPOSE(centered_data)) ● Correlation matrix: =CORREL(array1,array2) 26. Advanced Time Series Analysis ● Exponential smoothing: =αcurrent+(1-α)previous_smooth ● Double exponential smoothing: =level+trend ● Holt-Winters forecast: =(level+trendh)*seasonal_factor ● Moving average convergence: =short_term_avg-long_term_avg ● Relative strength index: =100-100/(1+avg_gain/avg_loss) ● MACD signal: =ema_12-ema_
