ROW

“ROW”

Used for – “ROW” function in MS Excel is used to get the absolute position of the row of the reference cell.

Syntax – ROW([reference])

             [reference] – This is an optional field as it is written in square brackets. It is a address of any cell for which we want to know the absolute position. The result of the this function is a numeric. If we do not put any reference it will consider the cell in which this function is typed and give the absolute position of the same.

                       

Example – Following example will clear the ROW function:-

In below table formulas are shown which which reflects the various reference type for the function ROW:-

=ROW(A1)	=ROW(AA1)	=ROW()
=ROW(A11)	=ROW(BA1)
=ROW(A9999)	=ROW(TAA1)

Below table shows the result of the above function if copied to cell A1 to C3 in any worksheet of workbook:

1	1	1
11	1
9999	1

Below is a typical summary of the Column Function in the pictorial form with more advance typical information:

Summarized Column Function

Note:
-Here it should be noted that reference should be used for single cell. If a range is selected then it will give the absolute row position of the top left cell of the range.

Read More

COLUMN

“COLUMN”

Used for – “COLUMN” function in MS Excel is used to get the absolute position of the column of the reference cell.

Syntax – COLUMN([reference])

             [reference] – This is an optional field as it is written in square brackets. It is a address of any cell for which we want to know the absolute position. The result of the this function is a numeric. If we do not put any reference it will consider the cell in which this function is typed and give the absolute position of the same.

                       

Example – Following example will clear the COLUMN function:-

In below table formulas are shown which which reflects the various reference type for the function COLUMN:-

=COLUMN(A1)	=COLUMN(AA1)	=COLUMN()
=COLUMN(A11)	=COLUMN(BA1)
=COLUMN(A9999)	=COLUMN(TAA1)

Below table shows the result of the above function if copied to cell A1 to C3 in any worksheet of workbook:

1	27	3
1	53
1	13547

Below is a typical summary of the Column Function in the pictorial form with more advance typical information:

Summarized Column Function 

Note:
-Here it should be noted that reference should be used for single cell. If a range is selected then it will give the absolute column position of the top left cell of the range.

Read More

BREAK and COMBINE Name's

Hey Guys!!!

Perhaps some time you find that you have to break name into Prefix, First Name, Middle Name and Last Name or you have to combine all these to get Name.If it is one time then you can do it manually. But if it is in whole data then what would you do?

Here's a example in which you can break or combine a name fields using formulas. See how it is being done:-

Read More

CONVERT DATE FORMAT

Hey Guys!!!

Perhaps some time you find that you have to convert the date format as shown below:-

24/02/2018  >>>>>>>  24th February, 2018. 

and If it is one time then you can do it manually. But if it is in whole data then what would you do?

Here's a example in which you can change it using formulas. See how it is being done

Read More

HYPERLINK

“HYPERLINK”

Used for – “HYPERLINK” function in MS Excel is used in the same manner as we add hyperlink by using link commands via insert tab> link group> link command i.e. it creates a shortcut / jump that opens a specific document which stored on network server / internet / intranet. And when you click on the link it will opens that file for which link have been created.

Syntax –HYPERLINK (link_location, [friendly_name])

            link_location – It is a path and file name of the document which we want to open using this formula. This path can be to a file that is stored on a hard disk drive or on intranet or internet. This path can also be a UNC (Universal Naming Convention) path on a server or a URL (Uniform Resource Locator) path on the Internet or an intranet. It can also be refer to a place in a document – such as specific bookmark in a word on cell/name range in a excel worksheet or workbook. It is also a compulsory field and if you did not fill any value excel consider it as 0 and does not give any error.

            [friendly_name] – It is an optional field and It can also be refer as jump text. It can be a text or a numeric value that will be displayed in the cell in blue color and is underlined. If friendly_name is not given then the cell displays the link_location as the friendly_name / jump text.

Example – Following example will clear some concept of HYPERLINK function:- 

Here Column A shows the Name which we want to display in Hyperlink and Column B Shows the Destination location of the file or site which we want to open on click and column C shows how to write HYPERLINK formula to get the result. You can write the formula directly as shown in Column D which will also give the same result.

	A	B	C	D	E
1	Display Name	Destination Location	Formula
2	Excelwithease	http://excelwithease.com	=hyperlink(B2,A2)	=HYPERLINK(“http://excelwithease.com”,”Excelwithease”)
3	Example	https://goo.gl/HPv7Fm	=hyperlink(B3,A3)	=HYPERLINK("https://goo.gl/HPv7Fm”,”Example”)
4
5
6
7

 And result you get as under:-

	A	B	C	D	E	F
1	Display Name	Destination Location	Formula	Direct Formula
2	Excelwithease	http://excelwithease.com	Excelwithease	Excelwithease
3	Example	https://goo.gl/HPv7Fm	Example	Example
4
5
6
7

This is how a HYPERLINK function work in excel and results in the text what we want to see and links to the address what we want to use.

Read More

MS EXCEL TRAINING | EXCELWITHEASE.COM

“Today in the time of technology where everyone use computer and MS excel in routine office work but still doesn't know how to reduce the work using the amazing shortcuts of MS excel and its some unbeatable commands"

 Hi Guys!!!

Today all of us are familiar with computer and MS Excel but still doesn't know much more about the awesome commands of MS Excel which really excel your future in any organization where you need to handle data and store the data. Following are the some major commands which meant for data management and the use of these are simple too.

1. IF , IFERROR, IFNA - These are logical test functions and used to what actions is to be performed under defined set of conditions.
2. LOOKUP, VLOOKUP, HLOOKUP - These are called lookup functions and used to lookup / find some type of data with reference to data available in a set of array of data.
3. INDEX - This function is also belong to lookup family but this function is used to index the value with reference of two availeble data.
4. MATCH - This function also belong to same lookup family and used to get the position of the data in any single row or column.
5. LEN, LEFT, RIGHT, MID - These function belongs to text function family and used to get the defined length lenght of a text string or find the total length of the text string written within the cell.

There are more commands also which can further be used to more simplify the work.

Now the question arieses........

From where you can learn these commands ?

Don't worry ! we are here to help you out.

Just visit www.excelwithease.com and learn MS excel in an easy way

Read More

MID

“MID”

Used for – “MID” function in MS Excel is used to pick the specified number of characters starting from a desired position in a text string or in a cell and display the same as a result.

Syntax – MID(text, start_num, num_chars).

Example – Following example will clear the date function:-

	A	B	C	D	E	F	G	H	I
1	EXCEL WITH EASE
2	=MID(A1,1,5)	=MID(A1,7,4)	=MID(A1,12,4)
3	=MID(A1,3,5)	=MID(A1,5,4)	=MID(A1,9,4)
4
5
6
7

And result you get as under:-

	A	B	C	D	E	F	G	H	I
1	EXCEL WITH EASE
2	EXCEL	WITH	EASE
3	CEL W	L WI	TH E
4
5
6
7

Note:- The MID Function counts each character in the text string and if there are any extra spaces are typed in the string it will count them too. So you should be more careful using this function

Read More