30 basic Excel formulas for everyone

Introduction

Developing Excel skills is essential in the working profession, regardless of the industry. Understanding Excel formulas is vital if you want to improve forms, gather information, and make solid recommendations. Now, consider the list of 30 basic Excel formulas that everyone should know.

General description:

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

What is an Excel formula?

An Excel formula is a mathematical phrase used to perform calculations 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 calculations and make effective data-driven decisions.

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 constantly maintain data integrity by applying formulas across all data sets.
• Analysis: By compiling, organizing, and analyzing data logically, formulas help with data analysis.
• decision making– Help users make well-informed judgments based on estimated results, such as financial or trend analysis.
• Productivity: Increase productivity by allowing users to complete complex calculations quickly and efficiently.

30 basic Excel formulas

Here are 30 basic formulas that everyone should know.

Arithmetic functions

1. ADDITION
   • 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. COUNT
   • Syntax: =COUNTA(value1, (value2), ...)
   • Example: =COUNTA(D1:D10)
5. ADD IF
   • Syntax: =SUMIF(range, criteria, (sum_range))
   • Example: =SUMIF(F1:F10, ">50")
6. COUNT YES
   • 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

1. YEAH
   • Syntax: =IF(logical_test, value_if_true, value_if_false)
   • Example: =IF(E1>50, "Pass", "Fail")
2. AND
   • Syntax: =AND(logical1, (logical2), ...)
   • Example: =AND(S1>10, S2<20)
3. EITHER
   • Syntax: =OR(logical1, (logical2), ...)
   • Example: =OR(T1>10, T2<5)
4. NO
   • Syntax: =NOT(logical)
   • Example: =NOT(U1>10)

Functions of searching and referencing

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

Text functions

1. CONCATENATE
   • Syntax: =CONCATENATE(text1, (text2), ...)
   • Example: =CONCATENATE("Hello ", "World")
2. LEFT
   • Syntax: =LEFT(text, (num_chars))
   • Example: =LEFT(K1, 3)
3. GOOD
   • Syntax: =RIGHT(text, (num_chars))
   • Example: =RIGHT(L1, 2)
4. HALF
   • Syntax: =MID(text, start_num, num_chars)
   • Example: =MID(M1, 2, 3)
5. LEN
   • Syntax: =LEN(text)
   • Example: =LEN(N1)
6. TRIM
   • Syntax: =TRIM(text)
   • Example: =TRIM(O1)
7. REPLACE
   • Syntax: =REPLACE(old_text, start_num, num_chars, new_text)
   • Example: =REPLACE(P1, 1, 3, "New")
8. SUBSTITUTE
   • Syntax: =SUBSTITUTE(text, old_text, new_text, (instance_num))
   • Example: =SUBSTITUTE(Q1, "old", "new")
9. TEXT
   • Syntax: =TEXT(value, format_text)
   • Example: =TEXT(R1, "mm/dd/yyyy")

Date and time functions

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

Information functions

1. IS NUMBER
   • Syntax: =ISNUMBER(value)
   • Example: =ISNUMBER(V1)
2. IT IS BLANK
   • Syntax: =ISBLANK(value)
   • Example: =ISBLANK(W1)
3. YES ERROR
   • Syntax: =IFERROR(value, value_if_error)
   • Example: =IFERROR(X1/Y1, "Error")

Random number functions

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

Conclusion

The foundation of effective data management and analysis is Excel formulas. These thirty essential formulas will give you a solid foundation to work from as you use Excel for a variety of tasks, from simple calculations to complex 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 formulas.

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