Excel Formulas Cheat Sheet
Master Excel with our comprehensive formulas reference guide. From basic math to advanced lookup functions, find syntax, examples, and expert tips for all essential Excel formulas.
Excel Mastery Guide
Beginner
Start with basic calculations and data organization
Intermediate
Master lookup functions and data analysis
📊 What is Microsoft Excel?
Microsoft Excel is the world's most popular spreadsheet application, developed by Microsoft and first released in 1985. Excel revolutionized data analysis, financial modeling, and business calculations by providing a powerful yet user-friendly interface for organizing, calculating, and analyzing data. With over 750 million users worldwide, Excel has become an essential tool for professionals across every industry, from accountants and analysts to scientists and entrepreneurs.
🚀 Core Capabilities
- ✓ Data organization: Create structured tables, databases, and lists
- ✓ Formula calculations: 400+ built-in functions for complex calculations
- ✓ Data visualization: Charts, graphs, and conditional formatting
- ✓ Data analysis: PivotTables, statistical analysis, and modeling
- ✓ Automation: Macros and VBA programming for repetitive tasks
- ✓ Collaboration: Real-time sharing and co-authoring features
💼 Who Uses Excel?
- • Finance professionals: Financial modeling, budgeting, forecasting
- • Data analysts: Statistical analysis, trend identification
- • Business managers: Reporting, planning, performance tracking
- • Researchers: Data collection, analysis, and visualization
- • Students: Academic projects, research, and learning
- • Small business owners: Inventory, sales tracking, bookkeeping
🏢 Industry Applications
Financial Services
Risk analysis, portfolio management, loan calculations, investment modeling, financial reporting
Healthcare & Research
Clinical data analysis, patient tracking, research statistics, medical billing, lab results
Education & Training
Grade tracking, student performance analysis, curriculum planning, educational research
⚡ Excel Formula Power
Formula Categories
- • Math & Statistics: SUM, AVERAGE, COUNT, STDEV
- • Logical: IF, AND, OR, NOT
- • Text: CONCATENATE, LEFT, RIGHT, MID
- • Date & Time: TODAY, NOW, DATEDIF
- • Lookup: VLOOKUP, INDEX, MATCH
Formula Benefits
- • Automation: Calculate complex results instantly
- • Accuracy: Eliminate manual calculation errors
- • Efficiency: Process thousands of data points
- • Dynamic: Update automatically when data changes
- • Scalability: Handle growing datasets seamlessly
🛠️ Excel Components
📋 Worksheets
Grid-based interface with rows, columns, and cells for data entry
🧮 Formulas
Mathematical expressions that calculate values automatically
📊 Charts
Visual representations of data in various graph formats
🔧 Functions
Pre-built formulas for specific calculations and operations
🎯 Excel vs. Alternatives
Excel Advantages
- • Industry standard with universal compatibility
- • Comprehensive feature set for all skill levels
- • Extensive formula library (400+ functions)
- • Advanced visualization and PivotTable capabilities
- • VBA programming for custom solutions
- • Seamless integration with Microsoft Office
Popular Alternatives
- • Google Sheets: Cloud-based, free, real-time collaboration
- • LibreOffice Calc: Open-source, free alternative
- • Apple Numbers: Mac-native, design-focused
- • Airtable: Database-spreadsheet hybrid
- • Notion: All-in-one workspace with table features
🚀 Getting Started with Excel
1. Learn the Interface
Familiarize yourself with the ribbon, worksheets, cells, and formula bar • Practice basic navigation and data entry
2. Master Basic Formulas
Start with SUM, AVERAGE, COUNT • Learn cell references (A1, $A$1) • Practice IF statements and basic logic
3. Explore Advanced Features
Learn VLOOKUP, PivotTables, and data visualization • Practice with real datasets • Gradually build complexity
Excel Mastery Tip: The key to Excel expertise is practice and progressive learning. Start with basic formulas, build confidence, then gradually tackle more complex functions. This cheat sheet is your roadmap to Excel formula mastery! 📈
Excel Pro Tips
F4 Key: Toggle between absolute and relative references ($A$1, A$1, $A1, A1)
Ctrl + Shift + Enter: Enter array formulas (older Excel versions)
F9 Key: Calculate formulas manually when set to manual calculation mode
Ctrl + `: Toggle between formula view and result view
AVERAGEIF()
Math & StatisticalAverages cells that meet a criteria
Syntax:
=AVERAGEIF(range, criteria, [average_range])Examples:
=AVERAGEIF(A1:A10, ">100") Average of values greater than 100
=AVERAGEIF(A1:A10, "Apple", B1:B10) Average B values where A contains 'Apple'
=AVERAGEIF(A1:A10, A12, B1:B10) Average B values where A equals value in A12
Note: If average_range is omitted, range is used for both criteria and average
AVERAGEIFS()
Math & StatisticalAverages cells that meet multiple criteria
Syntax:
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)Examples:
=AVERAGEIFS(C1:C10, A1:A10, "Apple", B1:B10, ">100") Average C where A='Apple' AND B>100
=AVERAGEIFS(D1:D10, A1:A10, ">=" & DATE(2024,1,1), A1:A10, "<" & DATE(2025,1,1)) Average D for dates in 2024
=AVERAGEIFS(C1:C10, A1:A10, "North", B1:B10, "Q1") Average for North region in Q1
Note: All criteria must be met for a cell to be included in average
AVERAGE()
Math & StatisticalCalculates the average of numbers
Syntax:
=AVERAGE(number1, [number2], ...)Examples:
=AVERAGE(A1:A10) Average of range A1 to A10
=AVERAGE(A1, B1, C1) Average of specific cells
=AVERAGE(A:A) Average of entire column A
Note: Excludes empty cells and text from calculation
RANK.AVG()
Statistical FunctionsReturns average rank for tied values
Syntax:
=RANK.AVG(number, ref, [order])Examples:
=RANK.AVG(A1, A$1:A$10) Average rank for tied values
=RANK.AVG(A1, A$1:A$10, 1) Ascending order with average ranking
=RANK.AVG(90, {95,90,90,85}) Returns 2.5 for both 90s
Note: Tied values get average of ranks they would occupy
MEDIAN()
Statistical FunctionsReturns the middle value in a dataset
Syntax:
=MEDIAN(number1, [number2], ...)Examples:
=MEDIAN(A1:A10) Middle value of range A1:A10
=MEDIAN(1, 2, 3, 4, 5) Returns 3 (middle value)
=MEDIAN(A:A) Median of entire column A
Note: For even number of values, returns average of two middle values
SUBTOTAL()
Math & StatisticalReturns a subtotal in a list or database using specified function
Syntax:
=SUBTOTAL(function_num, ref1, [ref2], ...)Examples:
=SUBTOTAL(9, A1:A10) Sum of visible cells (function 9)
=SUBTOTAL(1, A1:A10) Average of visible cells (function 1)
=SUBTOTAL(109, A1:A10) Sum ignoring hidden rows and filtered data
Note: Functions 1-11 include hidden cells, 101-111 ignore hidden cells
Master Excel formulas with practice and repetition. Bookmark this page for quick reference!