1. Excel Formulas List Pdf Download
2. Excel All Formulas List Pdf 2016
3. Excel Formulas List Pdf File
4. Microsoft Excel All Formulas List With Examples Pdf
5. List Of All Excel Formulas Pdf
6. Excel All Formulas List Pdf In Hindi

Page 4 Advanced Excel Tips & Tricks Figure 3 Figure 4 Note: In this example, a new Column C was inserted prior to beginning the steps to split the cell data. The result of using the Text to Columns command is that the split data is saved into cells to the right of the original cell. Excel 2016 Formulas is fully updated to cover all of the tips, tricks, and techniques you need to maximize the power of Excel 2016 through the use of formulas. This comprehensive book explains how to create financial formulas, release the power of array formulas.

This is a complete alphabetical list of all the Functions in Excel with a description. To see the syntax and a more in-depth description, the function is a link to the Microsoft Excel site.

Download the complete list.

FUNCTIONTYPE AND DESCRIPTION

ABS function Math and trigonometry: Returns the absolute value of a number

ACCRINT function Financial: Returns the accrued interest for a security that pays periodic interest

ACCRINTM function Financial: Returns the accrued interest for a security that pays interest at maturity

ACOS function Math and trigonometry: Returns the arccosine of a number

ACOSH function Math and trigonometry: Returns the inverse hyperbolic cosine of a number

ADDRESS function Lookup and reference: Returns a reference as text to a single cell in a worksheet

AMORDEGRC function Financial: Returns the depreciation for each accounting period by using a depreciation coefficient

AMORLINC function Financial: Returns the depreciation for each accounting period

AND function Logical: Returns TRUE if all of its arguments are TRUE

AREAS function Lookup and reference: Returns the number of areas in a reference

ASC function Text: Changes full-width (double-byte) English letters or katakana within a character string to half-width (single-byte) characters

ASIN function Math and trigonometry: Returns the arcsine of a number

ASINH function Math and trigonometry: Returns the inverse hyperbolic sine of a number

ATAN function Math and trigonometry: Returns the arctangent of a number

ATAN2 function Math and trigonometry Returns the arctangent from x- and ycoordinates

ATANH function Math and trigonometry: Returns the inverse hyperbolic tangent of a number

AVEDEV function Statistical: Returns the average of the absolute deviations of data points from their mean

AVERAGE function Statistical: Returns the average of its arguments

AVERAGEA function Statistical: Returns the average of its arguments, including numbers, text, and logical values

AVERAGEIF function Statistical: Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria

AVERAGEIFS function Statistical: Returns the average (arithmetic mean) of all cells that meet multiple criteria.

BAHTTEXT function Text: Converts a number to text, using the ß (baht) currency format

Take advantage of this course called Full list of Excel functions to improve your Office skills and better understand .

This course is adapted to your level as well as all pdf courses to better enrich your knowledge.

All you need to do is download the training document, open it and start learning for free.

This tutorial has been prepared for the beginners to help them understand basic Office. After completing this tutorial you will find yourself at a moderate level of expertise in from where you can take yourself to next levels.

This tutorial is designed for students who are completely unaware of concepts but they have basic understanding on Office training.

This PDF tutorial is aimed at beginner who wants to creat their own PowerPoint presentation ,easy course with illustrative images,free training lesson under 14 pages by Dawn Rauscher.

Download free VBA tutorial course in PDF, training file in 31 chapters and 128 pages. Free unaffiliated ebook created from Stack OverFlow contributor.

Download free Microsoft Office Excel 2013 Tutorial course material, tutorial training, PDF file on 25 pages intended to beginners.

Tutorial: Creating a Database This tutorial guides you through the creation of an InterBase database application with which you can view and update a sample employee database. You will use the Delphi IDE to create the database application.

With this PDF tutorial you will learn how to build a web application with Laravel PHP Framework version 4, free training document under 58 pages.

Download PDF tutorial about AJAx and JQuery ,the basics that you should know to build an interacting web site without requiring a page reload.

I’m sure you agree with me that 'Functions can make you great in Excel'.

Well, once you master Excel basics this is the next thing you need to master.

But the question is:

How you can learn any of these functions in the easiest way?

For this, I have this comprehensive list for you. In this list, you can find Top 100 Excel Function with Examples. Every single function in this list is explained in a way that you can learn it in no time.

Here’s how this function guide can help you to master some of the most useful functions.

• First, you will learn what's the purpose of a function and its syntax.
• Next thing is arguments and how to declare an argument.
• And in the end, its usage through a simple example so that you can use it in real life.

For example, if you want to learn about IF function, you have a quick intro, syntax description, some additional information and then an example.

In the end, you have links to the relevant sources to learn further. And all the functions are structured in the same way.

In Excel, a function is a predefined formula which performs a specific calculation by using values which a user input as arguments.

Every Excel function has a specific purpose, in simple words, it calculates a specific value.

Each function has its own arguments which you need to specify to get the result value in the cell.

To understand an Excel function you need to understand its components:

• Function Name
• Parentheses
• Arguments

Every Excel function has its name, which actually helps you to identify its purpose..

..then you need to enter opening parentheses after that specify it’s arguments, and in the end..

..enter closing parentheses.

Every Excel function has its name, which actually helps you to identify its purpose, then you need to enter opening parentheses after that specify it’s arguments, and in the end, enter closing parentheses.

There are two different types of arguments:

• Required: A required argument is compulsory for a user to specify and without which a function can’t calculate its result.
• Optional: If you skip specifying these arguments it will not stop a function to calculate its result value.

In this functions guide, I’ll be using the square bracket for every optional argument to make you understand.

Quick Note: There are some functions in Excel which don’t require any arguments, in that case, all you need to do is create type opening and closing parentheses and hit enter.

The easiest way to insert a function in a cell in Excel is to type the name of the function you want to insert starting with a equals to sign.

• First of all, you need to type = and the then type SUM.
• After that, enter the opening parentheses.
• Specify the arguments (refer to a cell or you can directly enter values into the function).
• In the end, type closing parentheses and hit enter.

A Complete List of Top 100 Excel Functions with Examples which YOU should Learn this Year to 10X YOUR SKILLS in No Time

If you deal with data where you have text, then below are some of the functions which you need to learn to work efficiently.

The LEFT function can help you to get a specific number of characters from a text starting from the left side of the text (starting position of text).

You can enter the text directly into the function or refer to the cell where you have text.

Syntax

LEFT(text,num_chars)

Arguments

• text is a text or number from which you want to extract characters.
• [num_char] is the number of characters you want to extract.

In the LEFT function, there are two arguments. the first is where need specify the text and the second where you need to specify the number of character of specify.

The [num_char] argument is optional and if you skip specifying it, Excel take 1 as default and will return the first character from the text.

In the below example, we have extracted the first five digits from the text string using LEFT by specifying the number of characters to extract.

In the below example, we have used LEN and FIND along with the LEFT to create a formula which extracts the name from the cell.

It first counts total length of the text string and then finds the position of space between first and last name.

And in the end, it extracts the first name from the test string.

Notes

1. num_char can be zero or greater than zero.
2. If you skip specifying num_char, it will assume 1 as default.
3. You can’t refer to more than one cell. If you do that it will return the characters from the top right cell of the selected range.
4. It can work with numbers also in the same manner.
5. It can also work as an array formula.
6. With negative num_char, it will return an error.
7. You can also enter a text string directly into the formula.

The RIGHT function can help you to get a specific number of characters from a text starting from the right side of the text (ending position of text).

You can enter the text directly into the function or refer to the cell where you have the text.

Syntax

LEFT(text,num_chars)

Arguments

• text is a text or number from which you want to extract characters.
• [num_char] is a number of characters you want to extract.

In the below example, we have extracted 6 characters using right function.

If you know, how many characters you need to extract from the string, you can simply extract them by using a number.

Now, if you look at the below example, where we have to extract the last name from the cell, but we are not confirmed about the number characters in the last name.

So, we are using LEN and FIND to get the name.

Let me show you, how we have done this.

First of all, we have used the LEN to get the length of that entire text string, then we used the FIND to get the position number of space between first and last name.

And in the end, we have used both the figures to get the last name.

• You will get an #VALUE! error on using negative numbers in num_char.
• If you skip specifying num_char, it will assume 1 as default.
• You can’t refer to more than one cell and if you do that, it will return the characters from the top-left cell of the range.

The MID function can help you to get a specific number of characters from another text using a starting from a specific position.

You enter the main text into the function directly or you can refer to a cell where you have text.

Syntax

MID(text,start_num,num_chars)

Arguments

• text is a text or number from which you want to extract characters.
• start_char is a number for the position of character from where you want to extract characters.
• num_chars is a number of characters you want to extract from the start_char.

In the below example, I have used different values in function arguments for your better understanding.

1. From 6th character to next 6 characters extracted.
2. From 6th character to next 10 characters extracted.
3. We have used starting character in negative and it has returned an error.
4. By using 0 for the number of characters to extract & it has returned a blank.
5. With negative number for the number of characters to extract & it has returned an error.
6. Starting number is zero and it has returned an error.
7. Text string directly into the function.

• You can’t specify start_char less than 1 and if you do, it will return an error.
• If you specify num_chars as 0, it will return nothing & if you specify it as a negative number it will return an error.
• You can’t refer to more than one cell and If you do that it will return the characters from the top right cell of the range.
• If the start_char is greater than the length of the text, then it will return and blank.

The LOWER function can convert a normal text into a lower-case text where you have all the characters in small letters.

In this function, you can directly enter the text into the function, or you can simply refer to a cell where you have text.

Syntax

LOWER(text)

Arguments

• text is that text which you want converts to lower case.

In the below example, I have compared lower case, upper case, proper case, and sentence case with each other.

A lower case text has all the letters in a small case compared to others.

• Anything other than letters will remain unaffected.
• If the referred text is an error it will return an error in the result.
• You can also insert text string directly into the function by using double quotation marks.

The UPPER function can convert a normal text into a upper-case text where you have all the characters in capital letters.

In this function, you can directly enter the text into the function, or you can simply refer to a cell where you have text.

Syntax

UPPER(text)

Arguments

• text is that text which you want converts to upper case.

As I said, in an upper case text we have all the letters in capital form.

In the below example, I have used the upper function to convert name text to capital letters from the text in which characters are in different cases.

• Characters other than alphabets remain unchanged.
• If the referred text contains an error it returns an error in the result.
• You can also insert text string directly into the function by using double quotation marks.

The PROPER function can convert a normal text into a proper case text where you have first letter of a word in capital and rest all in small case.

In this function, you can directly enter the text into the function, or you can simply refer to a cell where you have text.

Syntax

PROPER(text)

Arguments

• text is that text string which you want converts to upper case.

In the below example, I have proper case has first letter in the capital case in a word and rest of the letters are in the lower case comparing to other two cases lower case and upper case.

In the below example, I have used the proper function to streamline first name and last name into the proper case.

• It can only convert text into the proper case, any value which is not text will not change.
• It capitalizes the first letter the word that follows a non-text character. =Proper(“excel-champs”) = Excel-Champs.

The REPT function can repeat a specific (by you) character a number of time.

You need to specify the character and a number to repeat that character. The best use of this function is for creating IN-CELL charts.

Syntax

REPT(text, number_times)

Arguments

• text is that text string which you want to repeat. You can either insert a text in the function directly or you can also refer to a text which contains the text.
• number_times is the number to specify the repetition of the text.

Examples

In the below example, we have used different types of text for repetition using with REPT function.

It can repeat any type of text or numbers and even symbols which you specify in function and the main use of REPT function is for creating in-cell charts.

In the below example, we have used REPT function to create an in-cell column chart. You can use REPT Function in different ways to create some good looking tiny charts.

• Maximum numbers of repetition you can do is 32767, and after that, it will return an error.
• If you skip specifying the number of repetition, the function will return blank.
• If a number of repetition is not an integer it will return an error.

The FIND function can find a string from another text string and remember that this function is a case sensitive while find the text.

If you want to non-case sensitive search then use SEARCH Function.

Syntax

FIND(find_text,within_text,[start_num])

Arguments

• find_text is that text which you want to find from another text.
• within_text is that text from which you want to locate the text.
• [start_num] is the number represent the starting position of the search.

In the below example, we have used the FIND to locate the “:”and then with the help of MID and LEN, we have extracted the name from the cell.

