Download Excel Certification Exam. and more Exams Advanced Education in PDF only on Docsity!
Excel Certification Exam.
Save import files - ANSWERdata>get external data specify delimitaton, column formats Comments - ANSWERReview > new comment Wrap text - ANSWERhome > wrap text center across selection - ANSWERhome > alignment > alignment setting (arrow in BR)> Horizontal alignment insert a column in between each column - ANSWERuse CTRL when selecting the columns manually adjust column and row height - ANSWERhome > format > column width export a worksheet to pdf - ANSWERfile > export >pdf inspect a w/s for personal info - ANSWERfile > check for issues > inspect> inspect> remove all doc prop and personal info
inspect a w/s for accessibility, compatibility - ANSWERfile > check for issues > check compatibility or accessibility adjust page breaks - ANSWERview > page break preview> drag around specify print titles - ANSWERpage layout > print titles > select rows and columns to have print on each page change page margins and scaling - ANSWERfile > print outline data - ANSWERdata > group >auto outline (has to already have subtotals) format cell borders - ANSWERhome > font > square format painter - ANSWERselect cells > clipboard > format painter DC to do sets of cells esc to stop insert image - ANSWEReasier fit: format, arrange and align, snap to grid sum using destination cells - ANSWERclick where you want it to go > auto sum sum using source cells - ANSWERselect the cells you want it to use > autosum
sum in a total table row and filter results - ANSWERdesign > total row can change filter to change results conditional formatting: highlighting - ANSWERhome > conditional formatting > highlight conditional formatting: icon sets - ANSWERconditional formatting > icon sets relative cell referencing - ANSWERcell reference in formula that changes automatically when formula moved absolute cell referencing - ANSWERcell reference in formula that wont change when moved to different lacation ex. $A$ mixed cell referencing - ANSWEReither the row or the column is an absolute reference Int function - ANSWERrounds number down to nearest integer INT(cell #) abs function - ANSWERreturns number without the - sign ABS(cell #) Statistical functions - ANSWERmedian: MEDIAN(cell 3)
mode.sngl: one most common occurring value in range mode.mult: more than 1 common number date and time functions - ANSWERdate: TODAY() NOW(): the current date and time DATEDIF - ANSWER=DATEDIF(cell #, cell #, "Y or M or D" Find, left,right fxns - ANSWERF: 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 - ANSWERto uppercase to lowercase capitalizes first letter in each word fxn(cell#) concatenate fxns - ANSWERjoins text strings together concatenate(cell#) Vlookup - ANSWERlooks 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))
insert data bars - ANSWERselect ref cells > conditional formatting > data bars CountIF fxn - ANSWERcount only the values that meet a specific criteria COUNTIF(range,"criteria", range if not the same as original) AverageIF function - ANSWERaverage only the values that meet a specific criteria AVERAGEIF(range,"criteria", range if not the same as original) SUMIF fxn - ANSWERsum 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 - ANSWERcopy sheet within page, rename backup advanced filters in a data table - ANSWERcopy column headings, place in some space above table, put criteria wanted under column data > advanced slicers in a data table - ANSWERinsert > slicer > select column to filter summarize a dataset - ANSWERmake a little table with average, subtotal and average if
create a pivot table - ANSWERcolumn > design > summ w/ PT set up a pivot table - ANSWERchoose fields to add, switch to rows, columns, modify pivot table and work with totals - ANSWERanalyze, active field, field settings pivot table > options to change error values slicer pivot tables - ANSWERanalyze > insert slicer add a pivot chart - ANSWERanalyze > pivot chart can move to different sheet or change filter on it trace precedents and dependants - ANSWERformula > trace precedents using a watch window - ANSWERformulas > watch window > select cells to watch using data validation - ANSWERdata > data validation allowances, (sources for list), input messages, error alerts codification scheme for generating numbers - ANSWERoh 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),"")
encrypt a workbook - ANSWERfile > middle of screen > protect wb > encrypt (with password mark a workbook as final - ANSWERfile > middle of screen > protect wb > mark as final
