Excel for Data Analysts

Cheatsheet Content

1. Navigation & Selection Ctrl + Arrow Key: Move to edge of data region Shift + Arrow Key: Extend selection by one cell Ctrl + Shift + Arrow Key: Select to edge of data region Ctrl + Home: Go to cell A1 Ctrl + End: Go to last used cell Ctrl + Page Up/Down: Switch between worksheets Ctrl + A: Select current region (or entire sheet if pressed twice) Ctrl + Space: Select entire column Shift + Space: Select entire row 2. Editing & Formatting F2: Edit active cell Ctrl + Z: Undo last action Ctrl + Y: Redo last action Ctrl + C: Copy selected cells Ctrl + X: Cut selected cells Ctrl + V: Paste content Ctrl + Alt + V: Paste Special dialog Ctrl + 1: Format Cells dialog Ctrl + B/I/U: Bold/Italic/Underline Ctrl + ;: Insert current date Ctrl + Shift + ;: Insert current time Alt + Enter: New line within a cell Ctrl + D: Fill Down (copy from cell above) Ctrl + R: Fill Right (copy from cell to the left) 3. Data Manipulation Ctrl + T / Ctrl + L: Create an Excel Table Ctrl + Shift + L: Toggle AutoFilter Alt + Down Arrow: Open AutoFilter dropdown F4: Repeat last action (e.g., apply format, insert row) Alt + H + O + I: AutoFit Column Width Alt + H + O + A: AutoFit Row Height Ctrl + + (Plus): Insert cells/rows/columns Ctrl + - (Minus): Delete cells/rows/columns F5 / Ctrl + G: Go To dialog (e.g., Go To Special for blanks) 4. Essential Formulas for Data Analysis 4.1. Logical & Conditional IF: $=IF(logical\_test, value\_if\_true, value\_if\_false)$ AND: $=AND(logical1, [logical2], ...)$ OR: $=OR(logical1, [logical2], ...)$ IFS: $=IFS(logical\_test1, value\_if\_true1, [logical\_test2, value\_if\_true2], ...)$ SUMIF: $=SUMIF(range, criteria, [sum\_range])$ SUMIFS: $=SUMIFS(sum\_range, criteria\_range1, criteria1, ...)$ COUNTIF: $=COUNTIF(range, criteria)$ COUNTIFS: $=COUNTIFS(criteria\_range1, criteria1, ...)$ AVERAGEIF: $=AVERAGEIF(range, criteria, [average\_range])$ 4.2. Lookup & Reference VLOOKUP: $=VLOOKUP(lookup\_value, table\_array, col\_index\_num, [range\_lookup])$ HLOOKUP: $=HLOOKUP(lookup\_value, table\_array, row\_index\_num, [range\_lookup])$ XLOOKUP: $=XLOOKUP(lookup\_value, lookup\_array, return\_array, [if\_not\_found], [match\_mode], [search\_mode])$ INDEX: $=INDEX(array, row\_num, [column\_num])$ MATCH: $=MATCH(lookup\_value, lookup\_array, [match\_type])$ INDIRECT: $=INDIRECT(ref\_text, [a1])$ OFFSET: $=OFFSET(reference, rows, cols, [height], [width])$ 4.3. Text Manipulation CONCAT/CONCATENATE: $=CONCAT(text1, [text2], ...)$ LEFT: $=LEFT(text, [num\_chars])$ RIGHT: $=RIGHT(text, [num\_chars])$ MID: $=MID(text, start\_num, num\_chars)$ LEN: $=LEN(text)$ FIND: $=FIND(find\_text, within\_text, [start\_num])$ (case-sensitive) SEARCH: $=SEARCH(find\_text, within\_text, [start\_num])$ (not case-sensitive) SUBSTITUTE: $=SUBSTITUTE(text, old\_text, new\_text, [instance\_num])$ REPLACE: $=REPLACE(old\_text, start\_num, num\_chars, new\_text)$ TRIM: $=TRIM(text)$ (removes extra spaces) CLEAN: $=CLEAN(text)$ (removes non-printable characters) TEXTJOIN: $=TEXTJOIN(delimiter, ignore\_empty, text1, [text2], ...)$ 4.4. Date & Time TODAY: $=TODAY()$ NOW: $=NOW()$ DATE: $=DATE(year, month, day)$ YEAR: $=YEAR(serial\_number)$ MONTH: $=MONTH(serial\_number)$ DAY: $=DAY(serial\_number)$ WEEKDAY: $=WEEKDAY(serial\_number, [return\_type])$ DATEDIF: $=DATEDIF(start\_date, end\_date, unit)$ (e.g., "y", "m", "d") EDATE: $=EDATE(start\_date, months)$ 4.5. Statistical & Math SUM: $=SUM(number1, [number2], ...)$ AVERAGE: $=AVERAGE(number1, [number2], ...)$ MEDIAN: $=MEDIAN(number1, [number2], ...)$ MODE.SNGL: $=MODE.SNGL(number1, [number2], ...)$ MIN: $=MIN(number1, [number2], ...)$ MAX: $=MAX(number1, [number2], ...)$ COUNT: $=COUNT(value1, [value2], ...)$ (counts numbers) COUNTA: $=COUNTA(value1, [value2], ...)$ (counts non-empty cells) COUNTBLANK: $=COUNTBLANK(range)$ RANK.EQ: $=RANK.EQ(number, ref, [order])$ LARGE: $=LARGE(array, k)$ SMALL: $=SMALL(array, k)$ ROUND: $=ROUND(number, num\_digits)$ ROUNDUP/ROUNDDOWN: $=ROUNDUP(number, num\_digits)$, $=ROUNDDOWN(number, num\_digits)$ 5. Array Formulas (Ctrl+Shift+Enter) Used for calculations on multiple items in an array, e.g. $=SUM(LEN(A1:A5))$ to sum lengths. Modern Excel versions often handle these implicitly (dynamic arrays). UNIQUE: $=UNIQUE(array, [by\_col], [exactly\_once])$ FILTER: $=FILTER(array, include, [if\_empty])$ SORT: $=SORT(array, [sort\_index], [sort\_order], [by\_col])$ SORTBY: $=SORTBY(array, by\_array1, [sort\_order1], ...)$ 6. Other Useful Features Data Validation: Restrict data entry to a specific type or list. Conditional Formatting: Apply formatting based on cell values. PivotTables: Summarize, analyze, explore, and present summary data. Power Query (Get & Transform Data): Connect, combine, and refine data sources. Goal Seek: Find input value needed to achieve a target result. Scenario Manager: Test different sets of input values for formulas.