WALL STREET PREP: EXCEL CRASH COURSE QUESTIONS

AND CORRECT ANSWERS

Keyboard Versus the Mouse

Almost everything that can be done in Excel using a mouse can also be done using the
keyboard shortcuts
Best way to learn is to disconnect the mouse and work through Excel using only the
keyboard
Name Box
Tells you what cell you are in (top left below the ribbon)
Formula Bar
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
Worksheets
An Excel file is called a workbook;
You start with 1 worksheet but you can add/delete more
The active worksheet is highlighted in Excel
Moving Between Worksheets
Ctrl + PageDown/PageUp
(Option + RightArrow/LeftArrow)
Adding Worksheets
(Fn Shift F11)
Columns
Alphabetically labeled (A, B, C, etc.)
Rows
Numerically labeled (1, 2, 3, etc.)
Main tabs
Although we focus on shortcuts, virtually all commands, functions, and Excel features
can be accessed through the task-oriented tabs which organize them into nine logical
categories:
(1) Home
(2) Insert
(3) Draw
(4) Page Layout
(5) Formulas
(6) Data
(7) Review
(8) View
(9) Developer
How to Access Ribbon on Mac
Ctrl Fn F2
Use arrow keys to peruse the Mac ribbon
Mac Settings to Disable
(1) Function Keys: Settings - Keyboard - Use F1, F2, etc. keys as standard function
keys
(2) Mission Control: System Preferences - Keyboard - Shortcuts - Mission Control -
Disable "Move a space left" and "Move a space right" to use Ctrl RightArrow or Ctrl
LeftArrow
Using Function Keys
Hit Fn and then the function key to use the function keys themselves on a Mac
Open a New Workout
Ctrl N
Cmnd N
The File Tab
Many Excel features (Open file, Save file, Print file, etc.) are located in this tab
Excel also has alternative keyboard shortcuts (using Ctrl) for many of these features
Open a File: Ctrl O (Cmnd O)
Save a File: Ctrl S (Cmnd S)
Print a File: Ctrl P (Cmnd P)
Save a File
Ctrl S
Cmnd S
Print a File
Ctrl P
Cmnd P
Open a File
Ctrl O
Cmnd O
Toolbars
Within each of the main tabs you will find all of Excel's features, grouped by commands
Navigating to the Toolbars Without the Mouse
Hit Alt and the appropriate letter (or use the right/left arrow keys) to get to the desired
tab
Once there, use the Tab and Shift Tab keys to navigate around, the Space to open a
drop down, and Enter to select
Bold Command
Ctrl B
Cmnd B
The Home Tab
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
Important Features: Pivot Table, Charts, Header & Footer
Page Layout Tab
Important Features: Print Area, Fonts, Gridlines
Formulas Tab
Important Features: Insert Function, Function Library, Name Manager, Formula Auditing
Tools (Trace Precedents, Trace Dependents)
Data Tab
Important Features: Filter, Sort, Text to Columns, Flash Fill, Data Validation, Data
Tables (Goal Seek), Group/Ungroup
Review Tab
Important Features: New Comment
View Tab
Important Features: Zoom, Gridlines
Developer Tab
Important Features: Record Macro, Add-Ins
Accessing Settings (Excel Options)
File > Options (Alt F T or Alt T O)
MAC: Excel > Preferences
Excel Settings Changes
General:
Change "Sheets in New Workbook" to 3
Uncheck "Show Workbook Gallery when opening Excel"

Calculation/Functions:
Change Calculation Options to "Automatic except for data tables"
Check "Enable iterative calculation"

