Tag Archives: How to use excel formulas

MS Excel Formula List and How to Use these formula..Learn with Examples !!

With more than 30 million users, Ms Excel is famous and very usable software developed by Microsoft. The Code name of MS Excel is Odyssey. Excel is a software program from Microsoft that from Microsoft Office suite of productivity software. Released in September 1985, Microsoft Excel is software for creating and editing spreadsheets which are saved with .xls or .xlsx file extension.

MS Excel is basically used for calculation, pivot tables and other graphics representations. You can keep number record of any data. You can keep monthly budget, salary, expenses and other calculations in Excel. It is very useful software. Excel contains a spreadsheet that has columns and rows. The intersection of columns and rows make cells. We keep data in cells.  There is no restriction to keep only numeric data; you can add text data too. There is a set of formulas to ease your calculation in Excel. It is different from Ms Word. The formulas are very helpful to reduce your calculation work. You can do addition, subtraction, multiplication, division and many more functions within typing a few keys. You will enjoy most sorted browsing experience with MS Excel formulas. Sharp your math with Excel.

Today here we will share information about MS Excel formulas and MS excel shortcut keys:

 

All excel formulas begin with Equal to sign (=) and followed by specific text tag that will denote the formula.  We are sharing some formula here:

1. SUM

Sum means addition of numbers, we can feel comfortable while putting numbers in Excel. No matter what numbers of excel you fill in excel and now looking for total of those numbers. There is a formula there.  = SUM (number 1, number 2, number 3….). The solo aim of Sum is add numbers together. Sum function sums values supplied as arguments; these can be up to 255 arguments. Sum function ignores text values.

Example

 

You can add cell number here too. Even you can add range of cells too. Here you can see sum of cell range.

Example

 

 

2. Average 

Average function in Excel does exactly as its name shows. Average function is used to find average or arithmetic mean of numbers. This work like Sum formula. You can find average of multiple numbers.
=AVERAGE(number1, [number2], …)

Example

Let’s have an example of the formula in Excel software

 

 

3. MAX and MIN 

The MAX and MIN formulas in Excel are used to find the largest and smallest number in a set of numbers, respectively. The use of this formula is very easy.

= MAX (number1, [number2], …)

=Min (number1, [number2], …)

Example

Let’s learn with an example given below:

 

 

4. COUNT & COUNTA

If you have curiosity to find the cells in a given range contain numeric values, you can save your time by using COUNT function. This will count all instantly as you apply the formula.

=COUNT(value1, [value2], …)
In COUNTA function, we can find all cells that are not blank, even they have contained numbers, letters, errors, text, dates, times, values, logical values (True/False), text strings etc.
=COUNTA (value1, [value2], …)

Example

Let’s apply both formula, watch following example please.

 

5. LEN

You often need a perfect formula to count the number of characters in a cell. Yes, LEN is suitable for you needs. You can easily count number of characters in a cell through using it.
=LEN(text)
=LEN (cell no.)

Example

Examples in Excel sheet given below:

 

6. TRIM

TRIM is extremely useful formula in Excel. You can remove extra spaces in text with it. You can get rid of unwanted spaces in text with TRIM formula. There may be multiple methods to avoid extra spaces but you will find TRIM formula, a cool one.
=TRIM(text)

Example

 

7. IF Formula

When we face ‘IF’ anywhere, we suddenly think about logic. It is very popular formula in Excel. It is used to test a specific condition and return a value or do a calculation if condition is met, and if the condition is not met, another value or calculation.
IF(logical_test, [value_if_true], [value_if_false])

Example

For example, the following IF statement command Excel to check the value in B3 and returns “OK” if it’s less than or equal to 10, “Not OK” if it’s less than 10:

=IF(B3>=10, “OK”, “Not OK”)

 

8. AND & OR

These are two very famous logical functions in Excel. These are used to check multiple criteria. Now, you may be eager to know different between both formulas. Let’s talk main difference between both functions:

AND returns TRUE if all conditions are met, otherwise FALSE

OR returns TRUE if any of conditions is met, otherwise FALSE

These formula are not often used as they are but both are massively used as a part of large formulas.

Example

we are using here IF formula with And. We will apply it for 2 columns. It returns “Best” if both values are greater than 2

=IF(AND(A2>1, B2>1), “Best”, “”)

In text of OR formula, you are satisfied with one value greater than 1 then it returns “Best”, you should use OR formula.

=IF(OR(A2>1, B2>1), “Best”, “”)

9. Today and Now

One amazing thing, you can watch current date and time without manually update daily. This magic begins with Today and Now function in Excel. This is also a popular formula in Excel.
=TODAY()            [current date]

=NOW()            [current date and time in a cell.]

Example

=TODAY()            [current date]

=NOW()            [current date and time in a cell.]

10. CONCATENATE

This formula is used in that condition where you want to take values from two or more cells and combine them into one cell; use the CONCATENATE function:
CONCATENATE(text1, [text2], …)

Example

For example, to combine the values from cells B4 and C8, just enter the following formula in a different cell:
=CONCATENATE(B4, C8)

Advertisements