Using Spreadsheets in Excel

1. Using Spreadsheets in Excel Using Spreadsheets in EXCEL

2. Uses of EXCEL Spreadsheets allow you to input, manage and evaluate data. Within Excel you can: • enter data. • perform calculations using formulas. • create charts to illustrate data. • use functions to generate statistics.

3. Cell Border Cell Numbering Color Merge Cells Indent Font Font Size Bold Underline Decimal Point Percent Italic Text Alignment Background Currency Comma The Excel Toolbars Chart Wizard Auto Sum Save Format Painter Draw Toolbar Spell Check Undo Print Redo Function Print preview Zoom Insert Hyperlink Sort Alphabetically Open file Cut Help Copy New file Paste

4. Cell Identification Cells are named with their corresponding letter & number. • When ACTIVE, the cell has a thick black border. • Only active cells can be edited.

5. Naming a RANGE of Cells Groups of cells are referenced by: The top left corner & bottom right corner of the selection. B2 D7 You would write the expression like this:B2:D7

6. How would you write the expression for this group of cells ? Referencing Cell Ranges B2:F2

7. Organizing your Workbook • Don’t put everything on a single sheet. • Using multiple sheets helps organize your workbook. • The white tab indicates the active sheet.

8. Renaming & Ordering Sheets By right-clicking on any tab, you can format the names and order of the sheets, as well as insert and delete sheets.

9. 1 Right-click on the Sheet 1 tab and select Rename. Type Year Summary and press the [Enter] key. 2 3 4 6 Rename Sheet 2, typing: Q4 Homework Rename Sheet 3, typing: Q4 Summary Select the (move to end) option. 5 Right-click on the Year Summary sheet tab and select Move or Copy… Let’s Practice!

10. Your workbook should look like this:

11. 2 3 In cells A1:I1, type: Last, First, SSN, Q1, Q2, Q3, Q4, Final Average and Final Grade In cell C11, type Class Average 1 Click on the Year Summary sheet tab. Type the Headings

12. In A2:A10, type:Evans Garcia Catalini Brantley Tierney Jones Seaborne Summers Handleman In B2:B10, type:Robert James Daya Ellie Gavin Dorothy Lisa Kathleen Skippy 1 2 Input the Data

13. In C2:C10, type:459809125 548712349 152845367 522098786 716228068 127659820 231452839 837493585 330260064 In D2:D10, type:82 88 90 87 88 95 80 92 79 3 4 Input the Data

14. In E2:E10, type:79 92 89 94 89 100 79 89 80 In F2:F10, type:80 90 93 90 88 100 74 85 84 5 6 Input the Data

15. Your spreadsheet should look like this:

16. Using Formulas in Excel • A formula is used to calculate a value. • Formulas are created by combining: • Numbers. • Cell References. • Arithmetic Operators. • Functions.

17. Arithmetic Operators Addition Subtraction Multiplication Division + - * /

18. 1 Click the cell in which you want the result to appear. Type =and then the rest of the formula. 2 3 When the formula is complete, press the [Enter] key. Entering a Formula(an overview)

19. The Formula Bar • As you type inside a cell, what you type will also be displayed in the Formula Bar located just underneath the toolbar. • To edit the contents ofa cell, select that cell and make changes in the Formula Bar.

20. =A1+A2+A3+A4+A5+A6+A7+A8+A9 =SUM(A1:A9) =(A1+A2+A3+A4+A5+A6+A7+A8+A9)/9 =AVERAGE(A1:A9) Using FUNCTIONS in Formulas(an overview) • Functions can simplify your formulas. • To write a function, type:=FUNCTION NAME(cell range) • Compare the formulas below:

21. Place the cursor over the bottom right corner of the cell, until the cursor turns into a crosshair. 1 Click and drag your mouse across or down to select the cells in which the formula will be pasted. Release the mouse. 2 Copying the contents of cells To copy a formula or text:

22. 1 Select Cell D11 and type =average(D2:D10) then press [Enter]. 2 Select Cell D11 and place your cursor over it’s bottom right corner. When the cursor changes to a crosshair, click & drag across to Cell G11 to copy the formula. Select Cell H2 and type =average(D2:G2)then press [Enter]. 3 Select Cell H2 and copy the formula down to Cell H10. 4 Do the Calculations

23. Your spreadsheet should look like this:

24. Select Format >> Cells 2 3 5 Under Category, select Special. Click OK. Under Type, select Social Security Number. 4 1 Highlight the cell range C2:C10 Formatting SSN’s

25. Select Format >> Cells 2 3 5 6 Under Category, select Number. Click OK. Repeat steps 1-5 for cells H2:H10. Use the down arrow to set the Decimal Places to 1. 4 1 Highlight the cell range D11:G11 Formatting Averages

26. Select Cells A1:I1 and click the Bold icon on the Formatting toolbar. 1 Select Cell C11 and bold that text as well. Select A1:I1 again.Click the arrow next to the Border icon.Select the double border. 2 3 The Little Extras

27. Select Column A by clicking on the column label, and drag across to Column I. 1 Select Format>> Column>> AutoFit Selection 2 Resizing Columns

28. Place the cursor directly over the gridline. 1 Click and drag left or right. 2 Resizing Columns To manually resize columns:

29. Your spreadsheet should look like this:

30. Organizing the Data Fields Records

31. Select Data >> Sort… Under Sort by, select Last. Select the Ascending option. Click OK. 2 3 4 5 Highlight range A2:F10. 1 Alphabetizing the Data

32. Highlight the cell range A2:A10. 1 Select Edit >> Copy. 2 Click on the Q4 Homework sheet tab. 3 Click on Cell A3. 4 Click on the Q4 Summary sheet tab, and repeat Steps 4 & 5. 6 Select Edit >> Paste. 5 Working with Multiple Sheets

33. Select File >> Save. 1 Navigate to the Excel folder on your Zip. 2 In the File Name box, type: Excel 3 Click the Save button. 4 Saving your Work