Excel – Part Two IS 240 Asela Thomason

1. Excel – Part Two IS 240 Asela Thomason

2. Competencies • Entering formulas using Point mode. • Copy formulas. • Enter functions. • Use the Paste function feature. Use absolute references. • Add cell comments. • Adjust column widths. • Use Undo.

3. Competencies • Zoom the worksheet. • Format numbers. • Apply styles. • Insert and delete rows and columns. Move and clear cell contents. • Change fonts and font styles. • Add predefined headers and footers. • Change page orientation.

4. Entering Formulas Using Point Mode • A way of generating formulas using the cell pointer • Start formula with = sign • Using mouse, click on first cell in formula, note that the cell address • Enter numeric operator • Click on second cell, that cell address shows in formula • Press Enter

5. formula begins with = sign

6. point to B5 with cell selector

7. key in numeric operator

8. point to B6 with cell selector

9. formula now displays results

10. Copying Formulas Formulas, like other cell contents, may be copied from one location to another The cell addresses in the source formula, unless specially set up, change as they are copied to the destination cell(s) The cells change because they are relative cell references

11. SS of copying formulas formula copied

12. formula pasted

13. Functions Built-in formulas that perform certain types of calculations automatically Rules of structure, or syntax: Function name (argument1, argument2 …)

14. Functions Arguments - data the function uses to perform the calculation Most often, arguments are numbers or cell references to numbers Argument enclosed in parentheses, multiple arguments separated by commas

15. Functions In cells containing both function and formula, begin the function with an = sign Excel has 233 functions, divided into 9 categories

16. AVERAGE Returns the average of its arguments Sample of Functions

17. AVERAGE UPPER Returns the average of its arguments Converts text to uppercase Sample of Functions

18. AVERAGE UPPER IF Returns the average of its arguments Converts text to uppercase Returns one value if a condition you specify evaluates to True and another value if it evaluates to False Sample of Functions

19. PMT Calculates payment for a loan based on constant payments and interest rate Sample of Functions

20. PMT TODAY Calculates payment for a loan based on constant payments and interest rate Returns serial number that represents today’s date Sample of Functions

21. PMT TODAY SUM Calculates payment for a loan based on constant payments and interest rate Returns serial number that represents today’s date Adds all the numbers in a range of cells Sample of Functions

22. Relative Cell References Cells or range references that Excel interprets in relation to the position of the cell that contains the formula If formula is located in C7, then the cell address C5 is two above the formula

23. Relative Cell References When the formula is copied one cell to the right, to D7, the former cell address C5, is now D5, still two above the formula This ability to retain the same relationship among formulas being copied allows users to usually copy formulas without special preparation

24. Paste Function feature

25. Absolute Cell References Cells or range references in a formula whose location does not change when the formula is copied Created when the relative adjustment of cell references is overridden

26. Absolute Cell References Accomplished by entering a $ sign before the column letter and row number of the cell reference Mixed references are cells where only the column letter or row number are made absolute

27. Examples of Reference Types

28. Copying formulas with relative references

29. Office Assistant Copying formulas with relative references

30. Office Assistant Copying formulas with relative references

31. Office Assistant Copying formulas with absolute references

32. Office Assistant Copying formulas with absolute references

33. Office Assistant Copying formulas with absolute references

34. Adding Cell Comments • Can annotate cells with comments • Appear whenever mouse pointer passes over that cell

35. Cell comments

36. Column Widths • Cell’s column controls how much information can be displayed in a cell • Text entries will “spill over” to the next cell, if empty, otherwise the label is truncated • Numbers too wide for the column will be displayed as ********** • Column widths may be from 1 to 255

37. Adjusting Column Widths • Click on Format, Column, Width • Click and drag on the column heading border • Format, Column, Autofit or double clicking on heading border will make automatic column width adjustments

38. Using Undo • For undoing errors • Important safeguard against time consuming errors

39. Zooming the Worksheet • Screen is defaulted at 100%, the amount that will print on one page, in Portrait orientation • Can adjust screen viewing to • See more, by zooming out, or • See less, by zooming in

40. Formatting Numbers • Number formats affect how numbers look onscreen and when printed • No effect on Excel’s storage or values in calculations • Select with Format, Cells, Number

41. Number formatting

42. Number formatting

43. Applying Styles • Styles are combinations of formats that have been selected and named • Can be quickly applied to a selection • Normal is the default style

44. Format, styles

45. Inserting Rows • Move to appropriate row • Click on Insert, Rows • For multi row insertion, highlight a range of rows before invoking menu command

46. Moving Cell Contents • Can use the Cut and Paste method or • Move cursor arrow to border of cell pointer, click and drag to the destination location

47. Centering Across a Selection • Cell alignment, center only applies to entries within an individual cell • Centering across a selection allows users to center selection as they would with a word processor • Click on Format, Cells, Alignment, Horizontal, Center Across Selection

48. Centering across a selection

49. Centering across a selection

50. Changing Fonts and Font Styles • Fonts - typefaces, size and style • Typeface - appearance and character shape • Size - generally measured in points (pts.), pts. are 1/72 of an inch • Arial 10 pt. is the Excel worksheet default • Change by Format, Cells, Font