25+Useful Excel Functions

25+Useful 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.

A B C
1 10 20 30

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:

A B C
1 Karthik 29 Eligible
2 Aarthy 16 Not Eligible
1 Kavitha 19 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.

  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

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

 

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
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

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!

Comments are closed.