Excel Flashcards
Yes or No
=If(D1>=100,”Yes”,”No)
Find the total category
=Sumif(B1:B10,G2,C1:C10)
B1:B10 is the total of food types
C1:C10 is the total of amounts
G2 is a specific food type
Lock row and column
=sumif($B$1:$B$10,G2,$C$:$C$10)
$ is used to lock the row and column
The formula for Automatically applying to a new row
=Sum(mytable[Amount])
This formula works when we name the table, especially select amount column from mytable
Unique values
=unique(mytable[Date])
it will display unique values
unique values with the total amount
=unique(mytable[Date],G7,mytable[amount)
G7 is cell of date
Leading Space
Space before Text
Trailing space
space after text
remove leading space
=TRIM(movies[studio])
FIND AND REPLACE
ctrl f or ctrl h
Paste the values
ctrl + alt + v
- Find Duplicates
- Remove Duplicates
- Conditional formatting
- In Data
Split the column
use text to column in data
delimited
fixed width
Combine data for a specific product
=VLOOKUP(productid, finance,2,false)
this only search first column not middle or last 2 is no of column
look vlookup value
=VLOOKUP([@[productid:[productid]], =finance,2,false)
vlookup
=vlookup([@[movie_id]],finance,2,false)
lock vlookup
=vlookup(Movies[@[movie_id]:[movie_id]],finance[#all],2,false)
print all headers
Food[#Headers]
food is table name
print Entire Table
food[#all]
food is table name
What is index
The index is used to return a value from a specific range or location
what is match
Match is used to return the position of specific value
formula of index
=index(food,2,1)
food is table name
2 is row
1 is column
formula of match
=match(movie_id, finance[movie_id],0)
movie id is column name
finance is table name
0 is match type
o return exact match
1 return approximate ascending match eg if 25 —> 30
-1 return approximate descending match eg if 25—>20
index match
=index(finance, match([@[movieid]], finance[movieid],0),match(“budget”,finance[#header],0)
finance is table name, movieid is header, budget is header
