25+Useful Excel Functions

excel-functions

Sharing is caring!

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.

ABC
1102030

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.

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:

ABC
1Karthik29Eligible
2Aarthy16Not Eligible
1Kavitha19Eligible

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:

ABC
1KarthikManiKarthikMani CONCATENATE(a1, b1)

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

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

5. ABS

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

Output

6. ADDRESS

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

Abs_Num Reference Styles

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

7. CEILING

Returns the given number rounded up, away from zero

8. CLEAN

Clean removes all non-printable characters from given text.

9. COMBIN

Returns the number of combinations for given numbers.

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.

11. COUNT

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

Method #1

Simply count the number of cells that selected.

Method #2

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

12. DAYS360

Retuns the number of days between the two dates

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

14. DSUM

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

 

See this scenario

ProductsYearMonthProfit
=”=Web Design”
ProductsYearMonthProfit
Web Design201652000
SEO201661000
Logo Design201672500
Web Design201682500
Web Design201691200

 

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.

16. FIND

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

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.

24. MAX & MIN

Returns Maximum, Minimum value of given series

18. RAND

Returns Random real number between the given two numbers.

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.

20. REPLACE

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

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.

Function_num
(includes hidden values)
Function_num
(ignores hidden values)
Function
1101AVERAGE
2102COUNT
3103COUNTA
4104MAX
5105MIN
6106PRODUCT
7107STDEV
8108STDEVP
9109SUM
10110VAR
11111VARP

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.

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.

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.

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.

Sharing is caring!