Excel Master Shortcuts
Speed up your audits and analysis with these essential keys.
🚀 Navigation Speed
Ctrl
Arrow
Jump to edge of data region
Ctrl
PgUp/Dn
Switch between worksheets
Ctrl
Home
Go to cell A1
Ctrl
End
Go to last used cell in sheet
Ctrl
G
Go To dialog (navigate to cell/range)
Ctrl
Shift
End
Select from current cell to last used cell
Ctrl
Shift
Home
Select from current cell to A1
Ctrl
Space
Select entire column
Shift
Space
Select entire row
Ctrl
Shift
*
Select current data region
Ctrl
A
Select all cells (or current region)
Ctrl
Shift
PgUp
Select current and previous sheet
Ctrl
Shift
PgDn
Select current and next sheet
Right-click
Sheet Tab
Select All Sheets (from menu)
Alt
H
O
M
Move or Copy Sheet
Alt
H
I
S
Insert New Sheet
Alt
H
O
R
Rename Sheet
Alt
H
D
S
Delete Sheet
F5
Open Go To dialog
Ctrl
F
Find dialog
Ctrl
H
Find and Replace dialog
✨ Formatting
Ctrl
1
Open Format Cells dialog
Ctrl
Shift
!
Apply Number format (2 decimals)
Alt
H
B
A
Add All Borders
Alt
H
F
F
Change Font Name
Alt
H
F
S
Change Font Size
Ctrl
D
Fill Down (copy from cell above)
Ctrl
R
Fill Right (copy from cell left)
Ctrl
Alt
V
Open Paste Special dialog
Ctrl
Shift
V
Paste Values only (quick)
Alt
H
V
V
Paste Values
Alt
H
V
T
Paste Formats only
Alt
H
V
F
Paste Formulas only
Alt
H
V
W
Paste Column Widths
Alt
H
V
E
Paste Transpose (rows↔columns)
Alt
H
V
A
Paste All (with source formatting)
Alt
H
V
U
Paste Values + Number Formats
📉 Data & Analysis
Alt
N
V
T
Insert Pivot Table
Ctrl
Shift
L
Toggle Filters
Alt
=
AutoSum
Alt
H
L
H
D
Highlight Duplicates
Alt
A
M
Remove Duplicates
Alt
H
S
U
Custom Sort
Alt
H
F
D
S
K
Find Blanks (Go To Special)
🕵️ Auditing
Ctrl
[
Trace Precedents (Go to source)
F2
Edit Active Cell
Ctrl
`
Show Formulas
📐 Essential Formulas Reference
=VLOOKUP()
Syntax: =VLOOKUP(lookup_value, table_array, col_index,
[range_lookup])
Looks up value in first column, returns value from specified column. Use FALSE for exact match.
Looks up value in first column, returns value from specified column. Use FALSE for exact match.
=HLOOKUP()
Syntax: =HLOOKUP(lookup_value, table_array, row_index,
[range_lookup])
Same as VLOOKUP but searches horizontally in first row.
Same as VLOOKUP but searches horizontally in first row.
=XLOOKUP()
Syntax: =XLOOKUP(lookup_value, lookup_array, return_array,
[if_not_found], [match_mode])
Modern replacement for VLOOKUP. More flexible, can search left-to-right or right-to-left.
Modern replacement for VLOOKUP. More flexible, can search left-to-right or right-to-left.
=INDEX()
Syntax: =INDEX(array, row_num, [col_num])
Returns value at specified row/column position in a range.
Returns value at specified row/column position in a range.
=MATCH()
Syntax: =MATCH(lookup_value, lookup_array, [match_type])
Returns position of a value in a range. Use 0 for exact match.
Returns position of a value in a range. Use 0 for exact match.
=INDEX() + MATCH()
The Power Combo:
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
Example: =INDEX(B2:B100, MATCH("John", A2:A100, 0))
Finds "John" in column A, returns corresponding value from column B.
Why better than VLOOKUP?
✓ Can lookup left (VLOOKUP can't)
✓ Faster on large datasets
✓ Column insertions won't break it
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
Example: =INDEX(B2:B100, MATCH("John", A2:A100, 0))
Finds "John" in column A, returns corresponding value from column B.
Why better than VLOOKUP?
✓ Can lookup left (VLOOKUP can't)
✓ Faster on large datasets
✓ Column insertions won't break it
2D INDEX-MATCH
Two-way lookup:
=INDEX(data_range, MATCH(row_lookup, row_headers, 0), MATCH(col_lookup, col_headers, 0))
Looks up both row and column to find intersection value. Perfect for matrix lookups.
=INDEX(data_range, MATCH(row_lookup, row_headers, 0), MATCH(col_lookup, col_headers, 0))
Looks up both row and column to find intersection value. Perfect for matrix lookups.
=SUMIF()
Syntax: =SUMIF(range, criteria, [sum_range])
Sum values that meet a single condition.
Sum values that meet a single condition.
=SUMIFS()
Syntax: =SUMIFS(sum_range, criteria_range1, criteria1,
[criteria_range2, criteria2]...)
Sum with multiple conditions. Note: sum_range comes FIRST!
Sum with multiple conditions. Note: sum_range comes FIRST!
=COUNTIF()
Syntax: =COUNTIF(range, criteria)
Count cells meeting a condition.
Count cells meeting a condition.
=COUNTIFS()
Syntax: =COUNTIFS(criteria_range1, criteria1, [criteria_range2,
criteria2]...)
Count with multiple conditions.
Count with multiple conditions.
=MAXIFS()
Syntax: =MAXIFS(max_range, criteria_range1, criteria1, ...)
Returns maximum value that meets conditions.
Example: =MAXIFS(B:B, A:A, "Sales") → Max value in B where A="Sales"
Returns maximum value that meets conditions.
Example: =MAXIFS(B:B, A:A, "Sales") → Max value in B where A="Sales"
=MINIFS()
Syntax: =MINIFS(min_range, criteria_range1, criteria1, ...)
Returns minimum value that meets conditions.
Returns minimum value that meets conditions.
=AVERAGEIF()
Syntax: =AVERAGEIF(range, criteria, [average_range])
Average of cells meeting a condition.
Average of cells meeting a condition.
=AVERAGEIFS()
Syntax: =AVERAGEIFS(avg_range, criteria_range1, criteria1, ...)
Average with multiple conditions.
Average with multiple conditions.
=AGGREGATE()
Syntax: =AGGREGATE(function_num, options, ref1, [ref2]...)
Function Numbers:
1=AVERAGE, 2=COUNT, 3=COUNTA, 4=MAX, 5=MIN, 6=PRODUCT, 7=STDEV.S, 9=SUM, 14=LARGE, 15=SMALL
Options (ignore):
5=Hidden rows, 6=Error values, 7=Hidden rows & errors
Example: =AGGREGATE(9, 6, A1:A100) → SUM ignoring errors
Super powerful! Ignores errors and hidden rows unlike regular functions.
Function Numbers:
1=AVERAGE, 2=COUNT, 3=COUNTA, 4=MAX, 5=MIN, 6=PRODUCT, 7=STDEV.S, 9=SUM, 14=LARGE, 15=SMALL
Options (ignore):
5=Hidden rows, 6=Error values, 7=Hidden rows & errors
Example: =AGGREGATE(9, 6, A1:A100) → SUM ignoring errors
Super powerful! Ignores errors and hidden rows unlike regular functions.
AGGREGATE + Conditional
MAX with condition (alternative to MAXIFS):
=AGGREGATE(14, 6, 1/(A:A="Sales")*B:B, 1)
Nth LARGEST with condition:
=AGGREGATE(14, 6, 1/(A:A="Sales")*B:B, 2) → 2nd largest
Works in older Excel versions that don't have MAXIFS.
=AGGREGATE(14, 6, 1/(A:A="Sales")*B:B, 1)
Nth LARGEST with condition:
=AGGREGATE(14, 6, 1/(A:A="Sales")*B:B, 2) → 2nd largest
Works in older Excel versions that don't have MAXIFS.
=SUMPRODUCT()
Syntax: =SUMPRODUCT(array1, [array2]...)
Conditional Sum: =SUMPRODUCT((A:A="Sales")*(B:B>100)*C:C)
Sum column C where A="Sales" AND B>100. Very powerful for complex conditions!
Conditional Sum: =SUMPRODUCT((A:A="Sales")*(B:B>100)*C:C)
Sum column C where A="Sales" AND B>100. Very powerful for complex conditions!
=IFERROR()
Syntax: =IFERROR(value, value_if_error)
Wrap around any formula to handle #N/A, #VALUE!, #REF! errors.
Wrap around any formula to handle #N/A, #VALUE!, #REF! errors.
=IFNA()
Syntax: =IFNA(value, value_if_na)
Only catches #N/A errors, lets other errors show (better for debugging).
Only catches #N/A errors, lets other errors show (better for debugging).
Pro Combo: Error-Safe Lookup
=IFERROR(INDEX(return_range, MATCH(lookup, lookup_range, 0)), "Not
Found")
INDEX-MATCH wrapped in IFERROR. Returns "Not Found" instead of #N/A when no match.
INDEX-MATCH wrapped in IFERROR. Returns "Not Found" instead of #N/A when no match.