Plug-In T2: Basic Skills Using Excel 2007

1. Plug-In T2: Basic Skills Using Excel 2007 Business Driven Technology

2. LEARNING OUTCOMES • Describe how to open, close, and save an Excel workbook • Explain how to insert and delete an Excel worksheet • Describe how to insert, delete, merge, and split cells in an Excel worksheet • Explain how to set up a worksheet in Excel for printing

3. LEARNING OUTCOMES • Describe how to insert and delete rows and columns in an Excel worksheet • Explain how to create and edit formulas in Excel using the formula bar • Describe how to create a chart using Excel

4. INTRODUCTION TO EXCEL • Plug-in T2 focus on six areas: • Workbooks and worksheets • Working with cells and cell data • Printing worksheets • Formatting worksheets • Formulas • Working with charts and graphics

5. INTRODUCTION TO EXCEL • Microsoft Excel is a spreadsheet program to enter, manipulate, calculate, and chart data • An Excel file is referred to as a workbook, which is a collection of worksheets • A worksheet is comprised of rows and columns of data to perform calculations on

6. INTRODUCTION TO EXCEL • Excel 2007 has been redesigned so that you can find and use program capabilities more easily • The overall look and feel has been streamlined

7. WORKBOOKS AND WORKSHEETS • To open an existing workbook: • Open Excel 2007 • Click the Office button, and then click Open • The Open dialog box appears; make sure the location in the Look in: box is correct • Select the workbook name in the large list box • Click the Open button in the dialog box

8. WORKBOOKS AND WORKSHEETS • To close a workbook and save your latest changes: • Click the Office button and then select Close Window • Click Yes in the dialog box

9. WORKBOOKS AND WORKSHEETS

10. Creating Workbooks Using Templates • A template is a file with predefined settings • An Excel template makes creating a new workbook easy and results in a professional appearance • Some examples of workbook templates: • Balance Sheet • Sales Invoice • Loan Amortization

11. Creating Workbooks Using Templates • To create a workbook using a template: • Click the Office button, and then click New • Click the Installed Templates category, and then click a template • Click Create

12. Creating Workbooks Using Templates

13. Saving A Workbook • To create a new folder at the same time you save the workbook do this: • Click the Office button, and select Save As... on the File menu • Click the Create New Folder button to the right of the Save in: box • Enter the name for the new folder in the dialog box that appears • Click OK • Enter the name for the file in the File name: box • Click the Save button

14. Saving A Workbook

15. Inserting and Deleting Worksheets • When creating a new workbook, it contains three worksheets • A workbook can contain as many worksheets as needed • To add a worksheet: • Right-click on any Sheet tab • Select Insert from the shortcut menu • To insert a simple worksheet, click the Worksheet icon in the dialog box • To insert a formatted worksheet, click the Spreadsheet Solutions tab, and click any of the template icons • Click OK

16. Inserting and Deleting Worksheets • To delete a worksheet: • Right-click on a Sheet tab • Select Delete from the menu

17. Inserting and Deleting Worksheets

18. WORKING WITH CELLS AND CELL DATA • You may want to add some extra space or more information into the middle of your worksheet • You must insert a new cell • This new cell can be left blank, or you can enter information into the cell • When you insert a new cell, you have the option to shift the existing data to the right or down

19. Inserting and Deleting Cells • To insert a cell: • Select the cell or cells where you want to insert the new cell(s) • Click the Home tab • Click the Insert Cells button arrow, and then click Insert Cells • Click the Shift cells right or Shift cells down radio button • Click OK

20. Inserting and Deleting Cells • To delete a cell: • Select the cell or cells that you want to delete • Click the Home tab • Click the Delete Cells button arrow, and then click Delete Cells • Click the Shift cells left or Shift cells up radio button • Click OK

21. Merging and Splitting Cells • Merging and splitting cells is one way to control the appearance of a worksheet • Excel allows you to merge and center cells • You can create a title that appears centered in one cell across the top of a workbook • Excel allows you to split cells • Splitting a cell converts a merged cell back to several cells, with the information displayed in the uppermost left cell

22. Merging and Splitting Cells

23. Cutting, Copying, and Pasting Cells • The Cut, Copy, and Paste commands are used to move data and other items within a workbook and between applications • Data that is cut is removed from the document and placed on the Clipboard • The Copy command places a duplicate of the selected data on the Clipboard • The Paste command is used to insert items from the Clipboard into a workbook

24. Cutting, Copying, and Pasting Cells • To cut or copy data within a workbook: • Select the cell or cells you want to cut or copy • Click the Home tab • Click the appropriate toolbar button: • Cut or • Copy • The cell appears with a flashing dotted line around it • Place the cursor where you want to insert data from the Clipboard • Click the Paste toolbar button

25. Entering Text In Cells • Adding text headers to your rows and columns creates structure to enter data • To add text to your workbook: • Click in the cell in which you want to add text • Type the text • Click outside the cell to accept your entry

