Basic excel for beginners Part-1

Basic Excel for Beginners

Excel is one of the most commonly used tool across all companies , no matter how much tech savvy  you become you will always use excel for basic modelling , publishing reports , storing information etc. So for those who have only used excel only as a phone directory so far this could be a very useful post.

Let's begin

First lets familiarize ourselves with the layout of an excel sheet. 

This gives you the list of basic things the excel screen contains . I am not going to go in the details of each thing as they are quite self explanatory. Lets jump into our first hands on with excel.

Time Savers using commonly used Excel shortcuts

The most important rule in using excel is using keyboard as much as possible . The lesser you use the mouse for small works the more proficient in excel you will become. These excel shortcuts will help you become more proficient in using excel. Here are the most commonly used shortcuts.

Commonly Used Excel shortcuts

How to use this excel shortcuts eg. if I want to move to the leftmost end of the data from any point I press Ctrl+Left Arrow , similarly if I want to move down I use Ctrl+Down Arrow , if I want to move between worksheets I use Ctrl+ Page Up for previous worksheet ,Ctrl+Page Down for next worksheet. Similarly we can use the other shortcuts as mentioned in the Table . As you master these excel shortcuts working on excel sheets becomes super-fast.

Power of cell referencing

 Cell referencing is a very important feature. To freeze a cell you put dollar sign next to it so if we have a cell C15 and we want to make sure when we drag the formula it remains unchanged so we put $C$15 ( you can use the F4 key ) this is called absolute referencing. You can also freeze rows or column separately $C15 where column cannot move and row is free to move so if you drag the formula across rows it changes the row but column remains same similarly the other way round C$15 would freeze row and change column.

Using IF function

If function is a real life if something is true then what we do and if it false what we do.

Syntax: =IF(Condition,value if true , value if false)

Lets say if a cell C15 has value 100 then we give it 1 else 0 so we write it like this

=IF(C15=100,1,0) , other eg.

-if value is equal =IF(C15=100,1,0)

-if value is greater than equal too=IF(C15>=100,1,0)

-if value is not equal too=IF(C15<>100,1,0)

Using IF function with logical operators OR , AND

Syntax for each 
AND ( cond1, cond2 ,...)
OR (cond1,cond2,....)
NOT(condition)

eg using AND: if C15 is less than 100 and greater than 80 than give 1 else 0
=IF(AND(C15<100,C15>80),1,0)

eg using OR: if C15 is greater than 100 or less than 80 than give 1 else 0
=IF(OR(C15>100,C15<80),1,0)

Nested IF.

Nested IF is using IF within a if function . Using multiple ifs in the same formula
Eg : Assume C15 cell contains Age then we have to group it like this if Age<10 then Group1, Age between 10 and 25 - Group2 , Age Greater than 25 -Group 3

=IF(C15<10,"Group1",IF(AND(C15>=10,C15<=25),"Group2", IF(C15>25,"Group3", "No Group")))

Here we see if a condition is false we move on to the next condtion . Like if it is not in group1 then we check if it is group2.

SUMIFS and COUNTIFS

When we want to count records based on some conditions we use Countifs, similarly if we want to sum up something on a condition we use Sumifs.

Syntax For: COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

Where criteria_range1 is a mandatory,the first range in which to evaluate the associated criteria, criteria1 is mandatory, it is the criteria in the form of a number, expression, cell reference, or text that define which cells will be counted eg, criteria can be expressed as 90, ">90", B4, "apples", or "80". criteria_range2 is an optional parameter and can be added if there are multiple conditions

Syntax: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Where sum_range is range to sum (always remember its the first range in sumifs and mandatory). Criteria_range1 is mandatory and refers to the range which has criteria, Criteria1 is the criteria that is to be used criteria can be expressed as 90, ">90", B4, "apples", or "80". Rest is optional and we can add as many number of criterias as possible.

To calculate the count of Group1 , we use =COUNTIFS($A$2:$A$10,$A$2) , where A2 is the criteria which says group1, we can also use =COUNTIFS($A$2:$A$10,"Group1")

To calculate the sum of Group1 , we use =SUMIFS($B$2:$B$10,$A$2:$A$10,A2) , where B2:B10 is the sum range which is always number and A2 is criteria again you can write it as text
=SUMIFS($B$2:$B$10,$A$2:$A$10,"Group1")

Now adding one more criteria lets say for group1 where money > 5000
=COUNTIFS($A$2:$A$10,"Group1",$B$2:$B$10,">5000")
=SUMIFS($B$2:$B$10,$A$2:$A$10,"Group1",$B$2:$B$10,">5000")

Data retrieval using lookup funtions

Now suppose if we have a list of companies and there market capitalization next to them and we want to find a market cap of some particular company we filter again and again to avoid that we can use functions called Vlookup and Hlookup 

Syntax: VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]) 

Lookup value is the value you want to lookup in the array.The table array is the array where you want to look ,please note for Vlookup to work the first column should contain the lookup_value. The column number (starting with 1 for the left-most column of table-array) that contains the return value. For range_lookup you can put FALSE always as it looks for exact match.

Eg: We have to find the age and salary of EMP5

For Age: =VLOOKUP("Emp5",$A$1:$C$12,2,FALSE)---->(Col_index is 2)

For Salary: =VLOOKUP("Emp5",$A$1:$C$12,3,FALSE)---->(Col_index is 3)

Syntax: HLOOKUP (lookup_value, table_array, Row_index_num, [range_lookup]) 

Searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array. Use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows. Use VLOOKUP when your comparison values are located in a column to the left of the data you want to find. The H in HLOOKUP stands for "Horizontal."

Eg: We have to find the age and salary of Emp1

For Age: =HLOOKUP("Emp1",$A$1:$E$3,2,FALSE)---->(row_index is 2)

For Salary: =HLOOKUP("Emp1",$A$1:$C$3,3,FALSE)---->(row_index is 3)

Applying Vlookup for two columns

Now to apply Vlookup to two columns the best way is to form a unique key . For eg:

if I Vlookup for the salary of Andrew I will get 20,000 as answer . But I wanted to check salary of Andrew Flintoff and not Andrew Hall so to avoid that I create a key by concatenating the first and the last name(Eg: ="fname" & " " & "lname") and now I can Vlookup on the key . Make sure the key is always the first column which is the requirement forVlookup as discussed earlier .

This is very useful as we can combine n number of column and create a unique key and perform Vlookup on it. There are other ways to Vlookup on more that one condition we can discuss this later.

We will discuss more useful formulas in Part2.