• It is a case sensitive function and if you want to search for a non-case-sensitive text, you can use the SEARCH function.
• You can refer to a single cell at a time.
• You can also use wildcard characters (* ? ~) in it.
• start_num should be a number 0 to max length of the text string from which you want to find your text.
• When you are searching for a whole word from another text string, it will only return its the starting point.

The SEARCH function can also search for a text string from another text string using a starting position.

It’s a non-case sensitive function, but if you want to perform a case- sensitive search that you can use FIND function.

Syntax

SEARCH(find_text,within_text,[start_num])

Argument

• find_text is that text which you want to find from another text.
• within_text is that text from which you want to locate the text. You can refer to a cell, or you can input a text in your function.
• start_num is a number to start the search from the text.

In the below example, we are searching for alphabet 'P' and we have specified start_num as 1 to start our search.

Our formula returns 1 as the position of the text.

But, if you look at the word, we also have a 'P' at 6th position.

That means search function can only return the position of the first occurrence of a text, or if you specify the start position accordingly.

• SEARCH is not a case-sensitive function and you want to search for a case-sensitive text, use FIND function.
• You can also use wildcard characters (* ? ~) with it.
• start_num should be a number 0 to max length of the text string from which you want to search your text.

The LEN function can return the number of characters from a cell. That means it counts the characters and returns in the result.

Syntax

LEN(text)

Arguments

• text is that string for which you want to count the characters.

In the below example, I have used LEN to count letters in a cell. “Hello, World” has 10 letters with a space between and I got 11 in the result.

In the below example, “22-Jan-2016” has 11 characters, but LEN function returns 5.

The reason behind it is that LEN function counts the characters in the value of a cell and not concerned with formatting.

• It will only count the characters as per the actual values in the cell.
• Any formatted character like “$” will not consider as a character.
• If you refer to an array range it will return the result in an array.
• If a cell is blank it will return 0 in the result.
• If a cell has an error value it will return that error in the result.
• You can also insert a text string directly into the function to count the length of the string.
• If you try to refer to more than one cell, It will return #VALUE!.

Dates are one of the major ingredients of data which you use every day, and if you want to analyze your data in a better way you need to know some of the Excel’s date functions.

The DATE function can create valid date according to the Excel’s date format using the day, month and, year specified by you.

Syntax

DATE(year,month,day)

Arguments

• year is the number to use as the year.
• month is the number to use as the month.
• day is the number to use as a day.

In the below example, I have used cell references to specify the year, month and day to create a date.

You can also insert arguments directly into the function to create a date as you can see in the below example.

And in the below example, I have used different types of arguments to see the result returned by the function.

• 1900 as the year for the date.
• If we mention -1 as a month it has gives us the date of November month of the last year. The point is, when you specify a year, month or day in negative number it will deduct that number from the starting month of the year you have mentioned.
• And, if you mention 0 as a month number it will give you the date of last month of the previous year.
• If you specify zero as a year number it will give you 1900 in the year as excel use dates from 1900 to 10000.
• When you mention zero as a day it will return last day of the previous month.
• And, if you mention the day in negative number it will deduct those days from last day of the previous month.

1. You can enter year from 1900 to 10000. If you enter any year below than 1900 it will that number to 1900. For example, if you enter the year as 102 it will return the year 2002.
2. It is recommended to enter a four-digit number for the year to avoid any issue.
3. Year, Month and Day can be negative or positive.
4. Excel store dates as serial numbers.

The DATEDIF function is a hidden function which doesn’t exist in Excel’s function list but you can still use it if you know it’s syntax.

Well, the best use of DATEDIF is calculate number date between two dates.

Syntax

DATEDIF(Date1,Date2,Interval)

Arguments

• Date1 is the date from where you want to start the calculation of your interval.
• Date2 is the date to which you want to calculate the interval, the end date of interval.
• Interval is the the unit of the time, which will return from the function. You can use have six specific text strings
  1. “y” for Years
  2. “m” for Months
  3. “d” for Days
  4. “ym” for Months over Years
  5. “yd” for Days over Years
  6. “md” for Days over Months

The DATEVALUE function converts a date which is stored as text into an actual which is recognizable by Excel (Excel stores a date as a serial number).

If you want to check whether a date is valid or not, just try to convert that date into a number.

Syntax

DATEVAUE(date_text)

Argument

• date_text is the date which is stored as a text and you want to convert that text into an actual date.

In the below example, I have inserted a date directly into the function by using double quotation marks. If you skip adding these quotation marks it will return a #NAME? error in the result.

In the below example, all the dates on the left side are in textual format.

• A simple textual date which I have converted into a valid date.
• A date with all three components (Year, Month, or Day) in numbers.
• If there is no year in your textual date, it will take the current year as the year.
• And, if you have month name is in alphabets and no year.
• If you don’t have the day in your textual date it will take 1 as the day number.

1. You can directly enter a date into the function by using double quotation mark.
2. When you insert a textual date in the function it will simply give you a serial number. You can convert that serial number into a date by changing the format to date or by using the shortcut key : Control + Shift + #.
3. If you use a date which has no month in it, it will not convert that date into a date serial number.

The EDATE function returns the future date or past date using exact number of month from the date you specify.

Syntax

EDATE(start_date,months)

Arguments

• start_date is the date from which you want to start calculation.
• months is the number of months to calculate future or past date.

Below I have used EDATE with different types of arguments.

1. In the first example, I have used 5 as numbers of months and it has added exactly 5 months in 1-Jan-2016 and returned 01-June-2016.
2. In the second example, I have used -1 months and it has given 31-Dec-2016, a date which is exactly 1 month back from 31-jan-2016.
3. In the third example, I have inserted a date directly into the function.

• Use a positive number in “months” to get a future date and a negative number for a past date.

The DAY function returns the day number from the month of the date supplied ranging from 1 to 31.

When you specify a date it ignores the month and year and return the day number.

Syntax

DAY(serial_number)

Arguments

• serial_number is a valid serial number of date from which you want to extract day number.

In the below example, I have used DAY function to simply get day from a date.

And in the below example, we have used DAY with TODAY to create a dynamic formula which returns current day number and it will update every time you open your worksheet or when you recalculate your worksheet.

1. It will only return day number from a date with year ranging from 1900 to 10000.
2. You can also insert a date directly into the function by using quotation marks.

The DAYS function returns the difference between two dates using a start and a end date.

Syntax

DAYS(end_date,start_date)

Arguments

• start_date is a valid date from where you want to start you days calculation.
• end_date is a valid date from where you want to end days calculation.

In the below example, I have referred the cell A1 as the start date and B1 as the end date and I have 9 days in the result.

You can also use subtract operator to get the difference between two dates.

In the below example, I have directly inserted two dates into the function to get the difference between them.

Days function works with other date and time functions to create some dynamic formulas.

• Both start date and end date should be a valid date.
• And, both should be within the range of valid dates. Excel accept dates which are from 1900 to 10000 year.
• You can also enter a date directly into the function by using double quotation marks.

The TODAY function returns the current date according to your system’s date.

As you know Excel stores a date as a serial number and TODAY returns the serial number of the current date.

Syntax

TODAY()

Argument

• In TODAY function there is not argument all you need to do is enter it in the cell and hit enter but make sure as TODAY is volatile function which updates it’s value every time you update your worksheet calculations.

In the below example, I have used today function with other functions to get current month number, current year and current day.

You can use this function with other function to create some dynamic formulas.

The best example is when you need the current date in your formula, you can use today and you don’t have to enter date every time.

1. It is a volatile function which will recalculate every time a calculation in performed.
2. Today function returns a valid date serial number as per excel’s date system & you can use this date further with other calculations.
3. When you enter today function in a cell where you have “General” number format, excel will convert that format into date format once you enter your formula.
4. To enter a static date in a cell you can use the shortcut key Control + ;.

The MONTH function returns the month number from the date your specify (a valid date) ranging from 1 to 12.

It ignores the day and year from the date and returns the number of the month.

Syntax

MONTH(serial_number)

Arguments

• serial_number is a valid date from which you want to get the month number.

In the below example, we have referred the cell A1 as the start date and B1 as the end date and we have 9 days in the result.

It will always give the number of current month whenever date changes.

In the below example, we have used month function along with CHOOSE and TODAY.

This dynamic formula will return the name of the month instead of month number.

1. Date from which you want to get the month number should be a valid date.
2. If you mention an invalid date it will return #VALUE! error.
3. If you skip entering any value in serial_number it will return 0.

The EOMONTH function returns the last date of a future month or a past month according to the value you have specified.

You can go number of months in future and in past.

Syntax

EOMONTH(start_date,months)

Arguments

• start_date is a valid date from where you want to start your calculation.
• months is the number of months you want to calculate before and after the start date.

Here in the below examples I have used this EOMONTH with different types of arguments.

Here in the below examples I have used this EOMONTH with different types of arguments.

• I have mentioned 01-Jan-2016 as the start date and 5 months for getting a future date. As June is exactly 5 months after January, it has returned 30-Jun-2016 in the result.
• As I have already mentioned, EOMMONTH is smart enough to evaluate the total number of days in a month.
• If you mention a negative number, it simply returns a past date which is the number of months back you have mentioned.
• In the fourth example, I have used a date which is in text format and it has returned the date without returning any error.

• If the start date which you have provided is not a valid date it returns #NUM!.
• After adding or subtracting months from the start date, if that date is invalid EOMONTH Function will return #NUM!.
• This function is smart enough to check the total number days while giving last day of a month. It doesn’t matter if there is 30 days, 31 days or 29 days in a month.

The EOMONTH function returns the last date of a future month or a past month according to the value you have specified.

You can go number of months in future and in past.

Syntax

EOMONTH(start_date,months)

Arguments

• start_date is a valid date from where you want to start your calculation.
• months is the number of months you want to calculate before and after the start date.

In the below example, I have used year function to get year number from the dates. You can use this function where you have dates in your data and you only need year number.

In the below example, I have used today function to get year number from the current date.

It will always update the year whenever you recalculate your worksheet.

1. Excel store dates as serial numbers.
2. It will only return year number if reference date is valid.
3. You can only extract year number from a date which is after 01-01-1900.
4. It always returns year in four digits.

The YEAR function returns the month number from the date you specify (a valid date) ranging from 1 to 12.

It ignores the day and month from the date and returns year.

Syntax

YEAR(serial_number)

Arguments

• serial_number is a valid date from which you want to get the year.

In the below example, I have used weekday function with TODAY to get dynamic weekday.

It will give you the weekday whenever current date changes.

You can use this method in your dashboards to trigger some values when needs to change when weekday change.

In the below example, I have used weekday with IF to create a formula which first checks weekday of a date and return “Weekday” or “Weekend” basis on the value return from weekday function.

• You can also use a date which is in textual form, a result of another function, or you can enter a date directly into the function by using double quotation marks.
• If you skip specifying return type it will take 1 as default.

The WEEKNUM function can return the number of the week. In a year, there can be 52 or 53 weeks and this function returns the week number of a date’s week using that range.

Syntax

WEEKNUM(serial_number,return_type)

Arguments

• serial_number is a date for which you want to get the week number.
• [return_type] is a number to specify the starting day of the first week of the year. You have two systems to specify starting date of the week.

In the below example, I have used TODAY with weeknum to get the week number of the current date.

It will update the week number automatically every time when the date is changed.

In the below example, I have added text “Week-” with the week number for a meaningful result.

• Excel stores the dates in serial numbers so that you can only use date which has the valid serial numbers.
• If the serial number of date is out of range for the current date base value, a #NUM! is returned.
• If the return type is out of the range specified in the table above, a #NUM! is returned.

The NETWORKDAY function returns the working days between two dates (start date and end date).

This different between dates will be excluding weekends and holidays.

Syntax

NETWORKDAYS(start_date,end_date,holidays)

Arguments

• start_date is a valid date from where you want to start your calculation.
• end_date is a valid date up to which you want to calculate working days.
• [holidays] is a valid date represents holiday between the start date and end date. You can refer to a cell, range of cell or an array containing dates.

In the below example, I have specified 10-jan-2015 as a start date and 20-Feb-2015 as an end date.

We have total 41 days between these two dates, out of which 11 days are weekends.

After deducting those 11 days it has returned 30 working days and I haven’t mentioned any holiday in this example.

