ASSIGNMENT-1

RANDBETWEEN: This function generates a random integer

between two specified number.
Question 1: Find the Net Income.

1. Open Excel and prepare the following heads.

2. Enter the Months under the month head in periodic

order.
 Enter Values under Total Revenue and
Total Expenditure using RANDBETWEEN
function.

3. To Find the income subtract the total expenditure

from total income.

1
Nischhal Sinha 01761188823
  Select the cell containing total income and
then put a minus sign and then select the cell
containing total expenditure.
 After obtaining the first value select the cell
and drag the cursor to apply the same
function in the remaining cell.

2
Nischhal Sinha 01761188823
 ASSIGNMENT-2

Absolute Reference ($A$1)

 The dollar sign before both the column letter and the row
number lock both the column and the row. If you copy the
formula from one cell to another, the reference will remain
exactly the same (e.g., it will always refer to cell A1).
Mixed Reference ($A1 or A$1)
 $A1: The dollar sign before the column locks the column
(A), but the row (1) will change if the formula is copied
across rows.
 A$1: The dollar sign before the row locks the row (1), but
the column (A) will change if the formula is copied across
columns.
Relative Reference (A1)
 No dollar sign means the reference is relative. When you
copy the formula, both the column and row will adjust
based on the new location.
Q1: Find the percentage obtain by student in class.

1. Open Excel and enter the following data.

3
Nischhal Sinha 01761188823
 2. To Calculate the percentage, we will use the
method of Absolute reference. In a cell type “=”.
 Select the cell containing the total marks
obtained by Arun i.e. “B6”.
 Now Divide the cell by the cell containing the
grand total i.e. “B4”.
 To keep the refence same for all the cell insert
dollar sign Before and after B i.e. “$B$4”.
 Now Multiple the whole formula by [Link]
output will be achieved.
 Select the cell containing the output and drag
the cursor down in order to apply the formula
in the remaining cells.

4
Nischhal Sinha 01761188823
 ASSIGNMENT-3
Question1: Calculate the total cost of the menu items, also
compute the Sales Tax.
1. Open Excel and enter the following values.

5
Nischhal Sinha 01761188823
 2. To Calculate the total cost of the Menu Items, Type
“=” in a cell.
 Select the cell containing the Unit price for
the menu item A.
 Put the Sign of Multiple “*” and then select
the cell containing the qty of menu items A.
 The total cost of item A will be displayed in
the cell.
 Select the cell containing the output and
drag the cursor down in order to apply the
formula in the remaining cell.

6
Nischhal Sinha 01761188823
 3. Calculate the total of the cost by typing “=SUM”
and then select the range. In the above case the
range will be from “D5:D14”.

4. Type the rate of sales Tax in one of the cell.

7
Nischhal Sinha 01761188823
 5. Now to calculate Sales Tax type “=” in a cell and
then select the cell containing the total cost of
menu item A.
 Then put the sign of multiply “*” and select
the cell containing the tax rate i.e. “I2”.
 To keep the refence same for all the cell insert
dollar sign Before and after I i.e. “$I$2”.
 The Sales tax for the item A would be
displayed in the cell.
 Select the cell containing the output and drag
the cursor down in order to apply the same
formula in the remaining cell.

Question 2: Calculate 18% of the food and beverage and also

the total cost of the Menu Items.
8
Nischhal Sinha 01761188823
 6. Enter the following Data in the Excel sheet.

7. To Calculate 18% of the food and beverage put “=”

sign in the cell.
 Select the cell containing the value of food
and beverage.
 Now put the multiple sign “*” and type 18%
and press enter.

8. To calculate the total cost put the “= SUM” and

select the range.
 In the above case the range would be
“D21:D24” and press enter.

9
Nischhal Sinha 01761188823
 ASSIGNMENT-4
10
Nischhal Sinha 01761188823
Question: Highlight the column that are greater than 50 under
the number head.
1. Enter the following data in excel.

2. To highlight the column greater than 50, Select the

number column and go to conditional formatting
located in the Home Tab under styles.
 A dialogue box will appear. Select the option
“Highlight Cell Rules” and then select
“Greater Than”.

3. Another dialogue box will appear. Enter the

number “50” and select “Custom Format”. Chose
the color you want your cells to be highlighted with
and the press enter.

11
Nischhal Sinha 01761188823
 4. The cells containing number more than 50 will be
highlighted.

Question: Highlight the text that contains Monday.

1. Enter the following data in Excel.

2. To highlight Monday, Select the text column and

then go to conditional formatting located in the
home tab under style.
 A dialogue box will appear, select the option
“Highlight cell rules” and then select “Text
that contains”.

12
Nischhal Sinha 01761188823
 3. Another dialogue box will appear. Type “Monday”
and then select “Custom Format”. Select the color
you want and press “ok”.

4. The text containing Monday will be highlighted.

Question: Perform all Conditional formatting under the date

header provided in Excel.
1. Enter the following data in excel.

13
Nischhal Sinha 01761188823
  DATE OCCURING THIS WEEK
1. To Highlight the cell that contains a Date Occurring
this week, Select the date column and then go to
conditional formatting.
 Go to “highlight cell rules” and select the
option “A Date Occurring”.
 A dialogue box will appear, Select the option
“This Week” and go to “Custom Format”.
Select the color and press “ok”.
2. The cell containing Date occurring this week will
be selected.

 DATE OCCURRING LAST MONTH

1. To Highlight the cell that contains a Date Occurring
Last month, Select the date column and then go to
conditional formatting.
 Go to “highlight cell rules” and select the
option “A Date Occurring”.
 A dialogue box will appear, Select the option
“Last Month” and go to “Custom Format”.
Select the color and press “ok”.
2. The Cell containing Date Occurring last month will
be selected.

14
Nischhal Sinha 01761188823
  DATE OCCURRING THIS MONTH
1. To Highlight the cell that contains a Date Occurring
This month, Select the date column and then go to
conditional formatting.
 Go to “highlight cell rules” and select the
option “A Date Occurring”.
 A dialogue box will appear, Select the option
“This Month” and go to “Custom Format”.
Select the color and press “ok”.
2. The Cell containing Date Occurring This month will
be selected.

Question: Highlight the cell under the condition that they are
unique or duplicate.
1. Enter the following data in excel.

 UNIQUE VALUE

