1 / 29

Day 4: MICROSOFT EXCEL – CHAPTER 1 Contd. MICROSOFT EXCEL – CHAPTER 2

Day 4: MICROSOFT EXCEL – CHAPTER 1 Contd. MICROSOFT EXCEL – CHAPTER 2. Akhila Kondai akhila.kondai@mail.wvu.edu August 28, 2013. Today . Managing Rows and Columns Ranges and their selection Formatting Page setup and Layout Semi-selection to create a formula

bill
Download Presentation

Day 4: MICROSOFT EXCEL – CHAPTER 1 Contd. MICROSOFT EXCEL – CHAPTER 2

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. Day 4:MICROSOFT EXCEL – CHAPTER 1 Contd.MICROSOFT EXCEL – CHAPTER 2 AkhilaKondai akhila.kondai@mail.wvu.edu August 28, 2013

  2. Today • Managing Rows and Columns • Ranges and their selection • Formatting • Page setup and Layout • Semi-selection to create a formula • Relative, absolute and mixed cell references • Functions • Statistical Functions • Date Functions • Logical Functions • VLOOKUP/HLOOKUP functions • Financial functions: PMT and FV

  3. Managing Columns and Rows • Inserting Rows and Columns • Deleting Rows and Columns • Adjusting Column Width • Adjusting Row Height • Hiding Columns and Rows • Moving Rows and Columns

  4. Ranges and their selection • A range is a rectangular group of cells in a worksheet • Can be one cell; may be entire worksheet • Ranges can be contiguous(together) or noncontiguous (not together) • A contiguous range is single rectangular block of cells Example A4:I4 covers all cells between A4 and I4 • For selecting the range for Contiguous cells • Click and hold left mouse button and drag from beginning of range to end • Select first cell, then hold the Shift key while clicking the last cell • A non-contiguous range consists of two or more separate non-adjacent ranges • For selecting the range for Non-Contiguous cells • Select the first range of cells then holding on to the CTRL key select the second Range of cells.

  5. Moving/Copying a Range • Moving/copying a range preserves text and values, but cell addresses in formulas will be altered in the pasted location • Move a range by cutting it and pasting to the upper left corner of the destination • Copy a range can by copying it and pasting to the upper left corner of the destination

  6. Formatting • Number: Control how numbers are displayed. Apply number formats and decimal places • Merge and Center: Merges cells together and displays the text in center • Wrap Text : Makes content visible in a single cell • Alignment: Control how text behaves inside of cell • Font: Control the color, size, look of text in cell • Border: Create lines around and between cells • Fill: Control the background color of a cell

  7. Formatting cells • Sometimes you might need to format the appearance of a cell. It accentuates and draws attention to meaningful portions of a worksheet • There are 12 different formatting options: General, Number, Currency, Accounting, Date, Time, Percentage, Fraction, Scientific, Text, Special, Custom.

  8. Paste Special • The Paste Special command is used to paste data from the clipboard using a different format

  9. Page setup and Printing • The Page Setup Dialog Box Launcher contains many common print-related options

  10. Page setup and layout - example • At some point you have to print your worksheets • Click on Page Layout and the icon for Page Setup • Now you can specify options for whether the page will be in Portrait or Landscape style • Whether you want to fit the contents to a single page • You can also specify margins • You can also set up headers and footers • You can also print gridlines, row and column headings, etc under the Sheet tab

  11. Semi-selection(Pointing) • Semi-selection uses the mouse pointer to build a formula containing cell references or ranges • Click the cell where you want to create the formula. Type ‘=‘ sign . Drag the cell range that contains the value. Type the operators. Continue selecting the cell ranges . • Press enter to complete the formula.

  12. Cell References • Used in formulas so that they can be copied and reused easily. • In some cases, we want the references to cells to move down, or over as we copy the formula. • In other cases, we do not want the reference to change even if we do move.

  13. Relative vs Absolute Addressing • Relative cell references indicate a cell’s relative location from the cell containing the formula; the cell reference changes when the formula is copied • Absolute cell references indicates a cell’s specific location; the cell reference does not change when you copy the formula - Indicated by dollar ($) signs in front of the column letter and row number - Most often used when the value need not change • Mixed cell references contains both an absolute and relative cell references in a formula • Use the F4 key to toggle between relative and absolute cell referencing

  14. Cell References(Contd) Relative reference A1 Column absolute mixed reference $A1 Row absolute mixed reference A$1 Absolute reference $A$1

  15. Avoiding Circular References • A circular reference erroroccurs if a formula refers to itself

  16. Function • Function is a predefined formula that performs a calculation. • Syntax is a set of rules that govern the structure and components for properly entering a function. • An Argument is an input, such as cell reference or value, needed to complete a function. • A function begins with the equal sign (=) followed by the function name and arguments in parentheses Example: =SUM(A1:A3)

  17. Inserting a Function • When a function is typed, Formula AutoComplete displays a list of functions matching the partial entry • Use the Insert Function dialog box to search for a function or select one from a list.

  18. Function Categories and Description

  19. SUM • SUM is the most commonly used function • represented by a sigma () • Adds values within a specified range • Syntax refers to the grammatical structure of a formula • Must adhere to stated structure of formula • Arguments are values ─ used as input and returned as output • Example: = Sum (B1:B10) Function Name Arguments

  20. AutoSum • Automates the SUM function • Click the cell where you want the result • Click AutoSum button • Select the range of cells you want to sum • Press Enter to complete

  21. Basic Statistical Functions • Perform a variety of calculations to aid in decision making process • AVERAGE calculates the average of a range of numbers • MEDIAN finds the midpoint value in a range • MIN calculates the minimum value in a range • MAX calculates the maximum value in a range • COUNT counts the number of values within a range

  22. COUNT • Statistical Functions – COUNT, COUNTA • COUNT function counts the number of cells in a range that contain numerical data. • COUNTA function counts the number of cells in a range that are not blank. • COUNTBLANK counts the number of blanks in the given range. • Ex: COUNT(C6:C24) COUNTA(E2:E10) COUNTBLANK(J9:J16)

  23. More Math and Statistical Functions

  24. Date/Time Functions

  25. Logical Functions • IF function • Logical Operators

  26. IF Function • Used to determine whether a given condition has been satisfied or not • When the condition is met, the formula performs one task; when it is not met, the formula performs another task • Has three arguments: • a condition tested to determine if it is true or false • the resulting value if the condition is true • the resulting value if the condition is false

  27. IF (contd.) • Syntax: IF(condition, value_if_true, value_if_false) Value when condition is false Value when condition is true Condition is true or false • IF(H4=100, “Good”, “Bad”)

  28. Reminder • Lesson A in MyITLab is due on 09/03/13 by 11:59 P.M. • No class on September 2nd, 2013 Monday (Labor day holiday)

  29. Questions ?

More Related