1 / 59

Excel – Part Two IS 240 Asela Thomason

Excel – Part Two IS 240 Asela Thomason. 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. Competencies. Zoom the worksheet.

levia
Download Presentation

Excel – Part Two IS 240 Asela Thomason

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. 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

More Related