15
Nischhal Sinha 01761188823
 1. To Highlight the cell that contains a Unique value,
Select the Text column and then go to conditional
formatting.
 Go to “highlight cell rules” and select the
option “Duplicate Value”.
 A dialogue box will appear, Select the option
“Unique” and go to “Custom Format”. Select
the color and press “ok”.
2. The cell containing a Unique Value will be selected.

 DULPICATE VALUE
1. To Highlight the cell that contains a duplicate
value, Select the Text column and then go to
conditional formatting.
 Go to “highlight cell rules” and select the
option “Duplicate Value”.
 A dialogue box will appear, Select the option
“Duplicate” and go to “Custom Format”.
Select the color and press “ok”.
2. The cell containing a duplicate Value will be
selected.

16
Nischhal Sinha 01761188823
Question: Highlight the lowest three items under the
number header.

1. Enter the following data in excel.

2. To Highlight the cell that contains the lowest three

item, Select the number column and then go to
conditional formatting.
 Go to “Top/Bottom rules” and select the
option “Bottom 10 items”.
 A dialogue box will appear, Select the option
“3” and go to “Custom Format”. Select the
color and press “ok”.

17
Nischhal Sinha 01761188823
 18
Nischhal Sinha 01761188823
 ASSIGNMNET-5
FILTER IN EXCEL
Filtering in Excel allows you to display only the rows that
meet specific criteria while hiding the others.
Question: On the following database use filters in excel.
1. Enter the following data in excel

Question 1(a): On region header, use the filter to find the

region starting with ‘N’.

19
Nischhal Sinha 01761188823
 1. Select the region column and go to “Sort and
Filter” located in the editing head located in home
tab.
 A dropdown menu will appear and select the
option of Filter.

2. Click on the arrow, located at the site of North. A

dropdown menu will appear, Select “Text Filters”.
 Another dropdown menu will appear select
the option “Begins with”
 A dialogue box will appear enter “N” and
press ok.

20
Nischhal Sinha 01761188823
 3. The following output will be received.

ASSIGNMNET-6
 Functions In Excel

1. SUMPRODUCT METHOD: This Function returns the sum of

the products of corresponding ranges or array. The
default function is Multiplication but addition, subtraction
and division are also possible

Question: Use Sum product Function in the table given below.

21
Nischhal Sinha 01761188823
  In the Adjacent cell, type the following function “=
SUMPRODUCT (Array1, Array2) i.e. for the
following question is A8 AND B8. A number will be
obtained, now drag the cursor down so that the
formula can be applied the remining cell.

 Now to obtain the Sum of the outputs in a cell

type “= SUM (C8,
C9, C10)”, these are the c ell that contain the
output. Press Enter and the following result will be
Obtained.

2. MROUND FUNCTION: This Function returns a number to a

multiple given by users. Mround will always Round up
away returns a number rounded to the desired multiple.
Question: Use Mround Function on the data given below.

 In a Cell adjacent to the number 4.7 type

“=MROUND(B16,5)”. Here B16 is the cell

22
Nischhal Sinha 01761188823
 containing the no 4.7 and 5 is the multiple
to which it should be rounded up. Press
enter and an output will be obtained, now
select the cell containing the output and
drag the cursor down. The following data
will be achieved.

3. FLOOR FUNCTION: This Function is used to round a given

number down to the nearest multiple of a specified
number.
4. Question: Round a Number down, towards zero, to the
nearest multiple of significance in accordance with the
data given below.

 In an adjacent cell type, the following

“= FLOOR(B27,2)”. Here B27 is the cell
containing the number and 2 is the significance.
Similarly, the significance for 16.4 and 4.4 will be
10 and 2 respectively. Now press enter and the
following output would be received.

23
Nischhal Sinha 01761188823
 CELLING FUNCTION: This Function rounds a given number to
the nearest specified multiple. Ceiling works like MROUND
Question: Use Ceiling function on the data given below.

 In an adjacent cell type “=CEILING(B35,20)”. Here

B35 is the cell containing the number and 20 is the
significance. Similarly, the significance of the
number 7.8 is 1. Now press enter, the following
output would be received.

ASSIGNMENT-7
STRINGS FUNCTION IN EXCEL

 LEFT AND RIGHT FUNCTION

 The Left Function is used to retrieve a chosen
number of characters, counting from the left side of
an excel cell.
 The chosen number has to be greater than 0 and is
set to 1 by default.
 The Right Function is a test string function of
characters from the right side of the string.
 It helps extract characters beginning from rightmost
side to the left
24
Nischhal Sinha 01761188823
Question: On “ABC” use the LEFT and RIGHT string function to
see what will be the output.
 The Syntax of Left function: “=LEFT(TEXT[NUM_CHARS])”.
 The Syntax of Right Function: “=
RIGHT(TEXT[NUM_CHARS])”.

 MID FUNCTION
 This Function is designed to pull a substring from
the middle of the original text string.
 The Mid function returns the specified number of
characters starting at the position you specify.

Question: On “ABC” Use MID function to see what will be the

output.

 The Syntax of Mid Function: “=MID (TEXT, START_NUM,

NUM_CHARS)

 UPPER AND LOWER FUNCTION

25
Nischhal Sinha 01761188823
  The Upper function is a text function, that converts
text to all capital letters. (UPPER CASE)
 The lower function returns the lower-case version of
string given.

Question: Use Upper and lower function on your names

 The syntax of upper function: “=Upper (Text)”
 The syntax of lower function: “=Lower Text)”

 LENGTH FUNCTION
 This function returns the length of a given text
string.

Question: Use Length function on your name.

 The Syntax of length function: “=LEN(TEXT)”.

 TRIM FUNCTION
 This Function is used to remove irregular text
spacing and keep single spaces between the words.

26
Nischhal Sinha 01761188823
Question: Use TRIM function on the following sentence.

 The syntax of Trim function: “=TRIM(TEXT)”

 CONCATENATE FUNCTION
 This function joins two or more Text strings in one
string.

Question: Use Concatenate Function on the following sentence.

 The Syntax of Concatenate Function: “=CONCATENATE

(text1, text2,…)”

 SUBSITUTE FUNCTION
27
Nischhal Sinha 01761188823
  This Function one or more text strings with another
