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