Day 24: Excel Shortcuts & Productivity

Navigation: Ctrl+End (last used cell), Ctrl+Home (cell A1), Ctrl+Arrow (jump to edge of data region), Ctrl+Shift+Arrow (select to edge). Editing: Ctrl+Z (undo), Ctrl+Y (redo), F2 (edit cell), Ctrl+D (fill down), Ctrl+R (fill right). Formatting: Ctrl+1 (Format Cells dialog), Ctrl+B/I/U (bold/italic/underline), Alt+= (AutoSum). Selection: Ctrl+A (select all), Ctrl+Space (select column), Shift+Space (select row). Analysis: Alt+N+V+T (insert Pivot Table), Ctrl+T (create Table), Ctrl+E (Flash Fill).

Ctrl+Shift+L toggles AutoFilter (dropdown filter arrows) on and off for the selected range or table. This is the fastest way to add or remove filters. Use it immediately when you receive a dataset to add filters for quick manual exploration. Also useful for removing filters from a filtered table: pressing Ctrl+Shift+L twice removes all filter dropdowns entirely.

Click any cell in the data → Ctrl+Shift+End to extend selection to the last used cell. Or: click the first data cell (A2 if headers are in row 1) → Ctrl+Shift+End. For just one column of data: click the first data cell in the column → Ctrl+Shift+Down to extend to the last non-empty cell. In an Excel Table: click any cell in a column → Ctrl+Space to select the entire data column (without header).

F4 toggles the reference type of the selected cell reference in a formula: A1 (relative) → $A$1 (absolute) → A$1 (row absolute) → $A1 (column absolute) → A1 (back to relative). Press F4 multiple times to cycle through. In formulas, press F4 after clicking or typing a cell reference to quickly make it absolute without typing $ manually. This is essential when building formulas that will be copied down or across.

Click the cell below the rows and to the right of the columns you want to freeze → View → Freeze Panes → Freeze Panes. To freeze just the top row: View → Freeze Panes → Freeze Top Row. To freeze just the first column: Freeze First Column. Frozen panes stay visible as you scroll. To unfreeze: View → Freeze Panes → Unfreeze Panes. For a dataset with a header row and a CustomerID in column A, freeze both: click cell B2, then Freeze Panes.