you may come up with situations to lookup the data in a same work
sheet or different work sheet or sometimes even from a different
work book .
common lookup function is the VLOOKUP [Vertical Lookup].
Excel VLOOKUP is
one of the most useful function, and unfortunately it’s also one of the least understood.
Here I will explain how to use Vlookup with some simple examples .
The given is the list of items our company sells
In this example I have mentioned only 4 items .But in real situations you
might have 1000’s of different items.
Now here is a situation where we just know only the Item code and we want to
know what is the Item name .
so in this situation we can use Vlookup function
1 .Select Vlookup function from Lookup & Reference in "Formulas"
2. Click On VLookup function
3.Select all the Values mentioned in the Table
Here Lookup value is the Item Code . Which is coming in the A11 Cell Table_Array -Is the Reference Table which we are looking for the Item .So we need to select the entire table [Table Name -List Of Items] Col_Index_num - Is the Column number in the table -array from which the matching value should be returned .Here Col_Index_num is "2" ,where the item name is given. Range Lookup -Is a Logical Value .For Exact match we should use FALSE or 0 And for approx match we should use TRUE or 1 4 Click On OK We will get the following result
This is a simple example for Vlookup function and in the coming blog we will see some complex situvations where we can use the Vlookup function
You can use watch window to keep track of data in a particular cell or range of cell even when the cells are no longer in view .This is especially useful when you want to keep your eye on a specific formula or cell you use to create a formula .You can also use a watch window to keep track of particular cell's data as you work with the rest of your work sheet.
You are running a stationary shop and entering the stock which you are planning for the next month .After entering each item you want to know the total amount of entire stock .Here you can use watch window on the cell where we are calculating the sum of all stock.
Here you want to keep an eye on Total stock amount [E15 cell]after each item entry . [E15 cell is the sum of all stock amount]
Click Formula Tab then Click On Watch Window
Click on the particular cell we want to watch ,here it is E15
Select the cell or Cells you want to watch .You can also type the cell reference.
Then a new window will come which will show you the value of that particular cell along with the formula applied in that cell.
If we enter a new item or change the value of any item we can always see the total of all stocks in "Watch Window"
Note : You can add multiple cells to the watch window
Pivot table in excel is the most powerful and very useful feature of
Microsoft Excel .It is a great tool for sorting and summarizing the data
in a spreadsheet. It be used to summarize, analyze, explore and present
your data in a meaningful way .
I have seen many people are not using Pivot table as they
think that it is a very tough tool to learn & use .Even i thought the same some
years back .
Instead of analyzing countless spreadsheet records, a pivot
table can aggregate your information and show a new perspective in a few
Given below are ID card details of some of my batch
mates .The information provided in the ID card are:-
Using Pivot table we can resolve so many queries we have
regarding the information given in the table
How many students
have got the same blood group and their names
How many are in the same age
Which are the
different branches etc etc etc....
How to make Pivot Table
Goto Insert Tab-Pivot Table
Click on Pivot Table
Then Select the Range of Cells
Please select New Work Sheet
We will Move to a new Sheet Like This
So Pivot Table Is Ready .Now We are going to Resolve our Queries
1.How Many Students Have Got The Same Blood Group And Their Name
You Have to Drag and Drop the concerned Field (Blood-group & Name ) Into Raw Label
II) How many Are In The Same Age
III) Different Branches ,Names & Age
Another Presentation for the same will be
This is Just the
starting of Pivot Table .We can do filtering Additional formatting etc in
our Pivot table ,That we will discuss in the next Section
Note :You can easily change the pivot table summary
formulas. Right click on pivot table and select “ summarize data by”
In most of the programs you have to click Enter Key to move the cursor to the next line .But in Excel if you press enter then the cursor will jump to the next cell .But if we want not to go to the next cell ,but in the same cell & want to type something in the next line
Then Press ALT+ENTER
Note :While using formulas pls use char(10) instead of ALT+ENTER