EXCEL CERTIFICATION ACTUAL FINALS QUESTIONS
AND ANSWERS SET A+
✔✔create a pivot table - ✔✔column > design > summ w/ PT
✔✔set up a pivot table - ✔✔choose fields to add, switch to rows, columns,
✔✔modify pivot table and work with totals - ✔✔analyze, active field, field settings
pivot table > options to change error values
✔✔slicer pivot tables - ✔✔analyze > insert slicer
✔✔add a pivot chart - ✔✔analyze > pivot chart
can move to different sheet or change filter on it
✔✔trace precedents and dependants - ✔✔formula > trace precedents
✔✔using a watch window - ✔✔formulas > watch window > select cells to watch
✔✔using data validation - ✔✔data > data validation
allowances, (sources for list), input messages, error alerts
✔✔codification scheme for generating numbers - ✔✔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 - ✔✔data > data validation > allow: custom
enter in your formula
✔✔adding trusted locations - ✔✔file> options>trust center>settings>trusted
location>add new location> browse>select folder
, ✔✔recording macros - ✔✔add developer tab
use relative references>record macro> give it a shortcut key, describe it > format the
cells like wanted > stop recording
✔✔macro button - ✔✔developer>insert>button (1st one)> select cells you want it to be
placed> select use
✔✔modifying macros using VBA - ✔✔developer > macros, select macro > edit > copy
code from one to another > file > close and return
✔✔hide or unhide a worksheet / tabs - ✔✔RC surface tab>hide
file>options>advanced, display options>show sheet tabs
✔✔unlock cells / protect sheet - ✔✔home>format>lock cell
protect: home>format>protect sheet> only have unlocked cells checked
✔✔hide formulas - ✔✔format>format cells>protection>hidden
✔✔encrypt a workbook - ✔✔file > middle of screen > protect wb > encrypt (with
password
✔✔mark a workbook as final - ✔✔file > middle of screen > protect wb > mark as final
✔✔ import files - ✔✔data>get external data
specify delimitaton, column formats
✔✔Comments - ✔✔Review > new comment
✔✔Wrap text - ✔✔home > wrap text
✔✔center across selection - ✔✔home > alignment > alignment setting (arrow in BR)>
Horizontal alignment
✔✔insert a column in between each column - ✔✔use CTRL when selecting the columns
✔✔manually adjust column and row height - ✔✔home > format > column width
✔✔export a worksheet to pdf - ✔✔file > export >pdf
✔✔inspect a w/s for personal info - ✔✔file > check for issues > inspect> inspect>
remove all doc prop and personal info
✔✔inspect a w/s for accessibility, compatibility - ✔✔file > check for issues > check
compatibility or accessibility
AND ANSWERS SET A+
✔✔create a pivot table - ✔✔column > design > summ w/ PT
✔✔set up a pivot table - ✔✔choose fields to add, switch to rows, columns,
✔✔modify pivot table and work with totals - ✔✔analyze, active field, field settings
pivot table > options to change error values
✔✔slicer pivot tables - ✔✔analyze > insert slicer
✔✔add a pivot chart - ✔✔analyze > pivot chart
can move to different sheet or change filter on it
✔✔trace precedents and dependants - ✔✔formula > trace precedents
✔✔using a watch window - ✔✔formulas > watch window > select cells to watch
✔✔using data validation - ✔✔data > data validation
allowances, (sources for list), input messages, error alerts
✔✔codification scheme for generating numbers - ✔✔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 - ✔✔data > data validation > allow: custom
enter in your formula
✔✔adding trusted locations - ✔✔file> options>trust center>settings>trusted
location>add new location> browse>select folder
, ✔✔recording macros - ✔✔add developer tab
use relative references>record macro> give it a shortcut key, describe it > format the
cells like wanted > stop recording
✔✔macro button - ✔✔developer>insert>button (1st one)> select cells you want it to be
placed> select use
✔✔modifying macros using VBA - ✔✔developer > macros, select macro > edit > copy
code from one to another > file > close and return
✔✔hide or unhide a worksheet / tabs - ✔✔RC surface tab>hide
file>options>advanced, display options>show sheet tabs
✔✔unlock cells / protect sheet - ✔✔home>format>lock cell
protect: home>format>protect sheet> only have unlocked cells checked
✔✔hide formulas - ✔✔format>format cells>protection>hidden
✔✔encrypt a workbook - ✔✔file > middle of screen > protect wb > encrypt (with
password
✔✔mark a workbook as final - ✔✔file > middle of screen > protect wb > mark as final
✔✔ import files - ✔✔data>get external data
specify delimitaton, column formats
✔✔Comments - ✔✔Review > new comment
✔✔Wrap text - ✔✔home > wrap text
✔✔center across selection - ✔✔home > alignment > alignment setting (arrow in BR)>
Horizontal alignment
✔✔insert a column in between each column - ✔✔use CTRL when selecting the columns
✔✔manually adjust column and row height - ✔✔home > format > column width
✔✔export a worksheet to pdf - ✔✔file > export >pdf
✔✔inspect a w/s for personal info - ✔✔file > check for issues > inspect> inspect>
remove all doc prop and personal info
✔✔inspect a w/s for accessibility, compatibility - ✔✔file > check for issues > check
compatibility or accessibility
