Exploring Microsoft Excel 2003/2007

1. Exploring Microsoft Excel 2003/2007 Introduction to Excel Exploring Office 2003 - Grauer and Barber Committed to Shaping the Next Generation of IT Experts.

2. Objectives • Describe potential spreadsheet applications. • Distinguish between a constant, a formula, and a function. • Distinguish between a workbook and a worksheet. • Explain how rows and columns are labeled. Exploring Office 2003 - Grauer and Barber

3. Objectives (continued) • Insert or delete rows and columns. • Print a worksheet to show displayed values or cell contents. • Distinguish between relative, absolute, and mixed references. • Copy and/or move cell formulas. • Format a worksheet. Exploring Office 2003 - Grauer and Barber

4. Introduction to Microsoft Excel • Common user interface with other Office applications • Menus and toolbars are similar to Word and Power Point Exploring Office 2003 - Grauer and Barber

5. Introduction to Spreadsheets • Workbook – contains one or more worksheets • Spreadsheet – a computerized ledger • Rows and Columns • Columns identified with alphabetic headings • Rows identified with numeric headings • Intersection of row and column forms a cell. Exploring Office 2003 - Grauer and Barber

6. An Excel Workbook Menu bar gives lists of commands Formatting toolbar Title bar shows name of workbook Standard toolbar Exploring Office 2003 - Grauer and Barber

7. Rows, Columns, and Cells Cell referenced by column, then number Active cell surrounded by heavy border Column headings above each column. Columns designated with letters Row headings to the left of each row. Rows designated with numbers Exploring Office 2003 - Grauer and Barber

8. Types of Cell Entries • Constant – an entry that does not change • Can be a numeric value or descriptive text • Functions – a predefined computational task • Users cannot change them • =AVERAGE(B3:B7) • =SUM(A1:B2) Exploring Office 2003 - Grauer and Barber

9. Formulas and Constants • Constant is entries that does not change. It may be a number, such as a grade on an exam, or it may be descriptive text (name). • Formula is combination of numeric constants, cell references, arithmetic operators, and/or functions. • Always begins with an equal sign • =(B3+B4+B5/5) • =AVERAGE(B3:B7) Exploring Office 2003 - Grauer and Barber

10. Toolbars • Appear beneath the menu bar • Contain buttons that perform commonly-used commands • Standard toolbar – buttons correspond to most basic commands in Excel • Examples include opening, closing, and saving a workbook • Formatting toolbar – buttons correspond to common formatting operations • Examples include boldface and cell alignment Exploring Office 2003 - Grauer and Barber

11. Formulas and Constants

12. Exploring Office 2003 - Grauer and Barber

13. Exploring Office 2003 - Grauer and Barber

14. Absolute and Related References Exploring Office 2003 - Grauer and Barber

15. Gross Pay = Regular Hours * Hourly Rate + Overtime Hours * Hourly Rate * 1.5 • Withholding Tax = Gross Pay * Withholding Rate Exploring Office 2003 - Grauer and Barber

16. Excel Summery • Spreadsheet is the PC application that is used most frequently by managers and executives. • It is the computerized equivalent of an accountant's ledger. • Enables us to organize data in a readily understandable format. Exploring Office 2003 - Grauer and Barber

17. Definitions • General format – it is the default format for numeric entries and displays a number according to the way it was originally entered. • Number format – displays a number with or without the 1000 separator and with any number of decimal places. Negative numbers can be displayed with parentheses and/ or can be shown in red. • Scientific format – which displays a number as a decimal fraction followed by a whole number exponent of 10; for example the number 12345 would appear as 1.2345E+04. The exponent, +04 in the example, is the number of places the decimal point is moved to the left. Very small numbers have negative exponents.

18. Relative references means – it would change frequently. • Absolute reference means – it would not change frequently, it’s permanent. For example: $C$11 in cell. • Pointing – it’s more accurate to just pointing to the cell that you needed to process it. You can directly click in the cell that will contain the formula and type an equal sign to begin entering the formula, and click in the cell you wants to reference.

19. Chapter 2 Gaining Proficiency • This chapter introduces several new capabilities to increase your proficiency in Excel the following example introduces a variable rate mortgage. • PMT Function • FV Future Value • Goal Seek Command

20. Relative versus Absolute Addresses • The distinction between relative and an absolute reference is: a cell reference changes during a copy operation (relative) versus one that does not (absolute). Consider the PMT function as it appears in cell B6:

21. PMT Calculates loan payment amounts • PMT function requires three arguments (the interest rate per period, the number of periods, and the amount of the loan). • = PMT (0.09/12, 36,-14999) • Interest rate per period (annual rate divided by 12) • Number of periods (3 year × 12 months/year) • Amount of loan (as a negative amount) In class activity 11

22. FV (Future Value • The interest rate is also called the rate of return, the number of periods, and the periodic investment. • Ex: If you plan to contribute $3000 a year to an IRA, expect earning 7% annually, and that you will be contributing for 40 years (begin contributing from25~65). And the amount of money would be $589,905.  • Amount at retirement = FV (Rate of return, Term, Periodic payment) • Computed value becomes $598905 • 7% • 40 years • $3,000 In class activity 12

23. Inserting a function: Insert -> Function • The Insert Function command places a function into a worksheet, you can select a function from a category and insert to a certain cell. • The Goal Seek Command Tools-> Goal Seek Command • This command may enable us to reduce the projected monthly payment at a specified level.

24. Chapter 4 Graphs and Charts

25. Figure 1-1 Public and Non-public Parks and Recreations Websites

27. What is a Chart? • A graphic representation of data in a worksheet • The chart is based on descriptive entries called category labels, and on numeric values called data point. • Deferent Kinds of Charts: • Column Chart • Line Chart • Pie Chart • Bar Chart • Scatter Chart

28. Chapter 6 Creating, Sorting and Querying a worksheet Database • Work sheet as database • Club members, list of students attending college,instructor’s grade book, and a list of company sales representatives. • Database -> Records -> fields (names, age, gender) • Formulas • Functions • Computational Fields • Display results based on other fields in the database • % of Quote and Grade

29. The Print Preview Command View and adjust margins by clicking the Margins button Exploring Office 2003 - Grauer and Barber

30. IF Function • Enables decision making to be implemented within a worksheet. • Three arguments: • Condition is either true or false • Value returned for a true condition • Value returned for a false condition • =IF (condition, value-if-true, value-if-false) Exploring Office 2003 - Grauer and Barber