Auto adjust the height or Word Wrap

1. Normally when we paste the values in the excel sheet the rows

1.1    heights are increased ; or 

as shown below

1.2        complete sentense is not shown in the cell

 as shown below

Reasons:  
2.   Either formatting of cell is 

2.1 set as " word wrap"

2.2 Not "Left alligned"

3. Solution for automatically adjust the height of all cell

3.1 first "Allign the left side " 
( "Alt+H+A+L" i.e Press "Alt" then "h" then "a" then "l")

 

         3.2 then if there is word wrap then cancel it by clicking the                     wrap icon

                                 ("Alt+H+W" i.e Press "Alt" then "h" then  "w")

                        Its done as below

Summary

1.   Alt+H+A+L" i.e Press "Alt" then "h" then "a" then "l")

2. ("Alt+H+W" i.e Press "Alt" then "h" then  "w")

INDEX MATCH Formula in Excel with Step by step illustrations

INDEX MATCH FORMULA

This function is very useful for that kind of job in which we have to find the things from one table and put in front of values present in other table.

Summarising the complete Post

1.              Write Index formula

then

2.     Replace the Row_no part of INDEX formula with MATCH formula.

Comparison of two Formulas

1. Index match 
2. Vlookup 

Index match and Vlookup both are similar. Using both in practical examples it is observed that  index match is better than Vlookup  formula.

          Because;

1. In the Index match formula lookup value can be from any column whether they are in right side or the left side of column containing our required value.
2. It requires only two columns :

                   a). Column in which Lookup value is present.

                   b). Column from which Resultant value is taken.

 However

1. Vlookup   formula works only from left side to right side of table.
2. It requires a continuous array of table and if table contains many column or some column are hidden then it becomes very difficult to calculate index_no for our formula. 

First method (Index Match formula) 

Let we want that when we write “NP”, The formula give us value “7875” As shown in the following fig.

It can be done by using two formulas

          1.     Index
                   2.       Match

1.    INDEX

Syntax
=INDEX(array, row_num, [column_num])     

1.    array :Range from which we have to select our value.(in our example 3rd column i.e “C5:C12” (No. of employees) is the array because we need 7875 from it.)

2.      row_num : Row Number in which 7875 is present( In above example  NP is on 3rd  row so we write 3 in index formula.)

=INDEX(C5:C12,3)

           It will give us 7875

Important thing in the above formula is 3 i.e 3 is Input for Index formula.

2.    MATCH

Syntax

MATCH(lookup_value, lookup_array, [match_type])

1) lookup_value: The value that we want to match in lookup_array. For example, when we look up someone's number in a telephone book, we are using the person's name as the lookup value, but the telephone number is the value we want. (In the given table abov, lookup value is NP.   
 {lookup_value in Vlookup and indexMatch formulas has same nature.}

2) lookup_array: The range of cells being searched. In the given table above, lookup array is A5:A12

3) [match_type]:zero “0” for exact match. It is recommended.

                

a)    =MATCH("NP",A5:A12,0)  or;
b)   =MATCH(F7,A5:A12,0)

(We have used the reference of the cell in second formula. F7 is the cell reference where NP is written.)

Both (a,b) will give us 3

Summary of both formulas

INDEX MATCH

INDEX(C5:C12,3)

MATCH(F7,A5:A12,0)

Now we will copy the complete match formula and paste in place of 3 in the Index formula.           

It gives us following string.

=INDEX(C5:C12,MATCH(F7,A5:A12,0)

                                        

As discussed earlier important thing in the Index formula is 3. Instead of writing the 3 we have used Match formula in its place this formula gives output 3 , which is used as Input for Index formula.

Summarising the complete Post

1.              Write Index formula

then

2.     Replace the Row_no part of INDEX formula with MATCH formula.

Vlook up Formula in Excel with Step by step illustrations

VLOOK UP

This function is very useful for that kind of job in which we have to find the things from one table and put in front of values present in other table.

Comparison of two Formulas

1. Index match 
2. Vlookup 

                   (As discussed in discussion relating  Index match )

               Index match and Vlookup both are similar in their work .
                 Using both in practical examples it is observed
                           that  Index match is better than Vlookup formula.

                                     

                                              Because;

1. In the Index match formula lookup value can be from any column whether they are in right side or the left side of column containing our required value.
2. It requires only two columns :

                   a). Column in which Lookup value is present.

                   b). Column from which Resultant value is taken.

 However

1. Vlookup formula works only from left side to right side of table.
2. It requires a continuouse array of table and if table contains many column or some column are hidden then it becomes very difficult to calculate index_no for our formula. 

Second method (Vlookup formula) 

Let we have a table having three columns first column contains the company names second column contains abbreviation of  company , and third name contains the total number of employees working in that company.

 

Now we want the excel put automatically the number of  employees if we  write the abbreviation of the company in a blank cell. Or;

We want the excel put automatically the Name of company, if we  write abbreviation of company in a blank cell.

As shown below writing the Abbreviation  "acl" in cell F7, Vlookup formula gave result in cell I7

Syntax

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

1.    lookup_value : It is the value about which we want the information.(Values in “F” column e.g acl, BATAA)

2.  table_array: It is the table from which we want to extract information. (A5:A12 is table array for our exapmple.)

3.  col_index_num: It represents the Number of column from which we want to extract information (In our example 3^rd column is our required information)

4.    [range_lookup]:  What kind of match we want .i.e for exact match select “False” we have selected “False” )

Write formula in the pattern given below.

                       
                       Its done......

For any query and or formula you can write in comments section.........

Fill blank cells in excel with value above

Fill blank cells in excel with value above 

If there is a data in a column and you want it to be automatically fill down in the blank cells in excel it can be  done in following very simple 7 steps.

Step 1 for Fill blank cells

  Select the column you want to fill down in the excel sheet.

Step 2 for Fill blank cells

 Press F5 button on the keyboard.(this step is the short cut way for “Go to” box
Or press “ctrl+g” keys on the keyboard

Now a message box naming “go to” will appear on the screen .  

 

 

Step 3 for Fill blank cells

      Click on the special (left bottom of the box)

                Or press “Alt+s” keys
 
A new window (go to special) will open

                                         

Step 4 for Fill blank cells

     Click on Blanks      

  Or press “Alt+k”

           

Step 5 for Fill blank cells 

       Press OK button

(in this way from 1^st to 5^th step we have selected the Blanks sells in our selected area)

.                       

 

Now the important task starts

Step 6 for Fill blank cells

     Press Plus sign “+” or equal sign “=” same way we write the formula in cell.
 

Step 7 for Fill blank cells

                         Press up arrow

   

Step 8 for Fill blank cells

                            Press "Ctrl+enter"

                                    Its done

      For more clarification video link is:

https://www.youtube.com/watch?v=QeQ3OI44Mt0