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 |
=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.
=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 |
=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.
=abs(-50)
Output
50
6. ADDRESS
Create Cell Reference as text for selected row & column numbers.
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
=ADDRESS(1,1)
OUTPUT: $A$1
7. CEILING
Returns the given number rounded up, away from zero
=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.
=CHAR(7)&"text"&CHAR(7) ->Non Printable Character Text =CLEAN(A1) -> Clear the Text
9. COMBIN
Returns the number of combinations for given numbers.
=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.
=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.
=COUNT(A2:A8)
Method #2
Count the number of cells that that contain value of particular number.
=COUNT(A2:A8, 5)
12. DAYS360
Retuns the number of days between the two dates
=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
=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 |
=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 FunctionCompares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive but ignores formatting differences.
=EXACT(text1,text2)
16. FIND
FIND function returns the position of one text string within given text string.
=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.
=FORECAST(x, y series, x series)
24. MAX & MIN
Returns Maximum, Minimum value of given series
=MAX(A1:A5) =MIN(A1:A5)
18. RAND
Returns Random real number between the given two numbers.
=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.
=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.
=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.
=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 |
=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.
=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.
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.
=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.
=PV(rate,nper,pmt,fv,type)