Name Range in Excel

What is a Name Range ?

Name Range in Excel, a name  to any cell, a range of cell, selection of a area, after naming the cell or area or a range of cells we only need to remember the cell or area reference by its name not from the cell or range reference.

Benefits of Name Range

1.   After naming the cell or range or selected area we do not need to remember its cell address or      

      reference.

2.   After naming the cell or range or selected area, now it will becomes very easy to put name in    

      formula and function.

3.   Name Range data always remain constant and does not change its cell references.

Below are some methods of Name Range Data

  1.   Using the Name Box
  2.   Using the Create from Selection Command
  3.   Using the Define Name command

Below we will discuss the the each of above mentioned heading of Name Range in Excel

1. Using Name Box

In Excel, we can create a name range by selecting a cell or a range of cell and typing a appropriate name in the "name box" field.  See the image below

In the below given image we have data set, in column 1st we have "Salesman" and 2nd column we have "Sales figure" of "Sales_value". So we will name range in this data that from range "B2:B5" we give a name as "Sales_Value" and type this name in the field of "name box"

Image 01

See below image :-

   

Image 02

In above image we can see data range "A2:A5", so according to name range rule we can give a name to this entire range by selecting the whole range area and typing the name in the name box field "Student".

So whenever we need to write any formula, then we do not need to give the entire range, then just name range require, like the whole area = "Student"

We want to sum sales value of all the Student, so the formula will be :-

=Sum(Sales_Value) or =Sum(B2:B5) bot are same.

Without giving a sales range, we mention directly "Sales_Value" instead of "B2:B5" and get the same result.

Image 03

2.   Using Create from Selection Command

This is the second method of creating name range. If we have a data set then for creating a name is simple process by selecting the data 

First we need to select the data range and then 

Image 04

go to formula tab, and click  on "Create from Selection"  or short cut key is "CTR+SHIFT+F3" see below image 

Image 05

Once the "Click" the following image will appear :-

Image 06

In this image we can see that there are two image 1st image is "data range" and 2nd image is "how to create name range" image. in this 2 image "Top row" and "Left column" both are checked it is suggesting us that if we press simply "OK" then it will create a name range. All the heading of all "Row" and "Column" will be names of their respective rows or columns."Top row" heading "Salesman", "East", "West" etc. and "Left column" heading will be "Amit", "Rahul" etc. See below image :- 

Image 07

When we select a rows from "B3:E3" it gives a name of this range as "Rahul" as "Left heading" and in second image when we select "C2:C5" it gives the name "West" as the "Top row" heading

So this is another example of  Name Range" of cell or range of cells.

 3.   Using Define Name command

There is another method for creating a name range in  Excel. We have a set of data as given in the following image :-

Image 08

For creating the name range with the help of "Define Name" method we have to go "Formula Tab " and click on "Define Name" drop down and click on "Define Name"

 

Image 09

We will get the following image

Image 10

From starting, see "New Name" name is "West" and then scope "Workbook" or "Sheet no." and at the end "Refers to:" "C2:C5" and simply press enter. And the name range is set for range "C2:C5" is "West"

In the field of  "New Name" instead of West we can put any name according to out need but there are some condition for putting a name and the condition are as follows:-

   

•  Name should not contain more than 255 character and begin with a letter, an underscore( _ ), or backslash(\) and should not contain any space

• Name  should not conflict with cell reference like "A1" or "B21"  or so on.

• Single letters will be OK for name like "A", "B", "F", etc), but "R", and "C" are not allowed they are reserved and "R=Row" and "C=Column"

Edit a Name Range

Some time if we have to edit our name range then what to do?

So in this case we need directly go to "Name Manager", see image below :-

Image11

and simply click on it we will get the following pictures :-

With help of above image we can easily modify or edit our name range.

Delete a Name Range

Now if we want to delete a name range  in excel simply go to "Name Manager"  and click we will get the following image and just click delete on it, the name range will be deleted. See below :-

So in this tutorial we learned about name range in excel. Name range has many more uses if we will go into deep of name range, we will learn more about it.

