Wall Street Prep: Excel Crash Course

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