Now, in below example with the same start and end dates, I have specified a holiday and, after deducting 11 days of weekend and 1 holiday it has returned 29 working days.

Again with the same start and end dates, I have used a range of three cells for holidays to deduct from the calculation and, after deducting 11 weekend days and 3 holidays which I have mentioned It has returned 27 working days.

• This function is a part of Analysis Tool Pack. If you find that it is not working properly you can reload your Analysis Tool Pack.
• It will not include Saturday and Sunday by default.
• You can enter a date using following methods.
• Referring to a cell which contains the date value.
• Inserting a date directly into the function by using double quotation marks.
• Inserting a serial number which represents a date as per excel’s date system.
• If any of the argument you have specified is not a valid date, it will return #VALUE!.
• If you specify a holiday which is a weekend day as well, that will be concluded only once.

The NETWORKDAYS.INTL function returns the difference between two days (using a start date and an end date) excluding weekends and holidays.

Syntax

NETWORKDAYS.INTL(start_date,end_date,weekend,holidays)

Arguments

• start_date is a valid date from where you want to start your calculation.
• end_date is a valid date up to which you want to calculate working days.
• [weekend] is a number represent to exclude weekends from the calculation.
• [holidays] is a list of dates which represents the holidays you want to exclude from the calculation.

Here I have some examples which will help you to understand this function in a better way.

In below example, I have used 01-Jan-2015 as a start date and 20-Jan-2015 as an end date.

I have specified 1 to take Sunday – Saturday as a weekend.

The function has returned 14 days after excluding 6 weekend days.

Below, I have used same dates. And, I have used 11 in for weekend days which means it will only consider Sunday as a weekend.

Along with that, I have also used 10-Jan-2015 as a holiday.

Now we have total 3 Sundays between both dates and a holiday. After excluding all these days the function has returned 16 days in the result.

In below example, I have used range to specify holidays. If you have more than one date for holidays you can refer to an entire range.

Quick Tip: If you want to create a dynamic range for holidays, you can use a table for that.

If you want to choose custom days to count as working days ora weekend, you can use below format in the weekend argument.

Here, 0 represents a working day and 1 represents a non-working day. And, total seven numbers represent 7 days of a week.

You can use 0 for the days want to take as a working day and 1 for weekends. Here I have specified Monday and Thursday as working days.

• If a start date is greater than ending date, it will return days in negative.
• If start date or end date is a date which is out of the range, it will return a #NUM!.
• You can enter a date using following methods.
• Referring to a cell which contains the date value.
• Inserting a date directly into the function by using double quotation marks.
• Inserting a serial number which represents a date as per Excel’s date system.

Below are the some of the important Excel functions which you can use to deal with time values.

The TIME function returns a valid time (as per Excel’s time format, as Excel stores time as decimals) by specifying hour, minute and seconds.

Syntax

TIME(hour,minute,second)

Arguments

• hour is a number to use as an hour value.
• minute is a number to use as a minute value.
• second is number to use as a second value.

In the below example, I have used different arguments for your better understanding.

• Time return by this function is in 12-hour format.
• If you use zero for the hour value it will give you time from starting hour of a day.
• When you specify a negative number it will deduct that value from the previous hour, minute or second.
• Time function returns a decimal number which represents a particular time in Excel.

• Excel stores date and time in a serial number. In a valid date, serial number integer represents date and decimal part represents time.
• You can also directly enter arguments into the function.
• It will return the same result if you use double quotation marks or not.
• When initially create a time value with this function it will give you time in 12-hour format using AM/PM with it but you can change it by changing time format.

The HOUR function returns the hour value from a time value. It returns a number ranging from 0 to 24 which represent the hour from the time specified.

Argument

HOUR(serial_number)

Arguments

• serial_number is a valid time from which you want to get the hour value.

1. Use Cell Reference

In the below example, I have referred to a cell in which I have a valid time value. The function has only extracted hour in 24-Hour format from that time value.

You can use this method where you have time data & you want to extract hour from that values.

2. Use Date as a Reference

In below example, I have used date which has a time value in it & HOUR Function has extracted the hour as an integer from it.

If you convert that date into an integer by changing its format, you’ll get to know that it has time value as decimals.

1. You can also input time as a text directly into the function using. For example HOUR(“10:45 PM”).
2. If refer to a blank cell it will return 0 in the result.
3. Excel stores date as a number, time represents in the decimals in that number.
4. HOUR Function always returns an hour in 24-hour format.

The TIMEVALUE function converts a time value which is stored as a text into a valid time value (Excel stored time as a decimal).

Syntax

TIMEVALUE(time_text)

Argument

• time_text is the text which is a time value but stored as a text.

If you use a textual time value which has no am/pm in it, the function will return a serial number which represents time in 24-hour format.

You can change format of serial number to get the time

Timevalue function will convert any textual time value which is recognizable by excel in the decimal number.

As 12:00 AM is the starting time of a day, the function has return 0 for it.

It represents the time from 12:00 am to 11:59 pm.

It's smart enough to get the time from a date if that date has time in it.

While using timevalue function all you have to take care is, a date which can recognizable by Excel.

1. You can provide a text format which excel can recognize.
2. It will convert text into a decimal number which represents time.

The MINUTE function returns the minute value from a time value. It ignores the hour and seconds returns a number ranging from 0 to 60 which represents the minute value in the time.

Syntax

MINUTE(serial_number)

Arguments

• serial_number is a valid time from which you want to get the minute value.

In the below example, we have used the minute function to extract minute value from different time values.

1. You can use it to get the minute number from a time value.
2. You can also extract the minute number from a date in which you have time.
3. The minute function is only concerned with the decimal part of a serial number.

The SECOND function returns the second value from the time ignoring minutes and hours. The number is returns ranges from 0 to 60.

Syntax

SECOND(serial_number)

Arguments

• serial_number is a valid time from which you want to get the second value.

In the below example, I have used different input value to get second.

Input value should be a valid date and time as per Excel’s date nad time system.

An input value which has no value of seconds in it will return 0 as a result.

1. Excel stores date as a number. “22-Apr-2016 12:33:10 PM” will represent “42482.5230324074” as a number.
2. Integer part represents date and decimal part represent time.
   You can directly insert time value into the function.

The NOW function returns the current date and time according to your system’s time. You just need to enter in the cell where you want to insert the current date and time.

The best use of this function is to insert a TIMESTAMP.

Syntax

NOW()

Arguments

• In NOW, there is no argument to specify, but you need to take care that this function is a volatile function which changes its value every time you update your worksheet’s calculation.

• In the first example, I have used TEXT Function along with now to extract current time from it.
• In the second example, I have changed the format of the cell to “General”. The number before decimal is the current date & number after the decimal is the time.

1. NOW is a volatile function and will update every time when a recalculation is done.
2. As it is a volatile function, it can effect on the processing speed of your calculation.
3. It enters a valid serial number of current data and time as per Excel’s date and time system.
4. When you enter this function in a cell it will convert the format of the cell into a custom format (m/d/yyyy h:mm).
5. To enter a static current date & time you can use keyboard shortcut key CTRL + SHIFT + : (Current Time) & CTRL + : (Current Date) to enter current date.

In Excel, there some specific functions which can help you to look up for a value or specific information about a cell or a range of cells.

Below are some of those which you need to know.

You can use ADDRESS function to create a cell address by specifying a row and a column number.
In simple words, you need to specify a reference by providing row and column number as an argument, it returns a cell range as a text.
For example, if you give row number 2 and column number 1 it will return A2 in the result.

ADDRESS(row_num,column_num,abs_num,A1,sheet_text)

1. row_num A number to specify row number.
2. column_num A number to specify column number.
3. [abs_num] Reference type.
4. [A1] Reference style.
5. [sheet_text] A text value as a sheet name.

In the below example, we have used different arguments to get all types of result.

1. With R1C1 reference style
   • Relative reference.
   • Relative row and absolute column reference.
   • Absolute row and relative column reference.
   • Absolute reference.
2. With A1 reference style
   • Relative reference.
   • Relative row and absolute column reference.
   • Absolute row and relative column reference.
   • Absolute reference.

You can use AREAS Function to check numbers of areas in a reference.

In simple words, it returns that how many areas you have included in a reference.

In the below example, I have referred to 3 cells and the function has returned 3 in the result.

• reference A Reference to a cell or a range of cells.

In the below example, I have used areas function to get the number reference in a named range.

• If you want to refer to more than a one cell reference, you have to enclose all that references in more than one set of parentheses and use commas to separate each reference from other.
• Reference can be a cell, a range of cells or a named range.

CHOOSE function can help you to get a value from a list based on its position.

In simple words, you can call a value from a list by specifying the position of that value.

For example, if you have created a list of months (Jan to Dec) and if you mentioned position 5 it will return “May” in a result.

In above example, I have used CHOOSE function with an option button to select the type of the service to calculate the shipping cost.

1. index_num A number for specifying the position of the value in the list.
2. value1 A range of cells or an input value from which you can choose.
3. [value2] A range of cells or an input value from which you can choose.

In the below example, I am using CHOOSE Function with a drop-down list to calculate four(sum, average, max, and mix) different things.

I have used below formula to calculate the all four things with CHOOSE Function.

=CHOOSE(VLOOKUP(K2,Q1:R4,2,FALSE),SUM(O2:O9),AVERAGE(O2:O9),MAX(O2:O9),MIN(O2:O9))

I have created a small table with the name of all four calculations which I want and inserted a serial number to each in the corresponding cell.

After that, I have created a drop down list for all four calculations.

Now, to get index number in choose function from that small table I have created a lookup formula which will return serial number as per the value selected in drop down list.

And, instead of values, I have used four formulas for 4 different calculation.

Whenever you select calculation type with drop down list it will change the calculation.

• It can handle up to 254 values.
• You can refer to a cell or you can also insert values directly in the function.

You can use COLUMN function to get the column number of a reference.

In simple words, it will return a number which represents the column number of the reference.

For example, if you refer to cell A3 it will return 1 in the result as A’s column number is one.

SHEET(value)

• reference A cell reference for which you want to get the column number.

In the below example, I have used column function to the get the column number of the cell A1.

As I have already mentioned, if you skip specifying cell reference it will return the column number of the current cell.

In the below example, I have used column function create a header with serial numbers.

• You cannot refer to multiple references.
• If you refer to an array, column function will also return the column numbers in an array.
• If you refer to a range of more than one cell, it will return the column number of the leftmost cell. For example, if you refer to the range A1:C10, it will return column number of the cell A1.
• You skip to specify a reference, it will return the column number of the current cell.

You can use COLUMNS function to get the number of columns include in a reference or array.

In simple words, you can get the number of columns which are included in the reference.

For example for reference A1:D1 if will return 4, as a result, because you have 4 columns in that reference A, B, C and D.

• array An array or range get from which you want to get the number of columns.

In the below example, I have used columns function to create serial numbers in headers.

I have used fixed cell reference for the first cell in the range and drag it to the left.

In the below example, I have used columns function to get the number of columns from range A1:F1.

• You can also use named range in columns function.
• COLUMNS function is not concerned with the values in the cells, it will simply return the number of columns in a reference.

You can use FORMULATEXT function to get a formula from a cell as a text string.
In simple words, when you refer to a cell which contains a formula, it will return that formula from the cell, like which is displayed in the formula bar if you select that referred cell.

FORMULATEXT(reference)

• reference A cell reference from which you want formula as a text.

In the below example, I have used formula text with a different type of references.

When you refer to a cell which doesn’t have any formula, it will return “#N/A” error value

• You can refer to another workbook & another worksheet.
• If you refer to another workbook that workbook should be open, otherwise it will not show the formula.
• If you refer to a range more than a single cell, it will return formula from the upper left cell of the given range.
• It will return an “#N/A” error value if the cell you are using as a reference does not contain any formula, has a formula with more than 8192 characters, a cell is protected or an external workbook is not opened.
• If you refer two cells in circular reference it will return result from both.

You can use HLOOKUP to lookup a value from a top row of the data table, and if that value found, you can retrieve a value which is a number of cells below in the same column. In short, for horizontal lookup.

The alphabet H in HLOOKUP means horizontal, and V in VLOOKUP is vertical. In the below table, we have months name in the first row and product name in the first column.

