__Autosum Command__

What you do
the most frequent in excel ?That is definitely the adding up numbers .The shortcut for doing the same is Autosum in excel.

.

.

What you do
the most frequent in excel ?That is definitely the adding up numbers .The shortcut for doing the same is Autosum in excel.

.

.

Microsoft Excel
doesn’t provide a direct way to generate a list of sheet names in a workbook. Here is a small macro to generate the list of all the sheets in a workbook.

First you open a workbook which contains lot of
worksheets and follow the steps

.

Step 6 :Close
the Macro window

Now the macro is ready to run .

goto “VIEW”
Tab- MACROS-VIEW MACRO -RUN

You can view all the formulas used in the worksheet by simultaneously Pressing **CTRL** and **~ **

This symbol **~** usually found on the key to the left of number 1 key in the keyboard

eg :

To goback to the orginal one click **CTRL ~** again

Excel **MATCH
function** searches for a value in an array and
returns the relative position of that item.

Match function in excel is used to searches for the value of item in an array and returns the relative position of that Item.

Syntax of the match function

Example:-

Arrange the data

Select the data and create a chart

.

Right click On chart ----Select "

Select a background Image

Click on the chart ---Goto "

Chart is ready with a background pic

Sometimes
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 .

The Most
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 .

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.

Example :-

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

Select the cell or Cells you want to watch .You can also type the cell reference.

Click Add

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
clicks.

Example :

Given below are ID card details of some of my batch
mates .The information provided in the ID card are:-

- Name
- Age
- Branch
- Blood group

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....

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

You Have to Drag and Drop the concerned Field (Blood-group & Name ) Into Raw Label

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”
option

Sometimes we might come into a situation that we should know what is the the number of words in a particular cell .

Eg: Given below are the name of 4 engineering colleges in Kerala .Here we want to know how many words are the in the name of each college

The formula for finding out the number of words in a cell is

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

Press

Some times we need to enter details in roman number .Here is the simplest way to convert a number into Roman Number

You can only use any number between 1 and 3999. (Romans never worked with numbers outside this range.)

Transpose function is
a very useful function and it is used to transpose a data which is in a column
to a raw or vice versa .Ie,it help to transpose a selected array.

The
TRANSPOSE function should be entered as an array formula in a range that has
the same number of rows and columns that equals the number of columns and rows
of the source array respectively.

The curly braces " **{ }** " surrounding
the function indicate that it is an array formula.

Press **"Shift + Ctrl+ Enter"** to make the curly braces {}.

We have a list of Engineering branches and all the entries here are vertical entries or they In a same column .We want to change that into same rows /In a horizontal line .

Select C9: G9 and Type the below mentioned Transpose formula .After typing the formula please Press "**SHIFT+CTRL+ENTER**" to make the formula into an array formula .

Subscribe to:
Posts (Atom)