EXCEL CERTIFICATION SURE PASS QUESTIONS AND
ANSWERS SET A+
✔✔conditional formatting: highlighting - ✔✔home > conditional formatting > highlight
✔✔conditional formatting: icon sets - ✔✔conditional formatting > icon sets
✔✔relative cell referencing - ✔✔cell reference in formula that changes automatically
when formula moved
✔✔absolute cell referencing - ✔✔cell reference in formula that wont change when
moved to different lacation
ex. $A$4
✔✔mixed cell referencing - ✔✔either the row or the column is an absolute reference
✔✔Int function - ✔✔rounds number down to nearest integer
INT(cell #)
✔✔abs function - ✔✔returns number without the - sign
ABS(cell #)
✔✔Statistical functions - ✔✔median: MEDIAN(cell 3)
mode.sngl: one most common occurring value in range
mode.mult: more than 1 common number
✔✔date and time functions - ✔✔date: TODAY()
NOW(): the current date and time
✔✔DATEDIF - ✔✔=DATEDIF(cell #, cell #, "Y or M or D"
✔✔Find, left,right fxns - ✔✔F: looks for a given character
, FIND("char wanted",cell name)
L/R: returns text starting from left side
LEFT(cell #, # of char wanted)
actual use: LEFT(cell number, FIND("char wanted",cell name)
✔✔Upper/lower/proper functions - ✔✔to uppercase
to lowercase
capitalizes first letter in each word
fxn(cell#)
✔✔concatenate fxns - ✔✔joins text strings together
concatenate(cell#)
✔✔Vlookup - ✔✔looks up the matching value in a table
VLOOKUP(value to look for, table to search, col # that matching value will be taken,
true (close) or false (exact))
✔✔PMT function - ✔✔finds periodic payment for paying off loans etc
=-PMT(rate per month, #of payments, amount borrowed)
✔✔if / and functions - ✔✔=IF(test, return if true, return if false)
=AND(test, test) will return true or false
✔✔move chart to new sheet - ✔✔chart tools > design > move chart
✔✔create the different charts - ✔✔select the table > box in BR > select wanted chart
✔✔create a combination chart - ✔✔select the table > box in BR >more>all
charts>combo
✔✔change series names in chart - ✔✔design > select data> edit series
✔✔insert object into chart - ✔✔same as inserting a picture, just click on chart first
✔✔exploding pie chart - ✔✔click on singular slice, then drag out
✔✔chart to 3D - ✔✔change chart type
✔✔insert sparklines - ✔✔home> sparklines
✔✔insert data bars - ✔✔select ref cells > conditional formatting > data bars
✔✔CountIF fxn - ✔✔count only the values that meet a specific criteria
COUNTIF(range,"criteria", range if not the same as original)
ANSWERS SET A+
✔✔conditional formatting: highlighting - ✔✔home > conditional formatting > highlight
✔✔conditional formatting: icon sets - ✔✔conditional formatting > icon sets
✔✔relative cell referencing - ✔✔cell reference in formula that changes automatically
when formula moved
✔✔absolute cell referencing - ✔✔cell reference in formula that wont change when
moved to different lacation
ex. $A$4
✔✔mixed cell referencing - ✔✔either the row or the column is an absolute reference
✔✔Int function - ✔✔rounds number down to nearest integer
INT(cell #)
✔✔abs function - ✔✔returns number without the - sign
ABS(cell #)
✔✔Statistical functions - ✔✔median: MEDIAN(cell 3)
mode.sngl: one most common occurring value in range
mode.mult: more than 1 common number
✔✔date and time functions - ✔✔date: TODAY()
NOW(): the current date and time
✔✔DATEDIF - ✔✔=DATEDIF(cell #, cell #, "Y or M or D"
✔✔Find, left,right fxns - ✔✔F: looks for a given character
, FIND("char wanted",cell name)
L/R: returns text starting from left side
LEFT(cell #, # of char wanted)
actual use: LEFT(cell number, FIND("char wanted",cell name)
✔✔Upper/lower/proper functions - ✔✔to uppercase
to lowercase
capitalizes first letter in each word
fxn(cell#)
✔✔concatenate fxns - ✔✔joins text strings together
concatenate(cell#)
✔✔Vlookup - ✔✔looks up the matching value in a table
VLOOKUP(value to look for, table to search, col # that matching value will be taken,
true (close) or false (exact))
✔✔PMT function - ✔✔finds periodic payment for paying off loans etc
=-PMT(rate per month, #of payments, amount borrowed)
✔✔if / and functions - ✔✔=IF(test, return if true, return if false)
=AND(test, test) will return true or false
✔✔move chart to new sheet - ✔✔chart tools > design > move chart
✔✔create the different charts - ✔✔select the table > box in BR > select wanted chart
✔✔create a combination chart - ✔✔select the table > box in BR >more>all
charts>combo
✔✔change series names in chart - ✔✔design > select data> edit series
✔✔insert object into chart - ✔✔same as inserting a picture, just click on chart first
✔✔exploding pie chart - ✔✔click on singular slice, then drag out
✔✔chart to 3D - ✔✔change chart type
✔✔insert sparklines - ✔✔home> sparklines
✔✔insert data bars - ✔✔select ref cells > conditional formatting > data bars
✔✔CountIF fxn - ✔✔count only the values that meet a specific criteria
COUNTIF(range,"criteria", range if not the same as original)