Thanking you.

Regards

NarendrasExceltips.blogspot.com

Excel Data Validation

Excel Data Validation is a option in excel by which we  can restrict the operater by typing the false entry in the Excel sheet, it has some criteria for user and from this criteria or condition the user can not enter the wrong input.

We will understand this by following example:-

Go to Data Tab -->

Image 01

In this this image we can see that in the Data Tab --> Data Validation and when we click on Data Validation following image will appear

Image 02

In this 2nd image, there are three table ---> "Settings", "Input Message", "Error Alert" and then Validation criteria "Allow" :- Any Value

When we select a area in excel and apply this condition in data validation, this criteria accepts all the numbers or text, it does not have any restriction to the field. But when this feature will  restrict us to put incorrect data, we will learn in further in this  blog.

Now come to the second image :-

image 03

This is the all list of data validation :-

"Whole number", "Decimal", "List", "Date", "Time", "Text length", "Custom".

We  will discuss all of the above given option one by one.

"Input Message " is the second tab in the Data Validation box and the third one is "Error Alert"

Input Message box in Data Validation

Image 04

"Input Message" box display the message about the cell or area containing the validation about, like in the above image we can see that we  have a message like in "Title = => Serial No." and in the "Input message" box "Only put serial no. 1 to 10" and same we can see above in the excel coloured area, when we put our cursor into cell "C1" it displays the message as we put in the "Input message" box.

Error Alert box in Data Validation

See the image below :-

Image 05

Now in this Data Validation "Error Alert" message box,  we notice that we have checked the check box "Show error alert after invalid data is entered" if we do not check this check box then the message will not be displays.

Now come to "Style" and the option are "Stop", "Warning" and " Information" all the option are available for we that in which option we want to see the message from these three option.

Then "Title" is that what title we want to give that error message, we have put Wrong Input.

Then "Error Message" what message will pop up when someone will put wrong data in the validation field.

See the  below image :-

Image 06

We put "11" in the coloured field that field has validation and the message is showing that "Wrong Input" as the error title and message is "Please enter the no. between 1 to 10 only" this coloured area has validation to put data only from 1 to 10 not beyond this.

Criteria Examples of Data Validation

Any Value Data Validation

If we select this option in data validation that means we can put any data into the data validation field whether a numeric or a text one.

Whole Number Data Validation

Now come to second option Whole number, if we choose this option that means we can specify the maximum and minimum range in the validation area, see the image below :-

Image 07

We have set the limit between minimum "1"  and  maximum "10" the data validation field will accept the data with-in this range only not apart from that.

Decimal Data Validation

In this image we can see that we have set the data validation value  from 100 to 125 and this has a additional feature that it can accept the decimal figure also, like in the below image we can see "100.253" and "124.362" but not greater than "125". It accept the value in decimal also.

Image 08

List Data Validation

Now move forward to another option and it is "List" option in data validation. See the image below 

Image 09

In this image we can see that in cell "A1 to A3" we have put some name and now  the cursor position in cell "B1". In the "List" option in data validation, select "list" from allow field and type all the names in the "Source" field or drag the name range from "A1:A3", result would be same from both option and press "OK" then go to cell "B1" the following image will appear.

The drop down menu have been created by simple pressing "OK" button.

Image10

In the above image we can see that when we click on cell "B1" then a drop down menu list open with containing all the list details of from "A1 to A3".

Date Data Validation

In the below image the selected area has a data validation for the "date range". From start date contain the date range of cell "A1" and end date range contains the date of cell "B1".

Image 11

If any other date will be entered in the selected validated area the date will not be accepted by excel.

Time Data Validation

In the below image we have put the time range  in start time and end time and the selected area will not accept any time range apart from this time range.

Image 12

Text Length Data Validation

Text length validation option gives a condition that we can put the  text into a validation area, that we can only put the data according to the condition as shows in the below image.

Image 13

And if we try to write something beyond our validation condition  then it does not allow us to do that by showing below error message ,

Image 14

Custom Data Validation

Custom data validation is the validation when we put our own condition in the formula point and get the result as appear in the below image :-

