Microsoft Excel is a powerful data management tool that provides a wide range of functions and formulas to make it easier to analyze and process information. In this article, we will look at the top 50 useful Excel formulas, providing a detailed analysis of each of them.
1-10: Basic formulas
1. SUM - Sums the values in a range of cells.
=SUM(A1:A10)
2. AVERAGE - Calculates the average value for a range of cells.
=AVERAGE(B1:B5)
3. COUNT - Counts the number of cells with numeric values in a range.
=COUNT(C1:C8)
4. MIN - Finds the minimum value in a range of cells.
=MIN(D1:D12)
5. MAX - Finds the maximum value in a range of cells.
=MAX(E1:E15)
6. IF (IF) - Performs a conditional action, depending on whether the condition is true or not.
=IF(F2>10, "Greater than 10", "Less than or equal to 10")
7. VLOOKUP (VLOOKUP) - Looks up the value in the first column of a range and returns the corresponding value from another column.
=VLOOKUP(G2, A1:B20, 2, FALSE)
8. HLOOKUP (HLOOKUP) - Looks up the value in the first row of a range and returns the corresponding value from another row.
=HLOOKUP(H2, A1:D5, 3, FALSE)
9. INDEX - Returns a value from a range of cells at the specified row and column coordinates.
=INDEX(A1:C3, 2, 3)
10. MATCH - Looks up a value in a range and returns its position.
=MATCH(I2, D1:D10, 0)
11-20: Text formulas
11. CONCATENATE - Combines text from several cells.
=CONCATENATE(A1, " ", B1)
12. LEFT - returns the specified number of characters from the beginning of the text.
=LEFT(C2, 5)
13. RIGHT - Returns the specified number of characters from the end of the text.
=RIGHT(D2, 3)
14. LEN - Returns the number of characters in the cell.
=LEN(E2)
15. MID (MEDIUM) - Returns a certain number of characters from the text, starting from the specified position.
=MID(F2, 2, 4)
16. LOWER - Converts text to lower case.
=LOWER(G2)
17. UPPER - Converts text to uppercase.
=UPPER(H2)
18. PROPER (CAPITAL1) - Converts text so that the first letter of each word is capitalized.
=PROPER(I2)
19. TRIM - Removes all spaces from text except single spaces between words.
=TRIM(J2)
20. SUBSTITUTE (REPLACEMENT) - Replaces one text with another in a cell.
=SUBSTITUTE(K2, "a", "o")
21-30: Formulas for working with dates and times
21. NOW (NOW) - Returns the current date and time.
=NOW()
22. TODAY - Returns the current date.
=TODAY()
23. DATE - Creates a date based on the specified year, month and day.
=DATE(2024, 1, 19)
24. TIME - Creates a time based on the specified hours, minutes and seconds.
=TIME(12, 30, 0)
25. YEAR - Extracts the year from a date.
=YEAR(M2)
26. MONTH - Extracts the month from a date.
=MONTH(N2)
27. DAY - Extracts the day from a date.
=DAY(O2)
28. HOUR - Extracts the hour from time.
=HOUR(P2)
29. MINUTE - Extracts minutes from time.
=MINUTE(Q2)
30. SECOND - Extracts seconds from time.
=SECOND(R2)
31-40: Financial formulas
31. PV (TP) - Determines the current value of the investment.
=PV(0.05, 10, 2000)
32. FV (TV) - Determines the future value of an investment.
=FV(0.05, 10, -2000)
33. RATE - Calculates the interest rate for an investment.
=RATE(10, -200, 1000)
34. NPER (Term) - Calculates the number of periods for an investment.
=NPER(0.05, -200, 1000)
35. PMT (PLT) - Calculates the monthly loan payment.
=PMT(0.05/12, 5*12, -20000)
36. IRR (IRR) - Calculates the internal rate of return.
=IRR({-5000, 1500, 2000, 2500, 3000})
37. NPV (NPV) - Calculates the net present value of an investment.
=NPV(0.1, -1000, 300, 400, 600, 800)
38. CUMIPMT (NAKOPPLT) - Calculates the accumulated interest on a loan up to a specified period.
=CUMIPMT(0.05, 3, 5*12, -20000, 1, 2)
39. CUMPRINC (NAKOPPLTOSN) - Calculates the accumulated principal debt on a loan up to a specified period.
=CUMPRINC(0.05, 3, 5*12, -20000, 1, 2)
40. DDB (AMORD) - Calculates depreciation using the declining balance method.
=DDB(1000, 100, 5, 1)
41-50: Logical and information formulas
41. IFERROR - Determines whether there is an error in a formula and returns a specified value if an error is detected.
=IFERROR(1/0, "Divide by zero error")
42. IFNA (IF NOT AVAILABLE) - Determines whether the value is a #N/A error and returns the specified value if an error is detected.
=IFNA(VLOOKUP(A1, B1:C10, 2, FALSE), "Value not found")
43. AND (AND) - Returns TRUE if all arguments are true, and FALSE otherwise.
=AND(D2>10, E2<100)
44. OR (OR) - Returns TRUE if at least one of the arguments is true, and FALSE otherwise.
=OR(F2="April", F2="May", F2="June")
45. NOT (NOT) - Inverts the value of a logical expression.
=NOT(G2>0)
46. IFERROR - Determines whether there is an error in the formula and returns the specified value if an error is detected
ena.
=IFERROR(1/0, "Divide by zero error")
47. TEXT - Formats the value as text using the specified format.
=TEXT(NOW(), "dd.mm.yyyy hh:mm:ss")
48. LEN - Returns the number of characters in the cell.
=LEN(E2)
49. ISNUMBER - Returns TRUE if the value is a number, FALSE otherwise.
=ISNUMBER(H2)
50. IF (IF) - Performs a conditional action, depending on whether the condition is true or not.
=IF(I2="Yes", "Paid", "Not paid")
These formulas represent only a small part of the capabilities of Microsoft Excel. Knowing these functions and formulas will allow you to process data more efficiently and create complex reports, which in turn will increase your productivity in working with spreadsheets and documents.
