1. What is a Formula in MS Excel?
A formula in Excel is an expression used to perform calculations on numbers, text, or cell values.
Formulas can add, subtract, multiply, divide, average, count, and much more.
✅ Every formula in Excel begins with an equal sign (=).
Example:
= A1 + B1
This formula adds the values in cell A1 and B1.
🪜 2. How to Insert a Formula in Excel
➤ Method 1: Manual Typing
- Click on a blank cell (where you want the result).
- Type
=followed by the formula.
Example:=A2+B2 - Press Enter.
Excel calculates and displays the result.
➤ Method 2: Using Formula Bar
- Click the cell → Click inside the Formula Bar (above the sheet).
- Type your formula → Press Enter.
➤ Method 3: Using Insert Function (fx Button)
- Select a cell.
- Click the fx icon next to the formula bar.
- The Insert Function dialog box appears.
- Choose a function category (e.g., Financial, Statistical, Math).
- Select a function name (like SUM, AVERAGE, PMT).
- Enter cell references → Click OK.
🔢 3. Basic Arithmetic Formulas
| Operation | Formula Example | Description |
|---|---|---|
| Addition | =A1 + B1 | Adds two cells |
| Subtraction | =A1 - B1 | Subtracts B1 from A1 |
| Multiplication | =A1 * B1 | Multiplies two cells |
| Division | =A1 / B1 | Divides A1 by B1 |
| Percentage | =A1*10% | Finds 10% of A1 |
| Sum of range | =SUM(A1:A10) | Adds values from A1 to A10 |
💰 4. Financial Functions in Excel
Excel provides built-in financial functions used in business, banking, and accounting.
| Function | Syntax | Purpose | Example |
|---|---|---|---|
| PMT | =PMT(rate, nper, pv) | Calculates monthly payment for a loan | =PMT(10%/12, 12*2, -100000) → EMI for ₹1,00,000 loan |
| FV | =FV(rate, nper, pmt, [pv]) | Calculates future value of investment | =FV(0.08, 10, -5000) → value after 10 years |
| PV | =PV(rate, nper, pmt) | Calculates present value of investment | =PV(0.1, 5, -2000) |
| RATE | =RATE(nper, pmt, pv, [fv]) | Returns interest rate per period | =RATE(5, -2000, 10000) |
| NPV | =NPV(rate, value1, [value2]...) | Calculates net present value of cash flows | =NPV(0.1, A2:A6) |
| IRR | =IRR(values) | Calculates internal rate of return | =IRR(A1:A5) |
| MIRR | =MIRR(values, finance_rate, reinvest_rate) | Modified IRR considering reinvestment | =MIRR(A1:A5, 0.1, 0.12) |
💡 Tip: Always make sure to use negative values for payments (outflows) and positive for receipts (inflows).
📊 5. Statistical Functions in Excel
Statistical functions help summarize, analyze, and interpret data — essential for reports and analytics.
| Function | Syntax | Description | Example |
|---|---|---|---|
| AVERAGE | =AVERAGE(A1:A10) | Calculates mean (average) | Average marks of 10 students |
| MEDIAN | =MEDIAN(A1:A10) | Returns the middle value | Median salary in a list |
| MODE.SNGL | =MODE.SNGL(A1:A10) | Most frequently occurring value | Mode of marks |
| COUNT | =COUNT(A1:A10) | Counts cells with numbers only | Count of numeric entries |
| COUNTA | =COUNTA(A1:A10) | Counts all non-empty cells | Counts names, text, and numbers |
| MAX | =MAX(A1:A10) | Returns largest value | Highest marks |
| MIN | =MIN(A1:A10) | Returns smallest value | Lowest marks |
| STDEV.S | =STDEV.S(A1:A10) | Standard deviation of sample | Spread of data points |
| VAR.S | =VAR.S(A1:A10) | Variance of sample data | Measure of data variation |
| LARGE | =LARGE(A1:A10, k) | kth largest value | 2nd highest marks |
| SMALL | =SMALL(A1:A10, k) | kth smallest value | 2nd lowest marks |
🧮 6. Example: Salary Data
| Employee | Salary |
|---|---|
| A | 35,000 |
| B | 42,000 |
| C | 38,000 |
| D | 50,000 |
| E | 45,000 |
Now we can use formulas:
| Function | Formula | Result |
|---|---|---|
| Average Salary | =AVERAGE(B2:B6) | 42,000 |
| Highest Salary | =MAX(B2:B6) | 50,000 |
| Lowest Salary | =MIN(B2:B6) | 35,000 |
| Count of Employees | =COUNT(B2:B6) | 5 |
| Median Salary | =MEDIAN(B2:B6) | 42,000 |
🧠 7. How to Identify a Formula Easily
Every formula starts with:
=
When you click a cell with a formula, the Formula Bar displays it (e.g., =SUM(A1:A5)).
💡 8. Pro Tips
- Use AutoSum (Alt + =) shortcut to quickly insert
SUM()formula. - Use absolute reference ($A$1) when you don’t want the cell reference to change in copy/paste.
- Combine functions, e.g.:
=AVERAGEIF(A2:A10, ">60")→ Averages only values greater than 60.
🏁 9. Summary Table
| Category | Function Examples | Purpose |
|---|---|---|
| Basic Math | SUM, AVERAGE, MAX, MIN | Simple calculations |
| Financial | PMT, FV, PV, RATE, NPV, IRR | Business & loan analysis |
| Statistical | COUNT, MEDIAN, MODE, STDEV | Data analysis |
| Logical | IF, AND, OR | Conditional testing |
| Text | LEFT, RIGHT, CONCAT, LEN | Manipulate text |
| Date/Time | TODAY, NOW, DATEDIF | Manage dates |