Image 15

And if we enter the value beyond  criteria then it shows the below message :-

Image 16

In this Custom Data Validation option we can create more formula's according to our need. 

Circle Invalid Data

If we have a data range and this data range contain a data validation of any given range as in the following image :- 

Image 17

In the above image we can see that we have given a range from 100 to 150, so the data range will accept the data from this criteria only but if there is any data beyond this range it will circle the cell, which is containing the the wrong value.

How to do that, just see below instruction and image :-

Excel Data - ->Data Validation --> Circle Invalid Data 

Image 18

and once you click on the "Circle Invalid Data" then the following result will come out from the selected data range which is containing the data validation.

Image 19

In this image we can see that which figure is beyond our data validation limit i.e. 100 to 150, the "Circle Invalid Data" option put a circle around that figure and again if we want to remove the circle then we have to follow this command. 

Go to Data -- ----  Data Validation ---- Clear Validation Circles and click on it.

Image 20

Once you click on the button "Circle Validation Circles" we will get the following image :-

Image 21

All the  red circles have been removed.

How to Remove Data Validation

Now we have learned all the option in data validation and now we will learn about how to remove data validation. Now simply follow the step 

Click Data ---- Data Validation ---- Click on Data Validation button and then click "Clear All" button, appearing below in the given image and then "OK" 

Image 22

All the data validation have been removed from the selected area and we can work on Excel as normally.

There is one more way to remove data validation from the given range. Simply go to a blank cell which does not contain any validation and copy it the go to the validation area and select all the validated area and right click as mentioned in the below image and clikc on  paste special.

Image 23

Once you clik on paste special you will get the following image :-

Click on the "Validation" radio button and simply press "OK" and we are done.

This is very easy way to remove data validation from easy step.

Thanks and Regards

NarendrasExcelTips.blogspot.com

Excel Match Function

Match Function

In this tutorial we will learn about Match Function in Excel with some example and we will also learn a more about how this function works when we combine this function with Vlookup and Match.

Basically Match Function searches for a specified item in a range of cells and then returns the relative position of that item in the range.

So lets start with the following example:-

Image 01

In this image we can see that this is a table which contain Column A "Product" and in Column B "Count" and in Column C there is a product name from the product list "Apples". The use of Match Function is to find the position of "Apples" in the list and in this list the position of "Apples"  is "3".

Now we will learn by break the argument of Match Function as follows :-

Syntax

MATCH(lookup_value, lookup_array, [match_type])

The MATCH function syntax has the following arguments:

lookup_value - The value to match in lookup_array.

lookup_array - The range of cells being searched.

match_type    -  Optional. The number -1, 0 or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1.


If Match_type is 1, [Match finds the largest value that is less than or equal to lookup_value and it requires sorting the lookup_array in ascending order, i.e. from small to large or from A to Z.]

If Match_type is 0, [Match finds the first value exactly equal to lookup_value  and lookup_array argument can be in any order.]

If match_type is -1 [Match finds the smallest value that is greater than or equal to lookup_value. The lookup_array must be sorted in descending order.]

1. Match returns the position of the matched value within lookup_array.
2. Match returns the #N/A error if no match is found.
3. Match is not case-sensitive.

If the lookup_array contains several occurrences of the lookup_value, the position of the first value will be returned.

We can understand this a little by the following example :-

Image 02

In the above image we put all three kind of formula (1,0,-1)

In the 1st formula, lookup_value is "35" but in the Column B there is no value appearing as "35", so in this formula we put match_type "1" because it will return a position of nearest and below of  value "35" and the position is 2nd, i.e. below "35", "34" is the nearest and below "35" value. So the  result is "2"

In the 2nd formula, lookup_value is "34" and Column B has the value "34" so we put match_type "0", and it gives us position of "34" is  2nd. So the result is "2".

In the 3rd formula, lookup_value is "52" and which exist in Column B but when we put  match_type (-1)  the result is  #N/A, The reason is #N/A is because as per the rule if the match_type "-1" then we have to sort the range in the form of descending order, so once we descending  the Column B then see what will be position of "52" 

