Chapter 15: Excel Tips, Tricks & Keyboard Shortcuts
Excel Tips, Tricks & Keyboard Shortcuts.
This chapter is like the “pro tips” section at the end of a long cooking class — all the small, clever things that make you much faster, avoid frustration, and look like an Excel wizard to your colleagues, friends, or family.
I’ll explain everything very slowly, step by step, like a patient teacher sitting next to you in Airoli (maybe with some nice hot chai while we go through these shortcuts together). We’ll use real examples from our tea shop workbook wherever possible.
Let’s become super-efficient!
1. Essential Keyboard Shortcuts (You Should Memorize These First)
These 20–25 shortcuts will cover 80–90% of your daily work.
Navigation & Selection
| Action | Shortcut | Example in TeaSales table |
|---|---|---|
| Go to cell A1 | Ctrl + Home | Jump to top-left of data instantly |
| Go to last used cell | Ctrl + End | Quickly find bottom-right of your sales data |
| Jump to edge of data (up/down/left/right) | Ctrl + Arrow key | Ctrl + ↓ from header → last row of Qty column |
| Select entire column | Ctrl + Space | Select whole Total column to format |
| Select entire row | Shift + Space | Select row to insert new sale |
| Select current table/data region | Ctrl + A (inside table) | Select all TeaSales rows & columns |
| Extend selection | Shift + Arrow | Select A2:E100 quickly |
Editing & Data Entry
| Action | Shortcut | Example |
|---|---|---|
| Edit active cell | F2 | Change Qty in cell C45 |
| Enter current date | Ctrl + ; | Type today’s date in Order Date column |
| Enter current time | Ctrl + Shift + ; | Timestamp when sale was recorded |
| Fill down (copy formula/value) | Ctrl + D | Copy Total formula =[@Qty]*[@Price] down |
| Fill right | Ctrl + R | Copy formatting across columns |
| AutoSum selected cells | Alt + = | Quick total of last 10 sales |
| Flash Fill (pattern recognition) | Ctrl + E | Split “Mumbai – Andheri” into two columns |
Formatting & Workbook
| Action | Shortcut | Example |
|---|---|---|
| Format Cells dialog | Ctrl + 1 | Open to change currency to ₹, decimals, date format |
| Bold / Italic / Underline | Ctrl + B / I / U | Bold headers in dashboard |
| Copy / Paste | Ctrl + C / V | Copy formatting from one cell to many |
| Paste Special | Ctrl + Alt + V | Paste only values (no formulas) after calculation |
| Undo / Redo | Ctrl + Z / Ctrl + Y | Recover from accidental delete |
| Find / Replace | Ctrl + F / Ctrl + H | Replace “Chai” with “Masala Chai” everywhere |
PivotTables & Charts
| Action | Shortcut | Example |
|---|---|---|
| Refresh all PivotTables | Alt + F5 or Data → Refresh All | Update dashboard after adding new sales |
| Group dates in Pivot | Right-click date → Group | Group sales by month/quarter |
| Create PivotTable from selection | Alt + N + V | Quick Pivot from filtered data |
VBA & Developer
| Action | Shortcut | Example |
|---|---|---|
| Open VBA Editor | Alt + F11 | Edit your FormatMonthlyReport macro |
| Run macro | Alt + F8 | Run cleanup macro |
Memorization tip: Practice 5–10 shortcuts per day for one week → muscle memory will form. Print a cheat sheet or use a wallpaper with your top 15.
2. Hidden Features & Productivity Hacks
These are lesser-known tricks that save huge time.
A. Quick Analysis Tool (very underrated) Select any data range → bottom-right corner shows small icon → Quick Analysis → Instant charts, conditional formatting, totals, sparklines, tables — 2 clicks!
B. Flash Fill on steroids Type two examples → Ctrl + E → Excel learns complex patterns (e.g., “MUM-2026-0456” → extract year “2026”).
C. Camera tool (old but gold) Developer tab → Insert → Camera (add it via Customize Quick Access Toolbar) → Take a live snapshot of any range → paste elsewhere → updates automatically when source changes Perfect for dashboard summary boxes.
D. Custom Views View tab → Custom Views → Add → Save different filter/sorting/print setups (e.g., “Mumbai Only”, “Full Year”, “Print Report”) → Switch in one click — great for shared files.
E. Power Quick Tips
- Ctrl + [ → jump to precedent cells (where formula gets data from)
- Ctrl + ] → jump to dependent cells (cells that use this one)
- Alt + = → AutoSum smarter than regular sum
- Ctrl + Shift + L → toggle filters on/off on table
- Ctrl + Shift + * → select current data region around active cell
- F4 → repeat last action (e.g., apply same border 50 times)
F. Status Bar Tricks Select numbers → look at bottom-right status bar → shows Average, Count, Sum instantly — no formula needed!
G. Name Box for navigation Type “Sales!E100” in Name Box (left of formula bar) → jump to cell E100 on Sales sheet.
3. Common Errors & Troubleshooting
| Error | Meaning & Fix | Tea Shop Example Fix |
|---|---|---|
| #DIV/0! | Divide by zero or empty cell | Wrap in IFERROR: =IFERROR(A1/B1,0) |
| #VALUE! | Wrong data type (text in number calc) | Check cell formats, use VALUE() or clean data in Power Query |
| #REF! | Deleted cell/range referenced in formula | Ctrl + Z or fix formula range |
| #NAME? | Function misspelled or add-in missing | Check spelling (SUMIFS not sumifs), enable Power Pivot if using DAX |
| #SPILL! | Dynamic array blocked by data in spill range | Clear cells below/right |
| ###### | Column too narrow for date/number | Double-click column border |
| Circular Reference | Formula refers to its own cell | Status bar shows warning → trace with Formulas → Error Checking |
| Slow file / freezes | Too many volatiles (INDIRECT/OFFSET), large Pivot caches, too many formats | Use Power Pivot, Manual calculation, remove unused styles |
Quick troubleshoot checklist:
- Press F9 → recalculate
- Check Formulas tab → Error Checking → trace errors
- Ctrl + [ → go to source cells
- Formulas tab → Name Manager → delete broken names
- Save as new file → sometimes corruption is fixed
- File → Info → Check for Issues → Inspect Document → remove hidden data
Homework / Practice (Do This Today!)
- Memorize & use these 10 shortcuts today:
- Ctrl + Home, Ctrl + Arrow, Ctrl + 1, F2, Alt + =, Ctrl + ;, Ctrl + Shift + L, Alt + F11, Ctrl + Z, Ctrl + F
- Select 20 cells with numbers → look at status bar for instant sum/average
- Try Quick Analysis on your Total column → add sparklines & conditional formatting in 2 clicks
- Break a formula on purpose → make #DIV/0! → fix with IFERROR
- Record a 30-second macro → add today’s date + “Updated by Webliance” in cell A1 of Dashboard
You’ve now finished the complete Excel course — from beginner to dashboard-building, macro-writing pro! 🎉☕
If you want:
- A printable shortcut cheat sheet (text version)
- Help with your real Excel file/project
- Transition to Power BI / Python in Excel
- Advanced VBA (email reports, auto-PDF export)
- Or just celebrate with a “what next?” discussion
just tell me! I’m very proud of how far you’ve come — you’re now ready to impress anyone with your Excel skills.
What would you like to do next? 😊