Let’s say if you want to lookup for “Jun” month from the first row and want quantity value of “Product-5” then you can use HLOOKUP for that.

And, another condition is, If you want to lookup for “Product 4” and want quantity value of “Apr” month then you can use VLOOKUP for that.

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

1. lookup_value A value you want to lookup.
2. table_array A data table or an array from which you want to lookup value.
3. row_index_num A numeric value represent a number of rows below from the top row from which you want the value. For example, if you specify 2 and your lookup value is in A10 in the data table, it will return value from cell B10.
4. [range_lookup] A logical value to specify the type of lookup. If you want to perform an exact match search use FALSE and if you want to perform non-exact match use TRUE (Default).

In the below example, I have used HLOOKUP function with match function to create a dynamic formula.

And, I have used a drop-down list to change the lookup value from the cell.

I have used below formula to perform a dynamic HLOOKUP formula.

I have used zone name from cell C7 as a lookup value. Range B1: F5 as table array. And, for row_index_num I have used match function to get the row number.

Whenever I change the value in cell C9, it will return the row number from table array.

You don’t have to change your formula again and again.

Just change values with the drop-down list and you will get value for that.

• You can use wildcard characters with HLOOKUP.
• You can perform an exact match or an approximate match.
• While performing an approximate match make sure to sort data in ascending order from left to right. And, if data is not in ascending order then it would return an inaccurate result.
• If range_lookup is true or omitted, it will perform a non-exact match but return an exact match if lookup value exists in lookup range.
• If range_lookup is true or omitted, and lookup value is not in the lookup range, it will return the nearest value which is less than lookup value.
• If range_lookup is false, then there is no need to sort data range.

This is the best way to create a hyperlink in excel. Yes, the best way.

You are already familiar with HYPERLINKS in Excel, HYPERLINK function is just like that but with some additional powers.

It’s more convenient, fast, and easy to change or edit.

HYPERLINK(link_location,[friendly_name])

• Link_Location Location for which you want to add a HYPERLINK. It can be further split into two terms.
  1. link: It can be an address of a cell or range of cells in the same worksheet or in any other worksheet or may be in any other workbook. We can also link a bookmark from a word document.
  2. location: It can be a link to a hard drive, a server using UNC path, or any URL from internet or intranet. (In Excel online you can only use web address for HYPERLINK Function). You can insert a link to the function by inserting it as a text with quotation marks or by referring to a cell containing the link as a text. Make sure to use “HTTP://” before a web address.
• [friendly_name] It is an optional part of this function. It acts as a face of the connected link.
  1. You can use any type of text, number, or both.
  2. You also refer to a cell which contains the friendly_name.
  3. If you skip it, the function will use link address to display.
  4. If friendly_name returns an error, the function will display error.

In the below example, we have linked to a cell in a file which is stored on OneDrive.

You can use hyperlink function to share files which are stored on OneDrive.

In the below example, I have used hyperlink function to link to a specific range in a file which is saved on the desktop folder on my laptop and when I click on this link, it open that file and navigate to that range.

• Link a file saved on a Web Address: You can use a file which is saved on a web address. This helps us to share the file in an effective way.
• Link a file saved on a hard drive: You can also use this function while working offline. You can link a file which is stored on your hard drive and access them through your single excel sheet, no need to go to every single folder to open them.
• Link a Word Document File: This is also an awesome feature of HYPERLINK function. You can link a Word document file or a specific place in word document file using a bookmark.
• Link a file without using Friendly Name: If you want to show the actual link to the file or place to the user. In this situation, you just need to skip the friendly name declaration in the HYPERLINK Function.

It happens sometimes when you want to get a value from a list and you know the position of that value from the list. Excel INDEX Function can help you to do that.

By using INDEX Function you can fetch a value from a range of cells in which you know the exact position of the value.

In the below example, we have a month wise quantity.

And, we have used index function to get quantity for the month of Jun as in the list Jun is on 6th.

In first, you can use array form of index to simply get a value from a list using its position.

Second is reference form which is less used in real life but you can use it if you have more than one range to get value from (I’ll show further, an example to make it understand).

INDEX(reference, row_num, [column_num], [area_num])

• array A range of cells or an array constant.
• reference A range of cells or multiple ranges.
• row_number Number of the row from which you want to get the value.
• [col_number] Number of the column from which you want to get the value.
• [area_number] If you are referring to more than one range of cells (using reference syntax), specify a number to refer to a range from all those.

In the below example, I have used INDEX Function to get the quantity of Jun month. In the list, Jun is on 6th position (6th row) that’s why I have specified 6 in row_number.

And, I’m only referring to a single column that’s why I have specified 1 in column_number.

In the below example, I have used INDEX Function to get the quantity of Jun month. In the list, Jun is on 6th position (6th row) that’s why I have specified 6 in row_number.

And, I’m only referring to a single column that’s why I have specified 1 in column_number.

INDEX has returned 1904 in the result.

And, if you want to get any other value, all you have to do, just mention the position of the value in the list. Whereas, if you referring to a range with more than one column you have to specify the column number.

INDEX will return the value from the intersection of row_number and column_number.

In below example, instead of selecting all the range in one go, I have selected it as three different ranges.

In the last argument, I have specified 2 in area_number which will define the range to use from these three different ranges.

Now, in the second range, I’m referring to 5th row & 1st column. INDEX has returned the value 172 which in the 5th row in the 2nd range.

You can also refer to ranges with multiple columns.

• When both the row_num and column_num arguments are specified, it will return the value in the cell at the intersection of both.
• If you specify row_num or column_num as 0 (zero), it will return the array of values for the entire column or row, respectively.
• When row_num and column_num are out the range, it will return an error #REF!.
• If area_number is greater than the number ranges you have specified then it will return #REF!.

Quick Tip: If you use INDEX Function as a range reference [A1:Index(B1:B5,1,1)] it will return cell reference instead of value.

It allows you to create a dynamic cell reference by using text instead of actual reference and the best part is when you want to change the reference, you just have to change the text, not the actual reference.

Once you refer to a cell using the indirect function it will evaluate that reference on a real time and return the content of the cell.

The indirect function is one of those functions which are really unique in their usage and, the best part is when you need to use this function it will work like a magic.

INDIRECT(ref_text, [a1])

• ref_text A text which represents the address of a cell, an address of a range of cells, a named range, or a table name. For example, A1, B10:B20, or MyRange.
• [a1] A number or a boolean value to represent the type of cell reference you are specifying in ref_text.
  • For example, if you want to use A1 reference style use TRUE or 1 and if you want to use R1C1 reference style use FALSE or 0 for R1C reference style. And, If you omit to specify cell reference type, it will use A1 style as default.

You can also refer to another worksheet using the indirect function.

For this, you have to insert worksheet name in the indirect function.

In above example, I have used the indirect function to refer to another worksheet. I have the sheet name in cell A2 and cell reference in cell B2.

In cell C2, I have used the following formula to combine the text.

=INDIRECT(“‘”&A2&”‘!”&B2)

This combination creates a text which is used by the indirect function to refer to the cell A1 in sheet1 and the best part is, when you change the worksheet name or cell address the reference will automatically change.

Cell A1 in “Sheet1” has the value “Yes”.

So, that’s why indirect function returns the value “Yes”.

You can also refer to another workbook, in the same way, we did for another worksheet.

All you have to do, just add workbook name in your text which you are using as a reference.

In above example, I have used below formula to get the value from the cell A1 of the workbook “Book1”.

=INDIRECT(“[“&A2&”]”&B2&”!”&C2)

As I have workbook name in cell “A2”, worksheet name in cell “B2” and cell name in cell “C2”. I have combined them to use as an input text in indirect function.

Important Note: While combining cell reference as a text make sure to follow the right reference structure.

Yes, you can also refer to a named range using the indirect function.

It’s just simple.

Once you create a named range you have to enter that named range as a text in indirect function.

In above example, I have a drop down list in cell E1 which has a list of named ranges. And, in cell E2 I have used that name in the indirect function.

As range B2:B5 is named as “Quantity” and range C2:C5 is named as “Amount”. When I select quantity from drop-down indirect function instantly refer to the named range.

When I am selecting quantity from the drop-down, indirect function instantly refers to the named range “Quantity” and return the sum of that range.

And, when I am selecting the amount from the drop-down, I have the sum of cell range C2:C5.

• When you referred to another workbook, that workbook should be opened.
• If you insert a row or a column in the range which you have referred, indirect will not update that reference.
• If you want to insert text directly into the function you have to put it in double quotation marks or you can also refer to a cell which has the text you want to use as a reference.

You can use the LOOKUP function to search for the position of a value from a column or a row and then return a value from the same position from another column or row.

For example, if a value which you have specified is on 3rd position in a column then this function will return the value which on the 3rd position in the second column.

There are two types of LOOKUP function.

• Vector Form
• Array Form

Instead of using array form it’s better to use VLOOKUP or HLOOKUP and, here I have mentioned about vector form and if you want to learn about array from you can check Microsoft’s Help Section.

LOOKUP( value, lookup_range, [result_range] )

• value A value which you want to search from a column or a row.
• lookup_range A column or a row from which you want to lookup for the value.
• [result_range] A column or a row from which you want to return a value. This is an optional argument. If you skip to specify it LOOKUP function will return

Instead of using array form it’s better to use VLOOKUP or HLOOKUP.

You can use MATCH function to get the position of a cell from a range or an array.

In simple words, you can get the position of a lookup value from a list of values.

MATCH (lookup_value, lookup_array, [match_type])

• lookup_value A value whose position you want to get from a list of values.
• lookup_array A range of cell or an array contain values.
• [match_type] A number (-1, 0 & 1) to specify how excel look for the value from the list of values.

In the below example, I have used 1 as match type and we are looking for value 5. As I have already mentioned if you use 1 in match type it will return the largest value which is equal or smaller than lookup value.

Now, in the entire list, there are 3 values which are smaller than 5 and 4 is the highest in them. And, the result is 3 which is the position of value 4.

• You can use wildcard characters with match function.
• If there is no matching value in the list if will return #N/A. You can use IFNA Function and IFERROR
• Function to show a meaningful message.
• Match function is non-case sensitive.
• You can use match_type as per below information:
  1. If you use 1, it will return the largest value which is equal or less than the lookup value. The values in the list must be sorted in ascending order.
  2. If you use -1, it will return the smallest value which is equal or greater than the lookup value.
  3. The values in the list must be sorted in ascending order.
  4. If you use 0, it will return the exact match from the list.

You can use the OFFSET function to create a reference to a range which is a specific number of rows and columns away from a cell or range of cells.

OFFSET(reference, rows, cols, [height], [width])

• Reference is the reference from which you want to offset to start. It can be a cell or range of adjacent cells.
• Rows argument is the number of rows which tells OFFSET to move up or down from the reference. To go downward you need a positive number and for going upwards you need a negative number.
• cols argument is the number of columns tells OFFSET to move to the left or right from the reference. To go right you need a positive number and for going left you need a negative number.
• [height] is a number to specify the rows to include in the reference.
• [width] is a number to specify the columns to include n the reference.

In the below example, I have used SUM with OFFSET to create a dynamic range which sums the values from all the months for a particular product.

1. OFFSET is a 'volatile' function, it is recalculates whenever there is any change to a worksheet.
2. It displays the #REF! error value if the offset is outside the edge of the worksheet.
3. If height or width is omitted, the height and width of reference is used.

You can use ROW Function to get row number of a reference.

ROW([reference])

• reference - A cell reference or a range of cells for which you want to check the row number.

In the below example, I have used row function check the row number of the same cell where I have used the function.

In the below example, I have referred to the another cell to get the row number of that cell.

In the below example, I have referred to the another cell to get the row number of that cell.

You can use row function to create a serial number list in your worksheet.

All you have to do just enter row functions in a cell and drag it up to the cell you want to add serial numbers.

• It will include all type of sheet (Chart Sheet, Worksheet or Macro Sheet).
• You can refer to sheets even they are visible, hidden or very hidden.
• If you skip specifying any value in the function it will give you the sheet number of sheet in which you have applied the function.
• If you specify an invalid sheet name, it will return a #N/A.
• If you specify an invalid sheet reference, it will return a #REF!.

You can use ROW function to get the number of rows in an array or reference.

In simple words, it will return the number of rows included in a reference.

ROWS(array)