Edit/Advanced:
Uncheck "After pressing Enter, move selection"
*Optional - Check "Automatically insert a decimal point"
Protection
Go to File > Info > Password Protecting Files (File > Passwords)
Alt F T I
You can password protect files "Encrypt with Password"
You can also protect individual worksheets so that people can't see or edit formulas
"Protect Current Sheet"
Shortcuts to Allow the User Input to Add More Sheets to a Workbook
Alt i w
Alt h i s
Shift F11
Alt Shift F1
Find or Find and Replace Shortcut
Ctrl F
(Cmnd F)
What is the recommended workbook calculation setting for Excel?
Automatic Except for Data Tables
Add a Cell Reference From a Different Worksheet into an Existing Formula
Hit F2 to get into the existing formula; delete any incorrect formulas or operators
Hit F2 again to enable "Enter" mode on the bottom-left corner of the Excel sheet
Holding down Ctrl, use PageUp or PageDown to find the desired worksheet
Let go of the Ctrl and PageUp/Down keys
Use the arrow keys to located the desired cell reference
Hit Enter
Autofit Row Height Command
Alt H O A
Autofit Column Height Command
Alt O C A
Assign Column Width Command
Alt H O W
(Home > Format > Column Width)
Assign Row Height Command
Alt H O H
(Home > Format > Row Height)
Command to Change Zoom Size
Alt+V+Z
(Ctrl+MouseScroll)
Autofit the Column Width Command
Alt H O I
(Home > Format > Autofit Column Width)
Basic Excel Drills
Most keyboard shortcuts involves Alt or Ctrl keys
Alt: Press each key and let go (do you NOT need to hold to the Alt key)
Ctrl: Ctrl key must be held down as you press the other key in the shortcut sequence
Ctrl Commands
Most commands involving Ctrl are shortcuts that are automated by default to make
Excel more efficient and user friendly
Save As Shortcut
Alt F A
Alt Commands
Most commands involving Alt are shortcuts to the commands and functions inside the
default eight Main Tabs
Select Column & Range of Columns
Ctrl Space
Range: Ctrl Space Shift+RightArrow
Select Row & Range of Rows
Shift Space
Range: Shift Space Shift+DownArrow
Undo
Ctrl Z
Excel Formulas
Start with the = sign
The = sign tells Excel that the info that will follow the = sign should be treated as a
formula and not as plain text
Once you type in the = sign, use the arrow keys to navigate around the Excel workbook
to find the cells you need for your formula
Operations in Excel
Copying Across Formulas
Ctrl + C to copy, Ctrl + V to paste
Open Format Cells Dialog
Ctrl+1
(Cmnd+1)
Navigating Format Cells Dialog
Using the Arrow keys to get around the tab
To get in a tab, either use Tab key or use Alt and the relevant letter
Use Space to toggle check boxes
Highlight a Contiguous Range
Ctrl + Shift + Arrows
(Cmnd + Shift + Arrows)
Combining Data in Two Sheets into Another Sheet
(1) Copy and paste the relevant headers into the third sheet
(2) Type "=" in the relevant column in the third sheet and then use Ctrl PageUp/Down
(Option Right/LeftArrow) to get to the first sheet and find the right data
(3) Press "+" and then find the relevant data in the second sheet and press "Enter"
(4) Ctrl+C to copy that formula and then apply it to the rest of the table
(5) Delete and retype any important formulas (ex. Net Profit)
(6) Add in formatting by Ctrl+C the table in sheet 1 or 2, moving to the upper corner of
the table in sheet 3 and using Paste Special and then
format: Alt+E+S+T or Ctrl+Alt+V+T (Ctrl+Cmnd+V+T)
Paste Special
Alt+E+S
(Ctrl+Cmnd+V+T)
Let's the user dictate the specific attributes of a cell or highlighted region that should be
pasted
Open a New Workbook
Ctrl+N
Toggle Between Workbooks
Ctrl+Tab
(Cmnd ~)
Referencing Cells from Other Workbooks
(1) Hit "="
(2) Hit Ctrl+Tab (Cmnd ~) to go to the other workbook
(3) Find the relevant data and hit Enter
How to Autofit a Range of Columns
(1) Select the columns by hitting Ctrl+SpaceBar
(2) Hold down the shift key and use left and right arrow keys to highlight the range of
columns
(3) Hit Alt+H+O+I to autofit the columns
Moving Around the Format Cells Dialog
Move across tabs with Ctrl+Tab
Move counterclockwise across form elements with Shift+Tab
Select a checkbox with Spacebar
Freezing and Splitting Panes
Excel worksheets often become too large to allow users to view all of their contents on
one screen
'Freezing Panes' and 'Splitting Panes' options provide users with the flexibility to select
specific rows and columns that always remain visible when scrolling in the worksheet
Panes
Portions of the worksheet that are bounded and separated by vertical and/or horizontal
bars
To Freeze Panes
Click the cell below the desired row and to the right of the desired column where you
want to freeze panes
Hit Alt+W+F+F to freeze
To unfreeze, hit Alt+W+F+F again
(View>Freeze Panes and then View>Unfreeze Panes)
*Note: To create a horizontal freeze, go to the leftmost column!
Splitting Panes
Allows users to scroll in both areas of the worksheet, while rows and columns in the
non-scrolled area remain visible
Directions:
(1) Click the cell below the desired row and to the right of the desired column where you
want to split panes and hit Alt+W+S
(2) To un-split, click Alt+W+S again
(3) Press F6 to move from pane to pane in a clockwise direction; press Shift+F6 to
move from pane to pane in a counter-clockwise direction
(View>Split)
*Note: To create a horizontal split, go to the leftmost column!
Redo
Ctrl+Y
Entering an Active Cell
F2
(Fn+F2)
Go to the Beginning of an Active Cell Formula
Ctrl+Home
(Fn+Ctrl+LeftArrow or Cmnd+UpArrow)
Go to the End of an Active Cell Formula
Ctrl+End
(Fn+Ctrl+RightArrow or Cmnd+DownArrow)
Jumping from One Formula Element to the Next
Ctrl+Arrows
(Cmnd+Arrows)
Highlight Formula Elements & Maintain Contiguous Elements
Ctrl+Shift+Arrows
(Cmnd+Shift+Arrows)
Highlight the Whole Formula of an Active Cell
Shift+DownArrow
(Shift+Up/DownArrow)
How to Revert Back to Original Formula after Editing an Active Cell
Esc
How to Exit Cell Edit Mode in an Active Cell
F2
(Fn+F2)
Clearing a Cell
Alt+H+E (Home>Clear)
Clear All: Alt+H+E+A
Clear Format: Alt+H+E+F
Clear Comments: Alt+H+E+M
Add Comment
Shift+F2 (Shift+Fn+F2)
Esc twice to exit
Right Fill from Cell Left
Highlight the cell you want to copy
Shift+RightArrow to the cells that you want to be filled
Hit Ctrl+R
Down Fill from Cell Up
Highlight the cell you want to copy
Shift+DownArrow to the cells that you want to be filled
Hit Ctrl+D
Inserting Rows
(1) Go to any cell in the row below the desired row
(2) Press Alt+I+R
OR
(1) Highlight the row below the desired row with Shift+Space
(2) Press Ctrl Shift + to insert the new row
Inserting Columns
(1) Go to any cell in the desired column
(2) Press Alt+I+C
OR
(1) Highlight the desired column by pressing Ctrl+Spacebar
(2) Insert a column by pressing Ctrl Shift +
Deleting Rows and Columns
Row: Alt+H+D+R
Column: Alt+H+D+C
OR
Highlight the row/column and press Ctrl -
Paste Only Formulas
Alt+E+S+F
Paste Only Formatting
Alt+E+S+T
How to change a list of numbers quoted in 1,000s to 1s
In another cell, enter the number 1000
Copy this cell, then highlight your list of numbers
Press Alt+E+S+M (Paste Special Multiply)
Paste Special Operations
Allows you to apply operations to large amounts of data
You can convert large amounts of data to positive to negative or vice versa
*Note: You should ONLY paste on numbers that are hard inputs
Converting Positive Numbers to Negative
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)
Paste Special Transpose
Allows users to convert a vertical list of data into a horizontal list of data, and vice versa
(1) Highlight and copy the list of numbers
(2) Move your cursor outside of the list range
(3) Press Alt+E+S+E (Paste Special Transpose)
*Note: Be careful when applying a paste special transpose on formulas -- they don't
work on formulas with relative references
Ctrl Shortcuts
Cut: Ctrl+X
Cut Cells
Ctrl+X
Note: Dependent calculations DO NOT change when you cut cells!
Number Format: 2 decimals, 000 separator
Ctrl+Shift+!
Currency Format: 2 decimal places
Ctrl+Shift+$
Percentage Format with No Decimal Places
Ctrl+Shift+%
Date Format with the Day, Month, and Year
Ctrl+Shift+#
Boldface
Ctrl+B
Italicize
Ctrl+I
Underline
Ctrl+U
(Cmnd+U)
Remove All Borders
Ctrl+Shift+_
Naming Cells
Ctrl+F3 > Alt+N to name a new cell
(Ctrl+L)
Anchoring Cells
Anchoring (or "fixing") cells that are being referenced in a formula tells Excel that even if
you copy the formula to another cell, the cells in the formula that are anchored should
not change
(Fn+)F4 on the name of the cell in a formula
Keep clicking F4 to toggle the type of anchor
Naming Worksheets
Alt+H+O+R
Inserting & Deleting Worksheets
Add a new worksheet by pressing Alt+H+I+S
Delete a worksheet by pressing Alt+E+L
Grouping & Hiding Columns
(1) Hit Ctrl+Spacebar to select the desired column
(2) Hit Shift+Alt+RightArrow (Option+Shift+RightArrow) to create the group
(3) Hit Alt+A+H to hide the columns
(4) Hit Alt+A+J to unhide the columns
(5) Hit Shift+Alt+LeftArrow (Option+Shift+LeftArrow) to remove the group
Grouping & Hiding Rows
(1) Hit Shift+Spacebar to select the desired row
(2) Hit Shift+Alt+RightArrow (Option+Shift+RightArrow) to create the group
(3) Hit Alt+A+H to hide the columns
(4) Hit Alt+A+J to unhide the columns
(5) Hit Shift+Alt+LeftArrow (Option+Shift+LeftArrow) to remove the group
Group - Don't Hide
You can hide data by hitting Alt+H+O+U+R for rows and Atl+H+O+U+C for columns
Don't ever use this method because there are no indications as to the data's hidden
location - stick to grouping
Grouping Worksheets
Helpful if you would like to format data in the same manner or enter the same data
across multiple sheets
By grouping multiple worksheets, any data and formatting that you would perform in one
of the grouped worksheets would automatically be reflected in all of them
Hold down Ctrl+Shift and press Page+Up/Down to reach the worksheets you would like
to group (Shift+Click on the desired worksheets)
All of the grouped worksheets are highlighted and the file name on top of the Excel
screen should show [Group] are it
To ungroup, press Ctrl+PageDown
Auditing Cells
Good: (Fn+)F2 -- go to a desired cell and hit the F2 key
Excel will highlight (in different colors) all the cell components of an existing formula
present in that cell
Better: Ctrl+[ and Ctrl+] -- hitting Ctrl [ on a cell will highlight the precedent cell(s)
Keep hitting Ctrl [ and it will take you to the next precedent
Hitting Ctrl ] jon a cell will do the same thing for dependent cells
Best: Excel Auditing Functions with Alt+M+P for precedents and Alt+M+D (Functions >
Trace Precedents or Functions > Trace Dependents)
Remove arrows with Alt+M+A+A
Allows you to navigate to connected worksheets -- hit (Fn+)F5+Enter to go back to
original cell
Center Across Selection
Highlight the region you want to center across
Go to Formatting with Ctrl+1 (Cmnd+1)
Go to Alignment
Click Center Across Selection in Horizontal Alignment
Go To Special
Useful to quickly format constants vs. formulas
(1) Highlight the relevant region
(2) Hit Fn+F5 for Go To menu
(3) Hit Alt+S for Special
(4) Hit Constants and Uncheck Text, Logicals, and Errors
(5) Hit Enter to select all constants
(6) Use Ctrl+1 (Cmnd+1) to add special formatting
Distinguishing Constants
Constants are usually distinguished from formulas, such as by blue text vs. black text
Identifying Where Blanks Are
(1) Highlight the relevant region
(2) Use Go To Special and select blanks
(3) Hit Enter
Identifying Where Comments Are
(1) Highlight the relevant region
(2) Use Go To Special and select comments
(3) Hit Enter
Bottom Bar Customization
Right click on the bottom bar to change what you see from your selection (ex. Average,
Count, Max, Sum, etc.)
Conditional Formatting
Allows you to create your own conditional formats or use a preset from Excel
Alt+O+D or Home > Conditional Formatting > New Rule / Alt+H+L > New Rule for
customs
Alt+H+L or Home > Conditional Formatting > Highlight Cells Rules for presets
Identify which numbers in a column are above some number
(1) Highlight the relevant range
(2) Hit Alt+O+D
(3) Select "Use a formula to determine which cells to format"
(4) Write "=" and select the first number in the column
(5) Anchor the column
(6) Write ">" and some number or cell that is your reference
(7) Press Enter
Ex. "= $C3>500"
And Functions
=and([first statement],[second statement)
Dynamic Headers & Text
Name: ="Income Statement for "&[Cell]
Date: ="Share price as of "&TEXT([Cell], "mm/dd/yy")
Custom Formatting
(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"
Insert Line Breaks
Alt+Enter (Option+Enter)
Custom Format: Aligning Decimal Points
Add "_)" after and "_(" before the positive numbers and zero formatting
Ex. _(0.0_);(0.0)
Custom Format: Adding a Comma Separator
Add "#,##" before the zeros
Ex. _(#,##0.0_);(#,##0.0)
Custom Format: Aligning Non-Multiple Numbers with a Multiple
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: Negative number in parenthesis, aligned with positive number
format, "Balance" when result is 0
#,##0.00_);(#,##0.00);"Balance"
Custom Format: Negative number in parenthesis, aligned with positive number
format
#,##0.00_);(#,##0.00)
Custom Format: Multiple "x" format. Negative numbers in parenthesis, aligned
with positive number format
_(#,###0.0x_);(##,##0.0x)
Custom Format: Negative number in parenthesis, aligned with positive number
format and multiple "x" format
_(#,###0.0_x_);(##,##0.0)_x
Custom Format: L + [] Basis Points
L + 0 "bps"
Custom Format: Changing the Color of Negative Numbers to Red
Add [red] at the beginning of the negative numbers format
Ex. _(#,##0.0_);[Red](#,##0.0)
Most Common Excel Errors
#DIV/0! Divided by zero
#REF! Referencing a previously deleted cell
#NUM! Number not valid
#NAME? Text not valid (ex. incorrect function name)
#VALUE! Incorrect arguments (ex. Using text as a number)
####### Column not wide enough
Find and Replace
Ctrl+F
Write what you want to find
Click Replace
Write what you want to replace
Use Options to restrict where you're searching, match the case, etc.
Note: You can replace references in formulas as well by finding "[cell name]" and
replacing with "[new cell name]"; Ex. Find "j7" and replace with "b2"
Preparing a Page for Printing
(1) Go to Page Layout > Page Setup
(2) Change to Fit to 1 pages wide by [however many you want] pages tall or change
from landscape to portrait
(3) Change the margins and center on page horizontally and vertically in the Margins
tab
(4) Add custom headers and footers in the Header/Footer tab (page numbers, date,
timestamp, file name, etc.)
(5) Limit the print area in the Sheet tab, or add rows to repeat at the top of each page
What are all the ways to trace precedent cells?
Ctrl+[
Alt+M+P
Alt+T+U+T
Create a Dropdown Menu Command
Alt+D+L
Change Text Color
Alt+H+F+C
Change the Cell to a Number Format
Ctrl+Shift+1
Functions
Pre-built combinations of operations in Excel that facilitate spreadsheet analysis
Structure of Functions
Every function starts with an "=" sign, then the function name, and then the cell range
(bound with parentheses)
All functions follow the same
syntax:=functionname(argument1,argument2,...,argumentx)
Within the parentheses, functions can have 0, 1, or many arguments, separated by
commas
Now Function
=NOW()
Spits out the current time and date
SUM Function
In the SUM function =SUM(A1:A10), there is only one argument, which is telling Excel
the specific cell range that needs to be summed up
SUM Function Autosum Command
Alt+=
(Cmnd+Shift+T)
Average Function
=Average([number 1],...,[number x])
Gives an average of the range
Logical Functions: IF
Returns one value if a condition you specify evaluates to TRUE and another value if it
evaluates to FALSE
Use IF to conduct conditional tests on values and formulas
All IF statements follow the same structure =IF(x, y, z), where... (see picture)
Text output is designated by quotation marks around the outputs
Greater Than or Equal to Functions
<=
Nested IF statements
Generally follows the structure:
=IF([First Criteria for Yes], =IF([Second Criteria for Yes],"Yes","No"),"No")
OR
=IFS(Criteria 1, Value if Criteria 1 is True, Criteria 2, Value if Criteria is True, ...)
Note: There's no longer a value if false with the IFS statement; However, you can make
a criteria TRUE, which becomes the if false argument
Error-Trapping Function IFERROR
=IFERROR(value, value_if_error) returns a value you specify if a formula evaluates to
an error
If the formula does not result in an error, IFERROR returns the result of the formula
Creating Dynamic Headers by Combining Cell References with Text ("&")
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

Date Functions (EOMONTH)
=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
EDATE Date Functions
=EDATE(start_date, months) is a similar function to EOMONTH
However, EDATE returns the exact date, x months from the start date
Dates in Excel as Serial Numbers
When using dates in general in Excel, understand that Excel stores dates as serial
numbers - they must be formatted as dates (use Ctrl+1) in. order to look like
recognizable dates to the user
Using IF statements to see whether a cell has anything in it
You can use IF statements as a test of whether a cell has anything in it
For example, =IF(C1,C2,C3) would see if there is anything in cell C1; if there was, it
would output C2, otherwise C3
ISNUMBER and ISTEXT Functions
Functions typically embedded within an IF statement that test whether there is a number
(ISNUMBER) or text (ISTEXT) inside a cell
Ex. =IF(ISTEXT(A1),"Error",A1/A3)
YEARFRAC
YEARFRAC(start_date, end_date, basis) returns the proportion of the year between two
given dates, the start_date and end_date
Basis is an optional parameter
Useful in financial modeling when projecting future cash flows or obligations for a
fraction of a year
We often term the fraction of a year a "Stub Year Fraction"
DATE, DAY, MONTH, and YEAR Functions
DATE(year,month,day) is a function that combines distinct year, month, and day
elements into a valid date function in Excel
Combining this function with DAY(serial number), MONTH(serial number), and
YEAR(serial number) is sometimes useful for creating date functions out of disparate
data

Ex. =DATE(YEAR(C4+1),MONTH(C4),DAY(C4))
AND Function
=AND(logical1,logical2, ...) evaluates to true if all its arguments are true; false if one or
more argument is false
OR Function
=OR(logical1,logical2,...) evaluates to true if at least one argument is true
HLOOKUP
=HLOOKUP(lookup value, table range, row number) searches for a value in the top row
of a table or an array of values, and then returns a value in the same column from a row
you specify in the table or array
Use this when your comparison values are located in a row across the top of a table of
data and you want to look down a specified number of rows
VLOOKUP
=VLOOKUP(lookup value, table range, column number) searches for a value in
the leftmost column of a table, and then returns a value in the same row from a
column you specify in the table or array
Use this when your comparison values are located in a column to the left of a table of
data, and you want to look across (and to the right) a specified number of columns
If looking up text, add a 0 as the [range lookup] value for the fourth argument
Range Lookup
INDEX
=INDEX(Array, Row Number, Column Number)
Selects a value from an array of values with the appropriate row number and column
number of the array
CHOOSE
=CHOOSE(Index Number, Value 1, Value 2, ...)
Selects a number of values (the index number) out of a list of delineated values
OFFSET
=OFFSET(Reference, Rows, Columns, [Height], [Width])
Define a reference point (the top left corner); this function spits out a result that is x rows
below and y columns to the right of the reference point
Common Errors with HLOOKUP, VLOOKUP, CHOOSE, OFFSET, & INDEX
If you add a row into a table, the HLOOKUP, INDEX, and OFFSET are instantly
screwed up, as they depend on the rows
If you add a column into a table, the INDEX, VLOOKUP, and OFFSET are messed up
MATCH Function
The MATCH function returns the relative position (number) of an item in an array that
matches specified lookup value
Syntax: =MATCH(lookup_value,lookup_array,match_type)
It does NOT return the value within the cell itself (as opposed to the HLOOKUP and
VLOOKUP functions)
Match type is an exact match (0), greater than (-1), less than (1) -- we only really use 0
Combining MATCH Function with Lookup & Reference Functions
Combining MATCH with functions like HLOOKUP, VLOOKUP, OFFSET, INDEX, and
CHOOSE makes formulas more durable and dynamic
See Lookup & Reference sheet on Practice Sheet for examples
INDIRECT
=INDIRECT(reference text) returns the reference specified by a text string
=INDIRECT("B4") will output the value of what is in cell B4
The most common way to get value out of this function is to combine with concatenate
(&)
Ex. When creating a model for a flexible user defined start and end date for calculating
a cumulative EBITDA result, you can use INDIRECT and &
Data Validation & Creating Drop-Down Menus
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)
Combining INDIRECT with MATCH
To combine INDIRECT with MATCH, you can use INDIRECT's second argument:
TRUE is in the form A1, FALSE is in the form R1C1, or row 1 column 1
Ex. =INDIRECT("R1C1",FALSE)
Now, you can use INDIRECT, MATCH, and & to create a dynamic function
Note: With INDIRECT, you have to start the MATCH array from the very edge of the
worksheet (first column and first row) for the correct row and column number; or, you
can add the number of rows above or columns to the left of the beginning of your array
to the MATCH function
The Address Function
=ADDRESS(row_number,column_number)
With INDIRECT and MATCH, you can dynamically find a value in a table using the
ADDRESS function (see picture)
Also makes working between worksheets easier -- add the relevant sheet name in
quotations with =ADDRESS(row_number,column_number,,,sheet_name)
Ex. =ADDRESS(1,2,,,"Sheet1") for R1C1 of Sheet1
COLUMN and ROW Functions
=COLUMN() gives you the current column and =ROW() gives you the current row
=COLUMN(reference) and =ROW(reference) gives you the column and row of a
reference point
=COLUMNS(array) and =ROWS(array) gives you the number of columns and rows in
an array
Using COLUMN and ROW Functions as Counters in Complex Formulas
Use COLUMNS(array) and ROWS(array) to act as counters when using INDIRECT w/
MATCH to create a dynamic counter in the function
Evaluate
Ctrl +
Data Tables
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
Building a Vertical Data Table
Layout assumptions on the LEFT
(1) Identify the output variable
The variable you are trying to sensitize is the output variable
Must be referenced from your analysis into the top right corner of the data table
(2) Hard-code the input variable sensitivities
The variables whose impact on the output variables you want to analyze are the input
variables
Input variable assumptions should not be referenced from the analysis, but rather be
hard-coded and arranged in the column to the left of the output variable
(3) Run the data table
Hit Alt+D+T (Data>Table...) to access the Data Table dialog
Row Input Cell: Not needed for vertical tables
Column Input Cell: Reference the input variable from the model
Highlight the entire range (including the output variable) and hit OK when done - the
data table should populate
You may need to hit F9 if Excel is set to "manual" or "automatic calculations except for
data tables"
Important: Data tables must always be in the same worksheet as the input variables
Get inside a drop-down list
Alt+Up/DownArrow (Option+Up/DownArrow)
Building a Horizontal Data Table
From a substance standpoint, it's the same as vertical
(1) Referenced output variable from your analysis into the bottom left corner of the
data table
(2) Input the input assumptions in the row above and one cell to the right of the output
reference
(3) Highlight the entire range (including the output variable) and hit Alt+D+T; the Data
Table dialog will appear
Row Input Cell: Reference the input variable from the model
Column Input Cell: Not needed
Hit OK when done - the data table should population; if not, hit F9
Building a Two-Sided Data Table
Same as vertical data table, but allows for 2 inputs instead of one
Output variable must be referenced from the model into the top left corner of the data
table
Get Rid of Borders
Ctrl+Shift+-
When Data Tables Fail: Self Referencing IF Statement
When a table if on a separate worksheet, it doesn't work
To solve this, you can use a self referencing IF statement
Using IF and AND, you can create a self referencing IF table where you manipulate the
original data to populate the table with the appropriate values
Ex. See picture and sheet "Self Referencing IFs" in Practice Workbook
Note: In order for this to work, iterations must be turned on and you cannot go into the
cells once you populate the table
XLOOKUP
=XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],...)
Allows you to choose the lookup value (what you want to look for), the array the lookup
is located in, and the corresponding array the return value should be located in
Also allows you to return an IFERROR message if not found
Note: Does not break if columns are added by decoupling the lookup array and the
return array, like OFFSET MATCH function but simpler
Nested (Two-Way) XLOOKUP
If you are finding a result from two different drop-down menus, you can use a nested
XLOOKUP to get a dynamic function with two variables
Ex.
=XLOOKUP(variable1,variable1_array,XLOOKUP(variable2,variable2_array,wholetable
))
(See XLOOKUP workbook)
Using XLOOKUP to Generate Multiple Values
If you set up your search returns as the same number of columns as the data table and
then set up the return array in the XLOOKUP function as the full data table, it will
automatically occupy the full search returns
Scenario Analysis Using XLOOKUP
You can use XLOOKUP's ability to generate multiple values to create a scenario
analysis for something like an income statement
Where XLOOKUP Loses to INDEX MATCH
When trying to create a master formula that can be copied across an entire range,
XLOOKUP loses to INDEX MATCH or INDEX XMATCH XMATCH
(See last worksheet of XLOOKUP workbook for example)
XMATCH
=XMATCH(lookup_value,lookup_array) only gives you an exact match, so you only
have to define two arguments instead of three!
Use this instead of MATCH
SUMPRODUCT
=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
Booleans in Excel
When Excel spits out a TRUE or FALSE (see picture), you can convert them
respectively into 1 or 0 by applying any operator on them
Interestingly, multiplying a TRUE (or FALSE) by another TRUE (or FALSE) also has the
effect of converting it into a 1 or 0, respectively
SUMPRODUCT with Embedded Criteria
A lesser known feature of SUMPRODUCT is the ability to embed criteria directly into the
arrays
For example, we can directly calculate proceeds on options that have an exercise price
less than the share price so you no longer need to calculate option proceeds for each
tranche:
We have 2 criteria -- the options # and the exercise price per tranche, which is
multiplied against a TRUE or FALSE criteria for each exercise tranche
Tranche 1 evaluates to TRUE, so Excel multiplies the TRUE by the Tranche 1 exercise
price, and then by the # of options
Tranche 2 & 3 evaluate to FALSE, and become 0 when multiplied by the exercise prices
Note: See MATH sheet of Excel Practice workbook for more examples
When Are Options Exercised?
When their exercise price is less than the strike price (they're "in the money")
SUMIF
=SUMIF(range, criteria, sum range) adds the cells specified by a given criteria
The range is the range that you want to evaluate with the criteria, whereas the sum
range is what is actually summed
Criteria can either be hardcoded which requires quotation marks as you see in the
picture, or a direct cell reference (which would not need quotes around it)
SUMIFS
=SUMIFS(range1, criteria1, sum range1, range2, criteria2, sum range2, etc.)
Same as SUMIF but can handle multiple criteria and sum ranges
AVERAGEIF and AVERAGEIFS
Identical to SUMIF and SUMIFS but instead of summing, this function averages the
data in the range
Absolute Value
=ABS(cell or number) gives you the absolute value
Ceiling
=CEILING(number, significance)
Rounds up to the nearest x amount with a certain level of significance
Ex. If you want it rounded up to the nearest 10th, the significance = 0.1
Floor
=FLOOR(number, significance)
Rounds down to the nearest x amount with a certain level of significance
Ex. If you want it rounded down to the nearest 10th, the significance = 0.1
Combinations Function
=COMBIN(number, number chosen)
Ex. =COMBIN(4, 2) gives you the number of two person combinations out of a number
of 4 people
Note: Given the useful life of an asset, you can find the return sum of years' digits with
=COMBIN(useful life + 1,2)
Round Functions
=ROUND(number,number of digits) rounds the number to the specified number of
decimal places
=ROUNDUP rounds up
=ROUNDDOWN rounds down
MIN Function
=MIN(number 1, number 2, ...) returns the smallest number in a specified set of values
MAX Function
=MAX(number 1, number 2, ...) returns the largest number in a specified set of values
A classic use in financial modeling is to use a max function to prevent a revolving credit
line balance from dipping below 0 when there is a cash shortfall
COUNT, COUNTA, and COUNTIF Functions
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 Syntax
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)
PV Function
Returns the present value of a series of future payments
Syntax: =PV(rate, nper, pmt, fv, type), where...
Note: Rate represents the rate per period
If the future value is 0, then omit the fv argument
FV Function
Returns the future value of an investment based on constant payment and interest rate
Syntax: =FV(rate, nper, pmt, pv, type), where...
NPV Function
=NPV(rate,value1,value2,...) returns the net present value of an investment based on a
discount rate and a series of future payments (negative values) and income (positive
values)
Values are assumed to occur at the end of each time period and must be referenced in
the order in which they occur
NPV vs. PV Functions
PV assumes constant payments, while NPV cash flows can vary from period to period
NPV does not require user to explicitly identify number of periods and simply assumes
equal periods based on the number of values
NPV assumes payments occur at the end of the period (but the formula can be adjusted
to simulate payments occurring at beginning of period)
XNPV Function
An improvement on NPV for when the timing of cash flows is uneven
=XNPV(rate, values, dates) returns the net present value for a set of cash flows that do
not necessarily occur at equal time intervals
Unlike NPV, the first cash flow is not discounted, while dates of subsequent cash flows
can be included in the formula in any order
Hacking NPV to Calculate Beginning of Period
Add the first payment and then take the NPV of the remaining payments
Ex. =1000+NPV(10%,1000,1000,1000) for four payments BOP
IRR Function
=IRR(values, guess) returns the IRR for a series of values
IRR is the rate corresponding to an NPV of 0
While values do not have to be identical each period, the periods are assumed to be
equally far apart
'Guess is an optional argument; Excel needs a starting point to iterate to the right IRR. If
you choose not to input a "guess", Excel will automatically use 10% as the guess
XIRR Function
=XIRR(values, dates, guess) returns the IRR for a series of values which may not be
periodic
The date of each cash flow must be referenced in the formula (the "dates")
Text Functions
For professionals that have to work extracting data from large inconsistent and poorly
formatted data sets, text functions can be absolutely critical
We will cover:
LEN
Identifies the length of the string of text or numbers
=LEN(text)
LEFT
=LEFT(text, number of characters)
Inputs the first however many characters of a string
RIGHT
=RIGHT(text, number of characters)
Inputs the last however many characters of a string
MID
=MID(text,start number,number of characters)
Starts with the start number on the string and then outputs the corresponding number of
characters starting at that number
PROPER
=PROPER(text)
Converts strings into the proper/upper case
UPPER & LOWER
=UPPER(text)
Converts strings into entirely upper case letters
= LOWER(text)
Converts strings into entirely lower case letters
TRUNC
=TRUNC(number, number of digits) takes a number and carries it to the specified
number of digits
SEARCH
=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
=FIND(find text, within text, [start number])
The same as SEARCH but is case sensitive!
SUBSTITUTE
=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
=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
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)
Text to Columns
Data > Text to Columns
Usually use delineated
Note: If there is a column that has spaces within it and the columns are delineated by
spaces, you can fix this by concatenating the columns with the corresponding data
within them, separating each column by a space (" ")
Then, copy and special paste the values into the correct column
If there are several columns included in this, you can use flash fill to separate the
columns
Remove Duplicates
Data > Remove Duplicates
Highlight the relevant data range and then click remove duplicates
Select all columns to ensure true duplicates
VALUE Function
Allows Excel to recognize a value as a number when it was previously recognized as
text
Ex. "Iphone - $499" in A1: =VALUE(RIGHT(A1,4)) to get $499 as a number
DATEFUNCTION
Allows you to consolidate separate date data points and have Excel recognize it as a
date
Ex. "December" "21" and "2015" in cells A1, A2, A3:
=DATEVALUE(CONCATENATE(A1," ",A2," ",A3))
Sorting Data
Highlight an entire table or have the cursor in one of the cells in the table
Hit Alt+D+S (Data Tab > Sort) to bring up the 'Sort' meenu
You can choose to sort various columns by value, cell color, or font color, and in
ascending or descending order
Note: You can sort multiple levels, so if you wanted to sort by industry first and then by
revenue within each industry, you just need to click 'add level'
Combining Sort & Subtotal
Combining Sort with Subtotal - Alt+A+B (Data > Subtotal) - can add further clarity to
data sets
Sort by category and then sum
Autofiltering
Alt+A+T (Data > Filter)
A tool that enables you to filter by a wide range of criteria
You should now see drop-down arrows in the column headings of your table
Notice that the filtered rows have been hidden but not deleted; fortunately, if you copy
and paste the filtered range to another area of the worksheet or a different worksheet
altogether, it does not copy over the hidden rows
Once a filter is applied you can tell which column was filtered via the funnel icon
To remove individual filters, click on the filter icon and 'Clear filter'
To remove all filters, hit Alt+A+T again
Pivot Tables
An efficient, visual tool for analyzing, exploring, and presenting large amounts of data
It is particularly useful when your data needs to be sliced and diced in a variety of ways
Answers Questions Like:
(1) Which property type is most expensive/most common in a specific region?
(2) Which REIT had the greatest sales within a specific region or for a specific property
type?
Has the ability to pivot, or quickly swap out different row and column parameters to
observe data in different ways
Creating a Pivot Table
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 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.
Distinct Count
Prior to 2010, a challenge was capturing distinct counts of duplicate items presented in
large data sets
In Excel 2013, Distinct Count has been added under Value Field Settings in a Pivot
Table
The Fiscal Half Date Problem
If you want to find the fiscal half date for a given transaction date, you can use
EOMONTH and ROUND functions together to solve it
Ex. For fiscal halves May 31st and Nov 30th and transaction date in cell B2:
=EOMONTH(B2,6+6*ROUND(MONTH(B2)/12,0)-MONTH(B2)-1)
COUNTIF as an array
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)
The Olympic Event Problem
If you want to count how many events a country or countries received a medal, you can
combine SUM, COUNTIF, and MIN functions to find this from a list of medalists
Ex. To see how many events the US, Germany, or France won medals, see the
picture...
See Sheet5 on the Excel Practice Sheet
Using Wildcards
When doing a COUNTIF function, you can use asterisks to find a word within a cell or
array of cells
Ex. To find cells with the word united somewhere in them, you can do:
=COUNTIF(array,"united")
For cells that start with the letter A, you only need one asterisk: =COUNTIF(array,"A*"
LAMBDA
Use a LAMBDA function to create custom, reusable functions and call them by a
friendly name
=LAMBDA(parameter_or_calculation,parameter_or_calculation, ...)
Note: You must identify the parameters you need upfront before defining them
LAMBDA for GRATE
For a growth rate function:
=LAMBDA(base,growthrate,base*(1+growthrate))(Cell for Base,Cell for Growth Rate)
Then, in another cell: Copy the LAMBDA portion of the function
=LAMBDA(base,growthrate,base*(1+growthrate))
Hit Alt+M+N (Formula > Define Name) to invoke the name manager
Create a new name GRATE and paste the LAMDA function in 'Refers to'
=GRATE(base,growthrate)
LAMBDA for CAGR
LAMDBA for CAGR: =LAMBDA(vfinal,vbegin,t,(vfinal/vbegin)^(1/t)-1)(vfinal,vbegin,t)
New Function named CAGR: =LAMBDA(vfinal,vbegin,t,(vfinal/vbegin)^(1/t)-1)
vfinal = final value, vbegin = beginning value, t = time period
=CAGR(vfinal,vbegin,t)
LAMBDA for DSO
For Days Sales Outstanding Calculations
LAMBDA for
DSO: =LAMBDA(AR,Revenue,DaysinPeriod,DaysinPeriod/(Revenue/AR))
(AR,Revenue,DaysinPeriod)
New Function named
DSO: =LAMBDA(AR,Revenue,DaysinPeriod,DaysinPeriod/(Revenue/AR))
AR = Accounts Receivable
=DSO(AR,Revenue,DaysinPeriod)
LAMBDA for IMPLIEDG
For implied terminal growth rate
LAMBDA for IMPLIED
G: =LAMBDA(rate,cashflow,value,(rate-cashflow/value)/(1+cashflow/value))
(rate,cashflow,value)
New Function named
IMPLIEDG: =LAMBDA(rate,cashflow,value,(rate-cashflow/value)/(1+cashflow/value))
rate = discount rate (WACC), cashflow = first period cash flow, value = present value of
the terminal value (annuity)
=IMPLIEDG(rate,cashflow,value)
LAMBDA for EOQUARTER
For end of quarter: This function takes a user-defined date, converts it to the nearest
upcoming end of quarter date, and allows for incrementing to future quarters
LAMBDA for EOQUARTER: =LAMBDA(date,quarters,EOMONTH(EOMONTH(date,
(ROUNDUP(MONTH(date)/3,0)*3-MONTH(date))),quarters*3))(date,quarters)
New Function named
EOQUARTER: =LAMBDA(date,quarters,EOMONTH(EOMONTH(date,
(ROUNDUP(MONTH(date)/3,0)*3-MONTH(date))),quarters*3))
=EOQUARTER(date,quarters)
quarters = Number of quarters from the quarter end date
LAMBDA for TSM
For Treasury Stock Method: For a company that has issued options to its employees,
how diluted are those options? Should they be included in the share count?
LAMBDA for
TSM: =LAMBDA(numoptions,strikeprice,currentprice,SUMIF(strikeprice,"<"&currentpric
e,numoptions)-SUMPRODUCT(numoptions,strikeprice*(strikeprice<currentprice))/
currentprice)(numoptions,strikeprice,currentprice)
New Function named
TSM: =LAMBDA(numoptions,strikeprice,currentprice,SUMIF(strikeprice,"<"&currentpric
e,numoptions)-SUMPRODUCT(numoptions,strikeprice*(strikeprice<currentprice))/
currentprice)
=TSM(numoptions,strikewprice,currentprice)
numoptions = array of options, strikeprice = array of strike prices for options,
currentprice = current share price
LAMBDA for SHEETNAME
For Sheet Name: Grabs the name of the current worksheet
LAMBDA for
SHEETNAME: =LAMBDA(reference,RIGHT(CELL("filename"),LEN(CELL("filename"))-
FIND("]",CELL("filename"))))(reference)
New Function named
TSM: =LAMBDA(reference,RIGHT(CELL("filename"),LEN(CELL("filename"))-
FIND("]",CELL("filename"))))
=SHEETNAME(reference)
reference = any cell reference on the corresponding worksheet
How to Use Lambdas Across Multiple Workbooks
To use lambdas across multiple workbooks, make a blank worksheet in the workbook
with your lambdas
Then, copy and paste it into another workbook to add the lambdas over
Recording Macros
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
Recording a Macro (Blue Color, 1 Decimal Place, Comma Delineated, Yellow
Background)
(1) Select 'Use Relative References'
(2) Place the cursor in any cell
(3) Alt+L+R brings up 'Record Macro' menu (File > Record Menu)
(4) Input a name and shortcut key (Ctrl+Shift+Z); as soon as you hit 'OK', you are
starting to record this macro
(5) Format the cell using Ctrl+1
(6) When finished, hit Alt_+L+R or stop recording to stop recording
(7) Try going to any cell and hit Ctrl+Shift+Z; those cell's contents should be formatted
per your preferences
Problems with Macros
After you run your macro, you lose the ability to undo all the prior work, so be careful!
Custom shortcuts override pre-existing shortcuts
If you want your macro to run every time you use Excel, select Personal Macro
Workbook instead of 'This Workbook'
After the macro is created, you can always delete or rename a macro or change
shortcut keys assigned to it by clicking 'Macros' in the Developer tab