Welcome to Part 1 of the Excel Formula Mastery & Excel Formula Cheat Sheet series!
If you’re new to Excel or looking to upgrade your spreadsheet skills, this guide is your best starting point.
Here, you’ll discover 16 powerful and practical Excel formulas used for data analysis, business reports, and academic work.
Each formula comes with:
- A short explanation
- Practical examples
- An exercise table to practice yourself
By mastering these Excel Formula Mastery & Excel Formula Cheat Sheet, you’ll have a solid foundation to handle financial models, analytics, or any Excel project confidently.
🧮 1. SUM() – Add Up Numbers Quickly
Description: Adds all numbers in a range. Use the SUM() function to calculate totals, such as sales, expenses, or scores.
| Item | A | B | C | Formula | Result |
| 1 | 10 | 15 | 20 | =SUM(A2:C2) | 45 |
| 2 | 5 | 12 | 18 | =SUM(A3:C3) | 35 |
| 3 | 8 | 7 | 25 | =SUM(A4:C4) | 40 |
| 4 | 6 | 14 | 10 | =SUM(A5:C5) | 30 |
| 5 | 20 | 5 | 15 | =SUM(A6:C6) | 40 |
Exercise Table:
| Task | Column Data | Formula |
| Total for each row | Copy numbers from A2:C6 | =SUM(A2:C2) and drag down |
💡 Tip: SUM is the most-used formula in every Excel sheet — it’s the foundation of all calculations.
📊 2. AVERAGE() – Find the Mean Value
Description: Calculates the mean of numbers. Use AVERAGE() to find typical values like average marks, monthly sales, test scores or ratings.
| Item | A | B | C | Formula | Result |
| 1 | 10 | 20 | 30 | =AVERAGE(A2:C2) | 20 |
| 2 | 5 | 15 | 25 | =AVERAGE(A3:C3) | 15 |
| 3 | 12 | 8 | 20 | =AVERAGE(A4:C4) | 13.33 |
| 4 | 7 | 14 | 21 | =AVERAGE(A5:C5) | 14 |
| 5 | 6 | 9 | 12 | =AVERAGE(A6:C6) | 9 |
Exercise Table:
| Task | Column Data | Formula |
| Find average for each row | Copy numbers from A2:C6 | =AVERAGE(A2:C2) |
🔽 3. MIN() / MAX() – Find Smallest or Largest
Description:
MAX()gives the largest number.MIN()gives the smallest number
Useful for ranges like results, temperatures, scores, or prices.
| Item | A | B | C | MIN Formula | MIN Result | MAX Formula | MAX Result |
| 1 | 10 | 5 | 20 | =MIN(A2:C2) | 5 | =MAX(A2:C2) | 20 |
| 2 | 7 | 12 | 3 | =MIN(A3:C3) | 3 | =MAX(A3:C3) | 12 |
| 3 | 14 | 6 | 8 | =MIN(A4:C4) | 6 | =MAX(A4:C4) | 14 |
| 4 | 5 | 15 | 10 | =MIN(A5:C5) | 5 | =MAX(A5:C5) | 15 |
| 5 | 9 | 11 | 7 | =MIN(A6:C6) | 7 | =MAX(A6:C6) | 11 |
Exercise Table:
| Task | Column Data | Formula |
| Find MIN/MAX per row | Copy numbers from A2:C6 | =MIN(A2:C2) / =MAX(A2:C2) |
🧩 4. COUNT() / COUNTA() / COUNTBLANK() – Count Cells
Description:
- COUNT() counts numeric cells
- COUNTA() counts non-empty cells (numbers + text)
- COUNTBLANK() counts empty cells
| Item | A | B | C | COUNT Formula | COUNT Result | COUNTA Formula | COUNTA Result | COUNTBLANK Formula | COUNTBLANK Result |
| 1 | 5 | “Hello” | 10 | =COUNT(A2:C2) | 2 | =COUNTA(A2:C2) | 3 | =COUNTBLANK(A2:C2) | 0 |
| 2 | 7 | “” | “Yes” | =COUNT(A3:C3) | 1 | =COUNTA(A3:C3) | 2 | =COUNTBLANK(A3:C3) | 1 |
| 3 | “” | 12 | 15 | =COUNT(A4:C4) | 2 | =COUNTA(A4:C4) | 3 | =COUNTBLANK(A4:C4) | 1 |
| 4 | “” | “” | 5 | =COUNT(A5:C5) | 1 | =COUNTA(A5:C5) | 1 | =COUNTBLANK(A5:C5) | 2 |
| 5 | 8 | “Hi” | “” | =COUNT(A6:C6) | 1 | =COUNTA(A6:C6) | 2 | =COUNTBLANK(A6:C6) | 1 |
Exercise Table:
| Task | Column Data | Formula |
| Count numbers/non-empty/blanks | Copy numbers/text from A2:C6 | COUNT(), COUNTA(), COUNTBLANK() |
✖️ 5. PRODUCT() – Multiply All Values
Description: Multiplies numbers in a range. Useful for calculating volumes, total combinations, or scaling values.
| Item | A | B | C | Formula | Result |
| 1 | 2 | 3 | 4 | =PRODUCT(A2:C2) | 24 |
| 2 | 1 | 5 | 2 | =PRODUCT(A3:C3) | 10 |
| 3 | 3 | 3 | 3 | =PRODUCT(A4:C4) | 27 |
| 4 | 2 | 6 | 1 | =PRODUCT(A5:C5) | 12 |
| 5 | 5 | 2 | 2 | =PRODUCT(A6:C6) | 20 |
Exercise Table:
| Task | Column Data | Formula |
| Multiply numbers per row | Copy numbers from A2:C6 | =PRODUCT(A2:C2) |
➗ 6. MOD() – Get Remainders
Description: Returns the remainder after division. Useful for checking even/odd numbers or cycles.
| Number | Divisor | Formula | Result |
| 10 | 3 | =MOD(A2,B2) | 1 |
| 7 | 2 | =MOD(A3,B3) | 1 |
| 15 | 4 | =MOD(A4,B4) | 3 |
| 12 | 5 | =MOD(A5,B5) | 2 |
| 9 | 6 | =MOD(A6,B6) | 3 |
Exercise Table:
| Task | Column Data | Formula |
| Find remainder per row | Copy numbers/divisors | =MOD(A2,B2) |
⚡ 7. ABS() – Absolute Value
Description: Returns the absolute value (positive number). Useful for profits/losses or deviations.
| Number | Formula | Result |
| -10 | =ABS(A2) | 10 |
| 5 | =ABS(A3) | 5 |
| -7 | =ABS(A4) | 7 |
| 0 | =ABS(A5) | 0 |
| -3 | =ABS(A6) | 3 |
Exercise Table:
| Task | Column Data | Formula |
| Convert negatives to positive | -10, 5, -7, 0, -3 | =ABS(A2) |
Use Case: Perfect for analyzing profit/loss or error margins in data.
📈 8. POWER() – Exponent Function
Description: Raises a number to a power. Use for exponents, growth calculations, and squares/cubes.
| Base | Exponent | Formula | Result |
| 2 | 2 | =POWER(A2,B2) | 4 |
| 3 | 3 | =POWER(A3,B3) | 27 |
| 5 | 2 | =POWER(A4,B4) | 25 |
| 4 | 3 | =POWER(A5,B5) | 64 |
| 6 | 2 | =POWER(A6,B6) | 36 |
Exercise Table:
| Task | Column Data | Formula |
| Raise numbers to power | Copy Base & Exponent | =POWER(A2,B2) |
🧮 9. SQRT() – Find Square Roots
Description: Returns the square root. Use for areas, distances, or variance calculations.
| Number | Formula | Result |
| 16 | =SQRT(A2) | 4 |
| 25 | =SQRT(A3) | 5 |
| 36 | =SQRT(A4) | 6 |
| 49 | =SQRT(A5) | 7 |
| 64 | =SQRT(A6) | 8 |
Exercise Table:
| Task | Column Data | Formula |
| Find square roots | Copy numbers | =SQRT(A2) |
🔢 10. ROUND(), ROUNDUP(), ROUNDDOWN()
Description:
- ROUND → nearest value
- ROUNDUP → always up
- ROUNDDOWN → always down
| Number | ROUND | Result | ROUNDUP | Result | ROUNDDOWN | Result |
| 4.67 | =ROUND(A2,1) | 4.7 | =ROUNDUP(A2,1) | 4.7 | =ROUNDDOWN(A2,1) | 4.6 |
| 3.141 | =ROUND(A3,2) | 3.14 | =ROUNDUP(A3,2) | 3.15 | =ROUNDDOWN(A3,2) | 3.14 |
| 7.89 | =ROUND(A4,0) | 8 | =ROUNDUP(A4,0) | 8 | =ROUNDDOWN(A4,0) | 7 |
Exercise Table:
| Task | Column Data | Formula |
| Round numbers | 4.67, 3.141, 7.89 | ROUND(), ROUNDUP(), ROUNDDOWN() |
💲 11. CEILING() / FLOOR()
Description: Rounds numbers to nearest multiple up or down. Useful for pricing, bins, or schedules.
| Number | Significance | CEILING | Result | FLOOR | Result |
| 7 | 5 | =CEILING(A2,B2) | 10 | =FLOOR(A2,B2) | 5 |
| 12 | 3 | =CEILING(A3,B3) | 12 | =FLOOR(A3,B3) | 12 |
| 14 | 4 | =CEILING(A4,B4) | 16 | =FLOOR(A4,B4) | 12 |
Exercise Table:
| Task | Column Data | Formula |
| Round to nearest multiple | 7,12,14 | CEILING(), FLOOR() |
🎲 12. RAND() / RANDBETWEEN()
Description: Generates random numbers. RAND → decimal, RANDBETWEEN → integer. Useful for mock data or simulations.
| Formula | Result (Example) |
| =RAND() | 0.483 |
| =RANDBETWEEN(1,100) | 57 |
| =RANDBETWEEN(10,50) | 23 |
Exercise Table:
| Task | Column Data | Formula |
| Generate 10 random integers | Blank | =RANDBETWEEN(1,100) |
| Generate 5 random decimals | Blank | =RAND() |
🔢 13. INT() – Round Down
Description: Rounds down to nearest integer. Useful for whole counts or truncating decimals.
| Number | Formula | Result |
| 5.9 | =INT(A2) | 5 |
| 7.8 | =INT(A3) | 7 |
| -3.7 | =INT(A4) | -4 |
Exercise Table:
| Task | Column Data | Formula |
| Convert decimals to integers | 5.9, 7.8, -3.7 | INT(A2) |
⚙️ 14. EVEN() / ODD()
Description: Rounds numbers up to nearest even or odd number. Useful for sequences, bins, or grouping.
| Number | EVEN | Result | ODD | Result |
| 3 | =EVEN(A2) | 4 | =ODD(A2) | 3 |
| 5 | =EVEN(A3) | 6 | =ODD(A3) | 5 |
| 12 | =EVEN(A4) | 12 | =ODD(A4) | 13 |
Exercise Table:
| Task | Column Data | Formula |
| Round to nearest even/odd | 3, 5, 12 | EVEN(A2)/ODD(A2) |
✅ All 16 formulas are now merged with descriptions, big demo tables, and exercises. This is the 1st Part of Excel Formula Mastery & Excel Formula Cheat Sheet.