• array A cell reference or an array to check a number of rows.

In the below example, I have used reference A1:A10 which has 10 rows in it and ROWS function has returned 10 in the result.

You can also use rows function to create a list of serial numbers.

In the below example, I have used the starting cell of the reference as an absolute reference.

1. You can also use named range in rows function.
2. Rows function is not concerned with the values in the cells, it will simply return the number of rows in a reference.

You can use TRANSPOSE function to change the orientation of a range.

In simple words, by using this function you can change the data from a row into a column and from a column into a row.

TRANSPOSE (array)

• array An array or a range you want to transpose.

Please follow below steps to understand how it works:

• In the below example, I want to transpose data from range B2:D4 to range G2 to I4.
  
• For this, first I need to go to the cell G2 and select cell range up to I4.
  
• Next is to enter (=TRANSPOSE(B2:D4)) in cell G2 and press Ctrl+Shift+Enter.
  
• TRANSPOSE will convert the data from the rows into columns, and the formula which I applied is an array formula, you cannot change a single cell from it.
  

• You have to apply TRANSPOSE Function as an array formula using the same number of cells as you have in your source range by pressing Ctrl + Shift + Enter.
• If you select cells less than source range, it will transpose data only for those cells. For example, In source range, you have 8 cells and in destination range, you have selected 6 cells then it will only transpose data from 6 cells.
• You can also use paste special option to transpose data.

You can use VLOOKUP when you want to look up for a value in a column, and once you find that value in the column you can able to get the value from the right of the value that you have found.

In the above table, if you want to find 5 from column 1.

You can do that by using VLOOKUP & once you find it you can able to get the values from the same row in the right direction.

In above example, you can get 13 and 21 if you looking for the value 5.

In simple words, VLOOKUP can find a value from the vertical direction (Column) and once you find the value you can get values from the horizontal direction(Row).

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

• lookup_value A value which you want to search in a column. You can refer to a cell that has the lookup value or you can directly enter that value into the function.
• table_array A range of cells, a named range from which you want to look up f the value.
• col_index_num A number represent the column number from which you want to retrieve the value. In above example, If you want to retrieve 13, column index number will be 2 & if you want 21 then column index number will be 3.
• range_lookup Use false or 0 to make an exact match and true or 1 for an appropriate match. The default is True.

In the above example, I have used “Leg Guard” as a lookup value from the “Products” column.

As I need value from the “Quantity” column I have used 3 as a column index because “Quantity” column is the 3rd column starting from the “Products” column.

And in the end, I have used 0 in range lookup to get an exact match.

Here I have explained you about the most common usage of VLOOKUP which you can easily understand and apply in your work.

In below example, I have a list of students with marks they have scored, and in the remarks column, I want to a grade according to their marks.

In above marks list, I want to add remarks as per below category range.

In this, I have two option to use. One is to create a nesting formula with IF which is a little bit time-consuming, and the second option is to create a formula with VLOOKUP with an appropriate match.

And, the formula will be:

=VLOOKUP(B2,$E$2:$G$5,3,TRUE)

..how it works

I am using “MIN MARKS” column to match the lookup value and I am getting value in return from “Remarks” column.

I have already mentioned that when you use TRUE and there is no exact match of lookup value then it will return the next smallest value from the lookup value.

For example, when I am looking for the value 77 from the category table, 65 is the next smallest value after 77.

That is why I got “Good” in remarks.

One of the most common problems which come when you are using VLOOKUP is that you’ll get #N/A whenever there is not match is found by it.

But the solution to this problem is simple and easy.

Let me show with an easy example.

In below example, I have a list of names & their age.

In cell E6, I am using VLOOKUP function to look up for a name from the list.

Whenever I type a name which is not on the list I am getting #N/A.

But what I want here is to show a meaningful message instead of the error.

The formula will be:

=IFNA(VLOOKUP(D6,Sheet3!$A$1:$B$14,2,0),”Not Found”)

..how it works

IFNA can test a value for #N/A and if there is an error you can specify a value instead of the error.

• If VLOOKUP can not find the value you are looking for, it will return an #N/A.
• VLOOKUP only able to give you the value which is on the right side of the lookup value. If you want to look up on the right side, you can use INDEX & MATCH Functions for that.
• If you are using exact match then it will only match to the value which is first in the column. In below example, if you are looking for the amount of product-1 then it will return 1632, not 1843 because 1632 is upper on the list than 1843.
• You can also use Wildcard Characters with VLOOKUP.
• You can use TRUE or 1 if you want an appropriate match & FALSE or 0 for an exact match.
• If you are using appropriate match (True):
  • It will return the next smallest value from the for the list if there is not exact match.
  • If the value which you are looking for is smallest than the smallest value in the list, VLOOKUP will return #N/A. For example, If you are looking for value 4 and the smallest value in the column is 5 then it will return #N/A.
  • If there is exact value exists which you are looking for, it will give you that exact value.
  • Make sure you have sorted the list in ascending order.

Logical functions can help you create some of the most helpful formula in your spreadsheet.

For example, you can create conditional to test a value, evaluate errors, etc. Below are some of the logical functions you need to know.

You can use IF function to test a condition, if that condition is TRUE then it will return a value which you have specified and if that condition is FALSE it will return another value.

In simple words, you can test a condition and function will a return result with you have specified with TRUE and FALSE respectively for both of the results.

Syntax

IF(logical_test,value_if_true,value_if_false)

Arguments

• logical_test A condition which you want to evaluate.
• value_if_true Value which you want to get if that condition is TRUE.
• value_if_false Value which you want to get if that condition is FALSE.

In the below example, we have used a comparison operator to evaluate different conditions.

• I have used a specific text to get in the result if condition met or not.
• You can also use TRUE and FALSE to get in result.
• If skip to specify a value to get the result if the condition is TRUE, it will return zero.
• And, if you skip specifying a value to get the result if the condition is FALSE, it will return zero.

In the below example, we have used a comparison operator to evaluate different conditions.

• I have used a specific text to get in the result if condition met or not.
• You can also use TRUE and FALSE to get in result.
• If skip to specify a value to get the result if the condition is TRUE, it will return zero.
• And, if you skip specifying a value to get the result if the condition is FALSE, it will return zero.

In the below above example, I have used IF function to create a nesting formula.

I have specified a condition and if that condition is false then we have used another if function to evaluate another condition and perform a task and if that condition is FALSE we have used another IF.

This way I have used IF for five times to create a nesting formula. You can use the same for 64 times for a nesting formula.

• You can use IF for nested formulas.
• The maximum number of nested condition you can perform is 64.
• You can use comparison operators to evaluate a condition in IF.

You can use IFERROR function to test an error from a cell and replace that error with as a meaningful result.

IFERROR can be your best friend if you are in fear of errors.

It was first introduced in the Excel 2007.

Most of the times IFERROR is used with other formulas to test their errors and replace them.

Syntax

IFERROR(value, value_if_error)

Arguments

• value Refer to the value or cell from which you want to test the error.
• value_if_error Value which you wants to get in return when an error occurs in value.

In the below example, I have used the IFERROR function to replace the #DIV/0! with some meaningful text.

IFERROR Function is only compatible with 2007 and earlier versions. To deal with this problem, you can use ISERROR instead.

This way I have used IF for five times to create a nesting formula. You can use the same for 64 times for a nesting formula.

• IFERROR function is concerned with the occurrence of an error, not with the type of the error.
• If you skip specifying value or value_if_error, if will return 0 in the result.
• It can test #N/A, #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME?, and #NULL!.
• If you are evaluating an array it will return an array of results for each item specified.

You can use IFNA function you can evaluate a value for #N/A.

If an error occurs you can change it by some specific value or with a more meaningful message and if an error doesn’t occur it will return original value.

Unlike IFERROR, IFNA only evaluates a value for #N/A.

Syntax

IFNA(value, value_if_na)

Arguments

• value Value you want to test for #N/A error.
• value_if_na Value you want to return if an error occurred.

In VLOOKUP function #N/A occurs when the lookup value is not in the lookup range and for that I have specified a meaningful message uisning IFNA.

Note: IFNA is introduced in Excel 2013 so it is not available in the previous versions.

• If you skip specifying any argument, IFNA will treat it as empty string (“”).
• If a value is an array then it will return the result as an array.
• If will ignore all other errors #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME?, and #NULL!.

You can use OR function you can check multiple conditions. It will return TRUE if any of those conditions is true, else it will return FALSE.

In simple words, if a single condition is true from all those conditions which you are testing it will return TRUE, else FALSE.

Syntax

OR(logical1, [logical2], …)

Arguments

• logical1 Condition which you want to verify.
• [logical2] Additional Conditions you want to verify.

In the below example, I have created a condition using IF function that if a student score 60 above marks in one of the both subjects the formula returns TRUE.

You can also use OR function to work with numbers as well. As you know TRUE and FALSE has values 0 and 1 respectively.

In the below example, I have used a number to get logical values in a formula. You can also perform above condition in reverse order. You can use TRUE and FALSE instead of numbers.

OR function treats these logical values as numbers.

• Values will be ignored if reference cell or array contained an empty cell or text.
• The result of conditions should be in logical value (TRUE or FALSE).
• It will return an error if there is no logical value is returned.
• The maximum numbers of values you can test are 255.

You can use AND function you can check multiple conditions. It will return TRUE if ALL of those conditions are true, else it will return FALSE.

In simple words, it will only return TRUE if allthe conditions are true and even if one condition is FALSE it returns FALSE.

Syntax

AND(logical1, [logical2], …)

Arguments

• logical1 Condition which you want to verify.
• [logical2] Additional Conditions you want to verify.

In the below example, I have created a condition using IF function that if a student score 60 above marks in both of the subjects then only it will return TRUE else FALSE.

You can also use AND function to work with numbers as well. As you know TRUE and FALSE has values 0 and 1 respectively.

In the below example, I have used a number to get logical values in a formula. You can also perform above condition in reverse order. You can use TRUE and FALSE instead of numbers.

AND function treats these logical values as numbers.

• Values will be ignored if reference cell or array contained an empty cell or text.
• The result of conditions should be in logical value (TRUE or FALSE).
• It will return an error if there is no logical value is returned.
• The maximum numbers of values you can test are 255.

By using NOT function you can reverse a logical value. If any formula results in a logical value or a cell has a logical value it will reverse that value.

In simple words, it will convert TRUE to FALSE and FALSE to TRUE.

Syntax

NOT(logical)

Arguments

• logical Value you want to test to reverse the logical value.

In the below example, I have used numbers instead of logical values. As you know logical values have values in number.

You can also use NOT function with numbers as well.

In the below example, I have used NOT function with formulas. While comparing values from two cells I have reversed the logical results.

• If you refer to a blank cell it will treat that value as a TRUE.

You can use FALSE function to get a logical value FALSE. You can simply enter this function and it will return the FALSE.

Syntax

FALSE()

Arguments

• It has no arguments.

In the below example, I have used FALSE() and FALSE, in the same manner, and both returns the same value.

You can also use FALSE in the numeric calculation as it has 0 value.

• FALSE and FALSE() both are identical.
• Best use of FALSE() is in integration with other function for evaluation.
• FALSE has a value 0. You can use this value in other calculations.
• Using FALSE without parentheses will also give you the same result.
• FALSE function is mainly provided for compatibility with the spreadsheet program.

You can use TRUE function to get a logical value TRUE. You can simply enter this function and it will return the TRUE.

In the below example, I have used TRUE() and TRUE, in the same manner, and both returns the same value.
You can also use TRUE in the numeric calculation as it has 0 value.

• FALSE and FALSE() both are identical.
• Best use of FALSE() is in integration with other function for evaluation.
• FALSE has a value 0. You can use this value in other calculations.
• Using FALSE without parentheses will also give you the same result.
• FALSE function is mainly provided for compatibility with the spreadsheet program.

Excel is all about calculations and analysis, and mathematical functions are one of those functions which you can use to get better in calculations and analysis.

Below are some of the important function which you need to know.

The SUM function returns the summation of numeric values. In simple words, you can sum numbers.

Syntax

SUM(number1,[number2],…)

Arguments

• number1 A number, a range of cells which contain numbers or a single cell which contain a number.
• [number2] A number, a range of cells which contain numbers or a single cell which contain a number.

You can insert numbers directly into the function by using commas between them.

