ShortcutdrillExcel shortcuts / Excel grouping and structure shortcuts
Excel grouping and structure shortcuts
Structure shortcuts keep a large model readable: grouped detail rows, frozen headers, helper columns hidden, sheets named and in order. None of them is used every minute, which is exactly why the mouse creeps back in for them.
The row and column versions share keys: Shift+Space or Ctrl+Space to select, then the same insert, delete, hide and group shortcuts.
| Action | Windows | Mac | Practice |
|---|---|---|---|
| Insert a column | Ctrl+Space, Ctrl+Shift+= | Control+Space, Cmd+Shift+= | Free drill |
| Delete a blank row | Shift+Space, Ctrl+- | Shift+Space, Cmd+- | Free drill |
| Group rows | Alt+Shift+Right arrow | Cmd+Shift+K | Pro drill |
| Ungroup rows | Alt+Shift+Left arrow | Cmd+Shift+J | Pro drill |
| Collapse the groups | Alt, A, H | Windows only | Pro drill |
| Insert a sheet | Shift+F11 | Fn+Shift+F11 | Free drill |
| Rename the sheet | Alt, H, O, R, type, Enter | Windows only | Pro drill |
| Freeze panes | Alt, W, F, F | Windows only | Pro drill |
| Sort by the current column | Alt, A, S, D | Windows only | Pro drill |
| Insert a pivot table | Alt, N, V | Windows only | Pro drill |
Insert a column Ctrl+Space, Ctrl+Shift+=
With a whole column selected, Ctrl+Shift+= inserts a blank column to its left without a dialog. Everything to the right shifts and the formulas adjust. Practice: Insert a column.
Delete a blank row Shift+Space, Ctrl+-
The blank row left by an export or a moved block: Shift+Space selects it, Ctrl+Minus removes it, the rows below move up. Two presses instead of a right-click. Practice: Delete the blank row.
Group rows Alt+Shift+Right arrow
Select the detail rows and press Alt+Shift+Right. An outline bracket appears in the margin with a minus button to collapse them. Grouped detail under subtotals is what makes a long schedule readable. Practice: Group the detail rows.
Ungroup rows Alt+Shift+Left arrow
Alt+Shift+Left removes one outline level from the selected rows. Do it before deleting or moving grouped rows, so the outline does not end up pointing at the wrong block. Practice: Ungroup.
Collapse the groups Alt, A, H
Alt, A, H hides the detail of the selected group, or of every group when the selection is outside them. Alt, A, J shows it again. The outline level buttons at the top-left do the same with the mouse. Practice: Collapse all groups.
Insert a sheet Shift+F11
Shift+F11 inserts a new worksheet in front of the current one and makes it active. Alt, H, I, S does the same through the ribbon. Practice: Insert a sheet.
Rename the sheet Alt, H, O, R, type, Enter
Alt, H, O, R selects the sheet name for editing. Type the new name and press Enter. Models with Inputs, Calc and Output tabs are named this way in seconds. Practice: Rename the sheet.
Freeze panes Alt, W, F, F
Alt, W, F, F freezes everything above and to the left of the active cell, so headers and labels stay in view while you scroll. Select the first cell that should scroll, then press the sequence. The same keys unfreeze. Practice: Freeze the header.
Sort by the current column Alt, A, S, D
From a cell in the column to sort by, Alt, A, S, D sorts the whole region largest first; Alt, A, S, A sorts smallest first. Rows stay together, the header stays on top. Practice: Sort by this column.
Insert a pivot table Alt, N, V
From inside a table, Alt, N, V (then Enter in newer versions) creates a PivotTable from the current region on a new sheet. Fields are then dragged, or added from the field list with the keyboard. Practice: Insert a pivot.