EXCEL CRASH COURSE ACTUAL STUDYS ANSWERS
AND QUESTIONS SURE A+
✔✔to ungroup worksheets - ✔✔press *Ctrl PageUp* or *Ctrl PageDown* until you reach
one of the ungrouped worksheets; this automatically groups all of the previously
grouped sheets
✔✔auditing cells - ✔✔go to the cel and hit F2
OR
hitting *Ctrl [* on a cell will highlight the precedent cells
-keep hitting *Ctrl [* and it will take you to the next precedent, and on and on
hitting *Ctrl ]* on a cell will do the same thing but for dependent cells
✔✔Excel's formula auditor - ✔✔trace precedents: *Alt m p*
-to trace cells that provide data to a formula
-a worksheet icon indicates that the precedent cells are in another worksheet
-double clicking anywhere with a mouse on the black arrow brings up the 'Go To'
screen; selecting either of the precedent cell locations and hitting 'Ok' will take you there
trace dependents: *Alt m d*
-to trace formulas that reference a particular cell (the cell highlighted)
remove trace arrows: *Alt m a a*
✔✔evaluate formula - ✔✔*Alt m v*
✔✔track changes - ✔✔*Alt r g*
✔✔zoom to selection - ✔✔*Alt w g*
✔✔"go to" - ✔✔*F5*
, ✔✔Error: formula treated as text - ✔✔forgetting the equal sign (=) at the start or
inserting a space before the equal sign of the formula is created by Excel as text
✔✔Error: #Div/0! - ✔✔dividing a number by 0
✔✔Error: #REF! - ✔✔an invalid cell reference
✔✔Error: #NUM! - ✔✔inserting invalid numerical values in a formula or function
✔✔Error: #NAME? - ✔✔unrecognizable text in a formula
✔✔Error: #VALUE! - ✔✔inserting incorrect arguments in a function
✔✔Error: ##### - ✔✔when the column is not wide enough to fit the data in a cell
you must widen the cell to fix the error (*Alt h o i*)
✔✔logical functions: IF - ✔✔the IF function returns one value if a condition you specify
evaluates to TRUE, and another value if it evaluates to FALSE
IF statements follow the same structure = IF(x,y,z) where:
x: criteria
y: output if criteria evaluates to true
z: output if criteria evaluates to false
text output is designated by quotation marks around the outputs
✔✔error-trapping function IFERROR - ✔✔=IFERROR(value, value_if_error) returns a
value you specify if a formula evaluates to an error. If the formula does not result in an
error, IFERROR returns the result of the formula
✔✔creating dynamic headers by combining cell references with text ("&") - ✔✔Excel
allow users to combine (or "concatenate") cells with a text string in them with other text
strings, creating one text string by using the "&" function
✔✔EOMONTH - ✔✔=EOMONTH(start_date, months)
allows you to create monthly date headers, by outputting the last day of a specified
month
start_date: represents a starting date reference
months: represents x number of months before or after the start_date
to output a date x months before a start_date, x should be negative
AND QUESTIONS SURE A+
✔✔to ungroup worksheets - ✔✔press *Ctrl PageUp* or *Ctrl PageDown* until you reach
one of the ungrouped worksheets; this automatically groups all of the previously
grouped sheets
✔✔auditing cells - ✔✔go to the cel and hit F2
OR
hitting *Ctrl [* on a cell will highlight the precedent cells
-keep hitting *Ctrl [* and it will take you to the next precedent, and on and on
hitting *Ctrl ]* on a cell will do the same thing but for dependent cells
✔✔Excel's formula auditor - ✔✔trace precedents: *Alt m p*
-to trace cells that provide data to a formula
-a worksheet icon indicates that the precedent cells are in another worksheet
-double clicking anywhere with a mouse on the black arrow brings up the 'Go To'
screen; selecting either of the precedent cell locations and hitting 'Ok' will take you there
trace dependents: *Alt m d*
-to trace formulas that reference a particular cell (the cell highlighted)
remove trace arrows: *Alt m a a*
✔✔evaluate formula - ✔✔*Alt m v*
✔✔track changes - ✔✔*Alt r g*
✔✔zoom to selection - ✔✔*Alt w g*
✔✔"go to" - ✔✔*F5*
, ✔✔Error: formula treated as text - ✔✔forgetting the equal sign (=) at the start or
inserting a space before the equal sign of the formula is created by Excel as text
✔✔Error: #Div/0! - ✔✔dividing a number by 0
✔✔Error: #REF! - ✔✔an invalid cell reference
✔✔Error: #NUM! - ✔✔inserting invalid numerical values in a formula or function
✔✔Error: #NAME? - ✔✔unrecognizable text in a formula
✔✔Error: #VALUE! - ✔✔inserting incorrect arguments in a function
✔✔Error: ##### - ✔✔when the column is not wide enough to fit the data in a cell
you must widen the cell to fix the error (*Alt h o i*)
✔✔logical functions: IF - ✔✔the IF function returns one value if a condition you specify
evaluates to TRUE, and another value if it evaluates to FALSE
IF statements follow the same structure = IF(x,y,z) where:
x: criteria
y: output if criteria evaluates to true
z: output if criteria evaluates to false
text output is designated by quotation marks around the outputs
✔✔error-trapping function IFERROR - ✔✔=IFERROR(value, value_if_error) returns a
value you specify if a formula evaluates to an error. If the formula does not result in an
error, IFERROR returns the result of the formula
✔✔creating dynamic headers by combining cell references with text ("&") - ✔✔Excel
allow users to combine (or "concatenate") cells with a text string in them with other text
strings, creating one text string by using the "&" function
✔✔EOMONTH - ✔✔=EOMONTH(start_date, months)
allows you to create monthly date headers, by outputting the last day of a specified
month
start_date: represents a starting date reference
months: represents x number of months before or after the start_date
to output a date x months before a start_date, x should be negative
