Transcript Microsoft Excel II
Microsoft Excel II
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
• 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
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 Format for Format for Negative Numbers Text entries Try this Example:
;;;
#,###;(#,###);0;"Error: Entry must be a number" Format for Positive Numbers Format for Zeros
4
Microsoft Excel II
Custom Formats, 3
• Some of the codes: • m Months as 1-12 • mm • mmm • mmmm Months as January-December • d Days as 1-31 • dd Months as 01-12 Months as Jan-Dec Days as 01-31 • ddd • dddd • yy • yyyy • H • hh Days as Sun-Sat Days as Sunday-Saturday Years as 00-99 Years as 1900-9999 Hours as 0-23 Hours as 00-23
5
Microsoft Excel II
Custom Formats, 4
• Some more codes: • m Minutes as 0-59 • mm Minutes as 00-59 • s Seconds as 0-59 • ss Seconds 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
Microsoft Excel II
Custom Formats, 5
• Custom formats can save typing time Format • “Acct. No.”0000 • 000-00-0000 • mmmm d, yyyy • h:mm AM/PM Type 8967 1234567890 5/10/01 16:48 Result Acct. No. 8967 123-45-7890 May 10, 2001 4:48 PM
7
Microsoft Excel II
Custom Formats, 6
• If you want text to follow custom text, type the @ symbol after it
8
Microsoft Excel II
Paste Special
• Transferring data to other worksheets can cause problems if it is generated by a formula
9
Microsoft Excel II
Paste Special, 2
• Copying the sums from the previous slide causes this result
10
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
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
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
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 • $A 1 locks the formula to column A • A $1 locks the formula to row 1
14
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
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
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
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
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
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
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
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
Function Arguments Pressing These Buttons Allows the User to Select a Cell that Contains the Data 22
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
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
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
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
Microsoft Excel II
Logical Functions
p AND q To be true, all elements must be true T p q T T F F F T F Only “True” Condition Only “False” Condition p OR q To be true, at least one element must be true T T F F p q T F T F
27
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
Microsoft Excel II
Logical Functions
The IF function’s logical test can include other logical functions: IF( OR(M2=“Yes”, M2=“NA”) , “Yes”, “No”) Translation: if cell M2 is Yes or cell M2 is NA, then type Yes; if not, type 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 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
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
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
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
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
Microsoft Excel II
Linking Worksheets, Part 4
• Type “ - ” • Select the Expenditure worksheet and click in cell C4 • Click “OK”
Formula Cell C4 34
Microsoft Excel II
Linking Worksheets, Part 5
• Use the fill handle to extend the formula downward to row 26
Fill Handle 35
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
My Chart
140 120 100
Value
80 60 40 20 0 180 160 140 120 100 80 60 40 20 0 Jan NORTH Feb EAST
Region
SOUTH Mar Apr WEST May Jun Paper Pens Pencils Jan Feb Mar
50
Microsoft Excel II
Chart Types, 2 Line Charts
• Show trends or changes in data over a period in time 80 20 10 0 70 40 30 60 50 Jan Feb Mar Jan Apr May Jun Pencils Pens Paper
Pie Charts
• Show the relationship or proportions of parts to a whole 50 15 20 Pencils Pens Paper
51
Microsoft Excel II
Chart Types, 3 High-Low-Close Charts
• Show a common presentation of stock prices
XY (Scatter) Charts
• Show the relationship or degree of relationship between numeric values of two data series 40 20 0 0 80 60 120 100 92 90 88 86 98 96 94 106 104 102 100 12-Mar 13-Mar 14-Mar 15-Mar 16-Mar 17-Mar High Low Close 1 2 3 4 5 6 7 Pencils Paper
52
Microsoft Excel II
Chart Types, 4 Surface Charts
• Find optimum combinations between two sets of data
Radar Charts
• Show changes or frequencies of data relative to a center point and to each other Vitamin D 100 90 80 70 60 50 40 30 20 10 0 100o F 200o F 300o F 400o F 1 min 2 min 4 min 3 min Vitamin A 600 500 400 300 200 100 0 Vitamin B 90-100 80-90 70-80 60-70 50-60 40-50 30-40 20-30 10-20 0-10 Trix Kix Life Vitamin C
53
Microsoft Excel II
Review
• Custom Formats • Paste Special • Absolute and Relative Addressing • Themes and Cell Styles • Functions • Linking worksheets • Conditional formatting • Filtering • Custom Charts
54