Essential Worksheet Operations

1. Essential Worksheet Operations Applications of Spreadsheets

2. Working with Excel’s Window • Workbook – • an Excel file • contains one or more worksheets or chart sheets • can have multiple workbooks open at once • each occupies a separate window which can be minimized or maximized (restored).

3. Moving and Resizing Windows • To move a window • make sure it is not maximized • click and drag it’s title bar with the mouse • To resize a window • click and drag any of its borders • to resize a window horizontally and vertically at the same time, click and drag any of its corners • To make all workbook windows visible • Select Window  Arrange • 4 window arrangements available: Tiled, Horizontal, Vertical, Cascade

4. Switching Among Windows • Only one window can be the active window • At the top of the stack • Can accept input and commands • Title bar is different color

5. Switching among Windows • To make a different window active • Click another window (if visible) • Press Ctrl+Tab (or Ctrl+F6) to cycle through available windows (Shift+Ctrl+Tab or Shift+Ctrl+F6 reverses direction). • Click the workbook icon in the Windows Taskbar (must be set in Options) • Select Window  select workbook you want (up to 9 can be displayed here)

6. Displaying and Closing Windows • Can display a single workbook in more than one window by selecting Window  New Window • Close a window by • using the Close button in the title bar • File  Close

7. Displaying and Closing Windows • The Excel workbook structure allows you to have multiple worksheets • Can easily move among worksheets • Can add and delete worksheets • Can move worksheets around • Can name each worksheet an identifiable name • Changing the names of worksheets in a multiple-sheet workbook makes it easier to understand the purpose of each sheet.

8. Displaying and Closing Windows • Excel allows you to copy, link or move cell contents • between worksheets • between workbooks • between applications

9. The Active Worksheet • Only one worksheet within the active workbook is the active worksheet. • To activate a different sheet • click on its sheet tab • Ctrl+PgUp (activates the previous worksheet, if exists) • Ctrl+PgDn (activates the next sheet, if exists)

10. The Active Worksheet

11. Multiple Worksheets • Navigating worksheets • use the tab-scrolling buttons to scroll the sheet tabs. • use the tab split box to display more or fewer tabs. • right click the tab scrolling buttons to the left of the worksheet tabs to display a list of all sheets in the workbook. • Within a workbook you can insert, move, rename, and delete worksheets.

12. Inserting Worksheets • Select the Insert  Worksheet command • Press Shift+F11 • Right-click a sheet tab, choose the Insert command from the shortcut menu, and select Worksheet from the Insert dialog box.

13. Inserting Worksheets • Excel positions an inserted worksheet to the left of the current worksheet. You can easily change its position, however, by clicking its sheet name and dragging left or right. • To insert more than one worksheet at a time • hold down the Shift key • click a range of worksheet tabs equal to the amount of new worksheets you wish to add.

14. Inserting, Arranging, and Deleting Worksheets

15. Deleting Worksheets • Select the Edit  Delete Sheet command or • Right-click a sheet tab, choose the Insert command from the shortcut menu, and select Worksheet from the Insert dialog box.

16. Deleting Worksheets • You cannot use Excel’s Undo feature to restore a deleted sheet! • As a precaution, save a workbook immediately before applying any action that cannot be undone. • If you accidentally delete a worksheet, close the current workbook without saving the most recent change – then open the workbook which will still have the deleted sheet.

17. Inserting, Arranging & Deleting Worksheets

18. Renaming Worksheets • Choose Format  Sheet  Rename • Double-click on the sheet tab. • Right-click on the sheet tab and choose Rename. • Good to provide meaningful names for your worksheets. • If you want to edit rather than entirely rename, best to choose the second action. • Can’t use the following characters in a sheet name: • : colon • / slash • \ backslash • ? question mark • * asterisk

19. Changing the Sheet Tab’s Color • Can color-code sheet tabs to quickly identify the contents. • Right-click the tab, choose Tab Color, and select the color in the Format Tab Color dialog box.

20. Rearranging the Worksheets • To move a worksheet: click the worksheet tab and drag it to it’s desired location (can be a different workbook that is open). • To copy a worksheet: click the worksheet tab, press Ctrl, and drag the tab to its desired location. • To move or copy multiple sheets: hold the Ctrl key down when selecting the sheets. • To use the Move or Copy Dialog Box: select the Edit  Move or Copy Sheet command, or right-click the tab, choose the Move or Copy command.

21. Specify the target location in another workbook Specify the target location within the current workbook Change the operation from a Move to a Copy by selecting this check box Inserting, Arranging & Deleting WorksheetsDisplaying the Move or Copy Dialog Box

22. Using Save As to Change Filename, File Type, or Storage Location • Clicking the Save button on the toolbar immediately saves the current workbook under its current name and in its current storage location. • If you want to change the name or storage location, or save the workbook as another file type, use the Save As option on the File menu. • Before clicking the Save button always check if you have the desired settings in three areas of the dialog box: • Save in • File name • Save as type

23. Hiding Worksheets and Workbooks • You can hide and unhide workbooks and worksheets within workbooks. • To hide the current worksheet, choose Format  Sheet  Hide • To unhide a worksheet, choose Format  Sheet  Unhide and select it from a list of hidden sheets in the Unhide dialog box. • To hide a workbook, open it, and choose Window Hide • To unhide a workbook, choose Window  Unhide, and select from a list of hidden workbooks.

24. Unhiding a Hidden Worksheet

25. Viewing and Printing Large Worksheets • Workbooks used in industry are typically quite expansive, containing multiple worksheets and storing hundreds of rows of data. • Knowing how to efficiently manage and work with large worksheets directly impacts your productivity.

