Excel is one of the best tools to create spreadsheets with neat little tables, data analysis, amazing charts, logics, nested statements, etc. All this is possible because of the hundreds of formulas and functions that Excel has. As useful and powerful as these features and functions are, most of us barely know the basic SUM function, let alone create logics and charts. If you are a beginner Excel user, here are some of the basic, yet very useful, Excelformulas that you should know.
1. SUM
SUM is one of the most basic formulas that you should know. As you can tell from the function name itself, it adds two or more numbers and spits out the result. The good thing about this function is that you can either directly use the actual numbers or the cell references so that you can add all the numbers within the range.
For instance, let’s say you want to add the numbers in cells A1, A2, and A3 and display the result in A4. You can use the SUM formula like the below one in the A4 cell and press the Enter button.
=SUM(A1:A3)
Since we are using the cell references, the total in the A4 cell will change according to the values in A1 through A3.
2. AVERAGE
The AVERAGE function in excel gives you the average of two numbers or a range of numbers. The formula for this function is similar to SUM. All you have to do is reference the cells and the function will spit out the result. The formula looks something like the below one.
=AVERAGE(A1:A5)
3. ROUND
Using the ROUND formula you can quickly round off numbers to however many decimals you want. For instance, if you have the number 15.68594 and wanted to round it off to two decimals, then the formula will look something like this.
=ROUND(A1, 2)
As you can see, the ROUND function has two arguments: the first one is the cell reference and the second one is the number of decimal places you want. Of course, if you want to round it off to the nearest integer, then you can use a formula like this:
=ROUND(A1, 0)
Besides ROUND, there are also
=ROUNDUP() and =ROUNDDOWN() functions which do the same exact job.4. MAX and MIN
MAX and MIN are simple but very useful function which return maximum and minimum numbers from a given range. To get a maximum number in from a range, you can use a formula like the one below. As you can see from the below formula, we are using the cell references just like in the SUM function.
=MAX(A1:A10)
If you want a minimum number from the range, then you can use the below formula.
=MIN(B1:B10)
5. NOW
NOW is a rather simple function in Excel which instantly displays current time and date in a given cell. Being a simple function, it doesn’t take any arguments in the brackets. The formula looks like the one below.
=NOW()
However, you can make it display the future date and time by simply adding a number to it.
=NOW()+10
6. TRIM
TRIM is yet another basic function or formula that is used to remove any leading and trailing spaces in cells. Since the TRIM function works on per-cell basis, you cannot use a range with cell referencing. But you can use this simple function to remove any unnecessary spaces between words in a cell. The formula looks something like the following.
=TRIM(A1)
7. LEN
Using the LEN function, you can quickly count the number of characters including spaces in a cell. Just like TRIM, LEN works on a per-cell basis which simply means that you cannot use it on a range of cells. For instance, if you want to know the number of characters in the cell A1, simply enter the below function into any other empty cell to get the result.
=LEN(A1)
8. CHAR
CHAR function lets you return a specific character based on its ASCII value. It’s a useful function when you are dealing with symbols like copyright, currency, etc. The formula for this function looks like the below one, and as part of the argument you need to pass in the ALT code of the target symbol. For instance, I want to display the “copyright” symbol, so I passed “169” as my argument.
=CHAR(169)
9. VALUE
Sometimes you may find yourself with cells that have numbers in them but are considered as text string by Excel. In those cases you can use the VALUE function to quickly convert that text string to a numeric value. The formula for this function looks something like the one below.
=VALUE("500")
10. LEFT and RIGHT
LEFT and RIGHT are both simple yet very useful functions which are often used to manipulate data in the cells. The LEFT function is used to pull the left most characters in a cell and the RIGHT function is used to pull the characters from the right. These functions are useful when you want to separate area code from phone numbers, extract the last four digits from a credit card number, etc.
Just like most functions in Excel, LEFT and RIGHT functions use two arguments where the first one is the reference cell number and the second one is the number of characters you want to pull. The formula looks something like the one below.
=LEFT(A1,3)
For the RIGHT function, it looks something like the one below.
=RIGHT(A1,3)
Conclusion
Excel is more than just a simple spreadsheet application, and there are many more basic, yet very useful, functions like SUMIF, AVERAGEIF, SQRT, DAYS, CONCATENATE, etc. So, if you think I missed any of your favorite basic Excel formulas or functions, then do comment below to share them.
0 comments:
Post a Comment