Excel Formulas | 20 Excel Formula Every Person Should KnowAnmar Merie
in Microsoft Excel is the most thing that will boost your productivity through work, and daily life. When you learn MS Excel, it will become an essential part of your daily life routine.
In the Formulas Bar (Shown in Figure) enter any of the following formulas:
1. Sum(B4:B13) : It gives the sum for cells range B4 to B13
2. Count(B4:B13): It Counts the numbers in a range (ignores blank/empty cells).
3. Counta(B4:B13): It Counts all characters (Numbers and Text Cells) in a range (also ignores blank/empty cells).
4. Today(): It Inserts today’s date.
5. Average(B4:B13): It divide the sum of the range by the number of its cells.
6. Max(B4:B13): It returns the highest value in the list.
7. Min(B4:B13): It returns the lowest value in the list.
8. Trim(A1): It removes the empty space from the beginning of a cell.
9. Concatenate(A1,A2,A3,”Text”): It merges the content of cells A1,A2,A3 and “Text” together
10. if(A1=”Red”,”The Color is Red”,”The Color is not Red”): It gives a conditional Value for the cell based on other cell.
11. Sumif(A4:A13, “Apple”, B4,B13): It will give you the sum of all cells in range B4 to B13, If the relevant cell in A4 to A13 equal to “Apple”.
12. Countif(A4:A13, “Apple”): It will count the cells in range A4 to A13 that equal to “Apple”
13. Vlookup(A1,A1:B13,2,false): It will look up for the value A1 in the first column of array A1 to B13, and when it finds the value, it will return the relevant cell in column 2 of the cell. (The False is to give the exact value)
14. index(A1:B13, 2,5): It will return the value in array A1 to B13, that located in row 2 and column 5.
15. match(A1,A1:B13,0): It will look for the value A1 in Array A1 to B13, and when it finds it, It will return the row number of it.
16. =A1=A2: It will return true or false if value in A1 equals A2
17. Simple Formulas like A1+A2, A1*A2 will work also
18. find(A1,B1): It will look for the text A1 in Text B1, then will return the character number
19. Len(A1): It will return the number of characters in A1
20. isnumber(A1): It will return true or false whether A1 is a number or no.
In next article we will learn how to combine those formulas in different cases of Excel.