Image 03

The position of "52" in count column in "1" and now we will learn formula combination with Match function.

Match Function with Vlookup

Following example is the best example to know that what is the use of Match Function with Vlookup.

Cell "G2" has a Vlookup formula which is showing in the  upper formula bar.

Here we can see that this is a simple Vlookup formula which returns a "Unit Price". Now we want to know that if we insert a new column between "Product Name" and "Unit Price" in this table, so will this effect to our result ?

Image 04

Lets understand in a new image :-

Now what we will do just insert a new column between Column A and Column B and see what will happen with the result "Unit Price" see the image below :-

Image 05

Here we can see that when we insert a new columns between Column A and Column B, the result of "Unit Price" have been change to "Exotic Liquids" form "19" as appearing in above "image 04" and which is not correct. So how to get the correct result.

Now in this situation "Match Function" comes in rescue.

Now we will combibe the the Match Function with Vlookup and then see what happened :-

Image 06

 

This image is the same as "image no 04" but in this image we get the result with the help of Vlookup and Match,  we can see the formula bar the formula is loaded by "Match Function". We have seen that there is no change in the result. It is same as we get by the single Vlookup.

Now we will do the same practical as we did in previous image to insert a new column bewtwee Column A and Column B and see what happen :-

Image 07

Now in this image we can see that we added a column "Supplier" between Column A and Column B. But after addition a new column we can see that there is no change on result, it is as before. So match function does not care about delete or add new column.

Now it is clear that the role of Match Function is to look for the matching figure and in this table Match Function search for Unit Price from the list A1:E1, and it does not matter that how many column we add or delete. The formula will produce the result accurately without any cell or column change or change in result.

Now this is clear that if Vlookup and Match function will work together, it will produce the dynamic result and one another benefit is that it prevents from error.

I hope this post has helped you understand how the Vlookup and Match function work together and produce a dynamic result.

Regards 

NarendrasExcelTips.Blogspot.com

Index Function

     Today we will learn about Index Function in Excel. Index Function in Excel is one of the most useful function. When we combine this function with another function like Match etc, it will produce the dynamic result.

The INDEX function returns a value or the reference to a value from within a table or range.

In the below image we can see the Index function has two different formula like :-

