EXCEL CERTIFICATION EXAM QUESTIONS AND ANSWERS

EXCEL CERTIFICATION EXAM
QUESTIONS AND ANSWERS
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
data > advanced

slicers in a data table - Answer-insert > slicer > select column to filter

summarize a dataset - Answer-make a little table with average, subtotal and average
if

create a pivot table - Answer-column > design > summ w/ PT

set up a pivot table - Answer-choose fields to add, switch to rows, columns,

modify pivot table and work with totals - Answer-analyze, active field, field settings
pivot table > options to change error values

slicer pivot tables - Answer-analyze > insert slicer

add a pivot chart - Answer-analyze > pivot chart
can move to different sheet or change filter on it

, trace precedents and dependants - Answer-formula > trace precedents

using a watch window - Answer-formulas > watch window > select cells to watch

using data validation - Answer-data > data validation
allowances, (sources for list), input messages, error alerts

codification scheme for generating numbers - Answer-oh my god okay
IF(cell # >0, TEXT(cell #, "YYYMMDD", "")&" "IF(cell#>0, TEXT(cell#, "HHMM"),"")&"
"&IF(cell#>0, VLOOKUP(cell#,table,column to look at, FALSE),"")

custom validation rules - Answer-data > data validation > allow: custom
enter in your formula

adding trusted locations - Answer-file> options>trust center>settings>trusted
location>add new location> browse>select folder

recording macros - Answer-add developer tab
use relative references>record macro> give it a shortcut key, describe it > format the
cells like wanted > stop recording

macro button - Answer-developer>insert>button (1st one)> select cells you want it to
be placed> select use

modifying macros using VBA - Answer-developer > macros, select macro > edit >
copy code from one to another > file > close and return

hide or unhide a worksheet / tabs - Answer-RC surface tab>hide
file>options>advanced, display options>show sheet tabs

unlock cells / protect sheet - Answer-home>format>lock cell
protect: home>format>protect sheet> only have unlocked cells checked

hide formulas - Answer-format>format cells>protection>hidden

encrypt a workbook - Answer-file > middle of screen > protect wb > encrypt (with
password

mark a workbook as final - Answer-file > middle of screen > protect wb > mark as
final
import files - Answer-data>get external data
specify delimitaton, column formats

Comments - Answer-Review > new comment

Wrap text - Answer-home > wrap text