Navigation & Data Selection
Ctrl + N
Ctrl + Tab
Shift + F11
Alt + H + D + S Ctrl + PgUp
Ctrl + PgDn
Alt + H + O + M
Shift + Ctrl + PgUp / PgDn Alt + H + O + U +S
Alt + H + O + U +H
Alt + H + O + R
Alt + H + O + T
Shift + F11
Alt + H + D + S Ctrl + PgUp
Ctrl + PgDn
Alt + H + O + M
Shift + Ctrl + PgUp / PgDn Alt + H + O + U +S
Alt + H + O + U +H
Alt + H + O + R
Alt + H + O + T
New Workbook
Switch
Workbook
New Worksheet Del Worksheet Move to Left Worksheet Move to Right Worksheet Move / Copy Worksheet Select Multiple Worksheets Hide Worksheet
Show Worksheet
Rename Worksheet Color Tab
New Worksheet Del Worksheet Move to Left Worksheet Move to Right Worksheet Move / Copy Worksheet Select Multiple Worksheets Hide Worksheet
Show Worksheet
Rename Worksheet Color Tab
Arrow Keys
Ctrl + Arrows
Shift + Arrows Shift + Ctrl + Arrows
Shift + F8
Shift + Arrows Shift + Ctrl + Arrows
Shift + F8
Move Around
Jump to
Boundary
Select Cells
Select to
Boundary
Select Multiple
Areas
F2
Del
Ctrl + Arrows Ctrl + Shift + Arrows
Alt + Enter Ctrl + Enter / Tab / Shift + Tab
Ctrl + Arrows Ctrl + Shift + Arrows
Alt + Enter Ctrl + Enter / Tab / Shift + Tab
Edit Cell
Delete Cell Contents
Skip Word(s) Highlight Word(s)
New Line in Cell Edit and... Stay in Place / Go Left / Go Right
Delete Cell Contents
Skip Word(s) Highlight Word(s)
New Line in Cell Edit and... Stay in Place / Go Left / Go Right
Editing Cells
Excel 2007 / 2010 / 2013+
Quick Reference – The Most Important Keyboard Shortcuts for Finance (IB, PE, HF/AM, ER, CF, etc.)
Quick Reference – The Most Important Keyboard Shortcuts for Finance (IB, PE, HF/AM, ER, CF, etc.)
The Fundamentals
Rows & Columns
http://breakingintowallstreet.com
Basic Formatting
Basic Formatting
Ctrl + O
Ctrl + N
Ctrl + P
Ctrl + S
F12
Ctrl + F4 Alt + F4 Esc
Ctrl + C Ctrl + X Ctrl + V Ctrl + Z Ctrl + Y Ctrl + A Ctrl + F Ctrl + H Alt + Tab Alt + T + O F4
Ctrl + F1
Ctrl + F4 Alt + F4 Esc
Ctrl + C Ctrl + X Ctrl + V Ctrl + Z Ctrl + Y Ctrl + A Ctrl + F Ctrl + H Alt + Tab Alt + T + O F4
Ctrl + F1
Open File
New File
Print
Save File Save File As Close File Close Excel Exit Dialog Copy
Cut
Paste
Undo
Redo
Select All
Find
Replace
Switch Windows Options Menu Repeat Last Action
Show / Hide Ribbon Menu
Save File Save File As Close File Close Excel Exit Dialog Copy
Cut
Paste
Undo
Redo
Select All
Find
Replace
Switch Windows Options Menu Repeat Last Action
Show / Hide Ribbon Menu
Ctrl + Spacebar
Shift + Spacebar
Ctrl + Shift + +
Ctrl + –
Right Mouse Button + E
Alt + A + G + G
Shift + Alt + Right
Alt + A + U + U
Shift + Alt + Left Alt + A + J
Alt + A + H
Ctrl + –
Right Mouse Button + E
Alt + A + G + G
Shift + Alt + Right
Alt + A + U + U
Shift + Alt + Left Alt + A + J
Alt + A + H
Select Column
Select Row
Insert Cells /
Rows / Columns
Delete Cells /
Rows / Columns
Insert Cut Cells
and Shift Over
Group Rows /
Columns
Group Rows / Columns Ungroup Rows / Columns Ungroup Rows / Columns
Show Grouped Rows / Columns Hide Grouped Rows / Columns
Group Rows / Columns Ungroup Rows / Columns Ungroup Rows / Columns
Show Grouped Rows / Columns Hide Grouped Rows / Columns
Alt+H
Ctrl+1 Ctrl+Alt+V Ctrl+Alt+V+T Ctrl+Alt+V+V Ctrl+Alt+V+F Alt+H+FC Alt+H+H Alt+H+B
Alt + H + A + L/C/R Alt+H +6 Alt+H +5 Alt+H +0
Alt+H +9
Ctrl+1 Ctrl+Alt+V Ctrl+Alt+V+T Ctrl+Alt+V+V Ctrl+Alt+V+F Alt+H+FC Alt+H+H Alt+H+B
Alt + H + A + L/C/R Alt+H +6 Alt+H +5 Alt+H +0
Alt+H +9
Format Menu
Format Dialog
Paste Special
Paste Formats
Paste Values
Paste Formulas
Font Color
Fill Color Border Options Align Left / Center/ Right Increase Indent Decrease Indent Increase Decimal Places Decrease Decimal Places Bold
Italics Underline Strikethrough Add Borders Delete Borders General Number
Time
Date
Currency Percentage Scientific Displays cell using custom format
Auto-Fit Col. Auto-Fit Row Column Width Row Height Conditional Formatting Format as Table
Fill Color Border Options Align Left / Center/ Right Increase Indent Decrease Indent Increase Decimal Places Decrease Decimal Places Bold
Italics Underline Strikethrough Add Borders Delete Borders General Number
Time
Date
Currency Percentage Scientific Displays cell using custom format
Auto-Fit Col. Auto-Fit Row Column Width Row Height Conditional Formatting Format as Table
Workbooks & Worksheets
+&
+–
+~
+!
+@
+#
+$
+%
+^
Ctrl+B
Ctrl+I
Ctrl+U
Ctrl+5
Ctrl + Shift Ctrl + Shift Shift + Ctrl Shift + Ctrl Shift + Ctrl Shift + Ctrl Shift + Ctrl Shift + Ctrl Shift + Ctrl =TEXT(Cell, Format)
Alt + H + O + I Alt + H + O + A Alt + H + O + W Alt + H + O + H Alt + H + L + R
Alt+H+T
Ctrl + Shift Ctrl + Shift Shift + Ctrl Shift + Ctrl Shift + Ctrl Shift + Ctrl Shift + Ctrl Shift + Ctrl Shift + Ctrl =TEXT(Cell, Format)
Alt + H + O + I Alt + H + O + A Alt + H + O + W Alt + H + O + H Alt + H + L + R
Alt+H+T
Dates & Times
Formulas and Calculations
http://breakingintowallstreet.com
Common Built-In Functions
Common Built-In Functions
=DATE (Year,
Month, Day)
=NETWORKDAYS
(Start, End Date)
=EOMONTH (Start Date, # Months)
Ctrl + Shift + ; Ctrl + ;
=EOMONTH (Start Date, # Months)
Ctrl + Shift + ; Ctrl + ;
Creates new
Date
Business days in between 2 dates
Last day of month after # months Current Time Current Date
Business days in between 2 dates
Last day of month after # months Current Time Current Date
=
F9
F4
Ctrl + F3 F5
Tab
Shift + F3
Ctrl+Alt+V+F Ctrl + Alt + V + R
Ctrl + D Ctrl + R Ctrl + ’
F5, Alt + S + F + X F5, Alt + S + O + X Ctrl+~ =IFERROR(Value, Value If Error)
F9
F4
Ctrl + F3 F5
Tab
Shift + F3
Ctrl+Alt+V+F Ctrl + Alt + V + R
Ctrl + D Ctrl + R Ctrl + ’
F5, Alt + S + F + X F5, Alt + S + O + X Ctrl+~ =IFERROR(Value, Value If Error)
Enter Formula
Refresh All
Anchor Cell
Name Cell
Jump to Cell
Use Suggested
Name
Enter Built-In Function
Paste Formulas Paste Formats & Formulas Copy Down Copy Right Copy from Above
Go to Formulas Go to Constants Show Formulas Calculates only if no error
Enter Built-In Function
Paste Formulas Paste Formats & Formulas Copy Down Copy Right Copy from Above
Go to Formulas Go to Constants Show Formulas Calculates only if no error
=SUM
Alt+=
=COUNT =AVERAGE =MAX
=MIN
=SUMIF / =SUMIFS =COUNTIF / =COUNTIFS =SUMPRODUCT
=ABS =IF =OR
=AND
=NPV (Discount Rate, Cash Flows)
=XNPV (Rate, Values, Dates) =IRR (Values)
=XIRR (Values, Dates)
=COUNT =AVERAGE =MAX
=MIN
=SUMIF / =SUMIFS =COUNTIF / =COUNTIFS =SUMPRODUCT
=ABS =IF =OR
=AND
=NPV (Discount Rate, Cash Flows)
=XNPV (Rate, Values, Dates) =IRR (Values)
=XIRR (Values, Dates)
Sum Numbers
Sum Adjacent
Cells
Count # Entries Average Maximum Minimum Conditional Sum Conditional Count Multiply and Sum Range Absolute Value Conditional One Must Be True
All Must Be True
Net Present Value of Cash Flows
NPV with irregular dates Internal Rate of Return of Investment IRR with irregular dates
Count # Entries Average Maximum Minimum Conditional Sum Conditional Count Multiply and Sum Range Absolute Value Conditional One Must Be True
All Must Be True
Net Present Value of Cash Flows
NPV with irregular dates Internal Rate of Return of Investment IRR with irregular dates
Text Tools & Functions
Alt + A + FT
=LEFT
=RIGHT
=MID
=FIND
=SEARCH
=LEN =SUBSTITUTE
=REPLACE
Alt + A + E =TRIM
=PROPER
=UPPER =LOWER
=SEARCH
=LEN =SUBSTITUTE
=REPLACE
Alt + A + E =TRIM
=PROPER
=UPPER =LOWER
Text File Import
Chars from left
Chars from right
Chars from...
Search for text
within text
Same, but not
case sensitive
Length of text
Replace text in
text with search
Same, but use
position instead
Text to Columns
Deletes Extra
Spaces
Capitalize All
First Letters
Make All Caps
Make All Lower
Lookups & Related Functions
Display & Printing
=DSUM (DB,
Field, Criteria)
=DCOUNT (DB, Field, Criteria)
Ctrl + Shift + Enter =TRANSPOSE (Rows or Columns)
=DCOUNT (DB, Field, Criteria)
Ctrl + Shift + Enter =TRANSPOSE (Rows or Columns)
Sums records
that match
criteria
Counts records that match criteria
Enter Array Function Converts rows to columns and vice versa
Counts records that match criteria
Enter Array Function Converts rows to columns and vice versa
Alt + W + F + F
Alt + W + Q
Ctrl + Mouse
Scroll Wheel
Alt + P + S + P
Alt + P + R + S
Ctrl + F2
Alt + W + I Alt + W + L Alt + W + VG
Ctrl + F2
Alt + W + I Alt + W + L Alt + W + VG
Freeze Panes
Zoom
Zoom
Page Setup
Set Print Range to Selected Area Print Preview Page Break View Normal View Toggle Gridlines
Zoom
Page Setup
Set Print Range to Selected Area Print Preview Page Break View Normal View Toggle Gridlines
Excel 2007 / 2010 / 2013+
Quick Reference – The Most Important Keyboard Shortcuts for Finance (IB, PE, HF/AM, ER, CF, etc.)
Quick Reference – The Most Important Keyboard Shortcuts for Finance (IB, PE, HF/AM, ER, CF, etc.)
=VLOOKUP
(Value, Table,
Column #)
=HLOOKUP (Value, Table, Row #)
=MATCH (Value, Row or Column Range)
=INDEX (Table, Row #, Col #)
=INDIRECT (Ref)
=ADDRESS (Row #, Col #)
=HLOOKUP (Value, Table, Row #)
=MATCH (Value, Row or Column Range)
=INDEX (Table, Row #, Col #)
=INDIRECT (Ref)
=ADDRESS (Row #, Col #)
Match Value in
Left Column
and Return
from Column #
Match Value in
Top Row and
Return from
Row #
Find Item’s Position in Row/Column Return Item at Row # and Column # Returns cell at reference given by text
Creates cell reference
Find Item’s Position in Row/Column Return Item at Row # and Column # Returns cell at reference given by text
Creates cell reference
Database and Array Functions
Auditing Formulas
Filtering, Sorting & Validating
http://breakingintowallstreet.com
Macros, VBA, and Forms
Macros, VBA, and Forms
Pivot Tables
Graphs & Charts
Excel 2007 / 2010 / 2013+
Quick Reference – The Most Important Keyboard Shortcuts for Finance (IB, PE, HF/AM, ER, CF, etc.)
Quick Reference – The Most Important Keyboard Shortcuts for Finance (IB, PE, HF/AM, ER, CF, etc.)
Ctrl+[
Ctrl+]
Alt + M + P
Alt + M + D
Alt + M + A + A Shift + Ctrl + { Shift + Ctrl + } F5 + Enter
Shift + F2
Alt + R + D Alt + R + A
F5, Alt + S + C
Ctrl+]
Alt + M + P
Alt + M + D
Alt + M + A + A Shift + Ctrl + { Shift + Ctrl + } F5 + Enter
Shift + F2
Alt + R + D Alt + R + A
F5, Alt + S + C
Immediate
Precedents
Immediate
Dependents
Trace
Precedents
Trace
Dependents
Erase Traces
All Precedents
All Dependents
Jump to
Original Cell
Add/Edit
Comment
Del Comment Show All Comments Highlight Cells w/ Comments
Del Comment Show All Comments Highlight Cells w/ Comments
Alt + A + SS
Alt+A+SA
Alt+A+SD
Ctrl + Shift + L Alt + A + Q
Right Mouse Button + E + V Alt + A + M
Alt + A + V + V
Ctrl + Shift + L Alt + A + Q
Right Mouse Button + E + V Alt + A + M
Alt + A + V + V
Sort Data
Sort Ascending Sort Descending Filter Data Advanced Data Filter
Filter by Cell’s Properties Remove Duplicates Validate Data
Sort Ascending Sort Descending Filter Data Advanced Data Filter
Filter by Cell’s Properties Remove Duplicates Validate Data
Alt + F11
F5 (in VBA)
F2 (in VBA)
Ctrl + G (in VBA)
Alt + L + I
Alt + W + M + U
Alt + W + M + R Alt + W + M + V
F5 (in VBA)
F2 (in VBA)
Ctrl + G (in VBA)
Alt + L + I
Alt + W + M + U
Alt + W + M + R Alt + W + M + V
VBA Editor
Run Macro
Object Browser
Immediate
Window
Form Control Use Relative References Record Macro View Macros
Form Control Use Relative References Record Macro View Macros
|
Alt + N + V
|
Pivot Table
|
Scenarios & Sensitivities
+ C
+ N
+ Q
+ B
+ X
+ SD
+ R +A
+ R +A
Column Chart
Line Chart
Pie Chart
Bar Chart
Text Box Combo Chart (2013+) Recommended Chart (2013+) Add Chart Element (2013+) Design Tab Layout Tab (2007, 2010) Format Tab (2007, 2010) Format Tab (2013)
Pie Chart
Bar Chart
Text Box Combo Chart (2013+) Recommended Chart (2013+) Add Chart Element (2013+) Design Tab Layout Tab (2007, 2010) Format Tab (2007, 2010) Format Tab (2013)
Alt + N
Alt + N
Alt + N
Alt + N
Alt + N
Alt + N
Alt + N Alt+JC
Alt+JC Alt+JA
Alt+JO Alt+JA
Alt + N Alt+JC
Alt+JC Alt+JA
Alt+JO Alt+JA
=CHOOSE
(Number, Item1,
Item2...)
=OFFSET(Cell, #
Rows, # Cols)
Alt + A + W + S
Alt + A + W + G Alt + A + W + T
Alt + A + W + S
Alt + A + W + G Alt + A + W + T
Select from List
based on
Number
Move # of Rows and Columns from Cell Scenario Manager
Goal Seek Data Table
Move # of Rows and Columns from Cell Scenario Manager
Goal Seek Data Table
-
Row Input Cell = Discount
Rate
-
Column Input Cell =
Terminal Growth Rate
Excel 2007 / 2010 / 2013+
Quick Reference – The Most Important Keyboard Shortcuts for Finance (IB, PE, HF/AM, ER, CF, etc.)
Quick Reference – The Most Important Keyboard Shortcuts for Finance (IB, PE, HF/AM, ER, CF, etc.)
Custom Number Formats
Example Data:
5
-1200
0 Wal-Mart
5
-1200
0 Wal-Mart
Displayed As:
5.0x
(1,200.00)
Balanced!
Model – Wal-Mart
5.0x
(1,200.00)
Balanced!
Model – Wal-Mart
Used For:
Valuation Multiples Negative Expenses Balance Sheet Checks Titles & Headers
Valuation Multiples Negative Expenses Balance Sheet Checks Titles & Headers
http://breakingintowallstreet.com
Text on Left: [Blue]0.0 x;[Red](#,##0.00);"Balanced!";"Model - "@
In Excel 2007 / 2010 / 2013+, you can add custom shortcuts by right clicking the Quick Access Toolbar and going to “Customize Quick Access Toolbar” – or you can right-click the button itself and go to “Add to Quick Access Toolbar.”
Then, you activate the shortcut by pressing the Alt key and the number it’s assigned to. Here, Alt + 4 would change the spreadsheet to a “Page Break” view.
-
The order for Custom Number Formats is: [Positive Style];
[Negative Style]; [Zero Style]; [Text Style]
-
If you include the “@” symbol and text, the text will appear
and the “@” will be replaced by what’s in the cell.
-
[Red] and [Blue] can be used for color coding.
-
For more on custom number formats, please see our separate
guide – this is just a brief summary.
In Excel 2007 / 2010 / 2013+, you can add custom shortcuts by right clicking the Quick Access Toolbar and going to “Customize Quick Access Toolbar” – or you can right-click the button itself and go to “Add to Quick Access Toolbar.”
Then, you activate the shortcut by pressing the Alt key and the number it’s assigned to. Here, Alt + 4 would change the spreadsheet to a “Page Break” view.
Always make sure you go to the
Options menu (Alt + T + O), select
Formulas, and use the settings
shown on the right:
Ideally, you will also disable the language bar and any plug-ins, add-ins, or macros that interfere with shortcuts, and any programs that override built-in Excel shortcuts.
Ideally, you will also disable the language bar and any plug-ins, add-ins, or macros that interfere with shortcuts, and any programs that override built-in Excel shortcuts.
The Optimal Excel Settings – IMPORTANT!
You can disable automatic error-checking if you want, but we recommend leaving it on unless you’re a pro; if you go the
“Advanced” tab you can change in the direction the cursor moves in after editing a cell, but we usually leave that one alone.
