Excel is a spreadsheet software which is used on a large scale world-wide. Excel helps one in preserving data in a very orderly and presentable way. Not only does it allow us to organize data but also perform a wide range of functions on our data using simple techniques. You can do a lot of mathematics and apply formulas to huge quantities of data without actually computing anything yourself. That is the power of excel and through it, you can drastically reduce the amount of time that you would otherwise spend in performing operations on data if excel wasn’t there.
By reading this article, you will be able to learn about the various essential formulas of excel. Thus, in this article, you will get an excel formulas list which you can customize by adding more formulas to suit your needs perfectly.
The ‘Sum’ function
The sum function will allow you to calculate the sum of any number of cells of a row or a column. There are various ways in which you can use this formula according to the data that you want to sum.
In case you simply want to sum two numbers you can write the sum function as: =SUM(3,3). This will give you the value 6 in the cell in which you wrote this formula. Now, in case you want to add numbers of a particular range then use the sum function as: =SUM(F5:F10). This function will add up all the cell values of column F from 5th row to the 10th row and put the answer in the associated cell.
Another way of using this function is as shown in the illustration below. In this example, you would get the sum of cells from C5 to D7 in the cell F12. Thus on pressing the enter key you would get the result 141 in the cell F12. This is the most widely used function in excel and helps you in finding the sum of lots of numbers very easily. Thus, this forms the first function of our essential excel formulas list.
The ‘Average’ function
This function helps you find the average of a range of numbers. Normally one would first sum the numbers and then divide it with the number of entities but this function cuts down your work drastically. You just need to write the average function and put in the range for which you want to get the average and with one tap of the enter key, your average will be computed.
The function can be applied to two numbers or a range just as the ‘Sum’ function is applied. For example: =AVERAGE(D5:D8) will compute the average of the values of the column D from the 5th to the 8th row.
The ‘Count’ function
This function helps you in finding out the exact number of cells in a range which have numbers in them. This function works with numerical values and counts only those cells which have a numerical value in them and ignores the other cells.
The count function can also be used on a set of values that you manually enter or on a range of values that you select from the sheet, just like the sum and the average function. For example: =COUNT(3,4,5) will give the output as 3. In case you select a range from the data in the sheet, it will ignore the cells having any non-numerical element and give the count of the ones having numbers only.
The ‘Concatenate’ function
The concatenate function will help you in adding texts from different cells and putting it into one cell. It will also help you a lot when you need to join various elements of a single component.
For example, you might be having the date of a particular event broken down in the month, date, and year format in separate columns and now you want to put the date together — concatenate function will help you in doing so. The function can be used in different ways for different data types. The general format for using this function is as: =CONCATENATE(cell address1, “ “, cell address2, “ “, cell address3….). In the cell address, you need to put the address of the entities you need to join and in between them you can put any separator enclosed in double quotes to put between the elements that you are joining.
The ‘Len’ function
A number of times we need to know the length of characters and then use it in other functions. Counting manually is a headache and so the ‘Len’ functions helps us here in finding the same easily in one go.
However, this function also counts the blank space as one character. So if you want to find the length of a cell which has “I am a cat” written in it, you will need to write: =LEN(cell address) and you will get 10 as the output. This length includes all characters present in the cell along with the spaces that separate the different words.
The ‘Trim’ function
While counting the length using the len function, we get the length which is inclusive of spaces too. To get only the count of the characters of the cell, we can use the trim function to remove all the blank spaces from a cell.
Thus, by using this function, you can get “I am a cat” as “Iamacat” and now when you will use the len function, you will get the length as 7, which includes only the actual characters in the text. The syntax of the trim function is: =TRIM(cell address).
The ‘If’ statements
There can be many types of conditions and different operations to be performed depending upon whether the condition is true or false. We might have a large amount of data and using conditional statements would help in implementing a lot of operations differently in different cases.
The syntax of this formula is as follows: IF(condition, return this if the condition is true, return this if statement is false).
Example: IF(G6>G8,“you have a good score”,“you have a bad score”). The sumif, countif and averageif functions can be used for summing, counting and taking average based on some specified condition. For example, if the function written is: =SUMIF(C1:C3,“BAD”,G3:G7), then if all the values from C1 to C3 read bad then the function will return the sum of the values from G3 to G7. These functions are a combination of logical and mathematical functions.
The above excel formula list puts forward all the important and most frequently used functions. You must always remember to put an ‘=’ sign before writing a formula and select the range of cells with care to get accurate results.
You can always customize this excel formula list to suit your needs. There are a number of other formulas in excel which might be of use to you. Examples of a few are: MAX, MIN, VLOOKUP, INDEX, ISBLANK, DATE, etc. The basic use would be suggested by the name of the function and its application will be similar to any of the above functions.
You can also open the ‘formulas’ tab in excel to know more about all the possible formulas that excel provides you. Make sure to use these formulas accurately and get the best benefit out of excel and hence, save your time drastically. For performing more advanced calculations, you can learn to combine a couple of formulas too. All the best! Happy learning!