Microsoft Excel is one of the major application that using for multiple purposes like Spreadsheet, Data Entry, Data Analysis and some people using it like their own database application that do all the business transactions with it. In this post, I try to give all essential excel functions that make your life easier than before.

## 1. SUM

SUM is one of the frequently used function that everybody knows its purposes. Anyway we can use **SUM** in a multiple methods.

A | B | C | |
---|---|---|---|

1 | 10 | 20 | 30 |

1 2 | =SUM(A1+B1+C1) =SUM(A1:C1) |

The both methods gives the same answer as ’60’.

## 2. AVERAGE

**Average** do the function as what it means. It calculates ‘Average’ Value in the series.

1 2 | =AVERAGE(A1, B1, C1) =AVERAGE(A1:C1) |

The both methods gives the same answer as ’20’.

## 3. IF

**IF** gives to check the data with some condition.

*For Example:*

We need to verify a people list with 18 years old:

A | B | C | |
---|---|---|---|

1 | Karthik | 29 | Eligible |

2 | Aarthy | 16 | Not Eligible |

1 | Kavitha | 19 | Eligible |

1 | =IF(B1>=18,"Eligible", "Not Eligible") |

## 4. CONCATENATE

Concatenate adds given strings into one simple string.

*For Example:*

We need to add people’s first name & last name into full name:

A | B | C | |
---|---|---|---|

1 | Karthik | Mani | KarthikMani CONCATENATE(a1, b1) |

**Note:** `concatenate`

function simply add strings as a dynamic value only. If you need to static values, use these steps.

- Copy the Text where we did concatenate
- Paste Special -> Paste as Values

## 5. ABS

**ABS** returns the absolute value of given number without any signs.

1 | =abs(-50) |

*Output*

1 | 50 |

## 6. ADDRESS

Create **Cell Reference** as text for selected row & column numbers.

1 2 3 4 5 6 | Row_num: A1 [row number used for reference] Column_num: B1 [column number used for reference] [for example 2 for column B] Abs_num: specify the reference style A1: is a logical value for reference style Sheet_text: "Reference Text" |

**Abs_Num Reference Styles**

- 1-Absolute
- 2-Absolute Row/Relative Column
- 3-Relative Row/Absolute Column
- 4-Relative

1 | =ADDRESS(1,1) |

1 2 | OUTPUT: $A$1 |

## 7. CEILING

Returns the given number rounded up, away from zero

1 2 3 | =CEILING(2.5, 1) => 3 =CEILING(-4.5, -2) =>-4 =CEILING(0.234, 0.01) =>0.24 |

## 8. CLEAN

Clean removes all non-printable characters from given text.

1 2 | =CHAR(7)&"text"&CHAR(7) ->Non Printable Character Text =CLEAN(A1) -> Clear the Text |

## 9. COMBIN

Returns the number of combinations for given numbers.

1 2 | =COMBIN(number, number_chosen) =>syntax =COMBIN(6, 2) => 15 |

## 10. CONVERT

Convert is a very useful function that converts a number from one measurement system to another.

i.e. For Example, if we want to convert a value from Horsepower(100) to Watt.

1 | =CONVERT(100, "HP", "W") |

## 11. COUNT

Count function may used in two ways to count the selected cells.

### Method #1

Simply count the number of cells that selected.

1 | =COUNT(A2:A8) |

### Method #2

Count the number of cells that that contain value of particular number.

1 | =COUNT(A2:A8, 5) |

## 12. DAYS360

Retuns the number of days between the two dates

1 | =DAYS360(A2,A3) |

## 13. DB

Returns the depreciation value of an asset for a specified period using **Fixed Declining Balance Method**

i.e. For example if we want to calculate the asset like below:

**Cost**: 100000

**Period**: 5

**Life**: 10

**Salvage**: 24000

**Month**: 0

1 2 | =DB(cost,salvage,life,period,month) =7514.98 |

## 14. DSUM

**D-Series excel functions** are very useful and particularly this DSUM function

See this scenario

Products | Year | Month | Profit |
---|---|---|---|

=”=Web Design” | |||

Products | Year | Month | Profit |

Web Design | 2016 | 5 | 2000 |

SEO | 2016 | 6 | 1000 |

Logo Design | 2016 | 7 | 2500 |

Web Design | 2016 | 8 | 2500 |

Web Design | 2016 | 9 | 1200 |

1 2 | =DSUM(A4:D10,"Profit",A1:A2) =5700 |

You can also use D-series functions for more research – **DCOUNT**, **DPRODUCT**, **DAVERAGE**, **DMAX**.

## 15. EXACT

**EXACT Excel Function**Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive but ignores formatting differences.

1 | =EXACT(text1,text2) |

## 16. FIND

**FIND** function returns the position of one text string within given text string.

1 | =FIND(find_text,within_text,start_num) |

## 17. FORECAST

Calculates, or predicts, a future value by using existing values. The predicted value is a `y-value`

for a given `x-value`

. The known values are existing x-values and y-values, and the new value is predicted by using linear regression. You can use this function to predict future sales, inventory requirements, or consumer trends.

1 | =FORECAST(x, y series, x series) |

## 24. MAX & MIN

Returns Maximum, Minimum value of given series

1 2 | =MAX(A1:A5) =MIN(A1:A5) |

## 18. RAND

Returns Random real number between the given two numbers.

1 | =RAND()*100 |

In the above example the function will produce random numbers but less than 100.

## 19. RANK

Returns the rank of a number in the given series. The rank of a number is its size relative to other values in the series. If you were to sort the list, the rank of the number would be its position.

1 | =RANK(number,reference,order) |

## 20. REPLACE

REPLACE replaces part of a text string, based on the number of characters you specify, with a different text string.

1 2 | =2009 =REPLACE(A3,3,2,"10") |

## 21. SUBTOTAL

Returns a subtotal in a list or database. It is generally easier to create a list with subtotals by using the Subtotal command in the Outline group on the Data tab. Once the subtotal list is created, you can modify it by editing the SUBTOTAL function.

1 | =SUBTOTAL(function_num, ref1, ref2,...) |

Function_num (includes hidden values) | Function_num (ignores hidden values) | Function |
---|---|---|

1 | 101 | AVERAGE |

2 | 102 | COUNT |

3 | 103 | COUNTA |

4 | 104 | MAX |

5 | 105 | MIN |

6 | 106 | PRODUCT |

7 | 107 | STDEV |

8 | 108 | STDEVP |

9 | 109 | SUM |

10 | 110 | VAR |

11 | 111 | VARP |

1 | =SUBTOTAL(9,A2:A5) |

## 22. VLOOKUP

Searches for a value in the first column of a table array and returns a value in the same row from another column in the table array.

The V in VLOOKUP stands for vertical. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data that you want to find.

1 | =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) |

## 23. HLOOKUP

Searches for a value in the top row of a table or an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) of values, and then returns a value in the same column from a row you specify in the table or array. Use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows. Use VLOOKUP when your comparison values are located in a column to the left of the data you want to find.

The H in HLOOKUP stands for **Horizontal**.

1 | HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) |

## 24.Code

Returns a numeric code for the first character in a text string. The returned code corresponds to the character set used by your computer.

1 2 | =CODE("A") =65 |

## 25.PV

Returns the present value of an investment. The present value is the total amount that a series of future payments is worth now. For example, when you borrow money, the loan amount is the present value to the lender.

1 | =PV(rate,nper,pmt,fv,type) |