1337skills.com

Appearance

Excel - Formulas, Functions & Productivity ​

Ultimate Excel reference for formulas, functions, shortcuts, and productivity features

Excel is the world's most popular spreadsheet application, used by millions for data analysis, financial modeling, and business intelligence. This comprehensive cheat sheet covers essential formulas, functions, shortcuts, and advanced features to boost your Excel productivity.

Basic Formulas & Functions ​

Essential Mathematical Functions ​

excel
# Basic arithmetic
=A1+B1          # Addition
=A1-B1          # Subtraction  
=A1*B1          # Multiplication
=A1/B1          # Division
=A1^B1          # Exponentiation

# Mathematical functions
=SUM(A1:A10)    # Sum range of cells
=AVERAGE(A1:A10) # Average of range
=MIN(A1:A10)    # Minimum value
=MAX(A1:A10)    # Maximum value
=COUNT(A1:A10)  # Count numeric values
=COUNTA(A1:A10) # Count non-empty cells

# Rounding functions
=ROUND(A1,2)    # Round to 2 decimal places
=ROUNDUP(A1,0)  # Round up to nearest integer
=ROUNDDOWN(A1,0) # Round down to nearest integer
=INT(A1)        # Remove decimal portion
=ABS(A1)        # Absolute value

Text Functions ​

excel
# Text manipulation
=LEN(A1)        # Length of text
=LEFT(A1,5)     # First 5 characters
=RIGHT(A1,3)    # Last 3 characters  
=MID(A1,2,4)    # 4 characters starting at position 2
=UPPER(A1)      # Convert to uppercase
=LOWER(A1)      # Convert to lowercase
=PROPER(A1)     # Proper case (title case)

# Text combination and splitting
=CONCATENATE(A1,B1) # Combine text (legacy)
=A1&B1          # Combine text (modern)
=CONCAT(A1:C1)  # Combine range of cells
=TEXTJOIN(",",TRUE,A1:C1) # Join with delimiter

# Text searching and replacing
=FIND("text",A1) # Find position of text (case sensitive)
=SEARCH("text",A1) # Find position of text (case insensitive)
=SUBSTITUTE(A1,"old","new") # Replace text
=TRIM(A1)       # Remove extra spaces

Date and Time Functions ​

excel
# Current date and time
=TODAY()        # Current date
=NOW()          # Current date and time
=DATE(2024,12,25) # Create specific date
=TIME(14,30,0)  # Create specific time (2:30 PM)

# Date calculations
=DATEDIF(A1,B1,"D") # Days between dates
=DATEDIF(A1,B1,"M") # Months between dates
=DATEDIF(A1,B1,"Y") # Years between dates
=WEEKDAY(A1)    # Day of week (1=Sunday)
=MONTH(A1)      # Extract month number
=YEAR(A1)       # Extract year
=DAY(A1)        # Extract day

# Date formatting
=TEXT(A1,"mm/dd/yyyy") # Format date
=TEXT(A1,"dddd")       # Full day name
=TEXT(A1,"mmmm")       # Full month name

Advanced Functions ​

Logical Functions ​

excel
# Conditional logic
=IF(A1>10,"High","Low") # Basic IF statement
=IF(A1>20,"High",IF(A1>10,"Medium","Low")) # Nested IF

# Multiple conditions
=AND(A1>10,B1<5)    # Both conditions true
=OR(A1>10,B1<5)     # Either condition true
=NOT(A1>10)         # Opposite of condition

# Advanced conditional functions
=IFS(A1>20,"High",A1>10,"Medium",TRUE,"Low") # Multiple IF conditions
=SWITCH(A1,1,"One",2,"Two",3,"Three","Other") # Switch statement

Lookup Functions ​

excel
# VLOOKUP (Vertical lookup)
=VLOOKUP(A1,Table1,2,FALSE) # Exact match lookup
=VLOOKUP(A1,Table1,2,TRUE)  # Approximate match lookup

