Introduction

Developing Excel skills is critical in the working profession, regardless of the industry. An understanding of Excel formulas is vital if you are to improve on ways, compile information, and make sound recommendations. Now, consider the list of 30 basic Excel formulas everyone should know.

Overview: 

• Learn what is an Excel formula and why it is important.
• Gain an understanding of the Excel formulas that everyone should know.

What is an Excel Formula?

An Excel formula is a mathematical phrase used to perform computations or work with data in a spreadsheet. It usually begins with an equal sign (=) and includes operands, operators, and functions. This allows users to automate computations and effectively make choices based on data.

Why Are Excel Formulas Important?

They are crucial for several reasons:

• Automation: They automate repetitive calculations, saving users time and effort.
• Accuracy: Formulas ensure accurate results, reducing the risk of manual calculation errors.
• Consistency: Users consistently maintain data integrity by applying formulas across datasets.
• Analysis: By logically compiling, arranging, and analyzing data, formulas help with data analysis.
• Decision Making: They help users make well-informed judgments based on estimated outcomes, such as financial or trend analyses.
• Productivity: They increase productivity by enabling users to swiftly and effectively complete complex computations.

30 Basic Excel Formulas

Here are 30 Basic formulas that everyone should know.

Arithmetic Functions

1. SUM
   • Syntax: =SUM(number1, [number2], ...)
   • Example: =SUM(A1:A10)
2. AVERAGE
   • Syntax: =AVERAGE(number1, [number2], ...)
   • Example: =AVERAGE(B1:B10)
3. COUNT
   • Syntax: =COUNT(value1, [value2], ...)
   • Example: =COUNT(C1:C10)
4. COUNTA
   • Syntax: =COUNTA(value1, [value2], ...)
   • Example: =COUNTA(D1:D10)
5. SUMIF
   • Syntax: =SUMIF(range, criteria, [sum_range])
   • Example: =SUMIF(F1:F10, ">50")
6. COUNTIF
   • Syntax: =COUNTIF(range, criteria)
   • Example: =COUNTIF(G1:G10, ">=60")
7. ABS
   • Syntax: =ABS(number)
   • Example: =ABS(Z1)
8. ROUND
   • Syntax: =ROUND(number, num_digits)
   • Example: =ROUND(AA1, 2)

Logical Functions

9. IF
   • Syntax: =IF(logical_test, value_if_true, value_if_false)
   • Example: =IF(E1>50, "Pass", "Fail")
10. AND
    • Syntax: =AND(logical1, [logical2], ...)
    • Example: =AND(S1>10, S2<20)
11. OR
    • Syntax: =OR(logical1, [logical2], ...)
    • Example: =OR(T1>10, T2<5)
12. NOT
    • Syntax: =NOT(logical)
    • Example: =NOT(U1>10)

Lookup and Reference Functions

13. VLOOKUP
    • Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
    • Example: =VLOOKUP(H1, A1:C10, 3, FALSE)
14. HLOOKUP
    • Syntax: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
    • Example: =HLOOKUP(I1, A1:E10, 5, FALSE)
15. MATCH
    • Syntax: =MATCH(lookup_value, lookup_array, [match_type])
    • Example: =MATCH(J1, B1:B10, 0)
16. INDEX
    • Syntax: =INDEX(array, row_num, [col_num])
    • Example: =INDEX(A1:D10, 3, 2)

Text Functions

17. CONCATENATE
    • Syntax: =CONCATENATE(text1, [text2], ...)
    • Example: =CONCATENATE("Hello ", "World")
18. LEFT
    • Syntax: =LEFT(text, [num_chars])
    • Example: =LEFT(K1, 3)
19. RIGHT
    • Syntax: =RIGHT(text, [num_chars])
    • Example: =RIGHT(L1, 2)
20. MID
    • Syntax: =MID(text, start_num, num_chars)
    • Example: =MID(M1, 2, 3)
21. LEN
    • Syntax: =LEN(text)
    • Example: =LEN(N1)
22. TRIM
    • Syntax: =TRIM(text)
    • Example: =TRIM(O1)
23. REPLACE
    • Syntax: =REPLACE(old_text, start_num, num_chars, new_text)
    • Example: =REPLACE(P1, 1, 3, "New")
24. SUBSTITUTE
    • Syntax: =SUBSTITUTE(text, old_text, new_text, [instance_num])
    • Example: =SUBSTITUTE(Q1, "old", "new")
25. TEXT
    • Syntax: =TEXT(value, format_text)
    • Example: =TEXT(R1, "mm/dd/yyyy")

Date and Time Functions

26. NOW
    • Syntax: =NOW()
    • Example: =NOW()

Information Functions

27. ISNUMBER
    • Syntax: =ISNUMBER(value)
    • Example: =ISNUMBER(V1)
28. ISBLANK
    • Syntax: =ISBLANK(value)
    • Example: =ISBLANK(W1)
29. IFERROR
    • Syntax: =IFERROR(value, value_if_error)
    • Example: =IFERROR(X1/Y1, "Error")

Random Number Functions

30. RANDBETWEEN
    • Syntax: =RANDBETWEEN(bottom, top)
    • Example: =RANDBETWEEN(1, 100)

Conclusion

The foundation of effective data management and analysis is Excel formulae. These thirty fundamental formulae will provide you with a strong basis on which to work while using Excel for various tasks, from straightforward computations to intricate data manipulations. You’ll learn new techniques to improve your work processes, extract more meaning from your data, and ultimately succeed in your career as you experiment and practice with these formulae.

If you want to learn more about Excel, consider doing Analytics Vidhya’s Free Course.