Autosum In Excel

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

Keyboard short cut for Autosum is     "Alt+equal sign(=)"

Case 2
What if you want to get the sum of each column  row & grand sum  in one command for the data given below


Generating List Of Sheet Names In A Workbook

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 1 : Insert a new worksheet where you want the worksheet names to be displayed 

Step 2  Goto –VIEW –MACROS-VIEW [Here I am using Excel 2013 so the tabs are slightly different from the version you might be using ;excel 2007 or Excel 2010 .But dont worry :D]



















.








Step 3:In “Macro name” column  please write WorksheetName [This is the name of the macro] 




.





























Step 4 Click On Create Button.You will Get a sceen like this







.























Step 5 Paste the following argument between  “Sub WorksheetName()”   and “End Sub”
Dim x As Integer
For x = 1 To Worksheets.Count
Cells(x, 1).Value = Worksheets(x).Name
Next x 












.










Step 6 :Close the Macro window

Now the macro is ready to run .



How to Run The Macro 



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













Excel Shortcuts


Show All Formula


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 :


Inorder see the equation used in the column D ,just select the column or the entire worksheet and then press  CTRL and ~

To goback to the orginal one click CTRL ~ again

Excel Match

 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
 "MATCH(Value looking For , Lookup Array, Match type)

Value Looking For :- The value that you want to find in the list of data. It  be a number, text, logical value, or a cell reference .

Lookup Array
 :-The range of cells need to be  searched

Match type for exact match is "0".

Example:-


Excel Match




If we want find the position of Vinoy in the list ,we can use Match function.




It is a very useful Function in Excel.

Background-Image-for-Chart-In-Excel

How To Create A Chart With A Background Image 

Eg:-% Of Marks Obtained By Some MBC College Students with College Photo as Background 

Step 1
Arrange the data











Step 2
Select the data and create a chart














.








Step 3
Right click On chart ----Select "Format Chart Area"
Select a background Image

































Step 4
Click on the chart ---Goto "Home" Tab -----Click on "Colors" and Put "Transparency" 40% Click OK

Chart is ready with a background pic











VLOOKUP 

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 .

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

Steps

1 .Select Vlookup function from Lookup & Reference in "Formulas"



2. Click On VLookup function
vlookup excel



3.Select all the Values mentioned in the Table 


vlookup

      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

Combining Cells- Concatenate

In order to combine 2 or more cells we can use a function  called "CONCATENATE" .It is one of the most useful function in Excel .

Syntax: Concatenate(text1,text 2,...)

Example:




Note :For giving space between words, we should give a space in between two double quotes


Watch Window



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]


Steps


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.


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

PIVOT TABLE

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




How to Count Words In A Cell

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 

LEN(Ref Cell )-LEN(SUBSTITUTE(Ref Cell," ",""))+1


How To Insert A Line Break 

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

Close All Worksheets Together 

If you are working with multiple worksheet and want to close all worksheet together then


Press Shift Key and Close any of the  worksheet then all the worksheets will get close together.


Note :If any of the worksheet  have not been saved, you'll get the standard prompt asking "if you want to save your workbook".

Convert Number Into A Roman Number 

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

=Roman(number)

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

Note: ROMAN function returns a text value, and you therefore cannot use the result in any sort of calculation as  it is no longer a number.

How To Delete Empty Rows

Sometimes we get data which contains empty rows in between .Here is the shortcut to delete the empty Rows.



Step1 

Select all columns containing data.

Step 2

Click On Sort Icon [Goto DATA Tab -Sort (either Ascending or Descending)]







TRANSPOSE FUNCTION

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.

Syntax

={Transpose(Array) }

Array - the range of cells to be copied from a row into a column or from a column into a row

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

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

Example


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 .

The Result Is 

Note : If you use Transpose In Paste special It just Transpose the Value but Transpose function is used for  transposing the Cell Value/Equation