Objectives

1. Objectives • Describe the advantages of spreadsheets • List several applications for spreadsheets • Explain the underlying principles of electronic spreadsheet use • Describe how to set up and modify a spreadsheet • Identify the elements of standard charts and graphs

2. Spreadsheets • Spreadsheet is an application software • Spreadsheet used to organize , present , • and analyze Data • Examples : • Excel - Lotus 123 - Quattro

3. Name Exam1 Exam2 Exam3 Exam4 Exam5 Total Rabih Abbas 80 70 60 75 95 380 Hiba Wasim 90 95 90 80 85 440 Mazin Aly 451 90 95 96 90 80 Yousef Bkar 68 70 70 75 68 351 82 82 79 80 77 Constructing a Spreadsheet • How to construct a student’s grade sheet • and each student has 5 grades • Manualsheet Average

4. Manual Spreadsheets Disadvantages • Time Consuming • Making a mistake is too easy • Manual recalculation • Formatting is not easy • Manual Analysis

5. Electronic Spreadsheets A computerized version of a manual sheet • Features • Automatic calculations with free error • Storing , Editing , Retrieving a sheet • Automatic recalculation (What-IF analysis) • Formatting makes sheet more readable • and appealing • Graphics that represent data in a Visual Format

6. MicrosoftExcel Opening Microsoft Excel Program Start  All Programs  Microsoft Excel The following main window will appear

7. MicrosoftExcel What are Workbooks and Worksheets? • A workbook is made up of multiple worksheets • A worksheet is made up of the rows and columns into which you enter your information Worksheets

8. MicrosoftExcel Creating and Opening Workbooks • Opening a workbook • Allows you to open an existing workbook • Creating a workbook • Allows you to create a new, ‘empty’ workbook NEW OPEN The last few files that you opened are displayed at the bottom of the File drop down menu!

9. Microsoft Excel • Cell • Storage unit on a sheet • The intersection of a row and a column • Each cell has : • Address : C4 , E10 • Content : • Value - Label • Formula • Function • Active cell - the cell available to be edited at a given time

10. Microsoft Excel • Cell • Operations on a cell • First : Select the intended cell • Second : Apply the operation • Enter the cell content • Edit the cell content • Delete the cell content • Format the cell content

11. Microsoft Excel • Range • A group of one or more cells occurring in a rectangular shape

12. Microsoft Excel • Range • Operations on a range • First : Select the intended range Mouse Shift key + arrows • Second : Apply the operation Move - Copy Delete - Format

13. Microsoft Excel Inserting and Deleting Cells, Rows and Columns • Insert cells, rows or columns using the Insert menu • Delete cells, rows or columns using the Edit menu

14. Microsoft Excel Saving Workbooks • To save a workbook • From the File menu, select Save • Or press CTRL+S • Or click on the Save button on the Standard toolbar • To save a back-up copy using a different name • From the File menu, select Save As to display the Save Asdialog box SAVE

15. Microsoft Excel • Formula • Making Calculations • General form : =Expression Numerical Label Operator Operand Operator Operand & % • Constant • String constant ^ • Cell address • Cell address * , / + , - =B2*(C1+6)/2 =D4 & “name”

16. SUMAdds all numbers in a range of cells Average Returns the average of its Arguments Example : =Average(C3:C8) Product Multiplies all the numbers in a range Microsoft Excel • Functions • A function is like a preprogrammed function • General form : =Function Name(Arguments) =SUM(A1,B1:B8) • Function Categories : (1) Math Functions

17. MINReturns the smallest value in a range. MAXReturns the largest value in a range . Example : max(C3:C8) COUNTCounts the numeric values in a range. Example : count(B3:D7) INDEXGets a value with an index within a range. Example : index(C4:C8 , 3) SUMIFAdds the cells specified by a given condition. SUMIF(B3:B8,”Tea”,D3:D8) Microsoft Excel Functions

18. LENReturns the No. of Chars in a text string. Example : LEN(B3) LEFTReturns the leftmost Chars in a text string. Example : LEFT(B3 , 2) RIGHTReturns the rightmost Chars in a text. Example : RIGHT(B3 , 3) MID Returns a specific No. of Chars from a text stringstarting at a position you specify. Example : MID(B3 , 2 , 3) Microsoft Excel Functions (2) Text Functions

19. Microsoft Excel Functions (3) Date & Time Functions Date Returns the No. that represents the date. Example : Date(86,11,3) Today Returns a No. that represents today’s date. Month Returns the month, an integer 1 12 Example : Month(today( )) Day Returns the day, an integer (1 31) Example : Day(today( )) Year Returns the year, an integer (1900 9999) Example : Year(today( ))

20. Hour Returns the hour, an integer (0 23) Example : Hour(now( )) Minute Returns the minute, an integer (0 59) Example : Minute(now( )) Second Returns the second, an integer (0 59) Example : Second(now( )) Microsoft Excel Functions (3) Date & Time Functions Now Returns the current date or time. Example : Now( )

21. MicrosoftExcel Worksheet Formatting Formatting makes sheet more readable and appealing • Toggling bold, italics and underlining • Applying borders, colors and patterns • Row height and column width • Changing the font type and size • Alignment • Number formatting

22. Microsoft Excel Worksheet Formatting

23. Microsoft Excel Charts • Represent data in a visual format 1- LineChart Showing trends over a period of time

24. Microsoft Excel Charts 2- ColumnChart Showing data comparisons by the height of bars • Single-range Column Chart Only one variable is involved

25. Microsoft Excel Charts • Multiple-range Column Chart Several variables are involved • Clustered Chart • Stacked Chart

26. Microsoft Excel Charts 3- PieChart Displays the contribution of each value to a total The whole amount is represented by a circle, and each wedge of the pie represents a value. Ex: Showing the proportion of all expenses during JAN

27. Microsoft Excel Charts Steps for Creating a Chart