You can also simply refer to a range for calculating the sum of the numbers and if there is a text, logical value or empty cell it will ignore them.

If there is an error value in a cell which you are referring, it will return #N/A in the result.

If you have numeric values which are formatted as text it will ignore them. It's recommended to convert them into numbers before using SUM.

• You can use IF for nested formulas.
• The maximum number of nested condition you can perform is 64.
• You can use comparison operators to evaluate a condition in IF.

SUMIF function sum values from a range of cells using a condition.

In simple words, it sum numbers from a range which meets specific criteria.

You need to define a range to check for criteria and a corresponding range of values which you want to sum.

Syntax

Download Bahnschrift Light For Free, View Sample Text, Rating And More On Fontsgeek.com. Bahnschrift Light. Here are the fonts having name similar to Bahnschrift Light. Fonts similar to Bahnschrift Light. Bahamas Light Italic Regular Uncategorized Total Downloads: 118. Download Bahnschrift font for PC/Mac for free, take a test-drive and see the entire character set. Moreover, you can embed it to your website with @font-face support. Bahnschrift light font download.

SUMIF(range, criteria, [sum_range])

Arguments

• range A range of cells from which you want to check for criteria.
• criteria A criteria which can be a number, text, expression, cell reference or a function.
• [sum_range] A cell range which has the values you want to sum.

In the below example, I have specified A1:A9 as criteria range and B1:B9 as sum range. After that, I have specified the criteria in A12 which has the value C.

You can also insert criteria directly into the function. In the below example, we have used an asterisk wildcard to specify a criterion which has an alphabet 'S'.

And, if you skip specifying the sum range it will give you the sum of criteria range. But, that will be only possible if criteria range has numeric values.

• You can use IF for nested formulas.
• The maximum number of nested condition you can perform is 64.
• You can use comparison operators to evaluate a condition in IF.

The SUMIFS function can sum values which meet multiple criteria.

In simple words, you can specify more than one criteria to check and get SUM of values where criteria are matched.

Syntax

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Arguments

• [sum_range] A range of cells & values which you want to sum.
• criteria_range1 A range from which you want to test criteria.
• criteria1 A criteria which can be a number, text, expression, cell reference or a function.
• [criteria_range2] A range from which you want to test criteria.
• [criteria2] A criteria which can be a number, text, expression, cell reference or a function.

In the below example, I have used three different criteria to check and it has returned the sum of the values which meet those criteria.

In the below example, I have used wildcard characters to specify three different criteria.

1. The first criteria range is A2:A9 and criterion is in the cell A12 which will only sum cells which have alphabet O.
2. The second criteria range is B2:B9 and criterion is in cell B12 which will only sum cells which have alphabet P. It will apply to all the cells which met the condition 1.
3. The third criteria range is C2:C9 and criterion is in cell C12 which will sum cells with any type of value. It will apply to all the cells which met the condition 1 and 2.

• The size of criteria range and sum range should be of the same size.
• You can also use wildcard characters with SUMIFS.
• If you want to sum values on the basis of single criteria, you can use SUMIF.
• If you want to specify criteria in the form of text, you have to use double quotation marks.
• If you skip specifying SUM range it will SUM the values from criteria range which meet the criteria.
• You can only specify criteria up to 255 characters.

SUMPRODUCT is one of the most powerful Excel functions. It can sum and multiply the ranges or arrays. In simple words, it first multiplies the corresponding cells from ranges and then sum up all the values.

Syntax

SUMPRODUCT(array1, [array2], [array3], ..)

Arguments

• array1 the first array you want to multiply and then add.
• [array2] the second array you want to multiply and then add.

In the below example, I have used SUMPRODUCT to multiple and sum up the values from column D and column F.

First, it has multiplied the values from column D with column F and then sums up the values.

• If skip you to specify array2, SUMPRODUCT will simply sum the array1.
• The maximum size of each array should be same. If array1 has 5 cells then cells in array2 should be 5.
• Text and other non-numeric entries will be treated as 0.

The SUM function returns the summation of numeric values. In simple words, you can sum a numbers.

Syntax

ABS(number)

Arguments

• number A number or value to get a positive number of.

In the below example, to convert negative values in positive I have used ABS. It has just removed the negative sign from the numbers.

And here we have used ABS to get absolute values against the change in the interest rate.

• You can also use this absolute function as an array function.
• You can also put the number in the function to get the positive number.
• If specify a number which is not an integer, it will return #VALUE!.

The EVEN function to rounds a number to a nearest even number. For example, it will round 5.1 to 6 and 3.1 to 4.

Syntax

EVEN(number)

Arguments

• number A number value which you want to round.

Here I have used different arguments for EVEN.

• When you refer to negative values, it will always round them to lower even number, away from zero.
• When you refer to positive values it will always round them to the upper even number.
• And, when you refer to a zero it will remain same.

• If you specify a value other than a number it will return a #VALUE! error.
• You can also insert a numeric value directly into the function by using double quotes or even without using them.

The INT function rounds down a number to the nearest integer.

In simple words, it returns only integer part of a number by removing decimals from it.

Syntax

INT(number)

Arguments

• number Number you want to round down to the nearest integer.

In the below example, I have rounded 9.9 to 9 and 8.9 to 8. You can use this rounding formula to remove decimals from the numbers

• It will round down both positive or negative numbers.

The MOD function returns the remainder after dividing two numbers. In simple words, it returns the remainder number which left after the division of two number.

Syntax

MOD(number, divisor)

Arguments

• number A number which you want to divide.
• divisor A number with which you want to divide.

In the below example, I have used MOD with the different types of values.

• If there is no remainder after dividing two numbers it will return zero.
• If there is a decimal in divisor it will consider that decimal and return result according to that.
• If you specify zero to divide, it will return 0 in the result. Dividing any number by zero results in zero.
• If divisor will zero, it will return #DIV/0!.
• If you skip specifying any number for dividing it will return 0 in the result.
• And, if you skip specifying any divisor, it will return #DIV/0!.
• It will consider negative values same as positive values and return same sign that divisor have.

• You can use IF for nested formulas.
• The maximum number of nested condition you can perform is 64.
• You can use comparison operators to evaluate a condition in IF.

The MROUND function rounds a number to the nearest multiple of a number. And while rounding a number, it doesn’t consider that number is greater or lower than the original number.

Syntax

MROUND(number, multiple)

Arguments

• number Number to round.
• multiple A number for which you want to find the nearest multiple.

In the below example, I have used different arguments to make you understand the concept of MROUND.

• 12.5 rounded to 12 to the nearest multiple of 3 is 12.
• 19 is rounded to 20 to the nearest multiple of 5
• 13 is rounded to 12 to the nearest multiple of 6.
• 3.6 is rounded to 2 to the nearest multiple of 4.
• The result is 0 as I have skipped specifying multiple.
• The result is 0 as I have specified 0 as the multiple.
• The result is an error as I have specified -2 as multiple.
• Here we have a tie between 20 and 18, but we get 20 as it always rounds away from zero.

• If you put zero in multiple it will return zero in the result.
• If you skip specifying multiple it will return with an error.
• It rounds away from zero.
• If you have two multiples on the same distance it will return the multiple which is higher than the number you are rounding.

The TRUNC function truncate decimals, that means remove fractional part of a number. In the simple words, it removes the decimals from a number to a specific precision and then returns the integer part in the result.

Syntax

TRUNC(number, [num_digits])

Arguments

• number Number you want to truncate.
• [num_digits] A number to specify precision to truncate a number.

In the below example, I have used this function to truncate data for removing time from the dates. As you know, dates are serial numbers and time is in decimal in Excel.

• If you put zero in multiple it will return zero in the result.
• If you skip specifying multiple it will return with an error.
• It rounds away from zero.
• If you have two multiples on the same distance it will return the multiple which is higher than the number you are rounding.

RAND function is a best fit to randomly generate numbers in Excel. It can generate evenly distributed numbers between 0 and 1.

It’s a volatile function.

Every time when you refresh your worksheet calculations, it will return a new random number.

Apart from having numbers between 0 and 1 you can also use RAND for random numbers between two specific numbers.

In the below example, I have used it to create a formula which generates a random number between 50 and 100.

When you enter this formula in a cell it returns a number between 100 and 50 by multiplying the values returned by the RAND with the equation we have used.

To understand this formula we need to split it into three parts:

• First of all, when it deducts the lowest number from the highest number you get the difference between both of them.
• Then secondly it multiplies that difference with the random number returned after the subtraction.
• And third, add that number with the lowest number left in the third part of the equation.

• If you put zero in multiple it will return zero in the result.
• If you skip specifying multiple it will return with an error.
• It rounds away from zero.
• If you have two multiples on the same distance it will return the multiple which is higher than the number you are rounding.

One of the best thing about Excel there are a bunch of statistical functions there which you can use to analyze data easily. Here are some of those:

You can use COUNT function to count cells which have numbers.

In simple words, it will only return the count of cells which have numbers and ignore all the other cell.

Syntax

COUNT(value1, [value2], …)

Arguments

• value1 A cell reference, an array or a number which is directly entered into the function.
• [value2] A cell reference, an array or a number which is directly entered into the function.

In the below example, I have used this function to count cells from the range A1:A10 and it has return 8 in the result.

In cell A3, I have a logical value and in cell A7 I have a text. COUNT has ignored both of the cells.

But, if you enter a logical value directly into the function it will count it.

In the below example, we have inserted a logical value and a number using double quotation marks.

• You can specify up to 255 arguments.
• It can count numbers, dates and text which represent numbers.
• If you enter a logical value or text representation of a number directly into the function it will count them.
• Text and ERROR values will be ignored.
• If you specify a reference or an array, count function will only return the number of cells which have a numeric value.

You can use COUNTA function to count non-blank cells.

In simple words, COUNTA function can count the cell which has any type of value in it, other than blank.

Syntax

COUNTA(value1, [value2], …)

Arguments

• value1 A value, a cell reference, a range of cells or an array.
• [value2] A value, a cell reference, a range of cells or an array.

In the below example, I have used COUNTA function to count cells from range A1:A11.

There are total 11 cells in the range and function has return 10.

I have one blank cell in the range which is ignored by the function.

In rest of the cells, I have numbers, text, logical value and a symbol.

1. It will count cell which has:
   • Text
   • Number
   • Formula
   • Cell with a formula resulting in an empty cell.
   • Symbol
   • Error
2. You can provide up to 255 arguments.
3. You can also insert arguments directly into the function.

You can use COUNBLANK function to count cells which are blank.

In simple words, when you specify a range of cells it will return the number of cells which have no value in it and if there is no blank cell in the range it will return “0”.

Syntax

COUNTBLANK(range)

Arguments

• range A range of cells from which you want to count blank cells.

In the below example, I have used COUNTBLANK function to count blank cells from the range A2:A8.

In this range, I have total 3 cells which are blank but cell A7 has a formula in it which results in a blank cell.

The function has returned 2 as cell A4 and A5 are the only cells which are blank and have no values.

• If there is a zero in a cell, that will consider as a non-blank cell, hence ignored.
• And, if a cell has a formula resulting in an empty (=” “), that will also consider as a non-blank cell.
• Cells contain numbers, text, errors and symbols are ignored.

You can use COUNTIF function counts the number of cells which meet a given criterion.

In simple words, you can specify a value and count the number of cells which have that value from a given range of cells.

Syntax

COUNTIF(range, criteria)

Arguments

• range A range of cells from which you want to count the cells which meet criteria.
• criteria A criteria to check to count cells.

Using Logical Operators as a Criteria: In the below example, we have used '>75' (a logical operator) to count the number of students who scored more than 75.

If you want to use a logical operator you have to put it inside the double quotation marks.

Using Dates as a Criteria: In the below example, we have used a date in criteria to find out how many employees have a week off on 4th July.

I have inserted a date directly into the function as COUNTIF automatically converts a text into a date.

In the above example, I have inserted same date as a number and as you know Excel stores a date as a number.

So you can also insert a number which represents a date as per Excel's date system.

• You can use a number, text, cell reference or a formula as criteria.
• You can also use wildcard characters in criteria.
• Criteria other than numbers and formulas need to enclose in double quotation marks.
• You can also use logical operators in criteria.
  Criteria are case insensitive.

COUNTIFS function helps you to count cells which meets multiple criteria.

