The Ten Commandments of Excel - NYU

The Ten Commandments of Excel

1 Thou shall not hard code any numbers in a formula 2 Thou shall always reference the source in a formula 3 Thou shall never hide columns/rows, rather group them 4 Thou shall use the mouse as little as possible 5 Thou shall make all worksheets presentable and easy to understand 6 Thou shall be consistent in formatting and coloring 7 Thou shall use relative and absolute references appropriately 8 Thou shall utilize the Help function for it is the Lord and Savior 9 Thou shall never claim to be an expert user less they desire to be smitten 10 Thou shall have no program before Excel

Excel Workshop Page 1

Commonly Used Shortcut Keys

Action

Anchoring cells Auditing toolbar

KeyStroke

F4 (in edit cell mode ) Alt T U

Auto sum Borders - outside area Borders - remove all

Comment - inserting Comment - deleting Copy / Cut

Alt + = Ctrl + Shift + 7 Ctrl + Underscore (Ctrl + Shift + -) Shift + F2 (or Alt I M) Alt E A M Ctrl + C / Ctrl + X

Paste

Ctrl + V (or enter)

Paste Special formats

Alt E S T

Paste Special formulas

Alt E S F

Edit cell

F2

Fill right / Fill down

Ctrl + R / Ctrl + D

Font - change size

Ctrl + Shift + P

Font - change font

Ctrl + Shift + F

Function - inserting

Shift + F3 (or Alt I F)

Go to

F5

Go to end (contiguous

Ctrl + Arrow Keys

range)

Highlight Contiguous range Ctrl + Shift + Arrow Keys

Go to precendent cell(s) / Ctrl + [ (or F5, Enter)

Return

Group rows or columns

Alt D G G (or Shift + Alt + )

Row Height

Alt O R E

Action

Ungroup rows or columns Edit Cell Mode / Show dependents Highlight entire column Highlight entire row Insert / Delete

KeyStroke Alt D G (or Shift + Alt + ) F2 (or Alt T U D)

Ctrl + Spacebar Shift + Spacebar Ctrl + + / Ctrl + -

Move a sheet / Copy a sheet Move between toolbars Move between worksheets

Name a cell Name a worksheet Page setup Print Print preview Redo Repeat Find Replace Set print area

Alt E M / Alt E M, Alt + C Alt, Ctrl + Tab Ctrl + Page Up / Ctrl + Page Down Alt I N D (or Ctrl + F3) Alt O H R Alt F U Ctrl + P Alt F V Ctrl + Y F4 Ctrl + F Ctrl + H Alt F T S

Undo Workbooks - toggle

Ctrl + Z Ctrl + Tab

Zoom - sizing Column Width

Alt V Z Alt O C W

Excel Workshop Page 2

Commonly Used Functions

Type of Function Absolute function And function Choose function Columns function Comp. annual growth rate Count function Hlookup function If statement Net present value Offset function Or function Sum-of-years digits depreciation Text function Vlookup function

Common Use Parity check If statement logic Scenarios To count N periods (e.g., rate function) IRR and growth over a period (1) Data Mining Used to reference inputs on an output page Error handling and switches Discounted cash flow analysis (2) Scenarios If statement logic Modeling depreciation Footnotes, titles, and subtitles Used to reference inputs on an output page

Formula =ABS(number) =AND(logical argument 1, logical argument 2...) =CHOOSE(index number, value 1, value 2....) =COLUMNS(reference range) =RATE(periods, payment, -PV, FV, type) =COUNT(value 1, value 2, value 3...) =HLOOKUP(lookup, array, row, index, range lookup) =IF(logical test, value if true, value if false) =NPV(rate, vlaue 1, value 2, value 3...) =OFFSET(reference, rows, columns, height, width) =OR(logical argument 1, logical argument 2...) =SYD(cost, salvage, life, period) =TEXT(value, "format of text") =VLOOKUP(lookup, array, column index, range lookup)

(1) Compound annual growth rate formula: [(FV/PV)^(1/N)] - 1 (2) Net present value formula: CF1/(1+r)1 + CF2/(1+r)2 + CFN/(1+r)N

Excel Workshop Page 3

Basic Excel Uses

? What is a spreadsheet? What are the advantages of using a spreadsheet? o A spreadsheet is a ledger composed of rows and columns where information can be easily stored, viewed and modified with extreme efficiency. o An advantage is that it easy to experiment with numbers without having to redo all of the calculations.

? HELP! = your new best friend! Hit F1 ? UNDO = CTRL + Z ? ALT brings you to the menu bar.

o Hit the corresponding letter that is underlined

? Rows, Columns & Cells o Selecting rows & columns: Use mouse to manually select Select a row: Shift + Spacebar Select a column: Ctrl + Spacebar Select cells: Ctrl Shift + arrow keys Select all = Ctrl + A

? Types of data: Format Cells or Ctrl + 1

o Text: Add a ` before typing

o Number o Currency o Date

? Inserting/Deleting Rows & Columns o Right click insert/delete o Menu bar Insert Row/Column o Rows: Alt+I+ R; Columns : Alt+ I+ C

Excel Workshop Page 4

o Delete: Ctrl -

? Entering Data o Click on the cell o Type in the formula bar o Hit F2 when in the cell you want to edit

? Manipulating Data - Fill down, Copy, Paste, Paste special o Copy = Ctrl + C o Paste = Ctrl + V o Paste Special = after copying, right click paste special or ALT + E + S and the letter corresponding to what you want to do o Fill down : select the cell that has the original formula , hold the shift key down and click on the last cell (in the series that needs the formula), Menu Bar Edit Fill Down or Ctrl+ D

? Formatting Cells and Spreadsheets o Format Format cells or Ctrl+ 1 o Hover your mouse between a column/row until the pointer turns into an arrow that points in two opposing directions, double click to make the column Auto fit o Format Conditional Formatting

? Auto-filter, Sort data: Another advantage of excel is being able to sift through large amounts of data very quickly. o Menu bar Data Sort or Alt + D + S o To Auto filter, select the row that you want filtered then : Menu bar Data Filter Auto filter or Alt + D+F+ F

? Basic operations - Referencing cells (see above figure) o The column comes first then the row number

Excel Workshop Page 5

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download