Excel Crash Course Exam from Wall Street Prep - Wall Street
Prep Questions And Answers Updated 2022
Auto-width columns to fit the size - ANSWER: Alt + H + O + I
Define width of columns - ANSWER: Alt + H + O + W
Rename a worksheet - ANSWER: ALT + H + O + R
Add worksheet - ANSWER: Alt + H + I + S
Freeze and unfreeze panes - ANSWER: Alt + W + F + F
Split pane - ANSWER: Alt + W + S
Jump from split pane to split pane - ANSWER: F6
To navigate outside the cell when in formula - ANSWER: F2
Add comment - ANSWER: Alt + N + C2
Delete Row/Column - ANSWER: Ctrl -
Add row - ANSWER: Ctrl + Shift + +
Paste special - ANSWER: Alt + E + S or Ctrl + Alt + V
Transpose - ANSWER: Alt + E + S + E
How to get to date format "April-24" - ANSWER: Go to Ctrl + 1, then Custom, then
select mm-yy
Remove all borders - ANSWER: Ctrl + Shift + _
Trace precedents - ANSWER: Alt + M + P
Trace dependents - ANSWER: Alt + M + D
Remove arrows - ANSWER: Alt + M + A
How to format inputs/hardcodes blue - ANSWER: Go to special (Ctrl + G), then
Custom, select Constants and format text blue
Go to special - ANSWER: Ctrl + G
Conditional formatting - ANSWER: Alt + O + D or Alt + H + L
, Custom formatting formula syntax - ANSWER: [positive number format; negative
number format, 0 format]
How to out text after a number in custom formatting - ANSWER: Use quote marks
How to align positive numbers with negative numbers in custom formats - ANSWER:
Put a ) after the positive number format, it will appear as a space
Zoom in and out - ANSWER: Ctrl + Alt + +/-
In custom formatting, what should you use instead of 0s to ensure commas only
appear in the thousands - ANSWER: #
What function is the same as EOMONTH but for a specific date? - ANSWER: EDATE
How to output different things depending on if there's text or numbers in a cell -
ANSWER: IF + ISTEXT functions
Function to turn things into a date - ANSWER: DATE
When to use HLOOKUP - ANSWER: When your comparison values are all in a row
across the top of the data table, and you want to look down a specified number of
rows
When to use a VLOOKUP - ANSWER: When your comparison values are located in a
col to the left of a data table and you want to look across a specified number of
columns
INDEX - ANSWER: allows you to pinpoint a location of a return value in an array
CHOOSE - ANSWER: allows you to choose which value in a set of values
OFFSET - ANSWER: instead of selecting a whole array, you select a reference point
which is usually the top left hand corner of the array or above the column if only one
column. Then select rows and columns as numbers across and down from the
reference point
MATCH - ANSWER: returns the relative position (number) of an item in an array that
matches a specified value
Where should the MATCH lookup array start when combined with OFFSET? -
ANSWER: Start at the OFFSET reference point and -1 after the MATCH function
INDIRECT - ANSWER: returns whatever is in a cell, but when combined with &
becomes a powerful way to dynamically sum a range with certain criteria
Prep Questions And Answers Updated 2022
Auto-width columns to fit the size - ANSWER: Alt + H + O + I
Define width of columns - ANSWER: Alt + H + O + W
Rename a worksheet - ANSWER: ALT + H + O + R
Add worksheet - ANSWER: Alt + H + I + S
Freeze and unfreeze panes - ANSWER: Alt + W + F + F
Split pane - ANSWER: Alt + W + S
Jump from split pane to split pane - ANSWER: F6
To navigate outside the cell when in formula - ANSWER: F2
Add comment - ANSWER: Alt + N + C2
Delete Row/Column - ANSWER: Ctrl -
Add row - ANSWER: Ctrl + Shift + +
Paste special - ANSWER: Alt + E + S or Ctrl + Alt + V
Transpose - ANSWER: Alt + E + S + E
How to get to date format "April-24" - ANSWER: Go to Ctrl + 1, then Custom, then
select mm-yy
Remove all borders - ANSWER: Ctrl + Shift + _
Trace precedents - ANSWER: Alt + M + P
Trace dependents - ANSWER: Alt + M + D
Remove arrows - ANSWER: Alt + M + A
How to format inputs/hardcodes blue - ANSWER: Go to special (Ctrl + G), then
Custom, select Constants and format text blue
Go to special - ANSWER: Ctrl + G
Conditional formatting - ANSWER: Alt + O + D or Alt + H + L
, Custom formatting formula syntax - ANSWER: [positive number format; negative
number format, 0 format]
How to out text after a number in custom formatting - ANSWER: Use quote marks
How to align positive numbers with negative numbers in custom formats - ANSWER:
Put a ) after the positive number format, it will appear as a space
Zoom in and out - ANSWER: Ctrl + Alt + +/-
In custom formatting, what should you use instead of 0s to ensure commas only
appear in the thousands - ANSWER: #
What function is the same as EOMONTH but for a specific date? - ANSWER: EDATE
How to output different things depending on if there's text or numbers in a cell -
ANSWER: IF + ISTEXT functions
Function to turn things into a date - ANSWER: DATE
When to use HLOOKUP - ANSWER: When your comparison values are all in a row
across the top of the data table, and you want to look down a specified number of
rows
When to use a VLOOKUP - ANSWER: When your comparison values are located in a
col to the left of a data table and you want to look across a specified number of
columns
INDEX - ANSWER: allows you to pinpoint a location of a return value in an array
CHOOSE - ANSWER: allows you to choose which value in a set of values
OFFSET - ANSWER: instead of selecting a whole array, you select a reference point
which is usually the top left hand corner of the array or above the column if only one
column. Then select rows and columns as numbers across and down from the
reference point
MATCH - ANSWER: returns the relative position (number) of an item in an array that
matches a specified value
Where should the MATCH lookup array start when combined with OFFSET? -
ANSWER: Start at the OFFSET reference point and -1 after the MATCH function
INDIRECT - ANSWER: returns whatever is in a cell, but when combined with &
becomes a powerful way to dynamically sum a range with certain criteria
