1 / 54

Microsoft Excel II

Microsoft Excel II. Bob Herring June 2010. Microsoft Excel II. Special Formats. Select the Home Ribbon and click the ‘Number’ list box down arrow Click ‘More Number Formats …’ and select ‘Special’ Select the desired format . 2. Microsoft Excel II. Custom Formats.

zan
Télécharger la présentation

Microsoft Excel II

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Microsoft Excel II Bob Herring June 2010

  2. Microsoft Excel II Special Formats • Select the Home Ribbon and click the ‘Number’ list box down arrow • Click ‘More Number Formats …’ and select ‘Special’ • Select the desired format 2

  3. Microsoft Excel II Custom Formats • Select the Home Ribbon and click the ‘Number’ list box down arrow • Click ‘More Number Formats …’ and select ‘Custom’ • Type the format in the box 3

  4. Microsoft Excel II Custom Formats, 2 • Formats have 4 parts, separated by semicolons • Parts apply formats to positive numbers, negative numbers, zeros, • and text. • Leaving sections blank between semicolons will suppress entries Format for Text Format for Negative Numbers Try this Example: ;;; #,###;(#,###);0;"Error: Entry must be a number" Format for Positive Numbers Format for Zeros 4

  5. Microsoft Excel II Custom Formats, 3 • Some of the codes: • m Months as 1-12 • mm Months as 01-12 • mmm Months as Jan-Dec • mmmm Months as January-December • d Days as 1-31 • dd Days as 01-31 • ddd Days as Sun-Sat • dddd Days as Sunday-Saturday • yy Years as 00-99 • yyyy Years as 1900-9999 • H Hours as 0-23 • hh Hours as 00-23 5

  6. Microsoft Excel II Custom Formats, 4 • Some more codes: • m Minutes as 0-59 • mm Minutes as 00-59 • s Seconds as 0-59 • ssSeconds as 00-59 • h AM/PM Hours as AM or PM • h:mm AM/PM Hours and minutes as AM or PM • h:mm:ss A/P Hours, minutes, and seconds as AM or PM • Colors (Must be the first item in the section) • [Black] [Blue] [Cyan] [Green] [Magenta] [Red] [White] [Yellow] 6

  7. Microsoft Excel II Custom Formats, 5 • Custom formats can save typing time • FormatTypeResult • “Acct. No.”0000 8967 Acct. No. 8967 • 000-00-0000 1234567890 123-45-7890 • mmmm d, yyyy 5/10/01 May 10, 2001 • h:mm AM/PM 16:48 4:48 PM 7

  8. Microsoft Excel II Custom Formats, 6 • If you want text to follow custom text, type the @ symbol after it 8

  9. Microsoft Excel II Paste Special • Transferring data to other worksheets can cause problems if it is • generated by a formula 9

  10. Microsoft Excel II Paste Special, 2 • Copying the sums from the previous slide causes this result 10

  11. Microsoft Excel II Paste Special, 3 • To paste the sums correctly, after copying choose ‘Paste Special…’ • from the Home Ribbon • Select ‘Values’ and click ‘OK’ to paste only the values 11

  12. Microsoft Excel II Paste Special, 4 • If the cells to be copied have formatting, choose ‘Paste Special…’ again • This time select ‘Formats’ and click ‘OK’ to paste the formats 12

  13. Microsoft Excel II Paste Special, 5 • Columns and rows can be transposed • Copy the data to be transposed, then click where it will be pasted • Click ‘Paste Special …’ and select ‘Transpose’ 13

  14. Microsoft Excel II Absolute and Relative Cell Addresses • Relative Addressing • Cell addresses in formulas refer to the address of the data • that the formula acts upon • When formulas are extended, Excel changes the addresses • so that the formula refers to the correct address • Absolute Addressing • In this case, new formulas continue to refer to the original • data • The dollar sign ( $ ) indicates an absolute address • Example: = 20 * $A$1 locks the formula to cell A1 • $A1 locks the formula to column A • A$1 locks the formula to row 1 14

  15. Microsoft Excel II Absolute and Relative Cell Addresses • Absolute addressing can be used for sales tax tables • Changing one cell changes the whole table • To use relative addressing here would mean changing all cells =A4*0.07 =A4*$C$3 15

  16. Microsoft Excel II Themes • The 2007 version has built-in Themes to format your work • Select the Page Layout Ribbon and click on the ‘Themes’ button 16

  17. Microsoft Excel II Theme Colors • Each theme has its own set of colors. Or, you can mix and match • Select the Page Layout Ribbon and click on the ‘Colors’ button 17

  18. Microsoft Excel II Theme Fonts • Each theme has its own set of fonts. Or, you can mix and match • Select the Page Layout Ribbon and click on the ‘Fonts’ button 18

  19. Microsoft Excel II Cell Styles • The 2007 version has added many new automatic styles for use in • spiffing up your worksheet. Styles are based on Themes (q.v.) • Select the Home Ribbon and click on the ‘Cell Styles’ button 19

  20. Microsoft Excel II Inserting a Function • Select the Formulas Ribbon and click the ‘Insert Function’ button, OR • Click the section of the ‘Function Library’ if you know where it is, OR • Click the on the left side of the Formula Bar • The Function Library contains all the same functions as the original • version of Excel despite its impressive new appearance 20

  21. Microsoft Excel II Inserting a Function • In the ‘Insert Function’ dialog box, select ‘PMT’, OR • If not in the box, select ‘Financial’ in the category list box • Once selected, the dialog box shows the arguments needed • If still not sure, click on ‘Help on this function’ to get instructions Arguments for the PMT Function 21

  22. Microsoft Excel II Mortgage Payment Example • If you know the function name, type ‘= ’ and the name and ‘(’ • After entering ‘=PMT(’ a dialog will appear to guide completion Pressing These Buttons Allows the User to Select a Cell that Contains the Data Function Arguments 22

  23. Microsoft Excel II Financial Functions • Depreciation (Declining Balance) -- DB(cost, salvage, life, period, month) • Depreciation (Straight-Line) -- SLN(cost, salvage, life) • Discount Rate -- DISC(settlement, maturity, pr, redemption, basis) • Future Value -- FV(rate, nper, pmt, pv, type) • Payment (Mortgage or Annuity) -- PMT(rate, nper, pv, fv, type) 23

  24. Microsoft Excel II Math and Trig Functions • Absolute Value -- ABS(number) • Sine -- SIN(number); Cosine -- COS(number); Tangent -- TAN(number) • Natural Logarithm -- LN(number) • Base 10 Logarithm -- LOG10(number) • Pi -- PI( ) • Random Number Between 0 and 1-- RAND( ) • Random Number Between Two Numbers -- RANDBETWEEN(bottom, top) • Square Root -- SQRT(number) 24

  25. Microsoft Excel II Statistical Functions • Average -- AVERAGE(number 1, number 2, …) • Binomial Distribution -- BINOMDIST(number_s, trials, probability_s, cumulative) • Confidence Interval -- CONFIDENCE(alpha, standard_dev, size) • Harmonic Mean -- HARMEAN(number 1, number 2, …) • Lognormal Distribution -- LOGNORMDIST(x, mean, standard_dev) • Median -- MEDIAN(number 1, number 2, …) • Mode -- MODE(number 1, number 2, …) • Poisson Distribution -- POISSON(x, mean, cumulative) 25

  26. Microsoft Excel II Logical Functions • And -- AND(logical 1, logical 2, …) • False -- FALSE( ) • If -- IF(logical_test, value_if_true, value_if_false) • Not -- NOT(logical) • Or -- OR(logical 1, logical 2, …) • True -- TRUE( ) Example: =IF(F15<0, 1, IF(F15>25, 1, F15)) =IF(OR(F15<0, F15>25), 1, F15) 26

  27. p q T T T F F T F F p q T T T F F T F F Microsoft Excel II Logical Functions p AND q To be true, all elements must be true p OR q To be true, at least one element must be true Only “True” Condition Only “False” Condition 27

  28. Microsoft Excel II Logical Functions AND(logical-test-1, logical-test-2, …) =AND(J2=0, K2=“Yes”) Translation: if cell J2 is zero and cell K2 is Yes, then return the value TRUE, and FALSE otherwise OR(logical-test-1, logical-test-2, …) =OR(M2=“Yes”, M2=“NA”) Translation: if cell M2 is Yes or cell M2 is NA, then return the value TRUE, and FALSE otherwise IF(logical-test, what to do if true, what to do if false) =IF(J2=0, “Yes”, “No”) Translation: if cell J2 is zero, type Yes; if not, type No 28

  29. Microsoft Excel II Logical Functions The IF function’s logical test can include other logical functions: IF(OR(M2=“Yes”, M2=“NA”),“Yes”, “No”) The “what to do” sections can also include logical functions; this is called “nesting”. Logical functions can be nested 7 layers deep. IF(J2=0, “Yes”, IF(J2<0, 1, “NA”)) Translation: if cell M2 is Yes or cell M2 is NA, then type Yes; if not, type No Translation: if cell J2 is zero then type Yes; if not, then if cell J2 is less than zero, type 1. If cell J2 is not less than zero, type NA. 29

  30. Microsoft Excel II Logical Functions Excel has several “IS” functions to act as auxiliaries to logical functions. ISBLANK(cell-name) Returns TRUE if cell is blank. ISNUMBER(cell-name) Returns TRUE if cell holds a number. ISTEXT(cell-name) Returns TRUE if cell is contains text. ISNONTEXT(cell-name) Returns TRUE if cell is blank, or has anything but text. ISLOGICAL(cell-name) Returns TRUE if cell generates a logical value. ISODD(cell-name) Returns TRUE if cell holds an odd number. ISEVEN(cell-name) Returns TRUE if cell holds an even number. 30

  31. Microsoft Excel II Linking Worksheets, Part 1 • Open “Forecast Example.xls” on the floppy disk • Click in cell D3 to view the formula: • “C3 + Replenishment!C4 - Expenditure!C4” Formula Cell D3 “On-Hand” Worksheet 31

  32. Microsoft Excel II Linking Worksheets, Part 2 • To recreate the link: • Select cells D3 through O26, as shown • On the Home Ribbon, select ‘Clear’, then ‘Clear Contents’ 32

  33. Microsoft Excel II Linking Worksheets, Part 3 • Select cell D3, then click “= ” in the formula bar • Type “C3 + ” • Select the Replenishment worksheet, and click in cell C4 Formula Cell C4 33

  34. Microsoft Excel II Linking Worksheets, Part 4 • Type “ - ” • Select the Expenditure worksheet and click in cell C4 • Click “OK” Formula Cell C4 34

  35. Microsoft Excel II Linking Worksheets, Part 5 • Use the fill handle to extend the formula downward to row 26 Fill Handle 35

  36. Microsoft Excel II Linking Worksheets, Part 6 • Use the fill handle again to extend the formula to the whole worksheet • Supplies on-hand can now be predicted by varying expenditure and • replenishment rates Fill Handle 36

  37. Microsoft Excel II Conditional Formatting • Excel can be set to “watch” for certain values in your spreadsheet • It responds to the values by changing the cells to a format you specify • Select the Home Ribbon and click the ‘Conditional Formatting’ button 37

  38. Microsoft Excel II Conditional Formatting, 2 • “Rules” can be set immediately by highlighting a cell or range, then • clicking ‘Conditional Formatting’, then ‘Highlight Cells Rules’, and • selecting one of the choices from the pop-out menu • Enter the rule in the popup dialog box and select desired formatting 38

  39. Microsoft Excel II Conditional Formatting, 3 • Access the main rules control by clicking ‘Conditional Formatting’, • and then clicking ‘Manage Rules’ • Click the ‘New Rule’ button to create a rule 39

  40. Microsoft Excel II Conditional Formatting, 4 • Inside the dialog that appears, select ‘Format only cells that contain’ • Select ‘Cell Value’, a mathematical operator, and the values • Click the ‘Format’ button to select the format to be used To use a fill: 40

  41. Microsoft Excel II Conditional Formatting, 5 • Continue to add rules as desired • The 2007 version allows more rules; previous versions are limited to 3 41

  42. Microsoft Excel II Conditional Formatting, 6 • Rules can be based on formulas, so that formats can depend on the • value of other cells or a comparison of values 42

  43. Microsoft Excel II Conditional Formatting, 7 • New in the 2007 version is ‘Data Bars’ formatting • The bars give a visual impression of the data values, and come in • various colors 43

  44. Microsoft Excel II Conditional Formatting, 8 • New in the 2007 version is ‘Color Scales’ formatting • The different colors and their intensities give a visual impression of the • data values, and come in various color combinations 44

  45. Microsoft Excel II Conditional Formatting, 9 • New in the 2007 version is ‘Icon Sets’ formatting • The icons are meant to convey a visual impression of the data values, • and come in various color and shape combinations 45

  46. Microsoft Excel II Conditional Formatting, 10 • New in the 2007 version is ‘Top/Bottom Rules’ formatting • The formatting highlights top or bottom results or averages 46

  47. Microsoft Excel II Filtering • Excel can also sort data by matching values • For fast filtering, use the AutoFilter • Select the Home Ribbon and click the ‘Sort & Filter’ button • Click on one of the column headings • Click ‘Filter’ 47

  48. Microsoft Excel II AutoFilter • After ‘Filter’ is selected, all columns appear with selector arrows • Click on the ‘Location’ down arrow • Uncheck ‘(Select All)’ and then click the ‘X’ box and ‘OK’ 48

  49. Microsoft Excel II AutoFilter, Continued • Excel suppresses all rows not containing the selected criterion • In the example, only office supplies at location X are displayed • Notice the row numbers are no longer sequential 49

  50. Microsoft Excel II Chart Types, 1 • Bar and Column Charts • Show variation over a • period of time or draw • comparisons between items • Area Charts • Show the relative importance • of values over time 50

More Related