Excel Exam
Insert a row right click on a row number on the left,
add row
Columns vertical lettering
Rows horizontal numbers
months/days Autofill, type 1 month, day hold left and
drag down, right click, filter weekdays etc
Order of excel calculations
1. () Use parentheses when adding and
2. ^ subtracting, otherwise Excel will multiply
3. */ or divide by
4. + -
BASIC
FUNCTIONS
=Function – to calculate something, =C2 + / * E2
=Function with $ to make sure a number is fixed,
=C2*$H$1+E2 OR C2* H1 press F4 +E2
= Sum - to calculate a total, continue to the last
number
Average or divide function =cell / count, enter
car sum – top left of the home menu, automatically
adds your rows, can also select cells in
advance
If you want to add several columns, select
everything plus an extra row to the left and
below
average – automsum arrow from below, same
principle as autosumn
% change calculate percent change, =(C6+B6)/B6,
home, percent, increase decimal to show %
C6= the number of which you want to
calculate the difference from the first
number B6= The first number
Create a customized list of names – from above in mac menu, excel,
preferences, custom list, add, type first
word and drag
=LARGEST/LOWEST(A4:A8) To find the largest/Smallest number
(A4:A8;2) second largest
, Excel Exam
flash fill -to extract and paste info from another
column, type first word, drag down, right
click, flash fill
IF function to change something if something is
true/false, =if(C2 >4; “4000”; ””)
Not equal to sign <> If cell is less than 4 it will be changed to
=> 4000 otherwise empty
>= IF(C3=”yes”;4;7
blank cells =””
Cell colors Conditional formatting new rule, add the
comment ( A2>A4), apply to whole sheet
AND function – Multiple criteria, – Multiple criteria, =if(and(F2=”Full
=if(and(F2=”Full Time”,J2>3),300,””) Time”,J2>3),300,””)
If both conditions are true, the answer is...
If F2 equals full time and j2 is less than 3,
the answer 300 is empty otherwise
=IF(OR function – multiple criteria not present,
=if(or(F2”Full Time”,J2>3),300,””
=CONCATENATE function – To combine 2 cells into a third
(=CONCATENATE(e4,“ “,f4)
=IF(NOT function If You're not fulltime you if not correct, you get this, (IF(not(“Full
get a bonus of 300, if you're full time and Time”), 300, if(J2>3,1000,500))
you have a job rating greater than 3, you
get 1000 dollars otherwise 500 If You're not fulltime you get a bonus of
300, if you're full time and you have a job
rating greater than 3, you get 1000 dollars
otherwise 500
=VLOOKUP function to search/compare something from a
column to another table, an approximately
correct answer VERTICAL
= VLOOKUP(C2,G1:H13 (where to look eg
table),2
= vlookup(C2,$G$1:$H$13.2)
C2 = number you want to look up
2= from which column he should look for
the answer
=HLOOKUP – same but for a horizontal table
=hlookup(c2,$G$1:$H*13.2)
New price – old price * Percent + old price
Insert a row right click on a row number on the left,
add row
Columns vertical lettering
Rows horizontal numbers
months/days Autofill, type 1 month, day hold left and
drag down, right click, filter weekdays etc
Order of excel calculations
1. () Use parentheses when adding and
2. ^ subtracting, otherwise Excel will multiply
3. */ or divide by
4. + -
BASIC
FUNCTIONS
=Function – to calculate something, =C2 + / * E2
=Function with $ to make sure a number is fixed,
=C2*$H$1+E2 OR C2* H1 press F4 +E2
= Sum - to calculate a total, continue to the last
number
Average or divide function =cell / count, enter
car sum – top left of the home menu, automatically
adds your rows, can also select cells in
advance
If you want to add several columns, select
everything plus an extra row to the left and
below
average – automsum arrow from below, same
principle as autosumn
% change calculate percent change, =(C6+B6)/B6,
home, percent, increase decimal to show %
C6= the number of which you want to
calculate the difference from the first
number B6= The first number
Create a customized list of names – from above in mac menu, excel,
preferences, custom list, add, type first
word and drag
=LARGEST/LOWEST(A4:A8) To find the largest/Smallest number
(A4:A8;2) second largest
, Excel Exam
flash fill -to extract and paste info from another
column, type first word, drag down, right
click, flash fill
IF function to change something if something is
true/false, =if(C2 >4; “4000”; ””)
Not equal to sign <> If cell is less than 4 it will be changed to
=> 4000 otherwise empty
>= IF(C3=”yes”;4;7
blank cells =””
Cell colors Conditional formatting new rule, add the
comment ( A2>A4), apply to whole sheet
AND function – Multiple criteria, – Multiple criteria, =if(and(F2=”Full
=if(and(F2=”Full Time”,J2>3),300,””) Time”,J2>3),300,””)
If both conditions are true, the answer is...
If F2 equals full time and j2 is less than 3,
the answer 300 is empty otherwise
=IF(OR function – multiple criteria not present,
=if(or(F2”Full Time”,J2>3),300,””
=CONCATENATE function – To combine 2 cells into a third
(=CONCATENATE(e4,“ “,f4)
=IF(NOT function If You're not fulltime you if not correct, you get this, (IF(not(“Full
get a bonus of 300, if you're full time and Time”), 300, if(J2>3,1000,500))
you have a job rating greater than 3, you
get 1000 dollars otherwise 500 If You're not fulltime you get a bonus of
300, if you're full time and you have a job
rating greater than 3, you get 1000 dollars
otherwise 500
=VLOOKUP function to search/compare something from a
column to another table, an approximately
correct answer VERTICAL
= VLOOKUP(C2,G1:H13 (where to look eg
table),2
= vlookup(C2,$G$1:$H$13.2)
C2 = number you want to look up
2= from which column he should look for
the answer
=HLOOKUP – same but for a horizontal table
=hlookup(c2,$G$1:$H*13.2)
New price – old price * Percent + old price
