100% A+ ANSWER SHEET - WALL STREET PREP
EXCEL CRASH COURSE EXAM ANSWER SHEET
"Get inside a drop-down list - CORRECT ANSWER Alt+Up/DownArrow (Option+Up/DownArrow)"
"Get Rid of Borders - CORRECT ANSWER Ctrl+Shift+-"
"Scenario Analysis Using XLOOKUP - CORRECT ANSWER You can use XLOOKUP's ability to generate
multiple values to create a scenario analysis for something like an income statement"
"SUMPRODUCT - CORRECT ANSWER =SUMPRODUCT(array1,array2,array3,...) multiples corresponding
components in two or more arrays and returns the sum of those products
A lesser known features is the ability to embed criteria directly into the arrays"
"SEARCH - CORRECT ANSWER =SEARCH(find text, within text, start number)
Finds the text within the string text starting at the start number position and outputs the position of the
text in the string"
"FIND - CORRECT ANSWER =FIND(find text, within text, [start number])
The same as SEARCH but is case sensitive!"
"SUBSTITUTE - CORRECT ANSWER =SUBSTITUTE(text, old text, new text, [instance number]) replaces
old text within a string of text ("text") with new text
If there are several instances of the old text, you can identify which instance with instance number"
"REPLACE - CORRECT ANSWER =REPLACE(old text, start number, number of characters, new text)
replaces a portion of a string with another string/number, where the portion of the string being replaced
is identified by the starting number position and the number of characters
Note: To replace with text, use quotation marks!"
"Flash Fill - CORRECT ANSWER Excel 2013 has introduced a real improvement to working with large data
sets in the form of Flash Fill
Tries to guess at to what kind of data you're trying to get at
Shortcut: Ctrl+E or Alt+A+F+F (Data > Flash Fill)"
"Remove Duplicates - CORRECT ANSWER Data > Remove Duplicates
Highlight the relevant data range and then click remove duplicates
Select all columns to ensure true duplicates"
,"Combining Sort & Subtotal - CORRECT ANSWER Combining Sort with Subtotal - Alt+A+B (Data >
Subtotal) - can add further clarity to data sets
Sort by category and then sum"
"COUNTIF as an array - CORRECT ANSWER If you want to see if any values in an array equal any values
in the criteria, you can use COUNTIF. However, just using COUNTIF gives you 0 and simply stores the
proper array.
To properly count, you can use SUMPRODUCT and COUNTIF: =SUMPRODUCT(COUNTIF(range array,
criteria array)"
"Recording Macros - CORRECT ANSWER Excel allows you to record a sequence of instructions, and assign
a keyboard shortcut to invoke them as desired
These instructions are called macros
Go to File > Record Macro"
"Name Box - CORRECT ANSWER Tells you what cell you are in (top left below the ribbon)"
"Formula Bar - CORRECT ANSWER When you insert a formula into a cell and hit return, the cell will show
you the output
The formula bar, however, will show you the formula
Next to "fx" right below the ribbon"
"Moving Between Worksheets - CORRECT ANSWER Ctrl + PageDown/PageUp
(Option + RightArrow/LeftArrow)"
"Adding Worksheets - CORRECT ANSWER (Fn Shift F11)"
"Columns - CORRECT ANSWER Alphabetically labeled (A, B, C, etc.)"
"Rows - CORRECT ANSWER Numerically labeled (1, 2, 3, etc.)"
"How to Access Ribbon on Mac - CORRECT ANSWER Ctrl Fn F2
Use arrow keys to peruse the Mac ribbon"
"Using Function Keys - CORRECT ANSWER Hit Fn and then the function key to use the function keys
themselves on a Mac"
"Open a New Workout - CORRECT ANSWER Ctrl N
Cmnd N"
, "Save a File - CORRECT ANSWER Ctrl S
Cmnd S"
"Print a File - CORRECT ANSWER Ctrl P
Cmnd P"
"Open a File - CORRECT ANSWER Ctrl O
Cmnd O"
"Toolbars - CORRECT ANSWER Within each of the main tabs you will find all of Excel's features, grouped
by commands"
"Bold Command - CORRECT ANSWER Ctrl B
Cmnd B"
"The Home Tab - CORRECT ANSWER Includes most formatting properties:
Font type, size, and color
Background color
Text/cell alignment
Changing currency, decimal, percent formats
Inserting, deleting, and hiding rows and columns
Adjusting columns and rows width
Inserting, deleting, and renaming worksheets"
"The Insert Tab - CORRECT ANSWER Important Features: Pivot Table, Charts, Header & Footer"
"Page Layout Tab - CORRECT ANSWER Important Features: Print Area, Fonts, Gridlines"
"Formulas Tab - CORRECT ANSWER Important Features: Insert Function, Function Library, Name
Manager, Formula Auditing Tools (Trace Precedents, Trace Dependents)"
"Data Tab - CORRECT ANSWER Important Features: Filter, Sort, Text to Columns, Flash Fill, Data
Validation, Data Tables (Goal Seek), Group/Ungroup"
"Review Tab - CORRECT ANSWER Important Features: New Comment"
"View Tab - CORRECT ANSWER Important Features: Zoom, Gridlines"
"Developer Tab - CORRECT ANSWER Important Features: Record Macro, Add-Ins"
"Accessing Settings (Excel Options) - CORRECT ANSWER File > Options (Alt F T or Alt T O)
MAC: Excel > Preferences"
EXCEL CRASH COURSE EXAM ANSWER SHEET
"Get inside a drop-down list - CORRECT ANSWER Alt+Up/DownArrow (Option+Up/DownArrow)"
"Get Rid of Borders - CORRECT ANSWER Ctrl+Shift+-"
"Scenario Analysis Using XLOOKUP - CORRECT ANSWER You can use XLOOKUP's ability to generate
multiple values to create a scenario analysis for something like an income statement"
"SUMPRODUCT - CORRECT ANSWER =SUMPRODUCT(array1,array2,array3,...) multiples corresponding
components in two or more arrays and returns the sum of those products
A lesser known features is the ability to embed criteria directly into the arrays"
"SEARCH - CORRECT ANSWER =SEARCH(find text, within text, start number)
Finds the text within the string text starting at the start number position and outputs the position of the
text in the string"
"FIND - CORRECT ANSWER =FIND(find text, within text, [start number])
The same as SEARCH but is case sensitive!"
"SUBSTITUTE - CORRECT ANSWER =SUBSTITUTE(text, old text, new text, [instance number]) replaces
old text within a string of text ("text") with new text
If there are several instances of the old text, you can identify which instance with instance number"
"REPLACE - CORRECT ANSWER =REPLACE(old text, start number, number of characters, new text)
replaces a portion of a string with another string/number, where the portion of the string being replaced
is identified by the starting number position and the number of characters
Note: To replace with text, use quotation marks!"
"Flash Fill - CORRECT ANSWER Excel 2013 has introduced a real improvement to working with large data
sets in the form of Flash Fill
Tries to guess at to what kind of data you're trying to get at
Shortcut: Ctrl+E or Alt+A+F+F (Data > Flash Fill)"
"Remove Duplicates - CORRECT ANSWER Data > Remove Duplicates
Highlight the relevant data range and then click remove duplicates
Select all columns to ensure true duplicates"
,"Combining Sort & Subtotal - CORRECT ANSWER Combining Sort with Subtotal - Alt+A+B (Data >
Subtotal) - can add further clarity to data sets
Sort by category and then sum"
"COUNTIF as an array - CORRECT ANSWER If you want to see if any values in an array equal any values
in the criteria, you can use COUNTIF. However, just using COUNTIF gives you 0 and simply stores the
proper array.
To properly count, you can use SUMPRODUCT and COUNTIF: =SUMPRODUCT(COUNTIF(range array,
criteria array)"
"Recording Macros - CORRECT ANSWER Excel allows you to record a sequence of instructions, and assign
a keyboard shortcut to invoke them as desired
These instructions are called macros
Go to File > Record Macro"
"Name Box - CORRECT ANSWER Tells you what cell you are in (top left below the ribbon)"
"Formula Bar - CORRECT ANSWER When you insert a formula into a cell and hit return, the cell will show
you the output
The formula bar, however, will show you the formula
Next to "fx" right below the ribbon"
"Moving Between Worksheets - CORRECT ANSWER Ctrl + PageDown/PageUp
(Option + RightArrow/LeftArrow)"
"Adding Worksheets - CORRECT ANSWER (Fn Shift F11)"
"Columns - CORRECT ANSWER Alphabetically labeled (A, B, C, etc.)"
"Rows - CORRECT ANSWER Numerically labeled (1, 2, 3, etc.)"
"How to Access Ribbon on Mac - CORRECT ANSWER Ctrl Fn F2
Use arrow keys to peruse the Mac ribbon"
"Using Function Keys - CORRECT ANSWER Hit Fn and then the function key to use the function keys
themselves on a Mac"
"Open a New Workout - CORRECT ANSWER Ctrl N
Cmnd N"
, "Save a File - CORRECT ANSWER Ctrl S
Cmnd S"
"Print a File - CORRECT ANSWER Ctrl P
Cmnd P"
"Open a File - CORRECT ANSWER Ctrl O
Cmnd O"
"Toolbars - CORRECT ANSWER Within each of the main tabs you will find all of Excel's features, grouped
by commands"
"Bold Command - CORRECT ANSWER Ctrl B
Cmnd B"
"The Home Tab - CORRECT ANSWER Includes most formatting properties:
Font type, size, and color
Background color
Text/cell alignment
Changing currency, decimal, percent formats
Inserting, deleting, and hiding rows and columns
Adjusting columns and rows width
Inserting, deleting, and renaming worksheets"
"The Insert Tab - CORRECT ANSWER Important Features: Pivot Table, Charts, Header & Footer"
"Page Layout Tab - CORRECT ANSWER Important Features: Print Area, Fonts, Gridlines"
"Formulas Tab - CORRECT ANSWER Important Features: Insert Function, Function Library, Name
Manager, Formula Auditing Tools (Trace Precedents, Trace Dependents)"
"Data Tab - CORRECT ANSWER Important Features: Filter, Sort, Text to Columns, Flash Fill, Data
Validation, Data Tables (Goal Seek), Group/Ungroup"
"Review Tab - CORRECT ANSWER Important Features: New Comment"
"View Tab - CORRECT ANSWER Important Features: Zoom, Gridlines"
"Developer Tab - CORRECT ANSWER Important Features: Record Macro, Add-Ins"
"Accessing Settings (Excel Options) - CORRECT ANSWER File > Options (Alt F T or Alt T O)
MAC: Excel > Preferences"