That means you can get the count of cells where the values you specified is there.

Syntax

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

Arguments

• criteria_range1 – The range of cells which you want to evaluate using criteria1.
• criteria1 – The criteria which you want to evaluate for criteria_range1.
• [criteria_range2] – The range of cells which you want to evaluate using criteria1.
• [criteria2] - The criteria which you want to evaluate for criteria_range1.

In the below example, I have used COUNTIFS to count females who are above 25 years of age.

And I have specified two criteria for evaluation, one is “Female” and other is a greater than operator to count cells with a number which is greater than “>25”.

In the below example, I have used an asterisk in one criterion and greater than operator in another one to count the number of the person whose name is starting with alphabet A and whose age is more than 25.

• All criteria ranges must have the same number of rows. (For example, if criteria_range1 has 10 rows then criteria_range2 must have 10 rows.)
• You can use a text, a number, a cell reference, or a formula as a criteria.
• You can evaluate up to 127 criteria ranges.
• Criteria which is a text or a logical operator should be enclosed in double quotes.
• You can also use wildcard characters with this.
• If criteria is a reference to a cell which is empty, the function will treat it as 0.
• It's not a case sensitive function.

You can use AVERAGE function to calculate the average of given numbers.

In simple words, it will first sum the numbers and then divide that sum by the count of numbers.

Syntax

AVERAGE(number1,number2,…)

Arguments

• number1 - A numeric value for which you want to get the average.
• [number2] - A numeric value for which you want to get the average.

In the below example, I have entered arguments directly into the function.

In the below example, I have referred to a range which has numbers.

You can refer to the unlimited cell by using a continues range and if you want to refer to a dynamic range you can use a table for that.

If any of the cells which is referred has an error value it will return an error in the result.

In the below example, I have referred to a range in which cells are formatted as a text value.

In this case, you can convert those text numbers into the actual numbers to calculate average.

A cell will also be ignored if that cell is preceded by an apostrophe.

• You can specify up to 255 arguments.
• It can count numbers, dates and text which represent numbers.
• If you enter a logical value or text representation of a number directly into the function it will count them.
• Text and ERROR values will be ignored.
• If you specify a reference or an array, count function will only return the number of cells which have a numeric value.

You can use AVERAGEA function to average numbers, logical values, and numbers which are stored as a text.

In simple words, It will calculate the average for the number, logical values (TRUE or FALSE) and number which are stored as text(in double quotes).

Syntax

Excel Formulas List Pdf Download

AVERAGEA(value1,value2,…)

Arguments

• value1 - A value which is a number, a logical value or a number which is stored as a text.
• [value2] - A value which is a number, a logical value or a number which is stored as a text.

In the below example, I have used arguments with different values:

• A cell which has text in it is considered as 0.
• A cell which has FALSE in it is considered as 0 & TRUE is considered as 1.
• All cells which have numbers in it as taken as their actual numbers.

And, value returned by the function is 10.17 which is “(0+0+1+10+20+30)/6”.

In above example, I have inserted values directly into the function:

• A number which I have inserted using double quotation marks, excel take it as a number.
• TRUE is considered as 1 & FALSE is considered as 0.

• The difference between AVERAGE and AVERAGEA is that AVERAGEA also considers text and logical values while calculating the average.
• Logical value TRUE will consider as 1 and FALSE will consider as 0.
• If you enter a text directly into the function it will return #VALUE! & if you refer a cell which has a text, it will treat that text as 0.
• If you enter a number directly into the function using double quotation marks, it will ignore that quotation marks and take it as a number. But if you refer to a cell which has a number formatted as text, it will consider that argument as 0. You can change the number format in that case.
• If any of the specified arguments is an error, it will return an error in the result.

You can use AVERAGEIF function to calculates the average of the values which meet a condition.

In simple words, you can specify a condition and this function will only average those values which meet that condition.

Syntax

AVERAGEIF(range, criteria, average_range)

Arguments

• range A range contain values from which you want to check the condition.
• criteria Condition which you want to check.
• [average_range] A range in which you have values to calculate the average.

In the below example, I have used AVERAGEIF to calculate the average of product C.

We have directly inserted criteria into the function by using double quotes.

We have two entries of product C in which we have quantities 12 and 8 respectively.

AVERAGEIF checks for the product C in column A and average corresponding values from column B.

In the below example, I have used an asterisk to calculate the average quantity of fruits who have alphabet A in their name.

I have two fruits in above list who have Alphabet A in their name.

AVERAGEIF verifies the condition from the column A and calculates the average of corresponding values from column B.

• All empty cells will be ignored in average_range.
  You can use wildcard characters in criteria.
• If a range has no value or text, it will return #DIV/0!.
• Cells in the range which contain TRUE or FALSE are ignored.
• The size of average_range and range should be same.
• Text, logical operators should be enclosed in double quotes.
• If you skip specifying the average range, it will return the average of cells from range (if the range has numeric values). And, if the range has other than numeric values it will return #DIV/0!.

You can use AVERAGEIFS function to calculates the average of the values which one or more conditions.

It can take more than one value as criteria and average only those cells which meet that.

Syntax

AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Arguments

• average_range A range of cells for which you want to calculate average.
• criteria_range1 A range of cells from which you want to test the condition.
• criteria1 A condition which you want to mention.
• [criteria_range2] Second range of cells from which you want to test the condition.
• [criteria1] A condition which you want to mention.

In the below example, I have used the averageifs function to calculate the average of quantity sold by salesman “Peter” and for the product “B”.

I have inserted criteria directly into the function. And, we have two entries of Peter’s sale of product B.

In cell A3, I have a logical value and in cell A7 I have a text. COUNT has ignored both of the cells.

In the below example, I have used AVERAGEIFS with an asterisk to calculate the average of price of fruits whose quantity is greater than 20 units and has B in its name.

In below data table, We have two fruits who meet these criteria.

• If cells in average range are blank/text value then it will return an error.
• If cells in criteria range are blank/text value then it will return an error.
• And, if any cell from criteria range or average range has TRUE or FALSE it will consider it as 1 and 0 respectively.
• You can also use logical operators and wildcard characters in criteria.
• The size and shape of average range and each criterion range should be same.
• It will return #DIV/0! if there is no match found in criteria range.
• Make sure to enclosed non-numeric values in double quotation marks.

You can use MAX function to get the largest value from a specified set of values. In simple words, it returns the highest value from a set of numbers.

Syntax

MAX(number1, [number2], …)

Arguments

• number1 A number, a cell which has a number or a range of cells having numbers from which you want to get the largest number.
• [number2] A number, a cell which has a number or a range of cells having numbers from which you want to get the largest number.

In the below example, I have entered numbers directly into the function by separating them using a comma.

You can also enter a number by using double quotation marks.

In the below example, I have referred to a range and it has return 1861 in the result as the largest value.

You can also refer to an array as well.

In the below example, I have encountered with an error value and function has returned an error value in the result.

• You can refer to a single cell, a named range, a range of cells or an array.
• If there are no numbers in specified arguments it will return 0.
• If there is text or an error value in the argument, it will return #N/A.
• If you directly enter a logical value or a text which represents a number into the function, that will be counted.
• It will only consider numeric values. Empty cells, text & logical values in a reference or in an array will be ignored.
• The maximum number of arguments you can refer is 255.

You can use Excel MIN function to get the smallest value from a specified set of values. In simple words, it returns the smallest value from a set of numbers given.

Syntax

MIN(number1, [number2], …)

Arguments

• number1 A number, a cell which has a number or a range of cells having numbers from which you want to get the smallest number.
  [number2] A number, a cell which has a number or a range of cells having numbers from which you want to get the smallest number.

In the below example, I have entered numbers directly into the function by separating them using a comma.

You can also enter a number by using double quotation marks.

It will consider them as a number.

In the below example, I have referred to a range and it has return 1861 in the result as the largest value.

In the below example, I have encountered with an error value and function has returned an error value in the result.

• You can refer to a single cell, a named range, a range of cells or an array.
• If there are no numbers in specified arguments it will return 0.
• If there is text or an error value in the argument, it will return #N/A.
• If you directly enter a logical value or a text which represents a number into the function, that will be counted.
• It will only consider numeric values. Empty cells, text and logical values in a reference or in an array will be ignored.
• The maximum number of arguments you can refer is 255.

If you deal with data where you have text, then below are some of the functions which you need to learn to work efficiently.

By using Excel SHEET function you can get the sheet number of a worksheet.

In above example, I have put worksheet name in sheet function to get the sheet number.

Syntax

SHEET(value)

Arguments

• [value] Name of the sheet or reference to any cell in a sheet.

In the below example, I have used different inputs to the get the sheet number.

Where I have invalid sheet name and invalid sheet reference, SHEET function has returned an error.

• It will include all type of sheet (Chart Sheet, Worksheet or Macro Sheet).
• You can refer to sheets even they are visible, hidden or very hidden.
• If you skip specifying any value in the function it will give you the sheet number of sheet in which you have applied the function.
• If you specify an invalid sheet name, it will return a #N/A.
• If you specify an invalid sheet reference, it will return a #REF!.

By using SHEETS function you can get the number of sheets in a reference. In simple words, it will return the number of sheets used in a reference.

In above example, I have referred to cell A1 in the same sheet & SHEETS Function returned with 1 as only 1 sheet is in reference.

SHEETS(reference)

• reference Reference for which you want to count numbers of sheets.

In the below example, we have used 3D cell reference to get the sum of cell A1 from five sheets and I have used the same reference to get the count of sheets.

• It will include all type of sheet (Chart Sheet, Worksheet or Macro Sheet).
• You can refer to sheets even they are visible, hidden or very hidden.
• If you skip specifying any value in the function it will give you the count of total sheets in a workbook
• If you specify an invalid reference, it will return a #REF!.

You can use TYPE function to check the type of value from a cell. In simple words, it will return that what type of a data captured in a cell.

TYPE function is helpful when a function is dependent on the value type of a cell.

In the below example, I have used type function check which type of value is entered in a cell. For a value which is a number, it has returned 1 & for a value which is text, it has returned 2.

TYPE will return 5 different numbers in the result depending on the type of values from a cell.

Following are the codes which can be returned by type function against the values from a cell.

TYPE(value)

• value Reference of a cell or a value for which you want to test the type.

In the below example, we have used TYPE function with IF.

This formula will return the message “Enter Valid Quantity” if the user enters a value other than a number.

• When you test value from a cell which has a formula, Type function will test the value return by that formula.
• If you refer to a cell which is blank, it will return number 1 as a result.
• If you refer to a cell which contains date it will return 1 in a result as a date has a value in number.

You can use NA Function to generate a #N/A error value.

Best use of NA function is to get an error when you have empty and missing information in a cell.

NA( )

• You can also type #N/A directly into a cell.
• NA is mainly provided for compatibility with other spreadsheet programs.

N function helps you to convert a Boolean into a number.

In the below example, I have used it to convert TRUE into 1 and FALSE into 0.

N(value)

• value A Boolean which you want to convert to a number.

In the below example, I have used N Function to insert a comment into a formula.

When you input a text in N function it will return 0 which have no effect on calculation and you have a nice technique to insert comments in your formulas for future reference.

• In a real life, N doesn’t have any use as a separate function as Excel can automatically convert Boolean into numbers where needed.

You can use CELL function to get some specific information about a cell.

For example, if you want to check whether a is protected or not, or you want to check the width of a cell, you can use cell function for that.

Cell function has total 12 different parameters to check about a cell reference.

CELL(info_type, [reference])

• info_type Type of information you want to check about a cell. You have a drop down to select which type of information you required about the reference.

• [reference] Cell for which you want to get the information.

In the below example, I have used all the parameters to get information about a cell.

You can use this function with other function where you need to use information about a cell.

• If change the format of a cell, to update the result in the function you have to recalculate the worksheet.
• Please check below format codes which can be return by the CELL function.

By using ERROR.TYPE function you can evaluate a value for an error and get a specific number in return if an error occurs.

And if an error doesn’t occur it will return #N/A.

ERROR.TYPE (error_val)

• error_val Value you want to evaluate for error & require error number in return.

In the below example, I have used ERROR.TYPE with VLOOKUP to show a relevant message whenever an error occurs.

You can use this method to deliver a relevant message to the user.

