The 10 Commandments of Excel - New York University
[Pages:6]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
Excel Workshop 10/20/04
Commonly Used Shortcut Keys
Action
Anchoring cells Auditing toolbar Auto sum Borders - outside area Borders - remove all Comment - inserting Comment - deleting Copy / Cut Paste Paste Special formats Paste Special formulas Edit cell Fill right / Fill down Font - change size Font - change font Function - inserting Go to Go to end (contiguous range) Highlight Contiguous range Go to precendent cell(s) / Return Group rows or columns Row Height
Keystroke
F4 (in edit cell mode ) Alt T U Alt + = Ctrl + Shift + 7 Ctrl + Underscore (Ctrl + Shift + -) Shift + F2 (or Alt I M) Alt E A M Ctrl + C / Ctrl + X Ctrl + V (or enter) Alt E S T Alt E S F F2 Ctrl + R / Ctrl + D Ctrl + Shift + P Ctrl + Shift + F Shift + F3 (or Alt I F) F5 Ctrl + Arrow Keys Ctrl + Shift + Arrow Keys Ctrl + [ (or F5, Enter) Alt D G G (or Shift + Alt + ) Alt O R E
Action
Keystroke
Ungroup rows or columns Edit Cell Mode / Show dependents Highlight entire column Highlight entire row Insert / Delete 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 Undo Workbooks - toggle Zoom - sizing Column Width
Alt D G (or Shift + Alt + ) F2 (or Alt T U D) Ctrl + Spacebar Shift + Spacebar Ctrl + + / Ctrl + 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 Ctrl + Z Ctrl + Tab Alt V Z Alt O C W
Excel Workshop 10/20/04
Commonly Used Function
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 10/20/04
Data Types
Data Type Text Number Currency Date
Examples
Jeff 10.0 $10.00 10/18/2004
=function() 10.000 ($10.00)
Monday, October 18, 2004
150 10.0000 ($10.00)
10/18
55.0%
10.0x
10.00
?10.00
18-Oct 18-Oct-04
10 10.00 zl 10/18/04 12:00 AM
Static
10
1
10
2
3
4
5
6
7
8
9
10
Sales COGS SGA Operating Income
Margins COGS/Sales SGA/Sales Operating Income/Sales
2004 1500 350 750 400
23.3% 50.0% 26.7%
2003
1450 305 620
525
2002
1400 260 490
650
2001
1350 215 360
775
2000
1300 170 230
900
21.0% 42.8%
36.2%
18.6% 35.0%
46.4%
15.9% 26.7%
57.4%
13.1% 17.7%
69.2%
1999
1250 125 100
1025
10.0% 8.0%
82.0%
Sum =
Average Median Mode Count CountIf Max Min
=RAND()
150 730 5,143 725 5,412 150 1,354 13,664
Running Total 150 880
6,023 6,748 12,160 12,310 13,664
% of Total 1.1% 5.3%
37.6% 5.3%
39.6% 1.1% 9.9%
100.0%
6,000 5,000 4,000 3,000 2,000 1,000
1,952 730
0
1
2
3
4
5
6
7
150
7
2 *Counts the number of times the Mode appears
5,412
150
Series1
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.
1) Create a set of 100 randomly chosen numbers between 1 and 1,000 2) Define which random numbers are associated with the different coin selections
(penny, nickle, dime, quarter) 3) Create a set of 100 coins based on the previous instructions 4) Determine how many of each coin were selected 5) Determine the total value of pennies, nickles, dimes, and quarters 6) Determine the total value of the most frequently selected coin 7) Determine the total value of the least selected coin 8) Make data look presentable 9) Create a bar chart to show the number of each type of coin selected
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- the 10 commandments of excel new york university
- excel keyboard shortcuts and function keys
- microsoft excel tips tricks queen s university
- advanced excel keyboard shortcuts excel keystrokes
- all 253 shortcuts for excel 2016
- important excel shortcuts macabacus
- microsoft excel 2007 basics for windows
- popular keyboard shortcuts for excel the company rocks
- excel 2013 shortcuts
- alt o c a fit column width f2 edit cells navigation
Related searches
- new york university ranking
- new york university transfer
- new york university common app
- new york university medical school
- new york university us news ranking
- new york university graduate school
- 10 commandments of satanism
- 10 commandments of satanic bible
- new york university transcript
- new york university acceptance rate 2019
- new york university undergraduate admissions
- new york university transcripts request