The 10 Commandments of Excel
1
Thou shalt not hard code any numbers in a formula
2
Thou shall always reference the source in a formula
3
Thou shalt 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 shalt never claim to be an expert user less they desire to be smitten
10
Thou shalt have no program before Excel
Commonly Used Shortcut Keys
Action Keystroke Action Keystroke
Anchoring cells F4 (in edit cell mode ) Ungroup rows or columns Alt D G (or Shift + Alt + ←)
Auditing toolbar Alt T U Edit Cell Mode / Show dependents F2 (or Alt T U D)
Auto sum Alt + = Highlight entire column Ctrl + Spacebar
Borders - outside area Ctrl + Shift + 7 Highlight entire row Shift + Spacebar
Borders - remove all Ctrl + Underscore (Ctrl + Shift + -) Insert / Delete Ctrl + + / Ctrl +
-Comment - inserting Shift + F2 (or Alt I M) Move a sheet / Copy a sheet Alt E M / Alt E M, Alt + C
Comment - deleting Alt E A M Move between toolbars Alt, Ctrl + Tab
Copy / Cut Ctrl + C / Ctrl + X Move between worksheets Ctrl + Page Up / Ctrl + Page Down
Paste Ctrl + V (or enter) Name a cell Alt I N D (or Ctrl + F3)
Paste Special formats Alt E S T Name a worksheet Alt O H R
Paste Special formulas Alt E S F Page setup Alt F U
Edit cell F2 Print Ctrl + P
Fill right / Fill down Ctrl + R / Ctrl + D Print preview Alt F V
Font - change size Ctrl + Shift + P Redo Ctrl + Y
Font - change font Ctrl + Shift + F Repeat F4
Function - inserting Shift + F3 (or Alt I F) Find Ctrl + F
Go to F5 Replace Ctrl + H
Go to end (contiguous range) Ctrl + Arrow Keys Set print area Alt F T S
Highlight Contiguous range Ctrl + Shift + Arrow Keys Undo Ctrl + Z
Go to precendent cell(s) / Return Ctrl + [ (or F5, Enter) Workbooks - toggle Ctrl + Tab
Group rows or columns Alt D G G (or Shift + Alt + →) Zoom - sizing Alt V Z
Row Height Alt O R E Column Width Alt O C W
Commonly Used Function
Type of Function Common Use Formula
Absolute function Parity check =ABS(number)
And function If statement logic =AND(logical argument 1, logical argument 2…)
Choose function Scenarios =CHOOSE(index number, value 1, value 2….)
Columns function To count N periods (e.g., rate function) =COLUMNS(reference range)
Comp. annual growth rate IRR and growth over a period (1) =RATE(periods, payment, -PV, FV, type)
Count function Data Mining =COUNT(value 1, value 2, value 3…)
Hlookup function Used to reference inputs on an output page =HLOOKUP(lookup, array, row, index, range lookup) If statement Error handling and switches =IF(logical test, value if true, value if false)
Net present value Discounted cash flow analysis (2) =NPV(rate, vlaue 1, value 2, value 3…)
Offset function Scenarios =OFFSET(reference, rows, columns, height, width) Or function If statement logic =OR(logical argument 1, logical argument 2…) Sum-of-years digits depreciation Modeling depreciation =SYD(cost, salvage, life, period)
Text function Footnotes, titles, and subtitles =TEXT(value, "format of text")
Vlookup function Used to reference inputs on an output page =VLOOKUP(lookup, array, column index, range lookup)
(1) Compound annual growth rate formula: (2) Net present value formula:
Excel Workshop 10/20/04 [(FV/PV)^(1/N)] - 1 CF1/(1+r) 1 + CF2/(1+r) 2 + CFN/(1+r) N
Data Types
Examples Data Type
Text Jeff =function() 150
Number 10.0 10.000 10.0000 55.0% 10.0x 10
Currency $10.00 ($10.00) ($10.00) 10.00₮ £10.00 10.00 zł Date 10/18/2004 Monday, October 18, 2004 10/18 18-Oct 18-Oct-04 10/18/04 12:00 AM
Static 10 1 10 2 3 4 5 6 7 8 9 10 2004 2003 2002 2001 2000 1999 Sales 1500 1450 1400 1350 1300 1250 COGS 350 305 260 215 170 125 SGA 750 620 490 360 230 100 Operating Income 400 525 650 775 900 1025 Margins COGS/Sales 23.3% 21.0% 18.6% 15.9% 13.1% 10.0% SGA/Sales 50.0% 42.8% 35.0% 26.7% 17.7% 8.0% Operating Income/Sales 26.7% 36.2% 46.4% 57.4% 69.2% 82.0%
Running Total % of Total 150 150 1.1% 730 880 5.3% 5,143 6,023 37.6% 725 6,748 5.3% 5,412 12,160 39.6% 150 12,310 1.1% 1,354 13,664 9.9% Sum = 13,664 100.0% Average 1,952 Median 730 Mode 150 Count 7
CountIf 2 *Counts the number of times the Mode appears
Max 5,412
Min 150
=RAND() 0.6526502 The number generated will always be between 0 and 1. This can be altered by multiplying/dividing,
adding/subtracting, etc the random number by a constant. 0 1,000 2,000 3,000 4,000 5,000 6,000 1 2 3 4 5 6 7 Series1