ADVANCE EXCEL TUTORIAL

VLOOKUP FORMULA

HLOOKUP FORMULA

PIVOT TABLE

IF FORMULA IN EXCEL

SUMIF AND SUMIFS FORMULA

HOW TO FILTER IN EXCEL

ADVANCE FILTER

EXCEL DASHBOARD

CUSTOM VALIIDATION

EXCEL DROP DOWN LIST

FORMULA CONCATENATION

CONDITIONAL FORMATTING

LOOKUP AND REFERENCE FUNCTIONS

ROW FUNCTION IN EXCEL
This function is used to return the selected row index number.
Syntax: =Row(E6)


ROWS FUNCTION IN EXCEL

This function is used to return the total number of selected rows.
Syntax: =Rows(A1:C6)


COLUMN FUNCTION IN EXCEL

This function is used to return the selected column index number.
Syntax: =Column(T5)

COLUMNS FUNCTION IN EXCEL
This function is used to return the selected column index number.

Syntax: =Column(B1:G6)

MATCH FUNCTION IN EXCEL
This function is used to find a particular value (Lookup_ value) in a given number list (Lookup_array)

FINANCIAL FUNCTIONS IN EXCEL

PMT FUNCTION

PMT stands for "Payment Made on Time"' and this function is used to calculate payment for a loan, that has a constant payment and constant interest rate with help of (Rate, Nper, and PV)

PMT FUNCTION ARGUMENTS
Rate: This is loan interest rate

Nper (Number of Periods): This is used to select the total number of loan periods.

PV (Present Value): This is a loan amount.

PMT FUNCTION EXAMPLE
Let's start PMT function with the following information.

Formula Explanation: In the above example we inserted data as ...
1. Loan Amount Rs. as PV
2. Interest Rate as Rate
3. Time Duration In Year as Nper

Note: PMT Function takes Nper as a month, therefore, we are multiplying Time duration with 12 and provide the output as a negative amount, therefore, we added (-) minus sign with PV value.



PV FUNCTION

PV stands for "Present Value" and this function is used to find present value with help of (Rate, Nper, and PMT)

FV FUNCTION

FV stands for "Future Value"' and this function is used to calculate the future value on an invested amount.

LOGICAL FUNCTIONS IN EXCEL

LOGICAL FUNCTIONS IN EXCEL

AND FUNCTION

This function is used to test one or more conditions and produce true when all the conditions would true otherwise it returns false.

EXAMPLE

=AND(I9="Purchase",H9>=15000)

OR FUNCTION

This function is used to test one or more conditions and produce true even only one condition would be true, produce false when all the conditions would be false.

EXAMPLE

=OR(B3>=30,C3>=35)

IF FUNCTION

This function is also known as condition maker because it used to implement one or more conditions. 

EXAMPLE 1: This example print "Pass" if marks>=33 and print fail if marks<33 .="" span="">

=IF(A1>=33,”Pass”,”Fail”)

GUI EXAMPLE

EXAMPLE 2: This example calculates tax 4% on salary when salary amount is greater than 15,000.

=IF(A1>15000,(A1*4/100),0)

CALCULATE GRADE BY IF FORMULA IN MS EXCEL

We can calculate grade by If function and to perform this task we have to use the nested if function.

Nested If: nested if consisted with multiple 'If' statement so in this statement one if statement defines in another if statement.

EXAMPLE
=IF(C2>=75,"A",IF(C2>=50,"B",IF(C2>=33,"C","F")))

Conditions With Output

PRACTICE QUESTION (SET - 5)

EXCEL PRACTICAL QUESTIONS
VLOOKUP AND HLOOKUP FORMULA


WORKSHOP - 1
Create the following data sheet in MS Excel.

1. Find the employee name, department and salary with help of Vlookup formula.

Note: Use the Emp. Code column data as search key in the Vlookup function. 

WORKSHOP - 2
Create two worksheet in MS-Excel.
1. Discount sheet
2. Product_Item sheet

DISCOUNT SHEET

PRODUCT_ITEM SHEET