text string.
 this function is useful when we wish to substitute
old text in a string with a new string.

Question: Use Substitute function on the following sentence.

 The Syntax of Substitute Function: “=SUBSITUTE (text,

old_ text, new _text, [instance _Num])”.

 REPLACE FUNCTION
 This function replaces part of a text string, based on
the number of characters you specify, with a
different text string.

Question: Replace Hello with Hi in the given sentence using

Replace Function.

 The Syntax of replace function:

“=REPLACE(old_text,start_num,num_chars,new_text)”.

28
Nischhal Sinha 01761188823
 ASSIGNMENT-8
 IF FUNCTION
 IF function is a logical function.
 It allows you to make decisions based on a condition.
 The Syntax of IF Function: “=IF (logical_test,
value_if_true, value_if_false)”.
 logical test: The condition you want to check (e.g., A1 > 10)
 value_if_true: The result if the condition is true
 value_if_false: The result if the condition is false

 IF & AND FUNCTION

 This combination lets you check multiple conditions
at once, and return results based on whether all of
them are true.

29
Nischhal Sinha 01761188823
  The Syntax of IF&AND Function: “=IF (AND
(condition1, condition2, ...), value_if_true,
value_if_false)”.
 AND (condition1, condition2, ...): Checks if all conditions are
TRUE.
 IF(...): Uses the result of the AND function to decide what to
return.

Question: Determine Whether the sales Target have been met

or not. In Accordance with the data given below.

30
Nischhal Sinha 01761188823
Question: Use IF function to determine whether the students
have passed or fail in the SUB-1 Subject.

31
Nischhal Sinha 01761188823
Question: Use IF & AND function in the given database to
determine the Division of the student based on their
percentage and marks.

32
Nischhal Sinha 01761188823
 ASSIGNMENT-9
 COUNT FUNCTION
 The COUNT function is used to count the number of numeric
values in a range of cells.
 The syntax of count function: “=COUNT (value1,
[value2], ...)”.
 It counts only numbers (not text, blank cells, or error values).
 You can enter individual values or cell ranges.

 COUNTA FUNCTION
 The COUNTA function counts the number of non-
empty cells in a range — including numbers, text,
dates, logical values (TRUE/FALSE), and even
errors.

33
Nischhal Sinha 01761188823
  The Syntax of Counta Function: “=COUNTA
(value1, [value2], ...)”.
 It counts all cells that are not empty, no matter what type of
data they contain.
 Useful when you want to know how many cells have
something in them, not just numbers.

Question: Use Count Function to determine the count of

enrolment number of students given in the database.

Question: Use CONTA Function to determine the count of cells

under the total marks section.

34
Nischhal Sinha 01761188823
 ASSIGNMENT-10

 SUM IF FUNCTION
 The SUMIF function adds up the values in a range only if they
meet a specified condition.
 The Syntax of SUMIF Function: “=SUMIF (range,
criteria, [sum_range])”.
 range: The range of cells to evaluate with the condition.
 criteria: The condition that determines which cells to sum.
 sum_range: The actual cells to sum — if different from the
range.

 COUNT IF FUNCTION

35
Nischhal Sinha 01761188823
  The COUNTIF function counts the number of cells
that meet a specific condition.
 The Syntax of COUNT IF Function: "=COUNTIF
(range, criteria)”.
 range: The group of cells you want to apply the condition to.
 criteria: The condition (like ">50", "Apples", or "=Yes") that
determines which cells to count.

 SUM IFS FUNCTION

 The SUMIFS function adds up values in a range
only if they meet all the specified conditions.
 The Syntax of Sum ifs Function: “=SUMIFS
(sum_range, criteria_range1, criteria1,
[criteria_range2, criteria2], ...)”
 sum_range – The cells you want to total.
 criteria_range1 – The range you want to apply the first
condition to.
 criteria1 – The first condition.
 You can keep adding more criteria range + criteria pairs for
more conditions.

Question: Solve the Following questions Using SUMIF AND CONT

IF Functions on the given database.

1. Calculate Total Sales.

36
Nischhal Sinha 01761188823
 [Link] Total sales for north region.

3. Find the total commission for red house.

4. Find the total commission for house red where the

count of new customer is less than 10.

37
Nischhal Sinha 01761188823
 5. Count the new customer where the number of
customer is more than 10.

38
Nischhal Sinha 01761188823
 ASSIGNMENT 11

AVERAGE IF & AVERAGE IF’s FUNCTION

Understanding the difference between AVERAGEIF and AVERAGEIFS is

crucial for effective data analysis in Excel. Here's a breakdown:
AVERAGEIF:
 Purpose:
o Calculates the average of cells within a range that meet a single
specified criterion.1
 Syntax:
o AVERAGEIF (range, criteria, [average_range])
 range: The range of cells to evaluate.
 criteria: The condition to be met.
 average_range: (Optional) The cells to average. If omitted,
the range is averaged.
 Key Feature:

39
Nischhal Sinha 01761188823
 o Deals with one condition.
AVERAGEIFS:
 Purpose:
o Calculates the average of cells that meet multiple specified
criteria.2
 Syntax:
o AVERAGEIFS (average_range, criteria_range1, criteria1,
[criteria_range2, criteria2], ...)
 average_range: The cells to average.
 criteria_range1: The first range to evaluate.
 criteria1: The first condition.
 criteria_range2, criteria2, ...: Additional ranges and
conditions.
 Key Feature:
o Handles multiple conditions.
o It uses "AND" logic, meaning that all criteria must be met for a cell
to be included in the average.
o The order of the arguments is different than Averageif. The Average
range is the first argument.
In simpler terms:
 Use AVERAGEIF when you need to find the average based on one
condition.
 Use AVERAGEIFS when you need to find the average based on two or
more conditions.
Essentially, AVERAGEIFS is a more powerful and versatile version of
AVERAGEIF, allowing for more complex data analysis.

QUES: Average- IF & Average Ifs Function on the given below database:

40
Nischhal Sinha 01761188823
Name Enrollment NO. College Grade Level Test Score

Anuj 1125 MAIMS 12 70

Aryan 1126 MAIMS 9 69

Gaurav 1127 MAIT 13 76

Ishaan 1128 MAIMS 7 73