26. Applying Number Formats • Formatting numbers changes the appearance of the data, but does not change its value • The formatted number is displayed in the cell, and the actual value is displayed in the formula bar • To format numbers: • Select the cells you want to format • Click the Home tab • Click the Number Format list arrow, and then click the number format from the list

27. Applying Number Formats

28. Applying Styles • A style is the combination of effects that can be applied at one time • Styles can include formatting such as: • Character effects • Background color • Typefaces • Number formatting • Excel comes with predefined styles including: • Currency • Comma • Percent styles • You can create your own styles in the Style dialog box

29. Applying Styles • To apply a basic style: • Select the cells you want to format • Click the Home tab • Click the Cell Styles button, and then click the cell style that you want to apply

30. Applying Styles

31. PRINTING WORKSHEETS • Excel allows you to adjust how your worksheet will print • You can adjust the scale of your worksheet, making it smaller and forcing it to fit on one page, or you can print your worksheet across multiple pages by changing the Fit to: options

32. Setting Up The Page For Printing • To set up a page to print: • Click Page Layout tab • To change the page orientation click the Orientation button • Click Portrait or Landscape from the submenu • To change the page size, click the Size button from the submenu • Click the Office button, select Print, then click on the Print Preview button to see what your printed worksheet will look like

33. Setting Up The Page For Printing

34. Setting Margins For Printing • Margins are the blank spaces at the top, bottom, left, and right of a printed page • Excel’s default margins are typically 1 inch for the top and bottom, and 0.75 inches for the left and right

35. Setting Margins For Printing • To adjust the margins for a document: • Click the Page Layout tab • Click the Margins button and then click Custom Margins • Click the arrows to adjust the top, bottom, left, and right margins • The Preview box shows you which part of the page you are changing • Click OK

36. Setting Margins For Printing

37. Previewing A Print Area • The Print Preview window shows you a reduced version of your worksheet as it will appear when printed • To preview your worksheet before printing it: • Click the Office button, select Print, then click Print Preview • When the Print Preview window opens, scroll the window to view the pages • Notice that the mouse pointer has changed to a magnifying glass • Click on a portion of the document to see it full-size • Click again to reverse the magnification • Click the Next Page and Previous Page buttons to view the pages of your workbook • To return to Normal view, click the Close Print Preview button on the toolbar

38. Printing Selections, Worksheets, and Workbooks • Use the Print dialog box to check your print settings before printing • Be sure your printer’s name is displayed in the section • To check your print settings and print: • Click the Office button, select Print, then click the Print button • Verify that the correct printer name is displayed in the Printer section • Verify that All is selected in the Print range section • Click OK

39. Printing Selections, Worksheets, and Workbooks

40. FORMATTING WORKSHEETS • Inserting rows • You may need to add rows or columns of new information into the middle of your workbook • To insert a row: • Click the row immediately below the location of the row you want to insert • Click the Home tab • Click the Insert button arrow, and click Insert Sheet Rows

41. FORMATTING WORKSHEETS • Inserting columns • Click to the right of the location of the new column you want to insert • Click the Home tab • Click the Insert button arrow, and click Insert Sheet Columns

42. Deleting Rows and Columns • When you delete a row or column, you are removing all of those cells from your workbook • Once you have deleted the row or column, it disappears and the rest of the columns and rows move to replace it • To delete a row or column: • Select the row header or column header you want to delete • Click the Home tab • Click the Delete button arrow and then click Delete Sheet Rows or Delete Sheet Columns

43. Modifying Row Heights • When you first enter data in your workbook, Excel automatically sets the rows of a worksheet according to preferences • To modify row heights: • Select the row or rows you want to change • Drag the boundary until the row is the height you want

44. Modifying Column Widths • When you first enter data in your workbook, Excel automatically sets the widths of the columns • As you type data into multiple columns, you may find that Excel does not display all the text in a cell • To modify column widths: • Select the column or columns you want to change • Drag the boundary until the column is the width you want

45. FORMULAS • Entering Formulas • A formula is an equation that performs calculations between cells in a worksheet or table • A formula always begins with an equal sign • A simple formula may contain cell references and operators • To enter a formula: • Click the cell in which you want to enter the formula • Type = (an equal sign) • Type the formula • Click outside the cell or press Enter

46. Entering Formulas

47. Using The Formula Bar • To enter a formula in the formula bar: • Select the cell in which you want to add the formula • In the formula bar, type an equal sign (=) • Enter the formula (including any functions, operators, references, and/or constants) • Click the Enter Formula button

48. Using The Formula Bar

49. How to show a formula • With your worksheet as the active sheet, hold down the Ctrl key and hit the tilde (~) key. • Hit Ctrl tilde (~) again to toggle back to the normal view.

50. Use Excel’s functions • You can easily calculate the sum of a large number of cells by using a function. • A function is a predefined, or built-in, formula for a commonly used calculation. • Each Excel function has a name and syntax. • The syntax specifies the order in which you must enter the different parts of the function and the location in which you must insert commas, parentheses, and other punctuation • Arguments are numbers, text, or cell references used by the function to calculate a value • Some arguments are optional