Excel Tips and Tricks: 15 small things that save an hour a week

None of these take more than a minute to learn. Together they save an hour a week.

Entering data

1. Double-click the fill handle. Instead of dragging a formula down 2,000 rows, double-click the small square. It fills to the bottom of the neighbouring column.
2. Flash Fill (Ctrl + E). Type the first result by hand — “Ana” from “Ana Roy” — then Ctrl + E in the next cell. Excel copies the pattern.
3. Ctrl + Enter fills a selection. Select 50 cells, type once, press Ctrl + Enter. All 50 get it.
4. Alt + Enter for a new line inside a cell.
5. Type 1, 2 then drag for a number series. Type Mon then drag for weekdays.

Reading data

6. The status bar is a calculator. Select numbers; Sum, Average and Count show at the bottom. Right-click the bar to add Min and Max.
7. Ctrl + ` shows every formula on the sheet at once. Press again to hide.
8. Ctrl + [ jumps to the cells a formula uses. F5 › Enter comes back.
9. Zoom to selection: View › Zoom to Selection fills the screen with the cells you picked.

Fixing data

10. Paste Values (Ctrl + Alt + V, V) turns formulas into plain numbers.
11. Transpose: copy a row, right-click › Paste Special › Transpose. It becomes a column.
12. Multiply a whole column by 1.18 without formulas: type 1.18 in a spare cell, copy it, select the column, Paste Special › Multiply.
13. Convert text-numbers in one go: select them, click the warning icon › Convert to Number.

Working faster

14. Ctrl + T makes a Table — filters, stripes and self-filling formulas in one press. Lesson: Excel Table.
15. Quick Access Toolbar: right-click any button › Add to Quick Access Toolbar. Alt + 1, Alt + 2 then trigger them.