Suyog 1129 AMITY 12 81

Vaibhav 1130 AMITY 11 76

Vishal 1131 MAIT 10 78

1) Calculate AVG. scores for grades- 9, 10, 11?

2) a.) Calculate AVG. Score for student whose grade is 9 and belongs to
MAIMS
b.) Calculate AVG. Score for students whose Grade is 10 and belongs to
Mait

41
Nischhal Sinha 01761188823
 42
Nischhal Sinha 01761188823
 ASSIGNMENT 12
NOT & ISBLANK FUNCTION

In Excel, the NOT and ISBLANK functions serve distinct logical

purposes. Here's a breakdown:
1. ISBLANK Function:
 Purpose:
o The ISBLANK function checks whether a cell is completely
empty.
o It returns TRUE if the cell is blank, and FALSE if the cell contains
any data, including spaces or formulas that result in an empty
string ("").
 Syntax:
o ISBLANK (value)
o Where "value" is the cell, you want to test.
 Key Points:
o A truly blank cell will return TRUE.
o A cell containing a space or a formula returning "" will return
FALSE.

2. NOT Function:
 Purpose:
o The NOT function reverses the logical value of its argument.
o If the argument is TRUE, NOT returns FALSE, and vice versa.
 Syntax:
o NOT (logical)
o Where "logical" is a value or expression that can be evaluated as
TRUE or FALSE.

 Key Points:
o It's often used to invert the result of another logical function.
o It's very common to see it used in conjunction with the ISBLANK
function.
43
Nischhal Sinha 01761188823
 Combining NOT and ISBLANK:
 When you combine NOT and ISBLANK (NOT (ISBLANK (cell))), you
effectively check if a cell is not blank.
 This is very useful in IF statements when you want to perform an action
only if a cell contains data.
 Example:
o =IF(NOT(ISBLANK(A1)), "Cell A1 is not empty", "Cell A1 is
empty")
o This formula will display "Cell A1 is not empty" if cell A1 has any
content, and "Cell A1 is empty" if it is truly blank.
In summary, ISBLANK tells you if a cell is empty, and NOT reverses that
result, allowing you to check if a cell is not empty.
QUES check if employees have generated Extra Sales or not in the given
database below if the did, provide 25% of extra sales as commission and if they
didn’t, display NO BONUS for them

44
Nischhal Sinha 01761188823
 ASSIGNMENT 13
SUM-IF FUNCTIONS

The SUMIF function in Excel is a powerful tool for conditionally

summing values. Here's a breakdown of its purpose and how it works:

Purpose:
 The SUMIF function allows you to sum values within a range that meet a
specific criterion. This means you can add up only the numbers that
match a certain condition.
Syntax:
 SUMIF(range, criteria, [sum_range])

Arguments:
 range (Required):
o This is the range of cells that you want to evaluate against the
criteria.
 criteria (Required):
o This defines the condition that determines which cells will be
summed. It can be:
 A number (e.g., 100)
 An expression (e.g., ">50", "<=20")
 A cell reference (e.g., A1)
 Text (e.g., "apples")
 sum_range (Optional):
o This is the actual range of cells that will be summed.
o If omitted, the range argument is used for both evaluating the
criteria and calculating the sum.

Key Points:

 Conditional Summing: It sums values based on whether they meet a

specified condition.
 Flexibility: The criteria can be numbers, text, or expressions.
45
Nischhal Sinha 01761188823
Optional Sum Range: If the range to be summed is the same as the range being
evaluated, the sum_range argument can be omitted.
 Wildcards: You can use wildcard characters like the asterisk (*) and
question mark (?) in the criteria argument.

QUES Perform the following conditions on the dataset given below:

[Link] ITEM SALES SHIPPING

1 MOUSE 15000 5
2 KEYBOARD 12000 5
3 CPU(i5) 7500 10
4 CPU(i7) 8300 10
5 MONITOR 10200 7

46
Nischhal Sinha 01761188823
 ASSIGNMENT 14
WLD CARD CHARACTERS

In Excel, wildcard characters are special symbols that you can use within text
strings in functions like SUMIF, COUNTIF, AVERAGEIF, VLOOKUP, MATCH,
and others, to match patterns of text. They provide flexibility when you don't
know the exact text you're looking for or when you want to match variations of a
text string.

Counta: - The COUNTA function in Excel is used to count the number of cells in
a range that are not empty.

QUES Calculate the following on the dataset given below:

NAME ENROLLMENT NO
ANUJ 1125
ARYAN 1041
GAURAV 1121
ISHAAN 1731
SUYOG 1098
VAIBHAV 1888
VISHAL 1034

1.) Calculate no of cells with text values only in student’s column

2.) Calculate no of cells with numeric values only in enrollemnt column

47
Nischhal Sinha 01761188823
 48
Nischhal Sinha 01761188823
 ASSIGNMENT 15
LOOKUP FUNCTION

LOOKUP FUNCTION: This function searches for a value in a vector or array

and return a value from the same position in a secont vector or array

The LOOKUP function has 2 syntax:

LOOKUP(lookup_value, lookup_vector,[result_vector])
LOOKUP(lookup_value, array)

Ques1 Calculate the following on the database given below

NAME ENROLLMENT NO TOTAL MARKS

ANUJ 1125 450
ARYAN 1041 440
GAURAV 1121 396
ISHAAN 1731 465
SUYOF 1098 358
VAIBHAV 1888 404
VISHAL 1034 391

A.) Find the enrollment no of students using their names

B.) Find the total marks of the students using their enrolment no

49
Nischhal Sinha 01761188823
 50
Nischhal Sinha 01761188823
QUES2

EMPLOYEE ID NAME LOCATION SALARY AGE

56815 Garry Manship Hong Kong 13836 25
51186 William Johnson Berlin 11771 32
51511 Bettle Bangkok 13046 35
50890 Ian Nash Cairo 18276 32
53700 Margaret Turley Shanghai 19327 26
55879 Michael Kaye Capetown 18996 35
59848 Paul Bell Bangkok 10387 25
58369 Ian Davies Capetown 12566 37
50217 Eric Green Warsaw 16406 42
50695 Williamr Black Cairo 15784 43
59673 Estelle Cornack Hong Kong 10959 30
Christopher
52130 Delhi 14562 32
Fallon

