CS 202, ICS 202…..Class 7

1. CS 202, ICS 202…..Class 7 By A.Penjira Kanthawongs A.Penjira Kanthawongs

2. Class 7 - Agenda • Lecture on “Excel Project 1.” • G. HW: Find and input inf. to createan Excel document with a Chart for your company (Look at requirements like Page E 1.60 and from the lecture) turn in next class. • Ind. HW: Do 20 MC and 10 SA questions for Excel P. 1 and turn in next class and read Excel Project 2 in advance. • Announce Midterm scores. A.Penjira Kanthawongs

3. Microsoft Excel is… • A spreadsheet program that allows you to … • Organize data • Complete calculations • Make decisions • Graph data • Develop professional looking reports A.Penjira Kanthawongs

4. Publish organized data to the Web • Access real-time data from Web sites A.Penjira Kanthawongs

5. 4 Vacation Packages Bahamas Repose Daytona Delight Key West Haven South Padre Del Sol 4 Sales Channels - Mail - Campus - Telephone - Web No. of Qtr in 1 year 0 1st/ 2nd/ 3rd/ 4th 3 months/ 6 months/ 9 months/ 12 months Jan. Mar. Jun. Sept. Dec. A.Penjira Kanthawongs

6. Source of the data: From Eric Jacobs, Chief financial officer (CFO) of Fun – N – Sun Sojourn. Calculation: Total sales for each vacation package Total company sales Total sales for each sales channel A.Penjira Kanthawongs

7. Chart requirements: Below the worksheet, construct a 3-D Column chart that compares the amount of sales to the 4 sales channels for each vacation package. A.Penjira Kanthawongs

8. 4 major parts of Excel are • Worksheets -> allow you to enter, calculate, manipulate, and analyze data such as numbers and text. • Charts -> pictorially represent data. • Databases -> manage data. A.Penjira Kanthawongs

9. Web support -> You can access real-time data using Web queries. From the orange box at the bottom of page E 1.7 • Worksheet Development …The key to developing a useful worksheet is careful planning. A.Penjira Kanthawongs

10. Worksheet Development • Define the problem, including need, source of data, calculations, and charting and Web requirements • Design the worksheet • Enter the data and formulas • Test the worksheet A.Penjira Kanthawongs

11. Workbook -> notebook Worksheets -> inside the workbook Sheet Tab -> at the bottom of the workbook Worksheets can be added -> 255 at a maximum Vocabularies in this chapter… A.Penjira Kanthawongs

12. Figure 1-6 E 1.10 Workbook Rectangular grid containing columns (vertical) ..and rows (horizontal) A.Penjira Kanthawongs

13. A Workbook has 256 Columns - A, B, C, …to..IV ..and 65,536 rows - 1, 2, 3, … to 65,536 .. Cell reference “C5” A.Penjira Kanthawongs

14. Block Arrow Block plus sign A.Penjira Kanthawongs

15. Standard Toolbar for Excel 97 AutoSum Paste Function E-mail Standard Toolbar for Excel 2000 A.Penjira Kanthawongs

16. Formula bar AutoCalculate Area Mode indicator Status bar A.Penjira Kanthawongs

17. Mouse pointer becomes an I-beam A.Penjira Kanthawongs

18. How AutoCorrect work in Page E 1.18? In Excel, text is Left-aligned unless you change it. Read Page E 1.20 carefully. Read in Orange box on Page E 1.20 about “Entering Data.” In Excel, numbers is Right-aligned unless you change it. What you need to know in this chapter? A.Penjira Kanthawongs

19. If you want Excel to treat numbers, such as ZIP codes, as text, you enter an apostrophe (‘) in front of the numbers. On Page E 1.20 Numbers can contain only … 0 1 2 3 4 5 6 7 8 9 + - () , / . $ % E e.. I have question about this part, see if anyone get this point?? (consider multiple sign) (5 points for person(s) who can explain. A.Penjira Kanthawongs

20. To use AutoSum button. • After you enter all data, click the cell you want the total to display, make sure the cell is below the number you want find the total, click AutoSum button. A.Penjira Kanthawongs

21. Marquee, SUM function, Range • A moving border is called a marquee. • SUM function in formula bar • SUM function display in active cell • The group of adjacent cells B3, B4, B5, and B6 is called a Range. A.Penjira Kanthawongs

22. Use Fill Handle to copy cells 1. With cell B7 active, point to the fill handle. 2. Drag the fill handle to select the past area, range C7:E7. 3. Release the mouse button. 2 1 3 See-through view A.Penjira Kanthawongs

23. SUM function • SUM function will sum numbers on the topof the active cell firstor sum numbers on the left of the active cell. A.Penjira Kanthawongs

24. Relative reference and Show Formulas • Each adjusted cell reference is called a relative reference. • For all homework, you need to Show Formulas … click Tool | Options | and check the box “Formulas” | OK A.Penjira Kanthawongs

25. Select the range you want to have AutoFormat (exclude the title). Click Format | AutoFormat | Accounting 2 To Use AutoFormat A.Penjira Kanthawongs

26. To Center a Cell’s Contents Across Columns (Page E 1.33) • Click cell A1. Drag the block plus sign to the rightmost cell (F1) of the range to center (A1:F1). Point to the Merge and Center button on the formatting toolbar. • Click Merge and Center button. • Click cell A9 to deselect cell A1. A.Penjira Kanthawongs

27. Figure 1 - 48 (Page E 1.36) • Embedded Chart = is a chart which is drawn on the same worksheet as the data. A.Penjira Kanthawongs

28. To Add a 3-D Column Chart • Select the range of the chart which includes the column title(s), the row title(s), data of the column(s), and data of the row(s). • Click the Chart Wizard button. • Choose the chart type • Click Finish A.Penjira Kanthawongs

29. Sizing handles, ALT key • Sizing handles indicate the chart is selected. • While holding down the ALT key, you can extend the width of the chart. Sizing Handles A.Penjira Kanthawongs

30. Legend Chart Location Y-axis = value axis X-axis = category axis A.Penjira Kanthawongs

31. To Save a workbook • You save 1 excel file = you save 1 workbook = you save all worksheets • At the save window, Option = let you set password (case sensitive = lower or upper cases are matter) and set backup A.Penjira Kanthawongs

32. Select the range. Right-click the AutoCalculate area. Click type of calculation you want to see the result. To use AutoCalculate A.Penjira Kanthawongs