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.
SUM is one of the frequently used function that everybody knows its purposes. Anyway we can use SUM in a multiple methods.
The both methods gives the same answer as ’60’.
Average do the function as what it means. It calculates ‘Average’ Value in the series.
=AVERAGE(A1, B1, C1)
The both methods gives the same answer as ’20’.
IF gives to check the data with some condition.
We need to verify a people list with 18 years old:
=IF(B1>=18,"Eligible", "Not Eligible")
Concatenate adds given strings into one simple string.
We need to add people’s first name & last name into full name:
|1||Karthik||Mani||KarthikMani CONCATENATE(a1, b1)|
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
ABS returns the absolute value of given number without any signs.
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
- 2-Absolute Row/Relative Column
- 3-Relative Row/Absolute Column
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
Clean removes all non-printable characters from given text.
=CHAR(7)&"text"&CHAR(7) ->Non Printable Character Text
=CLEAN(A1) -> Clear the Text
Returns the number of combinations for given numbers.
=COMBIN(number, number_chosen) =>syntax
=COMBIN(6, 2) => 15
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")
Count function may used in two ways to count the selected cells.
Simply count the number of cells that selected.
Count the number of cells that that contain value of particular number.
Retuns the number of days between the two dates
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:
D-Series excel functions are very useful and particularly this DSUM function
See this scenario
You can also use D-series functions for more research – DCOUNT, DPRODUCT, DAVERAGE, DMAX.
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.
FIND function returns the position of one text string within given text string.
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
Returns Random real number between the given two numbers.
In the above example the function will produce random numbers but less than 100.
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.
REPLACE replaces part of a text string, based on the number of characters you specify, with a different text string.
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,...)
(includes hidden values)
(ignores hidden values)
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.
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.
Returns a numeric code for the first character in a text string. The returned code corresponds to the character set used by your computer.
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.