Excel & Power BI Cheat Sheet
Excel formulas
=SUMIFS(sum, range1, crit1, range2, crit2)
=COUNTIF(range, crit)
=XLOOKUP(key, look_in, return, "None")
=INDEX(col, MATCH(key, keys, 0))
=IFERROR(x, 0)
=TEXT(date, "dd-mmm-yyyy")
=FILTER(data, cond) =UNIQUE(col) =SORT(data, col, -1)
Shortcuts
Ctrl+T table · Ctrl+Shift+L filter · F4 lock ref
Alt+= AutoSum · Ctrl+E Flash Fill · Ctrl+; today
DAX
Total = SUM(T[Amount])
Filtered = CALCULATE([Total], T[City] = "Delhi")
Ratio = DIVIDE([A], [B], 0)
YTD = TOTALYTD([Total], 'Date'[Date])
LastYear = CALCULATE([Total], SAMEPERIODLASTYEAR('Date'[Date]))
Full Excel & Power BI documentation