1 of 26
Excel Crash Course Exam - Wall Street Prep 150 Questions And
Answers Updated 2025/2026
Converting Positive Numbers to Negative - ......ANSWER........Ctrl+C the data values
Special paste the new numbers using the Subtract operation - Alt+E+S+S
OR
In another cell, enter the number -1 Ctrl+C -1 and special paste on the relevant numbers
using the multiply operation (Alt+E+S+M)
Copying Across Formulas - ......ANSWER........Ctrl + C to copy, Ctrl + V to paste
COUNT, COUNTA, and COUNTIF Functions - ......ANSWER........COUNT =COUNT(value1,
value2, ...) counts the number of cells that contain numbers within the list of arguments;
cells with text are disregarded COUNTA Same as COUNT except cells with numbers and
text are counted COUNTIF =COUNTIF(range, criteria) counts the number of items in the
range that satisfy a specific criteria - similar to the SUMIF function
COUNTIF as an array - ......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)
COUNTIF Syntax - ......ANSWER........If you want the criteria to be "equals a cell", just input
the cell number If you want it to be less than or greater than, use quotation marks and &:
Ex. =COUNTIF(A1, "<"&A2)
Create a Dropdown Menu Command - ......ANSWER........Alt+D+L
Creating a Pivot Table - ......ANSWER........Use your arrows to move to any cell inside the
table of data and hit: Alt+N+V or Alt+N+V+T (Insert > Pivot Table) Excel will automatically
highlight the entire table of data. Select to open the Pivot Table in a new worksheet. A new
worksheet will open showing the Pivot Table Field List. This shows a list of the data's
categories (REIT, property type, etc.) and the 4 areas of the Pivot Table: Filters, Columns,
Rows, Values (the outputs). Using your mouse, you can drag any of the data categories into
one of the 4 areas of the table, the Pivot Table itself will automatically reorganize to
accommodate the newly added category Clicking the drop-down arrow allows you to sort
and filter the column headers in various ways (ex. if you wanted to show data only for BRE
Properties, you could unselect the other REITs) Note: Values are automatically summed. If
,2 of 26
you want to get something else (count, average, max, etc.) you can find this under the
Value Field Settings by clicking on the corresponding value. You can also change what the
values are shown as (% of grand total, etc.) under the same menu under Show Values As.
Creating Dynamic Headers by Combining Cell References with Text ("&")
- ......ANSWER........Excel allows users to combine (or "concatenate") cells with a text
string in them with other text strings, creating one text string by using the "&" function
Ex. ="Income Statement for "&A1, where A1 is the company name
Ctrl Commands - ......ANSWER........Most commands involving Ctrl are shortcuts that are
automated by default to make Excel more efficient and user friendly
Ctrl Shortcuts - ......ANSWER........Cut: Ctrl+X
Currency Format: 2 decimal places - ......ANSWER........CTRL Shift+$
Custom Format: Adding a Comma Separator - ......ANSWER........Add "#, ##" before the
zeros Ex. (#, ##0.0) ;(#, ##0.0)
Custom Format: Aligning Decimal Points - ......ANSWER........Add ")" after and "(" before the
positive numbers and zero formatting Ex. (0.0) ;(0.0)
Custom Format: Aligning Non-Multiple Numbers with a Multiple - ......ANSWER........Add
"x_" after the end _ for positive numbers and after the closing parenthesis for negative
numbers Ex. (#,##0.0_x) ;(#, ##0.0) _x
Custom Format: Changing the Color of Negative Numbers to Red - ......ANSWER........Add
[red] at the beginning of the negative numbers format Ex. (#, ##0.0);Red
Custom Format: L + [] Basis Points - ......ANSWER........L + 0 "bps"
Custom Format: Multiple "x" format. Negative numbers in parenthesis, aligned with
positive number format - ......ANSWER........(#,###0.0x) ;(##,##0.0x)
Custom Format: Negative number in parenthesis, aligned with positive number format
- ......ANSWER........#,##0.00_);(#,##0.00)
Custom Format: Negative number in parenthesis, aligned with positive number format and
multiple "x" format - ......ANSWER........(#, ###0.0_x);(##, ##0.0) _x
, 3 of 26
Custom Format: Negative number in parenthesis, aligned with positive number format,
"Balance" when result is 0 - ......ANSWER........#,##0.00_);(#,##0.00);"Balance"
Custom Formatting - ......ANSWER........(1) Type the number (2) Go to format tab (3) Go to
"Custom" (4) Type in your custom format as [positive numbers];[negative
numbers];[zero];[text]
Multiple: 0.0x_);(0.0x);@_) 1 = True/0 = False: "True";"Invalid";"False" n "Years": 0 "years"
Cut Cells - ......ANSWER........Ctrl+X Note: Dependent calculations DO NOT change when
you cut cells!
Data Tab - ......ANSWER........Important Features: Filter, Sort, Text to Columns, Flash Fill,
Data Validation, Data Tables (Goal Seek), Group/Ungroup
Data Tables - ......ANSWER........Allow us to examine a piece of output data - such as a
company's EPS - and how it is impact by changes in input variables such as revenues and
gross margin assumptions Output the results in a presentation-friendly matrix Often used
for sensitivity analysis (i.e. EPS's sensitivity to changes in gross profit margin) and is used
widely by analysts to illustrate a range of possible output values
Data Validation & Creating Drop-Down Menus - ......ANSWER........A utility in Excel whose
most frequently used feature is its ability to create simple and quick drop-down menus (1)
To create a dropdown menu, with the cell where you want your drop-down menu active,
open the data validation form Alt+D+L or Alt+A+V+V (Data>Data Validation) (2) Within the
Settings tab, select list from the dropdown menu (3) Within the 'Source:' field, identify a
contiguous cell range containing the data you want to include in your dropdown, and hit OK
and you should see your dropdown menu appear (note: it only appears when you are on
the active cell)
Date Format with the Day, Month, and Year - ......ANSWER........CTRL Shift+#
Date Functions (EOMONTH) - ......ANSWER........=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 Note: To output a date x months before a start_date, x should
be negative
DATE, DAY, MONTH, and YEAR Functions - ......ANSWER........DATE(year,month,day) is a
function that combines distinct year, month, and day elements into a valid date function in
Excel Crash Course Exam - Wall Street Prep 150 Questions And
Answers Updated 2025/2026
Converting Positive Numbers to Negative - ......ANSWER........Ctrl+C the data values
Special paste the new numbers using the Subtract operation - Alt+E+S+S
OR
In another cell, enter the number -1 Ctrl+C -1 and special paste on the relevant numbers
using the multiply operation (Alt+E+S+M)
Copying Across Formulas - ......ANSWER........Ctrl + C to copy, Ctrl + V to paste
COUNT, COUNTA, and COUNTIF Functions - ......ANSWER........COUNT =COUNT(value1,
value2, ...) counts the number of cells that contain numbers within the list of arguments;
cells with text are disregarded COUNTA Same as COUNT except cells with numbers and
text are counted COUNTIF =COUNTIF(range, criteria) counts the number of items in the
range that satisfy a specific criteria - similar to the SUMIF function
COUNTIF as an array - ......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)
COUNTIF Syntax - ......ANSWER........If you want the criteria to be "equals a cell", just input
the cell number If you want it to be less than or greater than, use quotation marks and &:
Ex. =COUNTIF(A1, "<"&A2)
Create a Dropdown Menu Command - ......ANSWER........Alt+D+L
Creating a Pivot Table - ......ANSWER........Use your arrows to move to any cell inside the
table of data and hit: Alt+N+V or Alt+N+V+T (Insert > Pivot Table) Excel will automatically
highlight the entire table of data. Select to open the Pivot Table in a new worksheet. A new
worksheet will open showing the Pivot Table Field List. This shows a list of the data's
categories (REIT, property type, etc.) and the 4 areas of the Pivot Table: Filters, Columns,
Rows, Values (the outputs). Using your mouse, you can drag any of the data categories into
one of the 4 areas of the table, the Pivot Table itself will automatically reorganize to
accommodate the newly added category Clicking the drop-down arrow allows you to sort
and filter the column headers in various ways (ex. if you wanted to show data only for BRE
Properties, you could unselect the other REITs) Note: Values are automatically summed. If
,2 of 26
you want to get something else (count, average, max, etc.) you can find this under the
Value Field Settings by clicking on the corresponding value. You can also change what the
values are shown as (% of grand total, etc.) under the same menu under Show Values As.
Creating Dynamic Headers by Combining Cell References with Text ("&")
- ......ANSWER........Excel allows users to combine (or "concatenate") cells with a text
string in them with other text strings, creating one text string by using the "&" function
Ex. ="Income Statement for "&A1, where A1 is the company name
Ctrl Commands - ......ANSWER........Most commands involving Ctrl are shortcuts that are
automated by default to make Excel more efficient and user friendly
Ctrl Shortcuts - ......ANSWER........Cut: Ctrl+X
Currency Format: 2 decimal places - ......ANSWER........CTRL Shift+$
Custom Format: Adding a Comma Separator - ......ANSWER........Add "#, ##" before the
zeros Ex. (#, ##0.0) ;(#, ##0.0)
Custom Format: Aligning Decimal Points - ......ANSWER........Add ")" after and "(" before the
positive numbers and zero formatting Ex. (0.0) ;(0.0)
Custom Format: Aligning Non-Multiple Numbers with a Multiple - ......ANSWER........Add
"x_" after the end _ for positive numbers and after the closing parenthesis for negative
numbers Ex. (#,##0.0_x) ;(#, ##0.0) _x
Custom Format: Changing the Color of Negative Numbers to Red - ......ANSWER........Add
[red] at the beginning of the negative numbers format Ex. (#, ##0.0);Red
Custom Format: L + [] Basis Points - ......ANSWER........L + 0 "bps"
Custom Format: Multiple "x" format. Negative numbers in parenthesis, aligned with
positive number format - ......ANSWER........(#,###0.0x) ;(##,##0.0x)
Custom Format: Negative number in parenthesis, aligned with positive number format
- ......ANSWER........#,##0.00_);(#,##0.00)
Custom Format: Negative number in parenthesis, aligned with positive number format and
multiple "x" format - ......ANSWER........(#, ###0.0_x);(##, ##0.0) _x
, 3 of 26
Custom Format: Negative number in parenthesis, aligned with positive number format,
"Balance" when result is 0 - ......ANSWER........#,##0.00_);(#,##0.00);"Balance"
Custom Formatting - ......ANSWER........(1) Type the number (2) Go to format tab (3) Go to
"Custom" (4) Type in your custom format as [positive numbers];[negative
numbers];[zero];[text]
Multiple: 0.0x_);(0.0x);@_) 1 = True/0 = False: "True";"Invalid";"False" n "Years": 0 "years"
Cut Cells - ......ANSWER........Ctrl+X Note: Dependent calculations DO NOT change when
you cut cells!
Data Tab - ......ANSWER........Important Features: Filter, Sort, Text to Columns, Flash Fill,
Data Validation, Data Tables (Goal Seek), Group/Ungroup
Data Tables - ......ANSWER........Allow us to examine a piece of output data - such as a
company's EPS - and how it is impact by changes in input variables such as revenues and
gross margin assumptions Output the results in a presentation-friendly matrix Often used
for sensitivity analysis (i.e. EPS's sensitivity to changes in gross profit margin) and is used
widely by analysts to illustrate a range of possible output values
Data Validation & Creating Drop-Down Menus - ......ANSWER........A utility in Excel whose
most frequently used feature is its ability to create simple and quick drop-down menus (1)
To create a dropdown menu, with the cell where you want your drop-down menu active,
open the data validation form Alt+D+L or Alt+A+V+V (Data>Data Validation) (2) Within the
Settings tab, select list from the dropdown menu (3) Within the 'Source:' field, identify a
contiguous cell range containing the data you want to include in your dropdown, and hit OK
and you should see your dropdown menu appear (note: it only appears when you are on
the active cell)
Date Format with the Day, Month, and Year - ......ANSWER........CTRL Shift+#
Date Functions (EOMONTH) - ......ANSWER........=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 Note: To output a date x months before a start_date, x should
be negative
DATE, DAY, MONTH, and YEAR Functions - ......ANSWER........DATE(year,month,day) is a
function that combines distinct year, month, and day elements into a valid date function in
