The Excel You Were Never Taught
Let’s get something out of the way.
Most Excel tutorials tell you what a formula does. But they rarely tell you why you’d ever need it when the clock’s ticking and your manager just asked for a report five minutes ago.
This isn’t that tutorial.
This is the unfiltered, no-BS guide to the Excel formulas and shortcuts that power real-world data work — complete with use cases and edge-case tips. If you’ve ever been the go-to Excel person on your team or want to be, this is the cheatsheet you didn’t know you needed.
Part I: The 15 Excel Formulas That Actually Save You From Drowning
Each formula below isn’t just explained — it’s framed in scenarios that matter in a job.
1. INDEX + MATCH
Use Case: Dynamic lookups across unsorted data.
=INDEX(B2:B100, MATCH("ClientX", A2:A100, 0))Forget VLOOKUP. INDEX + MATCH is not just faster—it’s directional. Want to search left of your lookup column? VLOOKUP can’t do that. But this duo can.
Scenario: You need to find a project’s status, but the project name is in Column C and the status is in Column B. VLOOKUP fails. INDEX + MATCH doesn’t blink.
2. XLOOKUP
Use Case: Modern Excel users only. Single formula to replace VLOOKUP, HLOOKUP, INDEX/MATCH.
=XLOOKUP("ClientX", A2:A100, B2:B100, "Not Found")Bonus: Supports searching top-down or left-right, optional return-if-not-found, and array returns.
Scenario: Auto-populating dashboards. One formula. No drama.
3. TEXTSPLIT()
Use Case: Splitting full names, comma-separated lists, or emails.
=TEXTSPLIT(A2, ", ")Scenario: You download a CRM dump and column B is “First Last”. You need separate columns for personalization.
Requires: Office 365 / Excel 2021+
4. IFERROR()
Use Case: Elegant error handling.
=IFERROR(VLOOKUP(A2, Data!A:B, 2, FALSE), "Missing")Scenario: Your formula is throwing #N/A, and it’s screwing up your pivot tables. Wrap it with IFERROR, replace it with something useful.
5. SUMIFS()
Use Case: Conditional summing with multiple criteria.
=SUMIFS(Sales!C:C, Sales!A:A, "East", Sales!B:B, "Q1")Scenario: Total sales in the East region for Q1. Think of it like SQL’s WHERE clause.
6. COUNTIFS()
Use Case: Count based on multiple filters.
=COUNTIFS(Status!A:A, "Open", Status!B:B, "High")Scenario: How many high-priority open tickets? Excel will tell you in one line.
7. UNIQUE()
Use Case: Extracting unique values from a column.
=UNIQUE(A2:A100)Scenario: You want a dropdown list of unique departments from a list of 1,000 employees.
8. FILTER()
Use Case: Pull rows based on condition(s).
=FILTER(A2:C100, B2:B100="Pending")Scenario: You want only the pending tasks, but don’t want to touch filters or slicers.
9. LET()
Use Case: Cleaner formulas with variable declarations.
=LET(x, A1*2, y, A2*3, x+y)Scenario: You’re repeating the same logic multiple times in a formula. LET assigns names so you don’t.
10. SEQUENCE()
Use Case: Auto-generate lists of numbers or dates.
=SEQUENCE(10,1,1,1)Scenario: You want 10 serial numbers starting from 1. Zero typing.
11. CHOOSE()
Use Case: Dynamic selection between inputs.
=CHOOSE(2, "Red", "Blue", "Green")Returns “Blue”.
Get TechnoChic’s stories in your inbox
Join Medium for free to get updates from this writer.
Scenario: Creating dynamic labels in dashboards based on drop-down values.
12. TEXT()
Use Case: Date/time formatting inside formulas.
=TEXT(A2, "MMMM YYYY")Scenario: Your pivot table needs month-year labels like “June 2025”, not serial numbers.
13. ISNUMBER() + SEARCH()
Use Case: Partial text match detection.
=ISNUMBER(SEARCH("Urgent", A2))Scenario: You want to highlight tasks with “Urgent” in their title using Conditional Formatting.
14. TRIM() + CLEAN()
Use Case: Data cleanup.
=TRIM(CLEAN(A2))Scenario: Imported CSV has extra spaces or invisible line breaks. These two clean the mess.
15. INDIRECT()
Use Case: Dynamic range references.
=SUM(INDIRECT("B2:B" & A1))Scenario: You want to sum rows based on a number in A1. Dangerous, but powerful.
Part II: 30 Excel Shortcuts You Should Tattoo Onto Your Keyboard Muscle Memory
Keyboard shortcuts are where 90% of Excel efficiency lives. Here’s the survival kit.
Navigation
Ctrl + Arrow keys— Jump to data edgesCtrl + Home— Jump to cell A1Ctrl + End— Jump to last used cellF5 > Special— Go To (Blanks, Formulas, Constants)
Selection
Shift + Space— Select rowCtrl + Space— Select columnCtrl + Shift + Arrow— Select block of dataCtrl + A— Select all (double-tap selects data region)
Editing
F2— Edit cellAlt + Enter— New line in same cellCtrl + D— Copy downCtrl + R— Copy rightCtrl + ;— Insert current dateCtrl + Shift + "+"— Insert new row/column
Formatting
Ctrl + 1— Format Cells dialogCtrl + Shift + $— Currency formatCtrl + Shift + %— Percentage formatCtrl + Shift + #— Date formatAlt + H + O + I— AutoFit column width
Data & Formulas
Alt + =— AutoSumCtrl + '— Copy formula from aboveCtrl + Shift + L— Toggle filterF9— Calculate all worksheetsCtrl + ~— Show all formulasCtrl + [/Ctrl + ]— Trace precedents/dependents
Navigation Hacks
Ctrl + Tab— Switch workbookCtrl + Page Up/Down— Switch sheetsCtrl + F3— Name Manager
Power Moves
Alt + E + S + V— Paste special valuesAlt + D + F + F— Toggle AutofilterAlt + A + T— Text to Columns
But Wait — Excel Isn’t Just About Knowing Stuff. It’s About Solving Things
Formulas don’t matter unless they mean something to your workflow.
Let’s say you’re:
- Building a KPI dashboard:
XLOOKUP,LET, andFILTER()will save you hours. - Cleaning dirty export data:
TRIM(),CLEAN(), andTEXTSPLIT()are your janitors. - Summarizing quarterly performance:
SUMIFS(),TEXT(), andUNIQUE()make reporting dynamic and powerful.
Excel, at its best, is not just a spreadsheet tool. It’s a logic engine. A lightweight database. A calculator with context. And sometimes, it’s a life-saver for someone who needs to make sense of a 10,000-row CSV before the 2 PM meeting.
Bonus: A Resource That Actually Teaches Excel Like This
Want to go further with real-world Excel techniques (no fluff)? Try:
- ExcelJet’s formula list: https://exceljet.net/formulas
- Leila Gharani’s YouTube tutorials: https://www.youtube.com/c/LeilaGharani
Final Thought
Every time someone says “Excel is basic,” show them a workbook where 10 formulas auto-generate 15 dashboard views, cleanse the data, summarize the KPIs, and alert you to anomalies — all without a single mouse click.
Excel isn’t basic. Your use of it might be.
Level up.
