590 likes | 728 Views
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.
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