a) What is the name of employee id 58369

b) What's the age of estelle Cormack

51
Nischhal Sinha 01761188823
 a) Return the location of the following employees

b) Find the Salary of the following employee

52
Nischhal Sinha 01761188823
 53
Nischhal Sinha 01761188823
 ASSIGNMENT 16
V-LOOKUP & H-LOOKUP

In Microsoft Excel, both VLOOKUP and HLOOKUP are lookup functions

used to search for data in a table and extract related information.

VLOOKUP (Vertical Lookup):

 Searches: Vertically down the first column of a specified range (table
array).
 Looks for: A specific value (lookup value) in that first column.
 Returns: A value from a specified column in the same row where the
match is found.
 Best used when: Your data has the lookup values in the leftmost column
and related information in columns to the right. Think of a list where
you're looking up an ID or a name and want to return other details
associated with it.

Syntax of VLOOKUP
=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
 lookup_value: The value you want to search for in the first column of the
table_array.
 table_array: The range of cells that contains the data you want to search
in. The first column of this range is where the lookup_value is searched.
 col_index_num: The column number within the table_array that contains
the value you want to return. The first column in the table_array is 1.
 [range_lookup]: An optional argument that specifies whether you want
an exact or approximate match:
o TRUE or omitted: Approximate match. If an exact match is not
found, it returns the next largest value that is less than the
lookup_value. The first column of the table_array should be sorted
in ascending order for this to work correctly.
o FALSE: Exact match. It will only return a value if an exact match
for the lookup_value is found in the first column. If no exact match
is found, it returns the #N/A error.

HLOOKUP (Horizontal Lookup):

54
Nischhal Sinha 01761188823
  Searches: Horizontally across the first row of a specified range (table
array).
 Looks for: A specific value (lookup value) in that first row.
 Returns: A value from a specified row in the same column where the
match is found.
 Best used when: Your data has the lookup values in the top row and
related information in rows below. Think of a table where the headers in
the first row are what you want to look up.

Syntax of HLOOKUP:
=HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup])
 lookup_value: The value you want to search for in the first row of the
table_array.
 table_array: The range of cells that contains the data you want to search
in. The first row of this range is where the lookup_value is searched.
 row_index_num: The row number within the table_array that contains
the value you want to return. The first row in the table_array is 1.
 [range_lookup]: An optional argument that specifies whether you want
an exact or approximate match (same as in VLOOKUP):
o TRUE or omitted: Approximate match. The first row of the
table_array should be sorted in ascending order.
o FALSE: Exact match.
o
In summary, the main difference is the direction of the lookup:
 VLOOKUP: Looks up vertically in the first column.
 HLOOKUP: Looks up horizontally in the first row.
The choice between VLOOKUP and HLOOKUP depends entirely on how your
data is organized in your Excel sheet.
NAME ARYAN ISHAAN SUYOG VAIBHAV
ITL 72 85 70 77
MA 79 79 75 67
CF 84 76 81 78
DA-LAB 83 72 71 78
BE-CSR 76 76 71 83
Ques Find out the marks of vaibhav in all the subjects from the database given
below:-

55
Nischhal Sinha 01761188823
 ASSIGNMENT 17
56
Nischhal Sinha 01761188823
 IF With V LOOKUP FUNCTION

Combining the IF function with VLOOKUP or HLOOKUP in Excel

enhances the power of lookup formulas by allowing for conditional logic.
Key uses include:
 Error Handling: Using IF(ISNA(VLOOKUP(...)), "Not Found",
VLOOKUP(...)) to display a custom message instead of #N/A when no
match is found.
 Conditional Lookups: Performing different lookups based on a specific
condition in another cell (e.g., looking up data from different tables based
on a product type).
 Conditional Returns: Evaluating the result of a VLOOKUP or
HLOOKUP and returning different values based on whether the found
value meets certain criteria (e.g., returning "High Value" if the looked-up
price is above a threshold).
In essence, nesting VLOOKUP or HLOOKUP within an IF function allows you to
create more flexible and intelligent spreadsheets that can handle
various scenarios and provide more user-friendly outputs.

SALES
PRODUCT SALES TARGET
ACHIEVED
APPLE 120 100
GRAPE 250 230
BLUE BERRY 170 180
STRAWBERRY 120 120
ORANGE 135 110
MANGO 205 220
PEACH 200 175
GUAVA 230 230
KIWI 100 90
PAPAYA 200 240
WATERMELON 90
57
21
Nischhal Sinha LICHI 10 01761188823
21
  IF with VLOOKUP Function
[Link] PRODUCT NAME AVAILABLE QTY
1 AAA 1000
2 BBB 500
3 CCC 0
4 DDD 65

5 EEE 250
6 FFF 600
7 GGG 0
8 HHH 900
9 III 1400
10 JJJ 0
11 KKK 800
12 LLL 340
13 MMM 320

58
Nischhal Sinha 01761188823
 59
Nischhal Sinha 01761188823
 ASSIGNMENT 18
VlOOKUP With MATCH FUNCTION

MATCH Function (Brief):

Finds the position of a specific value within a single row or column. Returns
the numerical position (1st, 2nd, etc.). Useful for finding where something is in
a list. Often combined with INDEX for more flexible lookups than
VLOOKUP/HLOOKUP.
Syntax of the MATCH Function:
=MATCH(lookup_value, lookup_array, [match_type])
The standard VLOOKUP function requires you to manually specify the column
index number from which you want to return a value. This becomes
problematic if:
 You insert or delete columns in your lookup table, as the hardcoded
column index will likely return the wrong data.
 The column you need to retrieve data from might change, and you want a
more dynamic way to specify it.
How MATCH Solves This:
The MATCH function returns the relative position of a lookup value within a
specified row or column. By using MATCH as the col_index_num argument in
VLOOKUP, you can dynamically determine the correct column number based
on a header row.
Syntax:
=VLOOKUP(lookup_value, table_array, MATCH(lookup_column_header,
header_row, 0), [range_lookup])
Breakdown:

 lookup value: The value you want to search for in the first column of
your table_array (same as in regular VLOOKUP).

60
Nischhal Sinha 01761188823
  table_array: The range containing your lookup data (same as in regular
VLOOKUP).
 MATCH(lookup_column_header, header_row, 0): This is where the
