EXCEL CRASH COURSE EXAM ACTUAL QUESTIONS AND
CORRECT ANSWERS
Auto-width columns to fit the size Alt + H + O + I
Define width of columns Alt + H + O + W
Rename a worksheet ALT + H + O + R
Add worksheet Alt + H + I + S
Freeze and unfreeze panes Alt + W + F + F
Split pane Alt + W + S
Jump from split pane to split pane F6
To navigate outside the cell when in formula F2
Add comment Alt + N + C2
Delete Row/Column Ctrl -
Add row Ctrl + Shift + +
Paste special Alt + E + S or Ctrl + Alt + V
Transpose Alt + E + S + E
How to get to date format "April-24" Go to Ctrl + 1, then Custom, then select mm-yy
Remove all borders Ctrl + Shift + _
Trace precedents Alt + M + P
Trace dependents Alt + M + D
Remove arrows Alt + M + A
How to format inputs/hardcodes blue Go to special (Ctrl + G), then Custom, select Constants and format text blue
Go to special Ctrl + G
Conditional formatting Alt + O + D or Alt + H + L
Custom formatting formula syntax [positive number format; negative number format, 0 format]
How to out text after a number in custom formatting Use quote marks
, How to align positive numbers with negative numbers Put a ) after the positive number format, it will appear as a space
in custom formats
Zoom in and out Ctrl + Alt + +/-
In custom formatting, what should you use instead of 0s #
to ensure commas only appear in the thousands
What function is the same as EOMONTH but for a EDATE
specific date?
How to output different things depending on if there's IF + ISTEXT functions
text or numbers in a cell
Function to turn things into a date DATE
When to use HLOOKUP 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 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 allows you to pinpoint a location of a return value in an array
CHOOSE allows you to choose which value in a set of values
OFFSET 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 returns the relative position (number) of an item in an array that matches a
specified value
Where should the MATCH lookup array start when Start at the OFFSET reference point and -1 after the MATCH function
combined with OFFSET?
INDIRECT returns whatever is in a cell, but when combined with & becomes a powerful
way to dynamically sum a range with certain criteria
SUM(INDIRECT( example ("B"&start year - number that equals row number & ":B"& end year - number that
equals column number))
When combining MATCH with INDIRECT, what should R1C1 (not A1)
the format be?
INDIRECT MATCH syntax INDIRECT("B"&MATCH(row lookup and range)&"C"&MATCH(col lookup and
range))
Where should MATCH ranges start when combined First cell in the worksheet
with INDIRECT?
ADDRESS Creates a cell reference as text, given a specified row and column
CORRECT ANSWERS
Auto-width columns to fit the size Alt + H + O + I
Define width of columns Alt + H + O + W
Rename a worksheet ALT + H + O + R
Add worksheet Alt + H + I + S
Freeze and unfreeze panes Alt + W + F + F
Split pane Alt + W + S
Jump from split pane to split pane F6
To navigate outside the cell when in formula F2
Add comment Alt + N + C2
Delete Row/Column Ctrl -
Add row Ctrl + Shift + +
Paste special Alt + E + S or Ctrl + Alt + V
Transpose Alt + E + S + E
How to get to date format "April-24" Go to Ctrl + 1, then Custom, then select mm-yy
Remove all borders Ctrl + Shift + _
Trace precedents Alt + M + P
Trace dependents Alt + M + D
Remove arrows Alt + M + A
How to format inputs/hardcodes blue Go to special (Ctrl + G), then Custom, select Constants and format text blue
Go to special Ctrl + G
Conditional formatting Alt + O + D or Alt + H + L
Custom formatting formula syntax [positive number format; negative number format, 0 format]
How to out text after a number in custom formatting Use quote marks
, How to align positive numbers with negative numbers Put a ) after the positive number format, it will appear as a space
in custom formats
Zoom in and out Ctrl + Alt + +/-
In custom formatting, what should you use instead of 0s #
to ensure commas only appear in the thousands
What function is the same as EOMONTH but for a EDATE
specific date?
How to output different things depending on if there's IF + ISTEXT functions
text or numbers in a cell
Function to turn things into a date DATE
When to use HLOOKUP 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 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 allows you to pinpoint a location of a return value in an array
CHOOSE allows you to choose which value in a set of values
OFFSET 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 returns the relative position (number) of an item in an array that matches a
specified value
Where should the MATCH lookup array start when Start at the OFFSET reference point and -1 after the MATCH function
combined with OFFSET?
INDIRECT returns whatever is in a cell, but when combined with & becomes a powerful
way to dynamically sum a range with certain criteria
SUM(INDIRECT( example ("B"&start year - number that equals row number & ":B"& end year - number that
equals column number))
When combining MATCH with INDIRECT, what should R1C1 (not A1)
the format be?
INDIRECT MATCH syntax INDIRECT("B"&MATCH(row lookup and range)&"C"&MATCH(col lookup and
range))
Where should MATCH ranges start when combined First cell in the worksheet
with INDIRECT?
ADDRESS Creates a cell reference as text, given a specified row and column