# HLOOKUP (Horizontal lookup)  
=HLOOKUP(A1,Table1,2,FALSE) # Horizontal table lookup

# INDEX and MATCH (more flexible)
=INDEX(B:B,MATCH(A1,A:A,0)) # Find value using INDEX/MATCH
=INDEX(Table1,MATCH(A1,Table1[Name],0),2) # Table lookup

# XLOOKUP (modern replacement for VLOOKUP)
=XLOOKUP(A1,Table1[Name],Table1[Value]) # Modern lookup function

# Multiple criteria lookup
=INDEX(C:C,MATCH(1,(A:A=A1)*(B:B=B1),0)) # Array formula lookup

Statistical Functions ​

excel
# Descriptive statistics
=MEDIAN(A1:A10)     # Middle value
=MODE(A1:A10)       # Most frequent value
=STDEV(A1:A10)      # Standard deviation
=VAR(A1:A10)        # Variance
=PERCENTILE(A1:A10,0.9) # 90th percentile

# Conditional statistics
=SUMIF(A:A,">10",B:B)      # Sum if condition met
=COUNTIF(A:A,"Text")       # Count if condition met
=AVERAGEIF(A:A,">10",B:B)  # Average if condition met

# Multiple criteria
=SUMIFS(C:C,A:A,">10",B:B,"<5")     # Sum with multiple criteria
=COUNTIFS(A:A,">10",B:B,"Text")     # Count with multiple criteria
=AVERAGEIFS(C:C,A:A,">10",B:B,"<5") # Average with multiple criteria

Data Analysis Tools ​

Pivot Tables ​

excel
# Pivot table creation (Alt+N+V)
1. Select data range
2. Insert > PivotTable
3. Drag fields to areas:
   - Rows: Categories to group by
   - Columns: Additional grouping
   - Values: Data to summarize
   - Filters: Data to filter

# Pivot table functions
=GETPIVOTDATA("Sales",A3,"Region","East") # Extract pivot data

Data Validation ​

excel
# Create dropdown lists
1. Select cells
2. Data > Data Validation
3. Allow: List
4. Source: =Sheet1!A1:A10 or "Option1,Option2,Option3"

# Validation formulas
=AND(A1>=0,A1<=100)     # Number between 0-100
=LEN(A1)<=10            # Text length limit
=ISNUMBER(A1)           # Must be number

Conditional Formatting ​

excel
# Formula-based formatting
=MOD(ROW(),2)=0         # Highlight every other row
=A1>AVERAGE($A$1:$A$10) # Highlight above average
=COUNTIF($A$1:A1,A1)>1  # Highlight duplicates
=AND(A1<>"",$B1="")     # Highlight missing data

Keyboard Shortcuts ​

# Basic navigation
Ctrl+Home          # Go to cell A1
Ctrl+End           # Go to last used cell
Ctrl+Arrow Keys    # Jump to edge of data
Ctrl+Page Up/Down  # Switch between worksheets
F5                 # Go to specific cell

# Selection shortcuts
Ctrl+Shift+End     # Select to last used cell
Ctrl+Shift+Arrow  # Select to edge of data
Ctrl+Space         # Select entire column
Shift+Space        # Select entire row
Ctrl+A             # Select all data

Editing Shortcuts ​

# Cell editing
F2                 # Edit active cell
Ctrl+D             # Fill down
Ctrl+R             # Fill right
Ctrl+Shift+Plus    # Insert cells/rows/columns
Ctrl+Minus         # Delete cells/rows/columns

# Formatting shortcuts
Ctrl+B             # Bold
Ctrl+I             # Italic
Ctrl+U             # Underline
Ctrl+Shift+$       # Currency format
Ctrl+Shift+%       # Percentage format
Ctrl+Shift+#       # Date format

Formula Shortcuts ​