You have to include IFERROR with this function because if there is no error, error.type will return an error.

• You can use it with other function to test errors.

You can use INFO function to get information about your current operating environment. In simple words, you can get information about your application.

INFO(type_text)

• type_text Information which you need as a result.

In the below example, I have used all the parameters to get info about the current operating environment.

• You have 7 different parameters to get info about your current operating environment.

You can use ISBLANK function to check if a cell is blank and it returns TRUE if that cell is blank else FALSE.

ISBLANK(value)

• value:A cell or a value which you want to test.

In the below example, I have used ISBLANK with IF to deliver a message to the user if cell F1 is blank.

Excel All Formulas List Pdf 2016

• If a cell has a formula which has to return a blank or empty value, ISBLANK will return FALSE in that case.

ISERROR function tells you that if there is any error in a cell or not.

If there is an error in the cell it returns TRUE else FALSE.

ISERROR(value)

• value A cell reference or a value from which you want to check the error.

In the below example, I have used ISERROR with IF to get a specific text if there is any error in a cell.

In the below example, I have used ISERROR with an array range to check the error from a range of cells.

• ISERROR will verify an error in both conditions, either that error is in an absolute value or is a result of another formula.
• It will evaluate all types of error from a cell. #N/A, #DIV/0!, #NAME?, #NULL!, #NUM!, #REF! & #VALUE!.

Unlike ISERROR, it checks about the error in cell excluding #N/A.

In simple words, it returns TRUE if there is an error in the cell other than #N/A.

• value A cell reference or a value from which you want to check the error.

In the below example, I have used ISERR With If Statement to get a specific text if there is any error in a cell.

In the below example, I have used ISERR with an array range to check the error from a range of cells.

• ISERR will verify an error in both conditions, either that error is in an absolute value or is a result of another formula.
• Evaluate all types of error from a cell #DIV/0! #NAME? #NULL! #NUM! #REF! & #VALUE! but not #N/A. To check #N/A, you can use ISNA and ISERROR.

You can use ISEVEN function to evaluate a value and it returns TRUE if that value is an odd number, else FALSE.

In simple words, you can check that a value is an even number or not.

ISEVEN(number)

• number A value which you wants to evaluate.

Here I have used different arguments for this function.

• It will return TRUE if given number is even.
• It will evaluate negative values in the same way.
• It will treat 0 as an even number.
• If you specify a number with decimal points it will ignore decimals & evaluate the integer. In this example, it has truncate .5 and evaluated 2.
• As excel store dates as serial numbers, it will evaluate them in the same manner.

• You can also insert a number into function directly by using double quotes or even without it.
• If you specify a non-numeric value it will return the #VALUE! error value.

You can use ISNA function if you want to test a value for #N/A error.

In simple words, it will return TRUE if a cell or a value you have specified has #N/A error, else it will return FALSE.

ISNA(value)

• value A cell reference or a value which you want to test.

In the below example, I have used ISNA to check different error values and I have got TRUE only in the case of #N/A error value.

As we know #N/A means that that a value is not available.

In the below example, I have used IF and VLOOKUP with ISNA to deliver a meaningful message to the user.

• It will only consider #N/A, ignore other error values.
• You can enter #N/A directly into the function.

You can use ISODD function to evaluate a value and it returns TRUE if that value is an odd number, else FALSE.
In simple words, you can check that a value is an odd number or not.

ISODD(value)

• value Number which you want to test for the odd number.

In the below example, I have used ISODD with IF to deliver an alert message to the user if a number nested in the cell other than an odd number.

• If a number is enclosed in double quotation marks it will treat that number as a text and return FALSE.
• If the value is not a number it will return a #VALUE error.

You can use ISFORMULA function to test if a cell contains a formula and it return TRUE else FALSE.

In simple words, you can check if a cell has a formula or not.

ISFORMULA(reference)

• reference A cell reference which you want to evaluate.

Here I have used different arguments in this function.

• A simple addition calculation & function has return TRUE.
• A volatile function & again function has return TRUE.
• One thing you have to note that ISFORMULA is concerned with the formula in a cell not with the result of a formula. It will return TRUE even if the result of the formula is empty or an error.
• If there is any value other than formula in a cell it will return FALSE.

• If cell reference is not a valid reference it will return #VALUE!.
• You can also use the shortcut key Control + ~ to display all the formula in a worksheet.

You can use ISLOGICAL Function to check if a cell has a logical value or not.

If there is a logical value in the cell it will return TRUE else FALSE.

Excel Formulas List Pdf File

In simple words, if a cell has a value TRUE or FALSE it will return TRUE else it will return FALSE.

ISLOGICAL(value)

• value A value which you want to evaluate.

Here I have used different arguments for this function.

• In the first cell, I have entered a simple entered a TRUE value and in the second cell, I have used TRUE Function. It has returned TRUE for both of the values as both of the values are logical values.
• In the third & fourth cell, I have used FALSE value & FALSE Function respectively & it has returned the same result for both of the value.
• As logical values, TRUE & FALSE has numeric values 1 & 0 as well but ISLOGICAL will not treat these number logical values.
• If you use a text to evaluate, it will return FALSE.
• For a blank a cell it will return FALSE.
• If a value contains an error it will return a #N/A value.

You can use ISNONTEXT function to verify if a cell contains a non-text value. If that value is non-text it will return TRUE, else FALSE.

ISNONTEXT(value)

• value A cell or a value which you want to test.

In the below example, I have used this function with IF to deliver a message to the user if a non-text value will enter into the cell.

• If a number is enclosed in double quotation marks will be treated as text and formula will return FALSE.

You can use ISNUMBER function to test whether a value is a numbers or not.

It will return TRUE if a value is a number else it will return FALSE. You can use this function to check for the numeric value where you are not sure about the format.

ISNUMBER(value)

• value A numeric value which you want to check.

In the below example, I have used ISNUMBER with IF to deliver an alert message if a user enters a non-number value in cell F1.

• Numbers which are enclosed in double quotation marks will be treated as text.

You can use ISREF Function to check whether a reference is valid or not.

You can check for a reference and it will return TRUE if that reference is valid, else FALSE.

ISREF(value)

• value A value for which you want to check for a valid reference.

In the below example, fruit is a valid named range but when you have used double quotation marks, ISREF Function is unable to test that reference.

• If a valid reference address enclosed in a double quotation marks ISREF will not able to test that reference and return FALSE even that reference is a valid reference.

Microsoft Excel All Formulas List With Examples Pdf

ISTEXT function is useful when you want to check if a value is a text string. It will return TRUE if a value is a text, else FALSE.

Even if a number is formatted as a text it will return TRUE. It’s useful to check outcomes for formulas or to check value type from a cell.

ISTEXT(value)

• value A cell reference or a value for which you want to test.

In the below example, I have used ISTEXT with IF to create a nesting formula to deliver an alert message if a user enters an invalid name other than a text.

• Any number enclosed in double quotation marks will be treated as a text.

If you deal with data where you have text, then below are some of the functions which you need to learn to work efficiently.

You can use FV Function to calculate the future value of an investment. An investment which is based on periodic and constant payments and on a constant rate of interest.

In simple words, it will return a future value of an investment where you have constant payments and constant interest rate throughout the investment period.

FV(rate,nper,pmt,[pv],[type])

Arguments

List Of All Excel Formulas Pdf

• rate A constant interest rate which you want to use in the calculation.
• nper Number of payments.
• pmt A constant payment amount to pay periodically throughout the investment time.
• [pv] The present value of future payments. It must be entered as a negative value. 0 if omitted.
• [type] A number to specify when payment is due. 0 = at the end of the period, 1 = at the beginning of the period.

In the below example, I have used 10% interest rate, 5 payments on a yearly basis, $1000 payment amount, no PV amount and payment type at the beginning of the period.

In the below example, I have used 10% interest rate, 5 payments on a yearly basis, $1000 payment amount, no PV amount and payment type at the beginning of the period.

The function has return 6716 in the result.

Let me explain you the phenomena working behind FV Function.

At the beginning of each period, it will calculate the interest on payment and carry forward that amount (Actual Amount + Interest)to the next period. And, in next period again same calculation will be done and so on.

The best part of FV function is that it can do this step by step calculation for you in a single cell.

In the below example, I have used monthly payments.

For this, I have converted annual interest rate into month by dividing by 12. And, I have mentioned 60 months instead of 5 years.

In PV, I have specified 10000 for payment and the function has returned $94536.

You can also use FV function to calculate compound interest.

• Make sure you have specified rate and number of payments in a consistent manner. If the rate is for annual basis then you have to specify payment periods on the annual basis as well. And, if you want to specify payments on a monthly basis you have to convert interest rate on the monthly basis by dividing by 12. Same for quarterly and half yearly basis.
• If pmt is the cash which you have paid (i.e deposits to saving, etc), the value must be negative; and if it is the cash received(income, dividends), the value must be positive.

You can use PMT function to a calculate loan payment based on fixed monthly payments and constant rate of interest (loan payment based on fixed monthly payments and constant rate of interest).

PMT(rate, nper, pv, [fv], [type])

• Rate The rate of interest for the loan. This rate of interest should be constant.
• nper The total number of payments.
• pv Present value or the total amount of loan.
• [fv] The future value or the cash balance which you want after the last payment. The default value is 0.
• [type] Use 0 or 1 to specify the due time of payment. You can use 0 when payment is due at the end of the each payment or 1 if payment is due at the start of the each period. If you omit to specify the type, it will assume 0.

Excel All Formulas List Pdf In Hindi

Suppose you want to take a 20 years mortgage loan for $250000 by assuming 2.5% as an interest rate.

Now here we can use PMT function to calculate your monthly installment.

In above calculation, I have converted annual interest rate into monthly by dividing with 12 and years into months by multiplying with 12.

I have not mentioned any future value, and payment type is a default. And in the result I have got a negative value, because amount $987.80 is what I have to pay every month for 30 years.

115 rows  Drivers Against Mad Methods; Drivers and Utility Compact Disk; Drivers Cd; Drivers Control Center Differential; Drivers Data Base; Drivers Display Interface; Drivers Ed, Not Teens Dead; Drivers Enhanced Vision System; Drivers Instrument Panel; Drivers licence; Drivers licence; Drivers License; Drivers License Number; Drivers License Service. 1 ways to abbreviate License updated 2019. How to abbreviate License? The most popular abbreviation for License is: lic. Three arrests for operating while intoxicated within 12 month period Accident Conviction Fatal. Looking for the abbreviation of Drivers License? Find out what is the most common shorthand of Drivers License on Abbreviations.com! The Web's largest and most. Abbreviation for blonde license.

• The amount of payment return by PMT function only includes payment and interest but not includes taxes and other fees which are related to the loan.
• You have to be sure while specifying the value for rate and nper arguments. If you want to pay monthly installments on a five-year loan at an annual interest rate of 8 percent, use 8%/12 for rate and 5*12 for nper. For annual payments on the same loan, use 8 percent for rate and 5 for nper.

You can use PV function to calculate the present value of a financial investment or a loan.

In the case of a loan, you can get the actual present value of a loan you can afford to pay in monthly payments.

You need a constant interest rate, constant periodic payments.

PV(rate, nper, pmt, [fv], [type])

• rate The rate of interest for the payment of the loan.
  nper Total number of payment periods
• pmt A constant amount of payment you have to make after every period.
• [FV] The future value or a cash balance of a loan or an investment you want to attain after the last payment is made. If omitted, it will b assumed as 0.
• [type] Time of the payment. Beginning of the period (use “0”) or end of the period (Use “1”).

Let’s say you want to invest $4000 in an investment plan and in return, you’ll get $1000 at the end of each year for next 5 years.

That means you’ll get total $5000 in next 5 years.

Now the thing is, you have to evaluate that this investment is profitable or not.

You are investing $4000 today and the return will come to you in next 5 years.

In the above calculation, it has returned -4329. The present value of your investment is $4329 and you are investing $4000 for it.

Hence, your investment is profitable.

• The units you use as arguments should be consistent. For example,
• If you are using periods in months (36 Months = 3 Years) then you have to convert the annual interest rate into a monthly interest rate (6%/12 = 0.5%).
• PV function is an annuity function.
• In annuity functions, the cash payments by you are represented by negative numbers and the payments you receive are represented by positive numbers.

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

LinkedIn - YouTube