Essential Excel Formulas and Functions Reference
Posted on Mar 11, 2026 in Mathematics
Quick Reference: Excel Formulas
- Adding many cells: =SUM
- Adding 2 cells: + or –
- Finding typical value: =AVERAGE
- Finding true middle: =MEDIAN
- Finding smallest: =MIN
- Finding largest: =MAX
- Counting numbers: =COUNT
- Counting text or anything: =COUNTA
- Applying rule/condition: =IF
- Calculating loan payment: =PMT
- Summarizing multiple sheets: 3D SUM
- Testing different scenarios: Goal Seek / Data Table
- Multiplying by fixed %: Absolute reference ($)
1. Mathematical & Statistical Functions
These are the workhorses of Excel for basic calculations.
| Formula | What it does | When to use it |
|---|
| =SUM() | Adds up all numbers in a range. | Totals (sales, expenses) or 3D sums across sheets. Use instead of ‘+’ for more than two cells. |
| =AVERAGE() | Calculates the arithmetic mean. | Finding central tendency (e.g., average sales). |
| =MEDIAN() | Finds the middle value. | Finding central tendency, less sensitive to outliers than average. |
| =MIN() | Returns the smallest number. | Finding the lowest value in a range. |
| =MAX() | Returns the largest number. | Finding the highest value in a range. |
2. Logical Functions
| Formula | What it does | When to use it |
|---|
| =IF() | Performs a logical test (True/False). | Calculating bonuses or any ‘what-if’ scenario. |
3. Financial Functions
Specialized functions for loan and investment calculations.
| Formula | What it does | When to use it |
|---|
| =PMT() | Calculates loan payments. | Property loans. Note: Divide annual rate by 12 for monthly payments. |
4. Date & Time Functions
| Formula | What it does | When to use it |
|---|
| =TODAY() | Returns the current date. | Dynamic date stamps on reports. |
| =NOW() | Returns current date and time. | Timestamps for when a report was viewed. |
5. Counting Functions
| Formula | What it does | When to use it |
|---|
| =COUNT() | Counts cells with numbers. | Checking how many numeric data points exist. |
| =COUNTA() | Counts non-empty cells. | Counting any information (text, numbers, errors). |
6. Referencing Techniques
| Technique | What it is | When to use it |
|---|
| Relative (B7) | Changes when copied. | Default behavior for row/column calculations. |
| Absolute ($B$24) | Locked reference. | When multiplying by a fixed tax rate or commission. |
| 3D (Sheet1:Sheet3!C4) | Refers to same cell across sheets. | Summarizing data from multiple identical worksheets. |
7. Specialized Tools
| Tool | What it does | When to use it |
|---|
| Goal Seek | Finds input needed for a result. | Working backwards to solve for an unknown variable. |
| Data Table | Calculates results by varying inputs. | Sensitivity analysis (e.g., changing interest and term). |
