Productivity

excelmicrosoftspreadsheetformulasoffice

Microsoft Excel Cheat Sheet

Essential Keyboard Shortcuts

Ctrl+Home - Go to cell A1
Ctrl+End - Go to last used cell
Ctrl+Arrow Keys - Jump to edge of data region
Ctrl+Page Up/Down - Switch between worksheets
Alt+Page Up/Down - Move one screen left/right
Ctrl+G or F5 - Go to specific cell (Go To dialog)

Selection

Ctrl+Space - Select entire column
Shift+Space - Select entire row
Ctrl+A - Select entire worksheet
Ctrl+Shift+Arrow - Extend selection to edge of data
Shift+Click - Select range from active cell

Editing

F2 - Edit active cell
Ctrl+D - Fill down
Ctrl+R - Fill right
Ctrl+; - Insert current date
Ctrl+Shift+; - Insert current time
Alt+Enter - New line within cell
Ctrl+K - Insert hyperlink
Ctrl+1 - Format cells dialog

Formulas & Functions

Alt+= - AutoSum
Ctrl+` - Show/hide formulas
F4 - Toggle absolute/relative references
Ctrl+Shift+Enter - Array formula
F9 - Calculate all worksheets
Shift+F9 - Calculate active worksheet

Formatting

Ctrl+B - Bold
Ctrl+I - Italic
Ctrl+U - Underline
Ctrl+5 - Strikethrough
Ctrl+Shift+$ - Currency format
Ctrl+Shift+% - Percentage format
Ctrl+Shift+# - Date format
Alt+H+B - Add borders

Data Management

Ctrl+T - Create table
Alt+A+T - Apply filter
Alt+D+S - Sort
Alt+A+M - Remove duplicates
Ctrl+Shift+L - Toggle AutoFilter
Alt+D+F+F - Advanced filter

Workbook Operations

Ctrl+N - New workbook
Ctrl+O - Open workbook
Ctrl+S - Save
F12 - Save As
Ctrl+W - Close workbook
Ctrl+P - Print
Alt+F+I - File info

Essential Formulas

Logical Functions

=IF(A1>100, "High", "Low")
=AND(A1>50, B1<100)
=OR(A1="Yes", B1="Yes")
=NOT(A1="")
=IFERROR(A1/B1, "Error")
=IFS(A1>90, "A", A1>80, "B", A1>70, "C", TRUE, "F")

Lookup & Reference

=VLOOKUP(A2, Table1, 2, FALSE)
=HLOOKUP(A2, Table1, 2, FALSE)
=INDEX(A1:C10, 5, 2)
=MATCH("Apple", A1:A10, 0)
=XLOOKUP(A2, A:A, B:B)
=INDIRECT("A" & ROW())
=OFFSET(A1, 2, 3)

Text Functions

=CONCATENATE(A1, " ", B1)
=A1&" "&B1
=TEXTJOIN(", ", TRUE, A1:A10)
=LEFT(A1, 5)
=RIGHT(A1, 3)
=MID(A1, 2, 4)
=LEN(A1)
=TRIM(A1)
=UPPER(A1)
=LOWER(A1)
=PROPER(A1)
=SUBSTITUTE(A1, "old", "new")
=FIND("text", A1)
=SEARCH("text", A1)

Math & Statistical

=SUM(A1:A10)
=SUMIF(A1:A10, ">100")
=SUMIFS(C1:C10, A1:A10, "Yes", B1:B10, ">50")
=AVERAGE(A1:A10)
=AVERAGEIF(A1:A10, ">100")
=COUNT(A1:A10)
=COUNTA(A1:A10)
=COUNTIF(A1:A10, ">100")
=COUNTIFS(A1:A10, "Yes", B1:B10, ">50")
=MAX(A1:A10)
=MIN(A1:A10)
=ROUND(A1, 2)
=ROUNDUP(A1, 2)
=ROUNDDOWN(A1, 2)
=ABS(A1)
=RAND()
=RANDBETWEEN(1, 100)

Date & Time

=TODAY()
=NOW()
=DATE(2024, 12, 25)
=YEAR(A1)
=MONTH(A1)
=DAY(A1)
=WEEKDAY(A1)
=EOMONTH(A1, 0)
=NETWORKDAYS(A1, B1)
=DATEDIF(A1, B1, "d")
=TEXT(A1, "mmm dd, yyyy")

Database Functions

=DSUM(Database, "Sales", Criteria)
=DAVERAGE(Database, "Sales", Criteria)
=DCOUNT(Database, "ID", Criteria)
=DMAX(Database, "Sales", Criteria)
=DMIN(Database, "Sales", Criteria)

Advanced Techniques

Array Formulas

{=SUM(A1:A10*B1:B10)}
{=MAX(IF(A1:A10="Yes", B1:B10))}
{=TRANSPOSE(A1:E1)}

Dynamic Arrays (Excel 365)

=FILTER(A1:C10, B1:B10>100)
=SORT(A1:C10, 2, -1)
=UNIQUE(A1:A10)
=SEQUENCE(10, 1, 1, 1)
=RANDARRAY(5, 5, 1, 100, TRUE)

Named Ranges

Ctrl+F3 - Name Manager
Create: Select range → Name Box → Enter name
Use in formulas: =SUM(SalesData)

Conditional Formatting

Home → Conditional Formatting → New Rule

Common Rules:

Highlight cells greater than…
Data bars
Color scales
Icon sets
Custom formulas: =$A1>100

PivotTables

Create:

Insert → PivotTable
Alt+N+V

Shortcuts:

Alt+Shift+Right - Group items
Alt+Shift+Left - Ungroup items
Ctrl+Shift+P - Create calculated field

Data Validation

Data → Data Validation
Alt+A+V+V

Types:

List: =A1:A10 or Apple,Orange,Banana
Number range: Between 1 and 100
Custom: =LEN(A1)<=10

Power Query Basics

Get & Transform Data

Data → Get Data → From Table/Range
Ctrl+T (convert to table first)

Common Transformations:

Remove duplicates
Filter rows
Group by
Merge queries
Append queries
Add custom columns

M Language Examples

= Table.SelectRows(Source, each [Sales] > 1000)
= Table.Group(Source, {"Category"}, {{"Total", each List.Sum([Sales]), type number}})
= Table.AddColumn(Source, "Full Name", each [First] & " " & [Last])

Tips & Best Practices

Performance Optimization

Use tables instead of ranges
- Automatic expansion
- Structured references
- Better performance
Avoid volatile functions
- INDIRECT, OFFSET, NOW, TODAY, RAND
- Use sparingly or alternatives
Calculate manually for large workbooks
- Formulas → Calculation Options → Manual
Use efficient formulas
- SUMIFS instead of SUMPRODUCT
- XLOOKUP instead of INDEX/MATCH when available

Data Organization

One header row
No blank rows/columns in data
Consistent data types in columns
Use tables (Ctrl+T)
Name ranges logically

Formula Auditing

Ctrl+[ - Go to precedents
Ctrl+] - Go to dependents
Formulas → Trace Precedents/Dependents
Formulas → Error Checking
F9 - Evaluate part of formula (in formula bar)

Protection

Review → Protect Sheet
Review → Protect Workbook

Allow specific actions:

Select locked/unlocked cells
Format cells
Insert/delete rows
Sort/Filter

Custom Number Formats

0 = Digit placeholder
# = Optional digit
? = Space for insignificant zeros
@ = Text placeholder

Examples:

0.00 - Two decimal places
#,##0 - Thousands separator
0.00% - Percentage
[Red]0.00;[Blue]-0.00 - Color coding
0" units" - Add text

Advanced Functions (Excel 365)

Dynamic Array Functions

=LET(x, A1:A10, y, B1:B10, SUM(x*y))
=LAMBDA(x, y, x*y)(A1, B1)
=FILTER(A1:D100, (B1:B100>100)*(C1:C100="Yes"))
=SORT(A1:C10, 2, -1, 3, 1)
=SORTBY(A1:C10, B1:B10, -1)
=UNIQUE(A1:A100, FALSE, FALSE)
=SEQUENCE(10, 5, 1, 1)
=XMATCH("Apple", A1:A100)

Common Errors & Solutions

Error	Meaning	Solution
#DIV/0!	Division by zero	Check divisor, use IFERROR
#N/A	Value not available	Check lookup value exists
#NAME?	Function name misspelled	Check spelling
#NULL!	Invalid range intersection	Check cell references
#NUM!	Invalid numeric value	Check number constraints
#REF!	Invalid cell reference	Check deleted cells
#VALUE!	Wrong data type	Check data types match
#####	Column too narrow	Widen column

Useful Add-ins

Analysis ToolPak - Statistical analysis
Solver - Optimization problems
Power Pivot - Advanced data modeling
Power Query - Data transformation
Inquire - Workbook analysis

Quick Tips

Flash Fill (Ctrl+E) - Auto-detect patterns
Quick Analysis (Ctrl+Q) - Instant charts/formatting
Format Painter (Ctrl+Shift+C/V) - Copy formatting
Paste Special (Ctrl+Alt+V) - Values, formats, formulas
Remove duplicates - Data → Remove Duplicates
Text to Columns - Data → Text to Columns
Freeze panes - View → Freeze Panes
Split screen - View → Split
Camera tool - Copy as picture that updates
Custom views - View → Custom Views

Resources

Microsoft Learn: Official Excel documentation
Excel Campus: Jon Acampora’s tutorials
Chandoo.org: Excel tips and dashboards
Mr. Excel: Bill Jelen’s forum and resources

Microsoft Excel Cheat Sheet

Essential Keyboard Shortcuts

Navigation

Selection

Editing

Formulas & Functions

Formatting

Data Management

Workbook Operations

Essential Formulas

Logical Functions

Lookup & Reference

Text Functions

Math & Statistical

Date & Time

Database Functions

Advanced Techniques

Array Formulas

Dynamic Arrays (Excel 365)

Named Ranges

Conditional Formatting

PivotTables

Data Validation

Power Query Basics

Get & Transform Data

M Language Examples

Tips & Best Practices

Performance Optimization

Data Organization

Formula Auditing

Protection

Custom Number Formats

Advanced Functions (Excel 365)

Dynamic Array Functions

Common Errors & Solutions

Useful Add-ins

Quick Tips

Resources