Excel Cheat Sheet
Quick reference guide for Excel formulas, shortcuts, and functions
Essential Keyboard Shortcuts
Navigation
- Move to edge of data Ctrl + Arrow
- Next worksheet Ctrl + PgDn
- Previous worksheet Ctrl + PgUp
- Beginning of sheet Ctrl + Home
- End of sheet Ctrl + End
- Go to specific cell Ctrl + G
- Switch workbooks Ctrl + Tab
- Scroll one screen up Page Up
- Scroll one screen down Page Down
- Move between panes F6
Selection
- Select all Ctrl + A
- Select entire column Ctrl + Space
- Select entire row Shift + Space
- Extend selection Shift + Arrow
- Select to last cell Ctrl + Shift + End
- Select current array Ctrl + /
- Select data region Ctrl + Shift + *
- Add to selection Ctrl + Click
- Select to start Ctrl + Shift + Home
- Select current region Ctrl + A (twice)
Editing
- Edit active cell F2
- Clear cell contents Delete
- Cut Ctrl + X
- Copy Ctrl + C
- Paste Ctrl + V
- Undo Ctrl + Z
- Redo Ctrl + Y
- Fill down Ctrl + D
- Fill right Ctrl + R
- Insert current date Ctrl + ;
Formatting
- Bold Ctrl + B
- Italic Ctrl + I
- Underline Ctrl + U
- Open format cells Ctrl + 1
- Apply border Ctrl + Shift + &
- Number format Ctrl + Shift + 1
- Time format Ctrl + Shift + 2
- Percentage format Ctrl + Shift + %
- Scientific format Ctrl + Shift + ^
Formulas & Functions
- Insert formula Alt + =
- Calculate all sheets F9
- Insert function Shift + F3
- Create absolute reference F4
- Array formula Ctrl + Shift + Enter
- Show formulas Ctrl + `
- Insert function arguments Ctrl + A
- Calculate active sheet Shift + F9
- Toggle formula bar Ctrl + F2
- Evaluate formula Alt + F9
Data & Tables
- Create table Ctrl + T
- Insert pivot table Alt + N + V
- Filter data Ctrl + Shift + L
- Sort ascending Alt + A + S + A
- Sort descending Alt + A + S + D
- Refresh data Alt + F5
- Group items Alt + Shift + →
- Ungroup items Alt + Shift + ←
- Subtotal Alt + A + B
- Remove duplicates Alt + A + M
Pro Tips
- Hold Ctrl while dragging to copy instead of move
- Double-click cell borders to auto-fit column/row size
- Press Alt to see keyboard shortcuts for ribbon commands
Common Excel Formulas
Formula Basics
- All formulas start with an equals sign (=)
- Cell references can be relative (A1) or absolute ($A$1)
- Use parentheses to control calculation order
Lookup & Reference
VLOOKUP
=VLOOKUP(lookup_value, table_array, col_index_num,
[range_lookup])
Example:
=VLOOKUP(A2, B2:D10, 2, FALSE)
Looks up value in A2 within range B2:D10 and returns matching value from column 2
XLOOKUP
=XLOOKUP(lookup_value, lookup_array, return_array,
[missing_value], [match_mode], [search_mode])
Example:
=XLOOKUP(A2, B2:B10, C2:C10, "Not Found", 0)
More versatile than VLOOKUP - can look left, exact match by default
INDEX MATCH
=INDEX(return_array, MATCH(lookup_value, lookup_array, 0))
Example:
=INDEX(C2:C10, MATCH(A2, B2:B10, 0))
More flexible than VLOOKUP - can look in any direction
Logical Formulas
IF Statement
=IF(logical_test, value_if_true, value_if_false)
Example:
=IF(A2>100, "High", "Low")
Nested IF
=IF(logical_test1, value1, IF(logical_test2, value2,
value3))
Example:
=IF(A2>90,"A",IF(A2>80,"B",IF(A2>70,"C","F")))
Assigns letter grades based on numerical scores
IFS Function
=IFS(logical_test1, value1, logical_test2, value2, ...)
Example:
=IFS(A2>90,"A",A2>80,"B",A2>70,"C",TRUE,"F")
Cleaner alternative to nested IF statements
Text Formulas
Concatenate Text
=CONCATENATE(text1, [text2], ...)
Examples:
=CONCATENATE(A2, " ", B2)
=A2 & " " & B2
Text Extraction
=LEFT(text, num_chars)
=RIGHT(text, num_chars)
=MID(text, start_num, num_chars)
Examples:
=LEFT(A2, 3)
=RIGHT(A2, 4)
=MID(A2, 2, 5)
Date & Time
Date Functions
=TODAY()
=NOW()
=NETWORKDAYS(start_date, end_date, [holidays])
=DATEDIF(start_date, end_date, "Y")
Examples:
=TODAY() // Current date
=NETWORKDAYS(A1, A2) // Working days between dates
=DATEDIF(A1, A2, "Y") // Years between dates
Date Calculations
=EDATE(start_date, months)
=WORKDAY(start_date, days, [holidays])
=WEEKNUM(date, [return_type])
Examples:
=EDATE(A1, 1) // Add one month
=WORKDAY(A1, 10) // Add 10 working days
Math & Financial
Mathematical Operations
=SUM(range)
=SUMIF(range, criteria, [sum_range])
=ROUND(number, num_digits)
=SUMPRODUCT(array1, [array2], ...)
Examples:
=SUMIF(A1:A10, ">100", B1:B10)
=ROUND(A1, 2)
=SUMPRODUCT(A1:A10, B1:B10)
Financial Functions
=PMT(rate, nper, pv, [fv], [type])
=FV(rate, nper, pmt, [pv], [type])
=NPV(rate, value1, [value2], ...)
=IRR(values, [guess])
Examples:
=PMT(0.05/12, 360, 200000) // Monthly mortgage payment
=FV(0.06/12, 10*12, -100) // Future value of savings
Formula Tips & Tricks
- Press F4 while selecting a cell reference to toggle through absolute/relative references
- Use Alt + Enter to add line breaks within formulas for better readability
- Press Ctrl + ` to show formulas instead of results in all cells
- Use F9 to evaluate parts of complex formulas during editing
Excel Functions by Category
Statistical Functions
AVERAGE
=AVERAGE(number1, [number2], ...)
COUNT
=COUNT(value1, [value2], ...)
Excel Chart Types & Usage
Chart Selection Tips
- Choose a chart type that best represents your data story
- Keep it simple - avoid unnecessary 3D effects or decorations
- Consider your audience when selecting chart complexity
Comparison Charts
Column Chart
Best for:
- Comparing values across categories
- Showing changes over time
- Displaying frequencies
Tip: Use stacked columns to show part-to-whole relationships
Bar Chart
Best for:
- Long category names
- Comparing many categories
- Ranking data
Tip: Sort bars for better data visualization
Trend Charts
Line Chart
Best for:
- Showing trends over time
- Comparing multiple series
- Continuous data
Tip: Use markers to highlight specific data points
Area Chart
Best for:
- Showing volume over time
- Cumulative totals
- Part-to-whole relationships
Tip: Use stacked area for multiple series
Distribution Charts
Pie Chart
Best for:
- Parts of a whole
- Percentage distribution
- Simple proportions
Tip: Limit to 6-8 segments for clarity
Scatter Chart
Best for:
- Correlation analysis
- Relationship patterns
- Data clustering
Tip: Add trendlines to show relationships
Chart Best Practices
Do's:
- Start Y-axis at zero for bar/column charts
- Use consistent colors for the same data series
- Label axes clearly and include units
Don'ts:
- Use 3D charts unless absolutely necessary
- Overcrowd charts with too many data series
- Use pie charts for more than 8 categories
Quick Search
Categories
Download Options
Instant download, no email required
Compatible with all Excel versions
Regular updates included