magic happens:
o lookup_column_header: The text in the header row that matches
the column you want to return data from (e.g., "Price", "Name",
"Date").
o header_row: The row containing the column headers of your
table_array.
o 0: Specifies an exact match for the lookup_column_header within
the header_row.
o The MATCH function will return the column number where
the lookup_column_header is found within the header_row. This
number is then used as the col_index_num for the VLOOKUP
function.
 [range_lookup]: (Optional) Specifies whether to find an exact or
approximate match (same as in regular VLOOKUP). FALSE (or 0) for
exact match is generally recommended.
Benefits of Using VLOOKUP with MATCH:
 Dynamic Column Selection: You can change the column from which
VLOOKUP returns data simply by changing the lookup_column_header.
The formula will automatically adjust.
 More Robust to Column Changes: If you insert or delete columns in
your table_array, as long as the header_row and the relative position of
your lookup column remain consistent, the formula will still work
correctly. You don't need to manually update the column index number.
 Improved Readability: The formula becomes more self-explanatory as it
directly references the column header you're looking for.

61
Nischhal Sinha 01761188823
 TIME
TABLE
12.0
9.00 10.00 11.00 1.00 2.00 3.00
0
AM AM AM PM PM PM
PM
LU
MOND CHEMI FRENC GERM PHY ENGLI
MATHS NC
AY STERY H AN SICS SH
H
LU
TUESD GEOGR COMP SOCCE FRENC GER SPANI
NC
AY APHY UTERS R H MAN SH
H
RELIGI
LU
WEDN GERM MATH HISTO OUS ENG ENGLI
NC
ESDAY AN S RY STUDI LISH SH
H
ES
FRENC
LU
THURS HISTO MATH CHEMI H MAT COMP
NC
DAY RY S STERY LITER HS UTERS
H
ATURE
LU
FRIDA COMPU COMP GERM GERM SOC SOCCE
NC
Y TERS UTERS AN AN CER R
H

62
Nischhal Sinha 01761188823
QUES 2
EMPLOYEE ID NAME LOCATION SALARY AGE
56815 Garry Manship Hong Kong 13836 25
51186 William Johnson Berlin 11771 32
51511 Bettle Bangkok 13046 35
50890 Ian Nash Cairo 18276 32
53700 Margaret Turley Shanghai 19327 26
55879 Michael Kaye Capetown 18996 35
59848 Paul Bell Bangkok 10387 25
58369 Ian Davies Capetown 12566 37
50217 Eric Green Warsaw 16406 42
50695 Williamr Black Cairo 15784 43
59673 Estelle Cornack Hong Kong 10959 30
52130 Christopher Fallon Delhi 14562 32

1) What is the name of employee ID 58369?

63
Nischhal Sinha 01761188823
2) What's the age of Estelle Cornack

3) Return the location of the following EE

64
Nischhal Sinha 01761188823
4) Find the salary of the following employees:

65
Nischhal Sinha 01761188823
 ASSIGNMENT 19
HLOOKUP With MATCH FUNCTION

The standard HLOOKUP function requires you to manually specify the row
index number from which you want to return a value. This can cause issues if:
 You insert or delete rows in your lookup table, as the hardcoded row
index will likely return the wrong data.
 The row you need to retrieve data from might change, and you want a
more flexible way to specify it.
How MATCH Solves This for HLOOKUP:
Just like with VLOOKUP, you can use the MATCH function to dynamically
determine the correct row index number for the HLOOKUP function based on
a header column (appearing to the left of your data table).
Syntax:
=HLOOKUP(lookup_value, table_array, MATCH(lookup_row_header,
header_column, 0), [range_lookup])
Breakdown:
 lookup_value: The value you want to search for in the first row of your
table_array (same as in regular HLOOKUP).
 table_array: The range containing your lookup data (same as in regular
HLOOKUP).
 MATCH(lookup_row_header, header_column, 0): This is the key part:
o lookup_row_header: The text in the header column that matches
the row you want to return data from (e.g., "Price", "Quantity",
"Sales").
o header column: The column containing the row headers of your
table_array.

o 0: Specifies an exact match for the lookup_row_header within the

header_column.
66
Nischhal Sinha 01761188823
 o The MATCH function will return the row number where the
lookup_row_header is found within the header_column. This
number is then used as the row_index_num for the HLOOKUP
function.
 [range_lookup]: (Optional) Specifies whether to find an exact or
approximate match (same as in regular HLOOKUP). FALSE (or 0) for
exact match is generally recommended.
Benefits of Using HLOOKUP with MATCH:
 Dynamic Row Selection: You can easily change the row from which
HLOOKUP returns data by changing the lookup_row_header. The
formula will update automatically.
 More Robust to Row Changes: If you insert or delete rows in your
table_array, as long as the header_column and the relative position of
your lookup row remain consistent, the formula will still function
correctly. You won't need to manually adjust the row index number.
 Improved Clarity: The formula becomes more understandable as it
directly refers to the row header you are trying to retrieve data from.
EMPLOY
101 102 103 104 105 106 107 108 109 110
EE ID
Davi
Bob Tom Jessic
EMPLOY John Jane Sarah Emily Mich d Rachel
Johns Dav a
EE NAME Doe Smith Lee Brown ael Mart Green
on is Davis
in
Departme Marketi Finan Operati Finan Marketi
HR IT OB IT HR
nt ng ce ons ce ng
500 6500 700 8500 9000
Salary 55000 60000 75000 80000 95000
00 0 00 0 0
200 400
Bonus 2500 3000 3500 4500 5000 5500 6000 6500
0 0
520 6850 740 9050 9600
Total Pay 57500 63000 79500 85000 101500
00 0 00 0 0

QUES 1

67
Nischhal Sinha 01761188823
QUES2

QUES 3
68
Nischhal Sinha 01761188823
 69
Nischhal Sinha 01761188823
 ASSIGNMENT 20
INDEX & INDEX With MATCH FUNCTION

The INDEX function in Excel returns a value or the reference to a value within
a range or array. You can use INDEX to retrieve a specific value based on its
row and column number within that range.
There are two forms of the INDEX function:
1. Array Form:
 Syntax: =INDEX(array, row_num, [column_num])
 array (Required): The range of cells or an array constant.
 row_num (Required): The row number in the array from which to return a
