114 Views

Download Presentation
## Excel – Part Two IS 240 Asela Thomason

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -

**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. • 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.**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**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**SS of copying formulas**formula copied**Functions**Built-in formulas that perform certain types of calculations automatically Rules of structure, or syntax: Function name (argument1, argument2 …)**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**Functions**In cells containing both function and formula, begin the function with an = sign Excel has 233 functions, divided into 9 categories**AVERAGE**Returns the average of its arguments Sample of Functions**AVERAGE**UPPER Returns the average of its arguments Converts text to uppercase Sample of Functions**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**PMT**Calculates payment for a loan based on constant payments and interest rate Sample of Functions**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**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**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**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**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**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**Office Assistant**Copying formulas with relative references**Office Assistant**Copying formulas with relative references**Office Assistant**Copying formulas with absolute references**Office Assistant**Copying formulas with absolute references**Office Assistant**Copying formulas with absolute references**Adding Cell Comments**• Can annotate cells with comments • Appear whenever mouse pointer passes over that cell**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**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**Using Undo**• For undoing errors • Important safeguard against time consuming errors**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**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**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**Inserting Rows**• Move to appropriate row • Click on Insert, Rows • For multi row insertion, highlight a range of rows before invoking menu command**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**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**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