BIWS-Excel-Shortcuts：Excel常用快捷键(华尔街发布)

Excel 2003 / 2007 Quick Reference – The Most Important Keyboard Shortcuts for Investment Banking & Private Equity http://breakingintowallstreet.com The Fundamentals Ctrl + O Open File Ctrl + N New File Ctrl + P Print Ctrl + S Save File Ctrl + F4 Close File Alt + F4 Close Excel Esc Exit Dialog Ctrl + C Copy Ctrl + X Cut Ctrl + V Paste Ctrl + Z Undo Ctrl + Y Redo Ctrl + A Select All Ctrl + F Find Ctrl + H Replace Alt + Tab Switch Windows Navigation & Data Selection Arrow Keys Move Left, Right, Up, Down Ctrl + Arrows Jump to Boundary Shift + Arrows Select Multiple Cells Shift + Ctrl + Arrows Select to Boundary Shift + F8 Select Multiple Areas Manipulating Cells F2 Edit Cell Del Delete Cell Contents Ctrl + Arrows Skip Word(s) Ctrl + Shift + Arrows Highlight Word(s) Ctrl + F3 Name Cell F5 Jump to Cell Rows & Columns Ctrl + Spacebar Select Column Shift + Spacebar Select Row Alt + I + R Insert Row Alt + I + C Insert Column Alt + E + D Delete Rows/Columns F4 Repeat Last Action Right Mouse Button + E Insert Cut Cells Alt + D + G + G Group Rows / Columns Alt + D + G + U Ungroup Rows / Columns Alt + D + G + S Show Rows / Columns Alt + D + G + H Hide Rows / Columns Workbooks & Worksheets Ctrl + N New Workbook Ctrl + Tab Switch Workbook Shift + F11 New Worksheet Alt + E + L Del Worksheet Ctrl + PgUp Move to Left Worksheet Ctrl + PgDn Move to Right Worksheet Alt + E + M Move / Copy Worksheet Shift + Ctrl + PgUp/PgDn Select Multiple Worksheets Alt + O + H + H Hide Worksheet Alt + O + H + U Show Worksheets Alt + O + H + R Rename Worksheet Alt + O + H + T Color Tab Formatting & Cleaning Data Ctrl + 1 Format Cells Alt + E + S + T Paste Format Alt + E + S + V Paste Values Shift + Ctrl + ~ General Shift + Ctrl + ! Number Shift + Ctrl + @ Time Shift + Ctrl + # Date Shift + Ctrl + $ Currency Shift + Ctrl + % Percentage Shift + Ctrl + ^ Scientific Ctrl + B Bold Ctrl + I Italics Ctrl + U Underline Ctrl + 5 Strikethrough Ctrl + Shift + & Add Outline Ctrl + Shift + - Delete Outline Alt + O + C + A Auto-Fit Col. Alt + O + R + A Auto-Fit Row Alt + O + C + W Column Width Alt + O + D Conditional Formatting Alt + D + E Text to Columns =TRIM Remove Extra Spaces =PROPER Fix Capitalization Alt + D + S Sort Data Display & Printing Alt + W + F + F Freeze Panes (Excel 2007) Alt + W + F Freeze Panes (Excel 2003) Alt + V + Z Zoom Alt + P + S + P Page Setup (Excel 2007) Alt + F + U Page Setup (Excel 2003) Alt + F + W + V Print Preview (Excel 2007) Alt + F + V Print Preview (Excel 2003) Excel 2003 / 2007 Quick Reference – The Most Important Keyboard Shortcuts for Investment Banking & Private Equity http://breakingintowallstreet.com Formula Fundamentals = Enter Formula Shift + F3 Built-In Functions Alt + E + S + F Paste Formulas Alt + E + S + R Paste Formats & Formulas Ctrl + D Copy Down Ctrl + R Copy Right F4 Anchor Cell Alt + E + S + L Paste Link F5, Alt + S + F + X Go to Formulas F5, Alt + S + O + X Go to Constants Ctrl + ~ + = Show Formulas Lookups & Scenarios =CHOOSE Select from List =OFFSET Move # of Rows/Columns from Cell =VLOOKUP Find Based on Column =HLOOKUP Find Based on Row =MATCH Find Item’s Position =INDEX Find Item Pivot Tables Alt + D + P Pivot Table Drop Data Items = Calculation Area Row/Column Fields = Categories Common Built-In Functions =SUM Sum Numbers Alt + = Sum Adjacent Cells =COUNT Count # Entries =AVERAGE Average =MAX Maximum =MIN Minimum =SUMIF Conditional Sum =COUNTIF Conditional Count =SUMPRODUCT Multiply/Sum Range =ABS Absolute Value =IF Conditional =OR At Least 1 Must Be True =AND All Must Be True Validating Data Alt + D + F + A Advanced Data Filter Alt + D + L Validate Data Data Tables Alt + D + T Data Table Row Input Cell = Purchase Price Column Input Cell = EBITDA Multiple Auditing Formulas Alt + T + U + T Trace Precedents Alt + T + U + D Trace Dependents Alt + T + U + A Erase Traces Shift + Ctrl + { All Precedents Shift + Ctrl + } All Dependents Ctrl + { Highlight Precedents Ctrl + } Highlight Dependents F5 + Enter Jump to Original Cell Shift + F2 Add/Edit Comment Alt + E + A + M Del Comment F5, Alt + S + C Highlight Comments Recording Macros Alt + T + M + R Start/Stop Recording Alt + T + M + M View Macros Excel 2003 / 2007 Quick Reference – The Most Important Keyboard Shortcuts for Investment Banking & Private Equity http://breakingintowallstreet.com Custom Number Formats Example Data: Displayed As: Used For: 5 5.0x Valuation Multiples -1200 (1,200.00) Negative Expenses 0 Balanced! Balance Sheet Checks WallSoft Model - WallSoft Titles & Headers 1. The order for Custom Number Formats is: [Positive Style]; [Negative Style]; [Zero Style]; [Text Style] 2. If you include the “@” symbol and text, the text will appear and the “@” will be replaced by what’s in the cell. 3. [Red] and [Blue] can be used for color coding. Custom Keyboard Shortcuts – Excel 2007 In Excel 2007, 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 + 5 would decrease decimal places. Custom Keyboard Shortcuts – Excel 2003 In Excel 2003, you have to right click the toolbar first, go to “Customize,” then right click the button you want to add a shortcut for, change the Name to the “&” symbol and then the shortcut key you want, and then click “Image and Text.” Iterations & Circularity Always make sure Workbook Calculation is set to “Manual” and “Enable Iterative Calculations” is checked with Maximum Iterations set to 100 when working with circular references – they occur most often in LBO models.