Last Updated: November 21, 2025
Essential Keyboard Shortcuts
| Shortcut | Action |
|---|---|
Ctrl + T
|
Create table from selection |
Ctrl + Shift + L
|
Toggle filters |
Ctrl + Arrow
|
Jump to edge of data region |
Ctrl + Shift + Arrow
|
Select to edge of data |
Alt + =
|
AutoSum |
Ctrl + ;
|
Insert current date |
Ctrl + Shift + ;
|
Insert current time |
F4
|
Toggle absolute/relative references |
F2
|
Edit cell |
Ctrl + D
|
Fill down |
Ctrl + R
|
Fill right |
Ctrl + +
|
Insert rows/columns |
Ctrl + -
|
Delete rows/columns |
Alt + Enter
|
New line in cell |
Ctrl + 1
|
Format cells dialog |
Ctrl + Shift + $
|
Apply currency format |
Ctrl + Shift + %
|
Apply percentage format |
Ctrl + Shift + #
|
Apply date format |
Ctrl + Space
|
Select entire column |
Shift + Space
|
Select entire row |
Ctrl + Page Up/Down
|
Switch worksheets |
Ctrl + Home
|
Go to cell A1 |
Ctrl + End
|
Go to last used cell |
Ctrl + F
|
Find |
Ctrl + H
|
Find and replace |
Alt + H + O + I
|
Auto-fit column width |
F9
|
Calculate all worksheets |
Shift + F9
|
Calculate active worksheet |
Ctrl + `
|
Show formulas |
Alt + A + T
|
Open filter menu |
Lookup & Reference Formulas
| Formula | Description | Example |
|---|---|---|
XLOOKUP
|
Modern lookup (Excel 365) |
=XLOOKUP(A2, B:B, C:C, "Not Found")
|
VLOOKUP
|
Vertical lookup |
=VLOOKUP(A2, B:D, 3, FALSE)
|
HLOOKUP
|
Horizontal lookup |
=HLOOKUP(A2, B1:Z5, 3, FALSE)
|
INDEX + MATCH
|
Powerful lookup combo |
=INDEX(C:C, MATCH(A2, B:B, 0))
|
INDEX + MATCH (2D)
|
Two-way lookup |
=INDEX(C2:F10, MATCH(A2,B2:B10,0), MATCH(G1,C1:F1,0))
|
INDIRECT
|
Reference from text string |
=INDIRECT("Sheet2!A"&ROW())
|
OFFSET
|
Reference offset from cell |
=OFFSET(A1, 2, 3, 5, 1)
|
CHOOSE
|
Select from list by index |
=CHOOSE(A1, "Red", "Green", "Blue")
|
Conditional & Logic Formulas
| Formula | Description | Example |
|---|---|---|
IF
|
Basic conditional |
=IF(A1>100, "High", "Low")
|
IFS
|
Multiple conditions |
=IFS(A1>100,"High", A1>50,"Med", TRUE,"Low")
|
SWITCH
|
Match against multiple values |
=SWITCH(A1, 1,"One", 2,"Two", 3,"Three")
|
AND
|
All conditions true |
=AND(A1>50, B1<100)
|
OR
|
Any condition true |
=OR(A1>50, B1<100)
|
XOR
|
Exactly one true |
=XOR(A1>50, B1<100)
|
NOT
|
Inverse logic |
=NOT(A1="")
|
IFERROR
|
Handle errors |
=IFERROR(A1/B1, 0)
|
IFNA
|
Handle #N/A errors |
=IFNA(VLOOKUP(...), "Not Found")
|
Aggregate & Statistical Formulas
| Formula | Description | Example |
|---|---|---|
SUMIF
|
Conditional sum |
=SUMIF(A:A, ">100", B:B)
|
SUMIFS
|
Multiple condition sum |
=SUMIFS(C:C, A:A, ">100", B:B, "Yes")
|
COUNTIF
|
Conditional count |
=COUNTIF(A:A, ">100")
|
COUNTIFS
|
Multiple condition count |
=COUNTIFS(A:A, ">100", B:B, "Yes")
|
AVERAGEIF
|
Conditional average |
=AVERAGEIF(A:A, ">100", B:B)
|
AVERAGEIFS
|
Multiple condition average |
=AVERAGEIFS(C:C, A:A, ">100", B:B, "Yes")
|
MAXIFS / MINIFS
|
Conditional max/min |
=MAXIFS(C:C, A:A, ">100")
|
AGGREGATE
|
Function with ignore options |
=AGGREGATE(9, 6, A1:A100)
|
SUBTOTAL
|
Ignores filtered rows |
=SUBTOTAL(9, A1:A100)
|
Text & String Formulas
| Formula | Description | Example |
|---|---|---|
TEXTJOIN
|
Join text with delimiter |
=TEXTJOIN(", ", TRUE, A1:A10)
|
CONCAT
|
Combine text/ranges |
=CONCAT(A1:A10)
|
LEFT / RIGHT / MID
|
Extract substring |
=LEFT(A1, 5)
=MID(A1, 3, 2)
|
LEN
|
String length |
=LEN(A1)
|
TRIM
|
Remove extra spaces |
=TRIM(A1)
|
UPPER / LOWER / PROPER
|
Change case |
=PROPER(A1)
|
SUBSTITUTE
|
Replace text |
=SUBSTITUTE(A1, "old", "new")
|
FIND / SEARCH
|
Find text position |
=FIND("@", A1)
|
TEXTSPLIT
|
Split text to array (365) |
=TEXTSPLIT(A1, ",")
|
Date & Time Formulas
| Formula | Description | Example |
|---|---|---|
TODAY()
|
Current date |
=TODAY()
|
NOW()
|
Current date and time |
=NOW()
|
DATE
|
Create date |
=DATE(2024, 12, 25)
|
YEAR / MONTH / DAY
|
Extract date parts |
=YEAR(A1)
|
EOMONTH
|
End of month |
=EOMONTH(A1, 0)
|
NETWORKDAYS
|
Workdays between dates |
=NETWORKDAYS(A1, B1, Holidays)
|
WORKDAY
|
Date after X workdays |
=WORKDAY(A1, 30, Holidays)
|
DATEDIF
|
Date difference |
=DATEDIF(A1, B1, "D")
|
Array & Dynamic Array Formulas (Excel 365)
| Formula | Description | Example |
|---|---|---|
FILTER
|
Filter array by condition |
=FILTER(A:C, A:A>100, "No results")
|
SORT
|
Sort array |
=SORT(A:C, 1, -1)
|
SORTBY
|
Sort by another column |
=SORTBY(A:B, B:B, -1)
|
UNIQUE
|
Get unique values |
=UNIQUE(A:A)
|
SEQUENCE
|
Generate number sequence |
=SEQUENCE(10, 1, 1, 1)
|
RANDARRAY
|
Random number array |
=RANDARRAY(10, 5, 1, 100, TRUE)
|
VSTACK / HSTACK
|
Stack arrays |
=VSTACK(A1:C10, A11:C20)
|
TOCOL / TOROW
|
Convert to column/row |
=TOCOL(A1:C10)
|
Power Query Basics
| Task | How To |
|---|---|
| Load Data | Data tab → Get Data → From File/Database/Web |
| Transform Data | Power Query Editor → Home tab → Transform options |
| Remove Duplicates | Select column → Right-click → Remove Duplicates |
| Split Column | Select column → Transform → Split Column → By Delimiter |
| Merge Queries | Home → Merge Queries → Select matching columns |
| Append Queries | Home → Append Queries → Stack tables vertically |
| Fill Down/Up | Right-click column → Fill → Down/Up |
| Pivot/Unpivot | Transform → Pivot/Unpivot Column |
| Group By | Transform → Group By → Select aggregation |
| Custom Column | Add Column → Custom Column → Write M formula |
| Replace Values | Right-click column → Replace Values |
| Change Type | Select column → Transform → Data Type |
| Filter Rows | Click filter dropdown on column header |
| Refresh Data | Data tab → Refresh All or right-click query |
Pivot Table Tips
| Tip | Description |
|---|---|
| Recommended PivotTables | Insert → Recommended PivotTables for quick setup |
| Slicers | PivotTable Analyze → Insert Slicer for visual filtering |
| Timeline | Insert Timeline for date-based filtering (date fields) |
| Calculated Fields | PivotTable Analyze → Fields, Items & Sets → Calculated Field |
| Show Values As | Right-click value → Show Values As → % of Total, Running Total, etc. |
| Group Dates | Right-click date field → Group → by Months, Quarters, Years |
| Refresh | Right-click pivot → Refresh or Alt + F5 |
| Drill Down | Double-click any value cell to see source data |
| Compact Layout | Design → Report Layout → Show in Compact Form |
| Repeat Labels | PivotTable Analyze → Field Settings → Repeat item labels |
Data Validation Tricks
| Validation Type | Setup |
|---|---|
| Dropdown List | Data → Data Validation → List → Source: range or comma-separated |
| Dynamic Dropdown | Use named range with OFFSET or table reference |
| Dependent Dropdown | Use INDIRECT in validation formula: =INDIRECT($A1) |
| Date Range | Data Validation → Date → between [start] and [end] |
| Number Range | Data Validation → Whole number/Decimal → between |
| Text Length | Data Validation → Text length → minimum/maximum |
| Custom Formula | Data Validation → Custom → =your_formula |
| Prevent Duplicates | Custom: =COUNTIF($A$1:$A$100, A1)=1 |
| Input Message | Data Validation → Input Message tab → Add help text |
| Error Alert | Data Validation → Error Alert tab → Custom error message |
Conditional Formatting Rules
| Rule Type | Use Case |
|---|---|
| Highlight Cells Rules | Greater than, Less than, Between, Equal to, Text contains |
| Top/Bottom Rules | Top 10, Bottom 10%, Above/Below average |
| Data Bars | Visual bars showing relative values |
| Color Scales | 2 or 3-color gradient based on values |
| Icon Sets | Traffic lights, arrows, ratings based on thresholds |
| Custom Formula | Highlight entire row: =$B1="Pending" |
| Duplicate Values | Automatically highlight duplicates or unique values |
| Dates | Yesterday, Today, Tomorrow, Last/This/Next week/month |
| Manage Rules | Home → Conditional Formatting → Manage Rules |
| Stop If True | In Manage Rules, check box to prevent further rules |
💡 Pro Tips:
- Convert data to Tables (Ctrl+T) for automatic formula expansion and better formatting
- Use named ranges (Ctrl+F3) to make formulas more readable and maintainable
- Press F9 in formula bar to evaluate parts of complex formulas while debugging
- Use Paste Special → Values (Ctrl+Alt+V, V) to remove formulas but keep results
- Create drop-down lists from table columns for automatic updates as data grows
- Use Ctrl+Shift+U to expand/collapse formula bar for long formulas
- Flash Fill (Ctrl+E) can detect patterns and auto-fill data transformations
- Use Go To Special (Ctrl+G → Special) to select blanks, formulas, constants, etc.
- Camera tool (add to Quick Access) creates live snapshots of ranges
- Use Error Checking options to customize which formula errors Excel flags
- Alt+D+F+F opens Advanced Filter for complex multi-criteria filtering
- Custom number format codes: #,##0;"(#,##0)";"-";@ shows negatives in parentheses