Excel

1. Excel Rob Curnow CVSD Staff Development

2. Workbooks and worksheets Figure 1   A blank worksheet in a new workbook. 1. The first workbook you open is called Book1 in the title bar at the top of the window until you save it with your own title. 2. Sheet tabs at the bottom of the workbook window.

3. Columns and rows • Figure 1    Column headings are letters. Row headings are numbers. • 1. Column headings. • Row headings. • Figure 2    After the first 26 column headings (A through Z), the next 26 column headings are AA through AZ. The column headings continue through column IV, for a total of 256 columns.

4. Cells are where the data goes Figure 1   The active cell is outlined in black. Figure 2   Cell C5 is selected and is the active cell. It has a black outline. 1. Column C is highlighted. 2. Row 5 is highlighted. 3. Cell C5, the active cell, is shown in the Name Box in the upper-left corner of the worksheet.

5. Start entering data Press TAB to move the selection one cell to the right. Press ENTER to move the selection down one cell. TIP: You can change the behavior of the TAB and ENTER buttons in Options.

6. Resize columns Click to watch video Click and drag between the columns or double click to have Excel resize it automatically.

7. Enter dates and times Excel aligns text on the left side of cells, but it aligns dates on the right side of cells. Tip:    To enter today's date, press CTRL and the semicolon together. To enter the current time, press CTRL and SHIFT and the semicolon all at once.

8. Enter numbers Excel aligns numbers on the right side of cells. To enter fractions, leave a space between the whole number and the fraction. For example, 1 1/8. To enter a fraction only, enter a zero first. For example, 0 1/4. If you enter 1/4 without the zero, Excel will interpret the number as a date, January 4. If you type (100) to indicate a negative number by parentheses, Excel will display the number as -100.

9. Quick ways to enter data Grab Here AutoFill   Enter the months of the year, the days of the week, multiples of 2 or 3, or other data in a series. You type one or more entries, and then extend the series.

10. Fill it up To add the first six months of the year, drag the fill handle until the ScreenTip says June and then release the mouse button to fill the list. TIP: For some lists you need to type two entries to establish a pattern. For example, to fill in a series of numbers such as 3, 6, 9, type two numbers, select both cells, and then drag the fill handle. TIP: You can also drag up or to the left as well as drag down or to the right.

11. Edit data • Double-click a cell to edit the data in it. • Or click the cell, and then edit the data in the formula bar. • The worksheet now says Edit in the status bar.

12. Remove data formatting Figure 1   Formatting stays with the cell. You can't delete formatting by deleting or editing data.  1. The original number is formatted bold and red.  2. Delete the number.  3. Enter a new number. Bold and red again! Figure 2   To delete cell formatting, point to Clear on the Edit menu, and then click Formats. Or click All to delete data and formatting both at once.

13. Insert a column or a row To insert a single column, click any cell in the column immediately to the right of where you want the new column to go. So if you want an order-ID column between columns B and C, you'd click a cell in column C, to the right of the new location. Then on the Insert menu, click Columns Click to watch video To insert a single row, click any cell in the row immediately below where you want the new row to go. For example, to insert a new row between row 4 and row 5, click a cell in row 5. Then on the Insert menu, click Rows.

14. Move data Click to watch video Grab the edge of the cell, hold down the mouse button and move.

15. Copy data Click to watch video Grab the edge of the cell and hold down the Ctrl key while dragging.

16. Formatting cells Click Format on the menu bar to format the data in the cells.

17. Practice time Recreate this spreadsheet. Look for shortcuts when entering data.

18. Conditional formatting You'd start by selecting column or data you want to call attention to, and then click Conditional Formatting on the Format menu.

19. State your conditions TIP:   You can add up to three conditions to a cell or a given range of cells.

20. Freeze here  Freeze column titles by selecting the row below.  Freeze row titles by selecting the column to the right.  Freeze both column and row titles by selecting the cell that is just below the column titles and to the right of the row titles.

21. Freeze  Column titles in row  Column titles disappear after scrolling down one or two rows.

22. Divide and conquer  Click Freeze Panes on the Window menu.  Column titles are divided from the rest of the worksheet by a horizontal line.

23. Use the List command • Click the Create List command... • 2...to open the Create List dialog box.

24. Now you have a list • AutoFilter arrows are automatically added in the header row. •  2. A dark blue border • appears around the • list.

25. How to filter

26. How to sort

27. Formulas A budget in a worksheet needs an amount in cell C6.

28. And the total is…  Selected numbers.  Total in the status bar at the bottom of the window. Tip:   The numbers you select don't have to be lined up together or in the same row or column. Add up numbers anywhere on the worksheet by pressing CTRL and then selecting each number. You'll see how in the practice session.

29. Begin with an equal sign  1. Type the formula in cell C6.  2. Press ENTER to display the formula result.  3. Any time you select cell C6, the formula appears in the formula bar.

30. Total all the values in a column • Select cell B7 and then click • the AutoSum button. • 2. A color marquee surrounds the • cells in the formula, and the • formula appears in cell B7. • 3. Press ENTER to display the • result in cell B7. • 4. Select cell B7 to display the • formula in the formula bar.

31. Update formula results Excel can automatically update totals to include changed values. Change a value and watch the sum change automatically!

32. Copy a formula instead of creating a new one Drag the black cross from the cell containing the formula to the cell where the formula will be copied, then release the fill handle. Auto Fill Options button appears but requires no actions.

33. Other ways to enter cell references  Type the equal sign, type SUM, and type an opening parenthesis in cell C9.  Click cell C4, then type a comma in cell C9.  Click cell C6, then type a closing parenthesis in cell C9.  Press ENTER to display the formula result.

34. More functions  Click Average on the shortcut menu.  Sum changes to Average on the status bar. Select the numbers then right click.

35. Find an average  Click in cell D7, click the arrow on the AutoSum button, and then click Average in the list.  Press ENTER to display the result in cell D7.

36. Find the largest or smallest value  Click in cell F7, click the arrow on the AutoSum button, and then click Max in the list.   Press ENTER to display the result in cell F7.

37. Print formulas You can print formulas to put up on your bulletin board to remind you how to create them. You do this by clicking Formula Auditing on the Tools menu and then clicking Formula Auditing Mode. TIP: You can also press CTRL+` (next to the 1 key) to display and hide formulas.

38. What’s that funny thing in my worksheet? The ##### error value indicates that the column is too narrow to display the contents of this cell.

39. Find more functions The Insert Function dialog box.

40. Create a chart

41. Meet the wizard To begin, you would select the data that you want to chart, as well as the column and row labels. Then you would click the Chart Wizard button on the toolbar to open the Chart Wizard.

42. Update and place charts The wizard placed this chart on the same worksheet as the data. You can move the chart on the worksheet by dragging it elsewhere. Any changes that you make to the worksheet data are instantly shown in the chart.

43. Add titles Enter chart and axis titles in the Chart Wizard.

44. Even more tabs and options  Gridlines  Legend  Data table

45. The End!