value.
 column_num (Optional): The column number in the array from which to
return a value. If omitted, it's assumed to be 1 if the array is a single
column.

The combination of the INDEX and MATCH functions in Excel is a powerful

and flexible alternative to functions like VLOOKUP and HLOOKUP for
performing lookups. It overcomes some of the limitations of those functions.
How it Works (Brief):
Instead of relying on the lookup value being in the first column (VLOOKUP) or
first row (HLOOKUP) and specifying a fixed column or row index, INDEX and
MATCH work independently to find the exact location of the desired value.
1. MATCH:
o Finds the position (row or column number) of a specific
lookup_value within a lookup_array.
o Returns the numerical position.
2. INDEX:

70
Nischhal Sinha 01761188823
 o Returns the value in a table or range based on a specified row and
column number.
Combining Them:
You nest the MATCH function inside the INDEX function to dynamically
determine the row and/or column number for INDEX.

Why Use INDEX and MATCH?

 Flexibility: You can look up values to the left (or above) the lookup
column/row, unlike VLOOKUP/HLOOKUP.
 Robustness: Formulas are less likely to break if you insert or delete
columns/rows, as you specify the lookup and return ranges separately.
 Two-Way Lookups: Easily perform lookups based on both row and
column criteria.
 Performance: Can be more efficient for large datasets as it only looks at
the necessary columns/rows

Employee ID EMP name Sales 2019 Sales 2020 Sales 2021

5001 Suraj 192000 555000 950000

5002 Poonam 183000 325000 874000

5003 Gopal 210000 650000 880000

5004 Rohan 369000 875000 894000

5005 Vidya 250000 380000 895000

5006 Sohan 198000 555000 694000

5007 Mohit 168000 325000 895000

5008 Rupak 215000 745000 650000

5009 Siya 365000 635000 875000

71
Nischhal Sinha 01761188823
5010 Poooja 260000 759000 780000
Ques tell the sales for the year 2020 for rupak , kunal and Poooja

72
Nischhal Sinha 01761188823
 ASSIGNMENT 21
CONDITIONAL FORMATING

Conditional formatting is a powerful feature in Excel that allows you to

automatically apply formatting (such as color, font, borders, etc.) to cells based
on specific rules or conditions. This helps you visually highlight important data,
identify trends and patterns, and make your spreadsheets easier to understand.
In short words
Conditional formatting lets you change the appearance of cells based on their
values or the results of formulas.

 Conditional Formatting in Excel Steps

1. Pick your cells: Select the data you want to format.
2. Go to "Conditional Formatting": Find it under "Home" tab, "Styles"
group.
3. Choose a rule: Pick a pre-set rule (like "Highlight Cells") or create your
own ("New Rule").
4. Set the condition: Tell Excel when to apply formatting (e.g., "if the
number is greater than 10").
5. Pick a style: Choose how you want the cells to look (color, font, etc.).
6. Click "OK": The formatting will automatically apply.
 To change or remove formatting later:
1. Select the cells.
2. Go to "Conditional Formatting" > "Manage Rules...".
3. Edit or delete rules as needed.

73
Nischhal Sinha 01761188823
 Item Quantity Rate Amount
LED 74 9800 725200
RAM 22 1250 27500
DVD 12 1450 17400
LCD 10 7800 78000
RAM 12 1690 20280
KEYBOARD 12 520 6240
MOUSE 14 230 2940
LED 33 11200 369600
HDD 24 3300 79200
LCD 36 12200 439200
HDD 28 2950 82600
RAM 18 1490 26820
CABINET 20 1850 37000
UPS 13 1450 18850
HDD 8 3340 26720
UPS 10 1690 16900
RAM 20 1050 21000
KEYBOARD 62 490 30380
LCD 14 4200 58800
MOUSE 22 310 6820
Ques1 Highlight cells with item "KEYBOARD"

74
Nischhal Sinha 01761188823
Ques2 Highlight cells where the rate of an item is below rs 2000

75
Nischhal Sinha 01761188823
Ques3 Highlight cells where the quantity of an item is equal to 10

ASSIGNMENT 22
PIVOT TABLE
76
Nischhal Sinha 01761188823
TABLE:-
Unit Test Unit Test
Name Gender Age Class House 1 2 Final Test
Abhimanyu M 16 10 Bhoomi 84 79 81
Arjun M 11 5 Vayu 82 83 91
Champa F 15 8 Jal 81 78 88
Gopal M 14 8 Bhoomi 70 75 79
Gopi F 16 10 Agni 88 92 96
Hari M 16 10 Bhoomi 82 81 80
Indu F 14 8 Vayu 90 86 89
Keshav M 15 9 Agni 87 89 96
Lalita F 17 10 Vayu 70 90 92
Madhav M 12 7 Jal 86 92 89
Sam M 11 6 Agni 91 81 94
Sanchit M 16 10 Agni 86 81 77
Parv M 15 9 Agni 87 89 95
Aryan M 15 8 Vayu 81 90 95
Gaurang M 17 10 Vayu 70 90 92
Nikunj M 12 7 Jal 86 92 89
yash M 16 10 Jal 81 80 87
Sudevi F 16 10 Jal 81 80 87
Varun M 15 9 Vayu 87 89 95
Vidya F 11 6 Vayu 88 90 92
Visakha F 16 10 Bhoomi 70 87 85
Vrinda F 14 8 Agni 91 96 98

77
Nischhal Sinha 01761188823
Q1 Who between the males and females performing better in final test?
SOLUTION: Summarize value field by AVERAGE

Row Labels Average of Final Test

F 90.875
M 88.57142857
Grand Total 89.40909091

Q2. Which house has the highest marks in final

test. SOL: Summarize Values by Sum or Average

78
Nischhal Sinha 01761188823
Row Labels Average of Final Test
Agni 92.66666667
Bhoomi 81.25
Jal 88
Vayu 92.28571429
Grand Total 89.40909091
Q3. What
is the average of female and male scores in different houses in the final
test scores?
SOL: Summarize Values by Average
Row Labels Average of Final Test
Agni 92.66666667
F 97
M 90.5
Bhoomi 81.25
F 85
M 80
Jal 88
F 87.5
M 88.33333333
Vayu 92.28571429
F 91
M 93.25
Grand Total 89.40909091

79
Nischhal Sinha 01761188823
 ASSIGNMNET 23
FILTER OPTION
In Excel, the Filter option is a tool that lets you temporarily hide rows in your
spreadsheet based on specific criteria, so you can focus on the data that
matters.
QUESTION1: Who are the top performers in the class?

Students Grade
James A
Mary B
Jack A
John A
Derrick C
Dennis B
Mark A
Alex B
Ann C
Alana C
Meera A
Keisha B
Rocco B
Joy A
Diara A
Alia C
Fred A

SOLUTION:
Students Grade
James A
Jack A
John A
Mark A
Meera A
Joy A
Diara A
Fred A
QUESTION2:Which employees reside in Mumbai?
Name Region Sales
Naya Mumbai 14738

80
Nischhal Sinha 01761188823
 Ishaan Pune 14623
Jai Delhi 14256
Inaya Mumbai 14678
Amar Pune 14567
Navi Delhi 15678
Dhruv Mumbai 12345
Kanan Pune 16789
Aarav Delhi 13456
Aayush Mumbai 12526

SOLUTION:
Name Region Sales
Naya Mumbai 14738
Inaya Mumbai 14678
Dhruv Mumbai 12345
Aayush Mumbai 12526

ASSIGNMNET - 24
81
Nischhal Sinha 01761188823
 POWER PIVOT

Power Pivot in Excel is a powerful data modelling and analysis tool that a
llows you to work with large datasets, create relationships between different tables, and
build complex calculations.

Steps to enable it-

1) Open Excel.
2) Click on the File tab.
3) Select Options at the bottom of the left panel.
4) In the Excel Options dialog box, select Add-ins.
5) At the bottom, next to Manage, make sure it says COM Add-ins, then
click Go.
6) In the COM Add-ins window:
7) Check the box next to Microsoft Power Pivot for Excel.
8) Click OK.
9) Now you’ll see a Power Pivot tab on the Ribbon at the top.

