Excel Certification Exam Questions
and Answers
mix / max functions - Answer-returns min or max number of the cells
MIN(cell #, cell #...) or MAX(cell #, cell #...)
sum in a total table row and filter results - Answer-design > total row
can change filter to change results
conditional formatting: highlighting - Answer-home > conditional formatting >
highlight
conditional formatting: icon sets - Answer-conditional formatting > icon sets
relative cell referencing - Answer-cell reference in formula that changes
automatically when formula moved
absolute cell referencing - Answer-cell reference in formula that wont change when
moved to different lacation
ex. $A$4
mixed cell referencing - Answer-either the row or the column is an absolute
reference
Int function - Answer-rounds number down to nearest integer
INT(cell #)
abs function - Answer-returns number without the - sign
ABS(cell #)
Statistical functions - Answer-median: MEDIAN(cell 3)
mode.sngl: one most common occurring value in range
mode.mult: more than 1 common number
date and time functions - Answer-date: TODAY()
NOW(): the current date and time
DATEDIF - Answer-=DATEDIF(cell #, cell #, "Y or M or D"
Find, left,right fxns - Answer-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 - Answer-to uppercase
to lowercase
capitalizes first letter in each word
, fxn(cell#)
concatenate fxns - Answer-joins text strings together
concatenate(cell#)
Vlookup - Answer-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 - Answer-finds periodic payment for paying off loans etc
=-PMT(rate per month, #of payments, amount borrowed)
if / and functions - Answer-=IF(test, return if true, return if false)
=AND(test, test) will return true or false
move chart to new sheet - Answer-chart tools > design > move chart
create the different charts - Answer-select the table > box in BR > select wanted
chart
create a combination chart - Answer-select the table > box in BR >more>all
charts>combo
change series names in chart - Answer-design > select data> edit series
insert object into chart - Answer-same as inserting a picture, just click on chart first
exploding pie chart - Answer-click on singular slice, then drag out
chart to 3D - Answer-change chart type
insert sparklines - Answer-home> sparklines
insert data bars - Answer-select ref cells > conditional formatting > data bars
CountIF fxn - Answer-count only the values that meet a specific criteria
COUNTIF(range,"criteria", range if not the same as original)
AverageIF function - Answer-average only the values that meet a specific criteria
AVERAGEIF(range,"criteria", range if not the same as original)
SUMIF fxn - Answer-sum only the values that meet a specific criteria
SUMIF(range,"criteria", range if not the same as original)
writing a nested if - Answer-
create a backup copy of data - Answer-copy sheet within page, rename backup
advanced filters in a data table - Answer-copy column headings, place in some
space above table, put criteria wanted under column
and Answers
mix / max functions - Answer-returns min or max number of the cells
MIN(cell #, cell #...) or MAX(cell #, cell #...)
sum in a total table row and filter results - Answer-design > total row
can change filter to change results
conditional formatting: highlighting - Answer-home > conditional formatting >
highlight
conditional formatting: icon sets - Answer-conditional formatting > icon sets
relative cell referencing - Answer-cell reference in formula that changes
automatically when formula moved
absolute cell referencing - Answer-cell reference in formula that wont change when
moved to different lacation
ex. $A$4
mixed cell referencing - Answer-either the row or the column is an absolute
reference
Int function - Answer-rounds number down to nearest integer
INT(cell #)
abs function - Answer-returns number without the - sign
ABS(cell #)
Statistical functions - Answer-median: MEDIAN(cell 3)
mode.sngl: one most common occurring value in range
mode.mult: more than 1 common number
date and time functions - Answer-date: TODAY()
NOW(): the current date and time
DATEDIF - Answer-=DATEDIF(cell #, cell #, "Y or M or D"
Find, left,right fxns - Answer-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 - Answer-to uppercase
to lowercase
capitalizes first letter in each word
, fxn(cell#)
concatenate fxns - Answer-joins text strings together
concatenate(cell#)
Vlookup - Answer-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 - Answer-finds periodic payment for paying off loans etc
=-PMT(rate per month, #of payments, amount borrowed)
if / and functions - Answer-=IF(test, return if true, return if false)
=AND(test, test) will return true or false
move chart to new sheet - Answer-chart tools > design > move chart
create the different charts - Answer-select the table > box in BR > select wanted
chart
create a combination chart - Answer-select the table > box in BR >more>all
charts>combo
change series names in chart - Answer-design > select data> edit series
insert object into chart - Answer-same as inserting a picture, just click on chart first
exploding pie chart - Answer-click on singular slice, then drag out
chart to 3D - Answer-change chart type
insert sparklines - Answer-home> sparklines
insert data bars - Answer-select ref cells > conditional formatting > data bars
CountIF fxn - Answer-count only the values that meet a specific criteria
COUNTIF(range,"criteria", range if not the same as original)
AverageIF function - Answer-average only the values that meet a specific criteria
AVERAGEIF(range,"criteria", range if not the same as original)
SUMIF fxn - Answer-sum only the values that meet a specific criteria
SUMIF(range,"criteria", range if not the same as original)
writing a nested if - Answer-
create a backup copy of data - Answer-copy sheet within page, rename backup
advanced filters in a data table - Answer-copy column headings, place in some
space above table, put criteria wanted under column
