Create new sheet

• Go to help when required(important)

Data file 15
• Relative reference D5*G3
• Absolute reference D5*G$3(Here relative reference is only for
column)
• Fill a table with absolute reference & relative reference(first fill
the table using formulae, then find a pattern)

Data file 13A

Data file 51
• Trace dependents(in formulas tab)
• Trace precedents
• Remove arrow

Data file 30
• To multiply 2 arrays and give result in another array:
• Select all rows of F, write D2* E2 then ctrl+enter
• Or = (d2:d7)*(e2:e7) then ctrl+enter
• First select all areas you want to fill
• For sum =sum((d2:d7)*(e2:e7)) then ctrl shift enter
• For power = either (c4:c9)^(d3:g3) or power((c4:c9),(d3:g3))

Data file 11
• Sumproduct
• Insert function

File data 35
• Copy column and paste as row (paste special and transpose)
• Or you can first select where to write transposed matrix
• Then write transpose(a4:a8) and ctrl+shift+enter

Read all paste special options

File data 7
• Q is total nights booked for each customer
• You do subtotal given in data tab.(works only when data is
sorted)
• Q2 for each month, we want sum of nights
• Subtotal doesn’t work here as data is not sorted
• So first use sort function in data tab (select the column and then
do the subtotal)
• Q3 sort monthlies then nights booked ascending order(just add
one more column in the sort function)

Data 75
• Fill subtotal using subtotal function(subtotal(9, c3:c5))
• 9 is for sum, there are many other options too

Data60
• Select first name from the names(go in data tab, then text to
columns, then use delimiter space)
• If we want to separate by width, use the same tab (text to
column) and then select fixed width and select that width
• HW is to deliminate the data (segregate no. and alphabet)
• ########################
• We can deliminate using commas too.
•
•

Data52
• Give values to names, select both columns(only key value pair
not the labels), then go In formula tab and select create from
selection. Now we can use the key to get the value(now u can
just type = AL in any cell to get the value)
• You can give the name to the array also. Go to formulas, then to
define names and then name it something. Now you can use the
name for accessing that array
•
Data 54
• Get average/sum by just calling the name
• Give name to each column

Session 4
Data 14
• To select A product, first select the header row(full), go to data,
apply filter, then use dropdown menu given on product header
and filter
• To select amount between 1000 to 2000, use filter as same but
when clicking dropdown menu and use number filter there
• You can give name to a table too. Select table, then go to
formulas, define names. Now you can use this from top left
dropdown
• Paste with a filter(want only product A) then you have to first
make a table which has same headers and the product A(fill
only product A in the new table). Then you go to Data tab, then
Advanced, Select original table in the list range. Select new
table in the criteria range. Then copy to new location.
• When you are using Advanced Filter to get the unique list,
make sure you have also selected the header. If you don’t, it
would consider the first cell as the header.
• When we have the criteria in the same row, it is
an AND criteria, and when we have it in different rows, it is
an OR criteria
• If there are two products in the table, we can make 2 columns
in new table and write A (or >=1000 for amount) in one column
and B (or <=2000 for amount) in another column and do same
• If it is in same row, then it will be && (not happening when
typing for B && Joe &&>4000
&&<8000)######################
• If we write conditions in different rows then it is ||
This is called advanced filter

Data 58
• <>=4(not equal to 4)
• Q if you want to highlight nights <10. Go to home, go to
conditional formatting and highlight cell rules
• Q month = jan, nights booked < 10 then go to conditional
formatting and then manage rules use this rule
=AND($C17=“January”, $D17 < 10)

Data 49
If rule for writing fail or pass
=IF(C5<=40, “FAIL”, “PASS”)

Data 25
• Highlight cells that contain repeated cities
• For this you can use countIF
• =COUNTIF(D2:D11, D2)>1 (it is the format for one cell, extend it
to all cells of the city). If we use only =COUNTIF(D2:D11, D2)
then it will give no. of occurrence of each city.
• Or you can use conditional formatting -> then highlight cells->
then duplicate values
• How to highlight rows the have Seattle or Spokane?
################