# Formula creation
=                  # Start formula
F4                 # Toggle absolute/relative references
Ctrl+Shift+Enter  # Array formula
Alt+=              # AutoSum
Ctrl+`             # Show/hide formulas
F9                 # Calculate formulas

Advanced Features ​

Array Formulas ​

excel
# Legacy array formulas (Ctrl+Shift+Enter)
{=SUM(A1:A10*B1:B10)}           # Multiply arrays and sum
{=MAX(IF(A1:A10="Text",B1:B10))} # Conditional max

# Dynamic arrays (Excel 365)
=FILTER(A1:C10,B1:B10>10)       # Filter data
=SORT(A1:C10,2,-1)              # Sort by column 2 descending
=UNIQUE(A1:A10)                 # Remove duplicates
=SEQUENCE(10,1,1,1)             # Generate sequence 1-10

Power Query (Data > Get Data) ​

excel
# Common transformations
1. Remove duplicates
2. Filter rows
3. Add custom columns
4. Group by columns
5. Merge/append tables
6. Change data types
7. Split columns
8. Pivot/unpivot columns

Macros and VBA Basics ​

vba
' Basic VBA syntax
Sub MyMacro()
    Range("A1").Value = "Hello World"
    Range("A1").Font.Bold = True
    Range("A1:A10").Select
End Sub

' Common VBA objects
Workbooks("filename.xlsx")     ' Reference workbook
Worksheets("Sheet1")           ' Reference worksheet  
Range("A1:B10")               ' Reference range
Cells(1,1)                    ' Reference cell by row/column

Tips and Best Practices ​

Formula Best Practices ​

excel
# Use named ranges for clarity
=SUM(SalesData)    # Instead of =SUM(A1:A100)

# Use table references
=SUM(Table1[Sales]) # Reference table columns

# Avoid volatile functions in large sheets
=NOW()             # Recalculates constantly
=INDIRECT()        # Can slow performance
=OFFSET()          # Can slow performance

# Use structured references
=[@Sales]*[@Quantity] # Reference current row in table

Performance Optimization ​

excel
# Efficient formulas
=SUMPRODUCT()      # Often faster than array formulas
=INDEX/MATCH       # Faster than VLOOKUP
=XLOOKUP()         # Modern, efficient lookup

# Avoid entire column references when possible
=SUM(A:A)          # Slower
=SUM(A1:A1000)     # Faster with known range

Error Handling ​

excel
# Common error functions
=IFERROR(A1/B1,"Division Error")    # Handle any error
=IFNA(VLOOKUP(A1,Table1,2,0),"Not Found") # Handle #N/A
=ISERROR(A1)       # Check if cell contains error
=ISBLANK(A1)       # Check if cell is empty
=ISNUMBER(A1)      # Check if cell contains number

Common Use Cases ​

Financial Calculations ​

excel
# Loan calculations
=PMT(5%/12,60,10000)    # Monthly payment for loan
=PV(5%/12,60,200)       # Present value of annuity
=FV(5%/12,60,200)       # Future value of annuity
=RATE(60,200,10000)     # Interest rate calculation

# Investment calculations
=NPV(10%,B1:B5)         # Net present value
=IRR(B1:B5)             # Internal rate of return
=XIRR(B1:B5,A1:A5)      # IRR with specific dates

Data Analysis Examples ​

excel
# Sales analysis
=SUMIFS(Sales,Region,"East",Month,"January") # Regional sales
=AVERAGEIFS(Sales,Product,"Widget",Year,2024) # Product average
=COUNTIFS(Status,"Complete",Priority,"High")   # Count criteria

# Ranking and percentiles
=RANK(A1,A:A,0)         # Rank value (descending)
=PERCENTRANK(A:A,A1)    # Percentile rank
=LARGE(A:A,3)           # 3rd largest value
=SMALL(A:A,3)           # 3rd smallest value

This comprehensive Excel cheat sheet covers the essential formulas, functions, and features needed for effective spreadsheet work. Master these concepts to significantly boost your Excel productivity and data analysis capabilities.