1. Find the Discount Rate in the Product_Item sheet from the Discount sheet by help of Vlookup function.

Note: Use the Price column data as search key in the Vlookup function.

PRACTICE QUESTION (SET - 1)

PRACTICE QUESTION (SET - 2)

PRACTICE QUESTION (SET - 3)

PRACTICE QUESTION (SET - 4)

PRACTICE QUESTION (SET - 5)

PRACTICE QUESTION (SET - 4)

EXCEL PRACTICAL QUESTIONS
PIVOT TABLE AND CHARTS

WORKSHOP - 1
Create the following data sheet in MS Excel.

1. Create a pivot with help of above data sheet to calculate total sales by Salesman A, Salesman B and Salesman C.

2. After insert above data items in the pivot table, show only Feb and March month's sales report by using filter buttons. 


WORKSHOP - 2
Create the following data sheet in MS Excel.

1. Create a pivot table for the above data sheet and place the data fields the following order.

A. Insert the "Dept." under the FILTERS.

B. Insert the "Emp. Name" under the ROWS.

C. Insert the "Basic Salary" under the VALUES.

2. Now filter the pivot table data by using "Dept." field that is placed under Filters.
Show all the employees under  HR and Purchase department.

PRACTICE QUESTION (SET - 1)

PRACTICE QUESTION (SET - 2)

PRACTICE QUESTION (SET - 3)

PRACTICE QUESTION (SET - 4)

PRACTICE QUESTION (SET - 5)

PRACTICE QUESTION (SET - 3)

EXCEL PRACTICAL QUESTIONS
FILTER AND ADVANCE FILTER

WORKSHOP - 1
Create the following data sheet in MS Excel.

Emp. Code	Emp. name	Dept.	Basic Salary
E101	Smith	Admin	$       28,000
E102	Alex	Sales	$       20,000
E103	David	Purchase	$       19,500
E104	Sam	Sales	$       21,000
E105	Cloudia	HR	$       27,000
E106	Drothi	DEO	$       16,500
E107	Amma	Receptionist	$       15,000
E108	Jack	Sales	$       18,000

1. Filter the above data and find out all employee's name those are working  in Sales department with help of Filter tool in MS-Excel.

2. Find the all employee's details who are working on Sales department and those basic salary are greater than 20,000.


WORKSHOP - 2
Create the following data sheet in MS Excel.

Emp. Code	Emp. name	Department	Basic Salary
E101	Smith	Admin	28,000
E102	Alex	Sales	20,000
E103	David	Purchase	19,500
E104	Sam	Sales	21,000
E105	Cloudia	HR	27,000
E106	Drothi	DEO	16,500
E107	Amma	Receptionist	15,000
E108	Jack	Sales	18,000

1. Copy all the employee records (those are not working in purchase department) by using Advance filter from the above data sheet in different location but within the same worksheet.

PRACTICE QUESTION (SET - 1)

PRACTICE QUESTION (SET - 2)

PRACTICE QUESTION (SET - 3)

PRACTICE QUESTION (SET - 4)

PRACTICE QUESTION (SET - 5)

PRACTICE QUESTION (SET - 2)

EXCEL PRACTICAL QUESTIONS
CONDITIONAL FORMATTING

WORKSHOP - 1
Create the following data sheet in excel.

1. Highlight all the marks, those are <33 b="" by="" nbsp="">Conditional formatting
 tool.

2. Highlight the students, who got distinction by Conditional formatting tool.

3. Highlight the students, who got marks between 50 - 70.

LEARN CONDITIONAL FORMATTING


WORKSHOP - 2
Create the following data sheet in excel.

1. Find the top three highest scorer from the 'Total' column with the help of Conditional formatting tool.  

2. Find the three lowest scorer from the 'Avg' column with the help of Conditional formatting tool.

PRACTICE QUESTION (SET - 1)

PRACTICE QUESTION (SET - 2)

PRACTICE QUESTION (SET - 3)

PRACTICE QUESTION (SET - 4)

PRACTICE QUESTION (SET - 5)