Excel is such a Spreadsheet Software, which is used by a small to large business. It is usually used to organize data and do financial analysis.
No matter what work you are using it to do, you will have to do basic math calculations. In such a situation, it becomes very important for you to have knowledge of Excel Formulas.
Using these Excel Formulas, you can do Math calculations from basic to an advanced level within a second. There is a separate formula for Add, Divide, Multiply, and Subtract Numeric Values.
So if you as a Beginner know about some Basic Excel Formulas & Functions then you can easily work in Excel.
In this article, we will learn about some of the most important Excel Formulas and their uses.
Table of Contents
Introduction To Excel Formulas
The formula is an expression in Excel. Which calculates the values entered by you in a cell or a range of cells on an Excel worksheet.
Formulas are equations that can do calculations. And can return results. Can manipulate other cells, and much more.
All formulas in Excel always begin with an equal sign (=), followed by constants which are numeric values, and calculation operators such as Plus (+), Slash (/), Asterisk (*), Minus (-) and Percentage (%) is a sign.
Practice the formulas for adding, multiplying, subtracting or dividing values on your worksheet. For example, below are some basic formulas:
=A1+A2 (Which adds the value of cell A1 to the value of cell A2.)
=A1-A2 (Decreases the value of cell A2 from the value of cell A1.)
=A1/A2 (Divides the value of cell A1 by the value of cell A2.)
=A1*A2 (Multiplies the value of cell A1 by the value of cell A2.)
What is the difference between Excel Formula and Function? The function is a predefined formula, and it is already available in Excel. It is a piece of code designed to calculate specific values used inside formulas. For example, =SUM(A1:B10), =MAX(B10:B20).
How To Use Formula Bar
You will see the Formula Bar just above the Excel Worksheet. It is a thin strip. If you enter Data or Formula in a cell, then it appears in this bar. Using this bar, you can view and edit the formula present in any cell of the worksheet.
You will be able to see the Function Symbol (fx) just before the Formula Bar. On clicking this, the dialogue box of Insert Function will open on your screen. From here you can select any Excel Function and insert it into the cell.
If the Formula Bar in your Excel is hidden for some reason, then you can show the Bar with the help of Show Group present inside the View Tab.
Important Excel Formulas & Functions
SUM
The SUM function is most commonly used in Excel. It is used to find the Sum (SUM) of two or more Numeric Values in a Spreadsheet.
Syntax: =SUM(number1, number2, …)
For example, you have to add these two numbers 20 and 30. So the formula for that would be =SUM(20,30).
If you want to find the sum total of the values present in the cells. For example, adding the value of A1 cell to B1 (see image below). You would put the Formula like this, =SUM(A1, B1).
Now if we want to find the sum total of the values of an entire row or column. For example, the formula to add values from B2 to B8 would be =SUM(B2:B8).
Subtraction Excel Formula
There are many formulas to subtract numbers in Excel. One way is to select a blank cell, put the equal sign (=), then type numbers separated by a minus sign (–). Finally press Enter, you will get the result. For example, =30-20.
You can also use Cell Reference to subtract the values. For example, you have to substrate the value of cell A2 from cell B2 in the worksheet. So for this you would apply the Formula like this, =A2-B2.You can also use SUM Function to do Subtraction in Excel. For this you have to apply this formula, =SUM(A2-B2).
Multiplication Formula
Multiplication Formula is used to multiply values in Excel. However, there are many ways to multiply in Excel. The first way is to use the asterisk (*) arithmetic operator. For example, you have to multiply the value of cell B1 by cell B2. So the Formula would be something like this, =B1*B2.
You can also use PRODUCT Function to multiply the values of these cells, =PRODUCT(B1,B2).
Division Excel Formula
Using the Division Formula in Excel, you can divide numbers, cells, and entire columns. The most common way to do this is to use a forward slash (/). Something like this, =B1/C1.
Average Formula
If you want to find the average of a set of numbers in Excel. For this use AVERAGE Function.
Syntax: AVERAGE(number1, number2, …)
For example, we have to find the average of the numbers from cell A1 to cell E1. So the formula would be something like this, =AVERAGE(A1:E1).
However, in the AVERAGE Function, you can also type Arguments like this, =AVERAGE(A1, B1, C1, D1, E1). But this method is correct to calculate the average of two or three numbers, otherwise, it will take a lot of your time.
Percentage Formula
As we know, the Percentage is a fraction of 100. Which is calculated by dividing the Numerator by the Denominator and multiplying the result by 100. If you want to calculate the percentage in Excel. So let’s know how to apply the formula.
For example, a student has secured 400 marks out of 600 in an examination. We want to calculate the percentage in Excel of the total numbers obtained by the student.
For that, we will first click on the cell on which we want the result. Then after applying the equal sign (=) we will give reference to the cell in which the numerator number is, then after applying the slash (/) we will give reference to the cell with the denominator number. Something like this, =C2/B2
When you press Enter the result will be 0.67. To convert this Decimal Value to Percentage, click on Home Tab. Under Number Group, click on Percent Sign.
Count Formula
The work of COUNT Function is to count those cells in a range of cells in which numbers are present.
Syntax: COUNT(value1, value2, …)
Suppose your worksheet has entries from cell A1 to cell A6. Now you have to see how many cells have numbers in this range. For this you would apply the COUNT Function like this, =COUNT(A1:A6).
IF Function
IF Function is used a lot in Excel. It tests a given condition and returns one value for a TRUE result and another value for a FALSE result.
Syntax: =IF(logical_test, value_if_true, value_if_false)
For example, a company has this condition. If an Employee sells more than 10,000 in a day, then return Yes for Commission, otherwise return No for Commission. For example, =IF(B2>C2,”Yes”,”NO”).
MAX Function
The MAX Function in Excel is used to find the largest number in a range of cells.
Syntax: =MAX(number1, number2, …)
For example, in our worksheet, there are numbers from cells A1 to E1. To find the Max Number in all these, the function will be something like this, =MAX(A1:E1)
MIN Function
Unlike MAX, the MIN function in Excel is used to find the smallest number in a range of cells.
Syntax: =MIN(number1, number2, …)
Now if we want to find the Min number in the numbers present from cell A1 to E1, then the function will be like this, =MIN(A1:E1)
VLOOKUP Function
VLOOKUP is a very useful function in Excel. Suppose you have a very large table in a spreadsheet. In which a vertical list of the product and its price is given.
You can use VLOOKUP to search the price of a particular product. That is to say that with the help of VLOOKUP, any particular information in the table can be searched.
Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
For example, you have a Table. In which the Product Name and their price are given in the Vertical List. Suppose you have to find out the price of a Product Named ‘E’ from this.
You will first select the cell. Now type =VLOOKUP and press Tab button. Now select the lookup value, this is the Product whose price you are looking for. Now select the table array, this is the table where your lookup value is located. Now type the Column Index Number, write the number of the column in which the lookup value is present in your table. Finally, instead of range lookup, type FALSE and press Enter. The price of Product will come in your selected cell.
For the above example VLOOKUP Function is like this, =VLOOKUP(D1,A2:B8,2,FALSE). Look in the image below.
Array Function
The Array Formula in Excel allows you to perform multiple calculations at once. By using Array Formula, we can do complex tasks very easily. For example look at the image below.
In the above image we have a list of Stationery Products. In which the Unit Price and Quantity of each product are written.
Now if we have to calculate Unit Price from Quantity and calculate Amount. So for this usually we use the Multiplication Formula. After that apply it on the rest of the Amount Cells.
But if we will use Array Formula here. So first you have to select Amount Cells, then put equal sign (=), select Unit Price Key Cells and after applying Multiplication Sign select Quantity Cells.
Finally, you have to press Shift+Ctrl+Enter together. Amount of all the products will be calculated.
In this post you learned about the most important Excel Formulas. Hope you have learned to use these formulas. If you have any question or suggestion related to the post, then please tell us by commenting below.
Also Read : The Definitive Guide To Excel SUBTOTAL Function