Data 49
• Nested if
• =IF(C5>=70, "A+", IF(C5>=60, "A", IF(C5>=50, "B",
IF(C5>=40, "C", “F"))))

Data 4
• The conditions are given in f2:f11 and g2:g11
• Excel doesn’t do noting after 8th.
• First give name to the range table (suppose marks)
• So use vlookup in this way =VLOOKUP(B3, marks, 2)
• 2 is written to give grade. If we write 1 here, then it will write 60,
65, 70 etc in grade

Data 32
• Search the name in 2nd column if it is available.
• Give name to list 2 (name it list2)
• =MATCH(D3, list2, 0). (0 is for exact match)
• It will give first occurance of each name of list1 present in list2
• If the name not found, it will give error #N/A
• Then use function =IFERROR(F5, “not available”). It will fill “not
available” if there is error in F5
• =ISERROR(F5) will give TRUE when there is error in F5
• =IF(ISERROR(F5), “not available”, “available”) can also be used

Data 34
Data 61
• Right number from 900 to 1000 and data validation
• Select some cells, Go to data->data validation-> whole number-
>between 900 & 1000
• Go to data->data validation-> error alert then give title and msg
• Go to data->data validation-> input msg to give msg when you
move your cursor to these cells
• Enter name drop down list. For this select some cells, go to data-
>data validation-> validation criteria ->list then input a source
list that you want to enter.
• For custom data validation, go to data->data validation->
validation criteria ->custom, then in formula type
=ISNUMBER(L11) or =ISTEXT(L11) to check whether a number
or text is mentioned in the cell

Data 10
• Sum of income when age less than 33.
• For this use =SUMIF(range for criteria, criteria, range of sum)
• E.g. =SUMIF(C3:C7, “<33”, D3:D7)

Data 57
• Sum of income when name is abc
• =SUMIF(A1:A7, “abc”, B1:B7)

Data 8b
• Sum of 1 & 4
• =SUMIF(A1:A7, “=1”, B1:B7)+ SUMIF(A1:A7, “=4”, B1:B7)
• =SUM(SUMIFS(C53:J53,C52:J52,{“1","4"}))
• How to use sumifs here
################################

Data 8a
• Change cell format by right click and format cells

Data 74
• Count student who took course x & y
• =COUNTIFS(D3:G3, "yes", D4:G4, “yes")
Data 9
• Write formula then use formula wizard by clicking Fx

Data 2
• Vlookup use & iferror

Data 18
• Hlookup

Data 12
• Text functions
• Left(text, 4) will give first 4 characters
• Right (text, 4) will give last 4 characters
• Trim(text) will remove extra spaces
• LEN(A2) will give length of string
• MID(A2, 2, 5) will give substring of 5 chars starting from position
2
• FIND(“ “, A3, 1) will give position of first space(this function is
case sensitive)
• Last argument(1) is not necessary
• SEARCH(“r”, A3, 1) will give position of first r irrespective of its
case(checks both lower as well as upper)(Not case sensitive)
• Last argument(1) is not necessary
• CONCATENATE(A1, B1) will concatenate two strings
• We can also join two text by this: =A1&B1
• =A1&” “&B1 will add a space too
• SUBSTITUTE(A3, “g”, “n”, 2) will replace g with n in the text for
first 2 instances. If we don’t write last argument then it will
replace all g with n
• REPLACE(A3,

Data 31
• How to extract 2nd digit of the number and when no digit is
there then write 0
• =MID(A3, 2,1) will give 2nd digit and then =VALUE(last result)
• It will give error if number is of one digit
• Then use iferror(last result, 0)
• Or you can use MID(A3, 2,1) then use NUMBERVALUE(Last
result) will give you 0 if the number is of one digit
• Read how to use NUMBERVALUE, VALUE
• Error trap

Quiz
• ISNA (read the usage) (used when want to check error #N/A)
• IFNA (used when want to take action if meet with error #N/A)
• EXACT (compares exact text)
• REPT (repeats the text n number of times)

Data76(Myfile)
• SUBSTITUTE(A1, “ri”, “mn”, 1) will substitute first instance of ri
with mn
• If we don’t give 1, then it will replace all the instances of ri with
mn

Data 26
• Use of MATCH

Data 27
• INDEX(Table, rownum, colnum) will give the value of particular
cell whose address is given.
• =INDEX(A1:D10, MATCH("Japan", B1:B10,
0),MATCH("Population", A1:D1, 0))
• Last function will find in which row Japan exists. Then it will find
in which column the population is. Then enter the row and
column number in the INDEX function along with the table array.
It will give population of japan’s capital.

Data 24
• What if analysis->goal seek
• Then what is the goal we are changing. Set that as a cell. It
must contain a formula.
• Now change the goal. (Means what if my goal is 69200 sales)
• Then select the cell which will change after setting that goal
• It is reverse of the normal formula (e.g. if A1=10, B1=20,
C1=A1*B1 then what happens is if we change A1 to 20, then C1
changes to 400. This is forward technique. What if our goal is
getting 400 sales then what is the value of A1. Here What if
comes into picture
Random
• Read some of the financial ratios
• Data analysis (regression and correlation)(go to data->data
analysis)
• If not given, then go to tools->add ins-> analysis tools that will
add data analysis tool
• Now use data analysis

Data 28
• Create pivot table
• Go to insert->pivot table
• Draw different columns in different areas of pivot table

Data 22
• Track change by changing price
• Use what if analysis->data table
• 1 way data table, 2 way data table

Data 37
• Draw graph after delimiter

Data 37-48
• Draw graph as per the requirement