Q Form the following data sheets show which employees are eligible for bonus according to
their sales profit using power pivot?

A) SALARY
Employee Level Title Years of Experience Base Salary Bonus
1 Analyst 0 $ 25,000 $ 5,000

2 Associate 2 $ 30,000 $ 6,000

3 Senior Associate 4 $ 45,000 $ 9,000

4 Manager 6 $ 60,000 $ 12,000

5 Director 8 $ 80,000 $ 16,000

Customer Product Sale Price Sale Cost Staff ID

Aliah Toyota $ 20,000 $ 14,580 10011

Clara Honda $ 22,550 $ 18,040 10013

Mikaela Honda $ 14,895 $ 11,022 10013
82
Nischhal Sinha 01761188823
 Joshua Mitsubishi $ 20,000 $ 15,400 10011
Kevin
Staff ID Nissan
Name $ 18,000
Email Country$ 14,400
Age 10011
Level
10010 Sam sam@[Link] UK 25 1
Rodrigo Toyota $ 19,500 $ 14,430 10011
10011 Sarah sarah@[Link] UK 22 1
10012
Jack Alex
Toyota alex@[Link]
$ 20,000 UK$ 26
15,400 210011
10013 Jones jones@[Link] Spain 29 3
Jackeline Toyota $ 22,000 $ 9,900 10012
10014 Mike mike@[Link] France 29 4
Lino
10015 Toyota ana@[Link]
Ana $ 25,000 Italy$ 13,750
31 310014
Jess Toyota $ 18,000 $ 9,900 10015
Kayla Nissan $ 16,580 $ 10,777 10012

Cris Honda $ 11,999 $ 7,799 10012

Namto Mitsubishi $ 22,500 $ 12,375 10012
Take Subaru $ 23,500 $ 15,275 10015
Shintoro Toyota $ 23,000 $ 12,650 10014

Edwin Nissan $ 20,000 $ 13,000 10014

Vinny Honda $ 22,550 $ 12,403 10014
Flyn Toyota $ 14,895 $ 9,682 10014
Benjamin Nissan $ 20,000 $ 14,200 10015

Leo Honda $ 20,000 $ 14,200 10010

Karin Honda $ 22,550 $ 16,011 10011
Mohammad Nissan $ 14,895 $ 11,320 10012
Jackson Mitsubishi $ 20,000 $ 15,200 10013

Olafur Mitsubishi $ 18,000 $ 13,680 10014

B)Transaction

c)Employee

83
Nischhal Sinha 01761188823
Sol- We will use POWER PIVOT

 In tab select Power Pivot option

 Click on manage and select “From other sources”

Then browse the document on which we have to work, Click on next Then tick
employee, salary and transaction.

Calculation Of Profit: - =Transaction [Sale Price] - Transaction [Sale Cost]

To Show relationship between them we will use diagram view.

 In Home table Select Pivot table and select existing worksheet or new
worksheet
 Select the field you want to display in pivot table

84
Nischhal Sinha 01761188823
 85
Nischhal Sinha 01761188823
RESULT

To Check performance, we will use KPI Function and type 20000 next to
absolute value and specify the KPI base field as Sum of profit

86
Nischhal Sinha 01761188823
 ASSIGNMNET 25
POWER PIVOT
Power Pivot is a powerful data analysis add-in for Microsoft Excel that allows
you to import and model large datasets from various sources, build relationships
between tables, and create complex calculations using the DAX formula
language. It also enables you to create PivotTables and Pivot Charts based on
your modelled data.
How Power Pivot Works: Import Data: You can import data from various
sources like Excel files, databases, text files, and more using Power Query.
Create Relationships: Establish relationships between tables in your Data
Model, allowing you to link data from different sources.
Create Calculations: Define calculated columns and measures using DAX
formulas to create new data or modify existing data.
Analyse Data: Use PivotTables, Pivot Charts, and other Power Pivot tools to
explore your data and uncover insights.
Ques 1. Using the following product and order dataset, make a pivot table and
pivot chart for the following:
a) Row label-sum of rate
b) Product Id-units sold

87
Nischhal Sinha 01761188823
Data set:

Creating relationship between the two data set:

88
Nischhal Sinha 01761188823
 a) Row label-sum of rate

89
Nischhal Sinha 01761188823
 90
Nischhal Sinha 01761188823