26. Viewing a Worksheet in Multiple Windows • You may need to view two parts of a worksheet simultaneously. • A worksheet can have many views or separate windows as you want, each being independent of each other (in terms of scrolling, not data!) • To create and display a new view of the active workbook: • Choose Window  New Window

27. Compare Side by Side Feature • New to Excel 2003 • Has its own tool bar to: • Change the synchronous scrolling setting • Reset the window positions • Close the Side by Side feature.

28. Compare Side by Side Feature • In a different workbook: • Make sure that the two worksheets are displayed in separate windows. • In the same workbook: • Use the Window  New Window command to create a new window for the active workbook. • Activate the first window • Choose Window  Compare Side by Side With. • Select Window  Arrange to modify the window arrangement.

29. Other Ways to View a Worksheet in Multiple Windows • You can split the worksheet window into two or four panes, and then scroll to any area of the worksheet in any pane. • You can also freeze selected rows and/or columns on the screen.

30. Splitting the Worksheet Window into Panes Top or upper window pane Vertical scroll bar for the top pane Bottom or lower window pane Vertical scroll bar for the bottom pane

31. Freezing and Unfreezing Panes

32. Freezing and Unfreezing Panes The row numbering skips from row 3 to 47

33. Editing When Split Panes are Active • When a worksheet is split into panes, it is possible to see the same section of a worksheet in multiple panes. • This relates to the screen display only. • The command doesn’t produce duplicate cells. • You can edit the contents of a cell in one pane and the changes appear immediately in any other pane that displays the same cell.

34. Updating Data in a Multi-paned Worksheet

35. Zooming In and Out for a Better View • You don’t want to alter the font sizes in your worksheets just to view a larger area of a worksheet because changes in font size are also reflected on printed output. • Instead, change the zoom level on the toolbar to decrease or increase your view of one area of a worksheet.

36. Saving Your View Settings • Can create a named view that includes settings for window size, window position, frozen panes, titles, outlining, zoom factor, active cell, print area, etc. • Set up the view settings the way you want them. • Select View  Custom Views • Click the Add button and provide a name in the Add View dialog box and click OK.

37. Saving Your View Settings • To view a worksheet in a custom view, select the particular view by selecting View  Custom Views to display the list, and then click the Show button. • To delete a custom view, click the Delete button.

38. The Watch Window • You may want to keep track of a particular value in a particular cell, but it goes out of view when you scroll the worksheet. • Display the Watch Window (a special toolbar) by • Selecting View  Toolbars  Watch Window • Click Add Watch • Specify the cell you want to watch • Can move the Watch window to a convenient location on your Excel workspace. • Can add any number of cells to the Watch Window.

39. Working with Rows and Columns • The number of total rows and columns in an Excel worksheet is fixed. • Rows: 65,536 • Columns: IV

40. Inserting and Deleting Rows and Columns • If you decide to add more data within an existing worksheet, you can insert rows and columns. • Inserting a row or column is a two-step process: • selecting a cell and then choosing Insert, Columns, or, Insert, Rows. • Excel always inserts a new row above the row you select, and inserts a new column to the left of the column you select. • Deleting a row or column is done in two steps: • select a row heading or a column heading in the worksheet frame and choose Edit, Delete.

41. Inserting and Deleting Rows and Columns • If you want to insert more than one row or column at a time • select as many adjacent rows or columns as you need blank rows or columns • choose Insert  Rows or Insert  Columns. • if you want to insert five new rows beginning at row 4, select rows 4 through 8, and choose Insert, Rows. • If you want to delete more than one row or column at a time • select the row or column headings in the worksheet frame • choose Edit  Delete.

42. Inserting and Deleting Rows & ColumnsSelecting two columns in the worksheet Drag the mouse pointer to the right in the column frame area to select multiple columns.

43. Displaying the Right-click Menu for Selected Columns

44. Inserting and Deleting Rows and Columns Entering data and extending formulas into the newly inserted row .

45. Hiding & Unhiding Rows and Columns • For privacy or other reasons, there may be rows or columns in a worksheet that you do not want to display. • Can hide and unhide rows and columns in Excel. • When you hide rows or columns in a worksheet, the data in those hidden parts are removed from view but not deleted. • If you print the worksheet, the hidden parts do not print.

46. Unhiding Row 1 or Column A • If the first column or row in a worksheet is hidden, you can select it by choosing Edit, Go To and specifying A1 in the Reference box. • After clicking OK to exit the Go To dialog box, point to either Row or Column on the Format menu and click Unhide.

47. Hiding Two Columns in a Worksheet Columns D and E are hidden temporarily from view.

48. Selecting Columns to Unhide Columns Notice the gap between rows 5 and 12 in the frame area. Rows 6 through 11 are temporarily hidden from view. Select the two columns on either side of the hidden columns that you want to unhide.

49. Changing Column Widths • Column width is measured in terms of the number of characters of a fixed pitch font that will fit into the cell’s width (8.43 characters) • Doesn’t really pertain because we use proportional fonts most of the time. • To change a column’s width: • Drag the right column border with the mouse until the column is the desired width. • Choose Format  Column  Width and enter a value in the Column Width dialog box. • Choose Format  Column  AutoFit Selection. • Double-click the right border of a column header to set the column width automatically to the widest entry in the column.

50. Changing Row Heights • Row height is measured in points (72 points = 1 inch). • Excel adjusts row heights automatically to accommodate the tallest font in the row. • To change row heights: • Drag the lower row border with the mouse until the row is the desired height. • Choose Format  Row  Height and enter a value (in points) in the Row Height dialog box. • Choose Format  Row  AutoFit Selection. • Double-click the bottom border of a row header to set the row height automatically to the tallest entry in the column.