1.   Index (Array, Row_num,[Col_num]

2. Index (Reference, Row_num,[col_num],[area_num]

In the first formula it is Array and in the second function in place array it is Reference.

We will give separate example for both of the format.

Array form

We can see that some values are in the bracket like [column_num] and in second function [column_num] and [area_num].  i.e. if these values does not exist then we will consider these values as "0" and the function will produce the result by without these values.

Image 1

We will understand the use of Index Function in Excel by following example

Example 1.

Image 2

In the above image when we put the Index Formula such as:- = index(A2:E10,5,4)

In this example we can see the Index Function parameters like (Array, row_num, col_num)

Where  Array is                (A2:E10),

Row_num      is                     5

Col_num        is                     4

Simply the pure explanation is we want the result from area A2:E10, where Row_num is "5" and Col_num "4" and simply answer is "13". We can see red circled, the above image is the intersection of the Row_num and Col_num.

Example 2.

Image 3

In this example we can see the range is (A2:C11) where row_num is "6" and column_num is "3"

Result is from range (A2:C11),row_num(6),column_num(3)= "1943"

In range (A2:C11) intersection of row_num=6, and column_num=3 is "1943"

Example 3.

In the below given example, we can see that row_num and col_num  has the cell reference like:- "D2=Row_num" and "D3=Col_num"

Image 4

In this example if we change the "row_num" and "col_num" according to need then we do not need to change the formula, it will produce the result automatically, "Just try it" because in this example "row_num" and "col_num" are cell reference. So we can change the value.

Reference form

In the second part of Index Function, the first parameter is reference, which is supplied as a reference to one or more cell range. The syntax of reference is :

Index (reference, Row_num.[col_num],[area_num]

Where [col_num] and [area_num] is optional

See the following example to know it better :

Image 5

=INDEX((A2:C5,A7:C10),2,2,1)

In this image we can see that the whole range is divided in two section such as "A2:C5" and "A7:C10"  then come to "row_num" (2) and column_num(2) the and the last section is "area_num"

which is the important in this example. "Area_num" is telling to us that the figure we are looking for, is in which area, whether it is area_num =1 ("A2:C5")  or area_num =2 ("A7:C10")

In the above example if we put "2" in the area_num the result would be "Vaucheron & Constantin 18K" but if we put "1" in the area_num then result would be different i. e. "Cartier Tank 18K"

In our further tutorial we will learn to combine the Index Function with the other function like "Match", "Count" and will see that what kind of the result we will get.

Regards

NarendrasExcelTips.blogspot.com

How to Record a Macro in Excel

Recording a macro in Excel VBA is a very very useful tool. Why we use this tool to record a macro?

Reason is very simple that if we are doing the same task in Excel and again and again, then macro recorder comes in existence. We use macro recorder for reducing time taken in our daily task.

By the below given image we will understand macro recording step by step:-

Open the Excel and go to Developer tab and click on it.

                                                     Image 1

And then click on the Record Macro and following image will appear

                                Image 2

Once you click on the "OK" button in above image then macro will record everything, whatever action or step you will do in excel.

Main thing about macro recording is that it will record every single step, we will take. So my suggestion is plan first  to record macro and then record it and if you will not make a plan and start recording, it will record unnecessary step also.

Below is the details about above circled number in macro recorder box.

1. Macro Name

Macro name box where we can type the name of our macro, the first  character of macro must be a letter and macro name can not have a space like "My first macro", it should be "My_first_macro" and macro names are not case sensitive.

2.Assign Shortcut Key

In serial no. 2 we have to give a shortcut key for when we run the macro by key, we simply press the shortcut key and macro will run.

3. Store macro in

Third no. is store macro in, there is a drop down list. and we need to select the file from "Personal Macro Workbook, "New Workbook", and "This Workbook".

This Workbook.

Save the macro in the  current workbook, we are working with and macro will work on that file only.

New Workbook

Create macros that you can run in any new workbooks.

Personal Macro Workbook

We will choose this option if we want the run macro in every file we will work, it does not matter on whatever file we are working.

4. Description Box

In this box we will type the description about the macro that for what purpose the macro is created but this is optional not necessary.

Please the see the below of "Macro Recorder Box" image of figure entered.

                                                           Image 3

And now simply click on "OK"

Once you click "OK" then we will see that the record button will changes into "Stop
Recording" button, this means now Excel has started record our steps and when our step for macro recording is finished then we simply click on this "Stop Recording" button and macro is recorded successfully.

Simply I did something in excel for "Record a Macro" in below image :-

                                 Image 4


I type "a" in cell "C5" and "b" in cell "C7" and click on Stop Recording button, and see how a macro recorder record a macro in below image:-

                                        Image 5


First it type the Macro name " Sub Macro4 ()" and then record below mention thing like, first it select cell "C5" and then type "a" and then select cell "C7" and type "b" and then select cell "C8" and then I stop recording by pressing "Stop Recording Button" as appearing in the below image.

 

                                                               Image 6

And once we press stop recording button the macro recorder has finished its job, macro recording is done.

Now if we want to check our macro, simply go to Developer tab --> Macro and click on the "Macro" button

                                                                           Image 7

Once you click on the "Macro" button, the following image will appear

                                                                   Image 8

We will see that  macro name, we put "Macro_name" and below "Macro in"  means will work on the, the workbook we selected that time when we record the macro, select "This Workbook" and press "Run" button the will get the following image

                                                                          Image 9

Both the figure comes automatically in the cell "C5" and "C7" and another way to run this macro is simply type "Ctrl+Shift+L" because we have given a shortcut name to our macro. See Image 3.
And we will get the same result. try this...

This is the tutorial about "How to Record a Macro in Excel". and I will come soon about a new topic.

Regards

NarendrasExceltips.blogspot.com