25 Excel formulas

# **25 Essential Excel Formulas You Should Know** Microsoft Excel is a powerful tool for handling numbers, text, and data. It comes with many built-in formulas to simplify calculations. Here are **25 essential Excel formulas** that will help you work smarter and faster. --- ## 📌 1. Summing a Range of Cells 👉 **Formula:** `SUM(range)` 📌 **Example:** `=SUM(A1:A5)` adds up all the numbers in cells A1 to A5. ## 📌 2. Calculating an Average 👉 **Formula:** `AVERAGE(range)` 📌 **Example:** `=AVERAGE(B1:B5)` finds the average of values in B1 to B5. ## 📌 3. Finding the Maximum Value 👉 **Formula:** `MAX(range)` 📌 **Example:** `=MAX(C1:C10)` returns the highest value in C1 to C10. ## 📌 4. Finding the Minimum Value 👉 **Formula:** `MIN(range)` 📌 **Example:** `=MIN(D1:D10)` returns the smallest value in D1 to D10. ## 📌 5. Linking Cells from Different Sheets 👉 **Formula:** `'SheetName'!CellReference` 📌 **Example:** `'Sales'!A1` pulls data from cell A1 in the "Sales" sheet. ## 📌 6. Combining Text from Two Cells 👉 **Formula:** `CONCATENATE(text1, text2)` *(or use `TEXTJOIN` in newer versions)* 📌 **Example:** `=CONCATENATE(A1, " ", B1)` joins A1 and B1 with a space in between. ## 📌 7. Summing Based on a Condition 👉 **Formula:** `SUMIF(range, criteria, [sum_range])` 📌 **Example:** `=SUMIF(A1:A10, ">50", B1:B10)` adds up values in B1:B10 if corresponding A1:A10 values are greater than 50. ## 📌 8. Retrieving a Value Using INDEX 👉 **Formula:** `INDEX(range, row_num, col_num)` 📌 **Example:** `=INDEX(A1:C3, 2, 1)` returns the value from row 2, column 1 of the selected range.

## 📌 9. Rounding a Number 👉 **Formula:** `ROUND(cell, decimal_places)` 📌 **Example:** `=ROUND(A1, 0)` rounds A1 to the nearest whole number. ## 📌 10. Extracting the Left Part of a Text String 👉 **Formula:** `LEFT(text, num_chars)` 📌 **Example:** `=LEFT(A1, 3)` extracts the first 3 characters from A1. ## 📌 11. Extracting the Right Part of a Text String 👉 **Formula:** `RIGHT(text, num_chars)` 📌 **Example:** `=RIGHT(A1, 4)` extracts the last 4 characters from A1. ## 📌 12. Looking Up Values Vertically (VLOOKUP) 👉 **Formula:** `VLOOKUP(lookup_value, table, col_index, [true/false])` 📌 **Example:** `=VLOOKUP(1001, A2:C10, 2, FALSE)` searches for **1001** and returns the value from the second column. ## 📌 13. Looking Up Values Horizontally (HLOOKUP) 👉 **Formula:** `HLOOKUP(lookup_value, table, row_index, [true/false])` 📌 **Example:** `=HLOOKUP(50, A1:J3, 2, FALSE)` searches for **50** in the first row and returns the corresponding value from the second row. ## 📌 14. Calculating a Square Root 👉 **Formula:** `SQRT(number)` 📌 **Example:** `=SQRT(64)` returns **8**. ## 📌 15. Generating a Random Number 👉 **Formula:** `RAND()` 📌 **Example:** `=RAND()` gives a random number between 0 and 1. ## 📌 16. Counting Cells Containing Numbers 👉 **Formula:** `COUNT(range)` 📌 **Example:** `=COUNT(A1:A10)` counts the number of numerical values in A1:A10. ## 📌 17. Counting Non-Empty Cells 👉 **Formula:** `COUNTA(range)` 📌 **Example:** `=COUNTA(A1:A10)` counts all non-empty cells in A1:A10. ## 📌 18. Getting the Current Date 👉 **Formula:** `TODAY()` 📌 **Example:** `=TODAY()` returns today’s date. ## 📌 19. Adding Days to a Date 👉 **Formula:** `Date_Cell + Days` 📌 **Example:** `=A1 + 7` adds 7 days to the date in A1. ## 📌 20. Calculating Percentage Change 👉 **Formula:** `(New_Value - Old_Value) / Old_Value` 📌 **Example:** `=(B2 - A2) / A2` calculates the percentage change between A2 and B2.

## 📌 21. Checking If a Cell Is Empty 👉 **Formula:** `ISBLANK(cell)` 📌 **Example:** `=ISBLANK(A1)` checks if A1 is empty. ## 📌 22. Getting the Absolute Value 👉 **Formula:** `ABS(number)` 📌 **Example:** `=ABS(-10)` returns **10**. ## 📌 23. Checking a Condition with IF 👉 **Formula:** `IF(condition, true_value, false_value)` 📌 **Example:** `=IF(A1 > 50, "Pass", "Fail")` checks if A1 is greater than 50 and returns either "Pass" or "Fail". ## 📌 24. Summing Only Visible Cells 👉 **Formula:** `SUBTOTAL(109, range)` 📌 **Example:** `=SUBTOTAL(109, A1:A10)` adds up only visible cells in A1:A10. ## 📌 25. Summing All Cells (Visible & Hidden) 👉 **Formula:** `SUBTOTAL(9, range)` 📌 **Example:** `=SUBTOTAL(9, A1:A10)` includes both visible and hidden cells in the sum. --- ### **Final Thoughts** Excel is a game-changer when it comes to data management and calculations! Start practicing these formulas, and soon, you'll be handling spreadsheets like a pro. 🚀 Happy learning!