Microsoft Excel is a powerful tool that is widely used in various industries. It is an essential tool for anyone who deals with data, whether it is for personal or professional use. One of the most important features of Excel is the ability to use formulas to automate calculations. In this article, we will discuss the complete formula guide for MS Excel.
What are Formulas in MS Excel?
Formulas in Excel are a set of instructions that are used to perform calculations on data. These formulas can be used to add, subtract, multiply, divide, and perform other mathematical operations on data. They can also be used to manipulate text, dates, and other types of information.
Basic Formulas in MS Excel
The following are some of the basic formulas in MS Excel:
Addition Formula
The addition formula in Excel is used to add two or more numbers together. The formula is as follows: =SUM(number1, number2, …)
Subtraction Formula
The subtraction formula in Excel is used to subtract one number from another. The formula is as follows: =number1 – number2
Multiplication Formula
The multiplication formula in Excel is used to multiply two or more numbers together. The formula is as follows: =number1 * number2 *
Division Formula
The division formula in Excel is used to divide one number by another. The formula is as follows: =number1 / number2
Advanced Formulas in MS Excel
MS Excel also has advanced formulas that can be used to perform complex calculations. The following are some of the advanced formulas:
VLOOKUP Formula
The VLOOKUP formula in Excel is used to search for a specific value in a table and return a corresponding value from another column in the same table. The formula is as follows: =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
IF Formula
The IF formula in Excel is used to perform a logical test and return one value if the test is true and another value if the test is false. The formula is as follows: =IF(logical_test, value_if_true, value_if_false)
SUMIF Formula
The SUMIF formula in Excel is used to add the values in a range that meet a specific criteria. The formula is as follows: =SUMIF(range, criteria, sum_range)
COUNTIF Formula
The COUNTIF formula in Excel is used to count the number of cells in a range that meet a specific criteria. The formula is as follows: =COUNTIF(range, criteria)
Array Formulas in MS Excel
Array formulas in Excel are used to perform calculations on multiple cells at the same time. They are entered into a single cell and then applied to a range of cells. The following are some examples of array formulas:
SUMPRODUCT Formula
The SUMPRODUCT formula in Excel is used to multiply corresponding values in two or more arrays and then add the results. The formula is as follows: =SUMPRODUCT(array1, array2, …)
TRANSPOSE Formula
The TRANSPOSE formula in Excel is used to transpose rows into columns and columns into rows. The formula is as follows: =TRANSPOSE(array)
INDEX Formula
The INDEX formula in Excel is used to return the value of a cell in a specific location within an array. The formula is as follows: =INDEX(array, row_num, column_num)
Conclusion
MS Excel is a powerful tool that can be used to perform various calculations on data. By using formulas, you can automate these calculations and save time. The formulas discussed in this article are just a few examples of what you can do with Excel. With practice, you can become proficient in using Excel and its formulas to perform complex calculations.
