IN THIS CHAPTER

Changing worksheet data

Making notes and commenting on a worksheet

Copying and moving data

Hiding and protecting worksheets so that they can’t be altered

This chapter delves into the workaday world of worksheets (say that three times fast). It explains how to edit worksheet data and move quickly here and there in a worksheet. For the purposes of collaboration, you find out how to make notes and comments on worksheets. You also discover techniques for entering data quickly, selecting cells, and copying and moving data in cells. This chapter describes how to move, delete, and rename worksheets as well as protect them from being edited or altered.

Editing Worksheet Data

Not everyone enters data correctly the first time. To edit data you entered in a cell, do one of the following:

• Double-click the cell. Doing so places the cursor squarely in the cell, where you can start deleting or entering numbers and text.
• Click the cell and press F2. This technique also lands the cursor in the cell.
• Click the cell you want to edit. With this technique, you edit the data on the Formula bar.

If nothing happens when you double-click, or if pressing F2 lands the cursor in the Formula bar rather than a cell, somebody has been fooling with the Options settings. On the File tab, choose Options, select the Advanced category in the Excel Options dialog box, and select the Allow Editing Directly in Cells check box.

Moving around in a Worksheet

Going from place to place gets progressively more difficult as a worksheet gets larger. Luckily for you, Excel offers keyboard shortcuts for jumping around. Table 2-1 describes these keyboard shortcuts.

As well as pressing keys, you can use the following techniques to get from place to place in a worksheet:

• Scroll bars: Use the vertical and horizontal scroll bars to move to different areas. Drag the scroll box to cover long distances. To cover long distances very quickly, hold down the Shift key as you drag the scroll box on the vertical scroll bar.
• Scroll wheel on the mouse: If your mouse is equipped with a scroll wheel, turn the wheel to quickly scroll up and down.
• Name box: Enter a cell address in the Name box and press Enter to go to the cell. The Name box is found to the left of the Formula bar.
• The Go To command: On the Home tab, click the Find & Select button and choose Go To on the drop-down list (or press Ctrl+G or F5). You see the Go To dialog box. Enter a cell address in the Reference box and click OK. Cell addresses you’ve already visited with the Go To command are already listed in the dialog box. Click the Special button to open the Go To Special dialog box and visit a formula, comment, or other esoteric item.
• The Find command: On the Home tab, click the Find & Select button and choose Find on the drop-down list (or press Ctrl+F). Enter the data you seek in the Find What box and click the Find Next button. Click the Find All button to find all instances of the item you’re looking for. A list of the items appears at the bottom of the dialog box; click an item to go to it.

To scroll to the active cell if you no longer see it onscreen, press Ctrl+Backspace.

Getting a Better Look at the Worksheet

Especially when you’re entering data, it pays to get a good look at the worksheet. You need to know which column and row you’re entering data in. These pages explain techniques for changing your view of a worksheet so that you always know where you are. Read on to discover how to freeze, split, and hide columns and rows. (On the subject of changing views, Book 1, Chapter 3 explains an essential technique for changing views: zooming in and zooming out.)

Freezing and splitting columns and rows

Sometimes your adventures in a worksheet take you to a faraway cell address, such as X31 or C39. Out there in the wilderness, it’s hard to tell where to enter data because you can’t see the data labels in the first column or first row that tell you where to enter data on the worksheet.

To see one part of a worksheet no matter how far you stray from it, you can split the worksheet or freeze columns and rows onscreen. In Figure 2-1, I split the worksheet so that column A (Property) always appears onscreen, no matter how far I scroll to the right; similarly, row 1 (Property, Rent, Management Fees, and so on) also appears at the top of the worksheet no matter how far I scroll down. Notice how the row numbers and column letters are interrupted in Figure 2-1. Because I split the screen, I always know what data to enter in a cell because I can clearly see property names in the first column and the column headings along the top of the worksheet.

Freezing columns or rows on a worksheet works much like splitting except that lines instead of gray bars appear onscreen to show which columns and rows are frozen, and you can’t adjust where the split occurs by dragging the boundary where the worksheet is split.

Giving the Split or Freeze Panes command

Follow these steps to split or freeze columns and rows onscreen:

1. Click the cell that is directly below the row you want to freeze or split and is in the column to the right of the column that you want to freeze or split.

   In other words, click where you want the split to occur.

2. On the View tab, split or freeze the columns and rows.

   Go to the View tab and use one of these techniques:

   • Splitting: Click the Split button and then click and drag the split bars to split the screen horizontally or vertically (refer to Figure 2-1). The other way to split a worksheet is to grab hold of a split bar, the little division markers directly above the vertical scroll bar and directly to the right of the horizontal scroll bar (in the lower-right corner of your screen). You can tell where split bars are because the pointer turns into a double arrow when it’s over a split bar.
   • Freezing: Click the Freeze Panes button and choose one of three Freeze options on the drop-down list. The second and third options, respectively, freeze the top row or first column. The first option, Freeze Panes, freezes the column(s) to the left and the row(s) above the cell you selected in Step 1.

   Bars or lines appear onscreen to show which row(s) and column(s) have been frozen or split. Move where you will in the worksheet. The column(s) and row(s) you froze or split stay onscreen.

Unsplitting and unfreezing

Use one of these techniques to keep your worksheet from splitting or freezing to death:

• Unsplitting: Click the Split button again; double-click one of the split bars to remove it; or drag a split bar into the top or left side of the worksheet window.
• Unfreezing: On the View tab, click the Freeze Panes button and choose Unfreeze Panes on the drop-down list.

Notes for Documenting Your Worksheet

It may happen that you return to your worksheet days or months from now and discover to your dismay that you don’t know why certain numbers or formulas are there. For that matter, someone else may inherit your worksheet and be mystified as to what the heck is going on. To take the mystery out of a worksheet, document it by entering notes here and there.

A note is a little explanation that describes part of a worksheet. Each note is connected to a cell. You can tell where a note is because a small red triangle appears in the upper-right corner of cells to which notes are attached. Move the pointer over one of these triangles and you see the pop-up box, a note, and the name of the person who entered the note, as shown in Figure 2-2. Click the Notes button on the Review tab and choose Show All Notes to see every note in a worksheet.

Starting on the Review tab, follow these instructions to handle notes:

• Entering a note: Click the cell that deserves a note, click the Notes button, and choose the New Note on the drop-down list (refer to Figure 2-2). Enter your note in the pop-up box. Click in a different cell when you finish entering your note.
• Reading a note: Move the pointer over the small red triangle and read the note in the pop-up box (refer to Figure 2-2). You can also right-click a cell and choose Show/Hide Note.
• Showing and hiding notes: Click the Notes button and choose Show All Notes on the drop-down list to see or hide all the notes in a worksheet. To show or hide an individual note, right-click its cell and choose Show/Hide Note.
• Finding notes: Click the Notes button and then choose the Previous Note or Next Note on the drop-down list to go from note to note.
• Editing a note: Select the cell with the comment, click the Notes button, and choose Edit Note. You can also right-click the cell and choose Edit Note.
• Deleting notes: Right-click the cell with the note and choose Delete Note. To delete several notes, select them by Ctrl+clicking and then right-click and choose Delete Note.

If your name doesn’t appear in the pop-up box after you enter a note and you want it to appear there, go to the File tab, choose Options, select the General category in the Excel Options dialog box, and enter your name in the User Name text box.

Comments for Collaborating with Others on a Workbook

When constructing a workbook is a team affair, consider writing comments to discuss with team members how to make the workbook better. Comments are meant for workbooks being shared on OneDrive (Book 10, Chapter 2 describes file sharing on OneDrive). Each comment is attached to a cell. You can tell where a comment is because a purple comment icon appears in the upper-right corner of cells that have been commented on. Write comments to suggest ways to make a workbook better and to reply to others’ suggestions.

Starting on the Review tab, follow these instructions to comment on a workbook you share with others on OneDrive:

• Entering a comment: Click the cell that needs a comment and then click the New Comment button. A pop-up box appears for entering a comment. Enter your comment and click the Post button to post it on OneDrive where others can see it. To edit a comment you made, click its icon, click the Edit button in the pop-up box, and make your edits.
• Displaying a single comment: Click the purple icon in the cell where the comment is to see the comment in a pop-up box.
• Displaying all comments: Click the Show Comments button on the Review tab or the Comments button in the upper-right corner of the screen. The Comments pane opens, as shown in Figure 2-3. From there you can read, reply to, and enter comments.
• Replying to a comment: Display the comment and enter your replay in the Reply text box. Click the Post button when you finish replying.
• Going from comment to comment: Click the Previous Comment or Next Comment button. You can also scroll through comments in the Comments pane.
• Deleting comments: Select the cell with the comment and click the Delete button on the Review tab. To delete a comment and all its replies, display it in the Comments pane, click the More Thread Actions button, and choose Delete Thread.

Selecting Cells in a Worksheet

To format, copy, move, delete, and format numbers and words in a worksheet, you have to select the cells in which the numbers and words are found. Here are ways to select cells and the data inside them:

• A block of cells: Drag diagonally across the worksheet from one corner of the block of cells to the opposite corner. You can also click in one corner and Shift+click the opposite corner.
• Adjacent cells in a row or column: Drag across the cells.
• Cells in various places: While holding down the Ctrl key, click different cells.
• A row or rows: Click a row number to select an entire row. Click and drag down the row numbers to select several adjacent rows.
• A column or columns: Click a column letter to select an entire column. Click and drag across letters to select adjacent columns.
• Entire worksheet: Click the Select All button, the square to the left of the column letters and above the row numbers; press Ctrl+A; or press Ctrl+Shift+Spacebar.

Press Ctrl+Spacebar to select the column that the active cell is in; press Shift+Spacebar to select the row where the active cell is.

You can enter the same data item in several different cells by selecting cells and then entering the data in one cell and pressing Ctrl+Enter. This technique comes in very handy, for example, when you want to enter a placeholder zero (0) in several different cells.

Deleting, Copying, and Moving Data

In the course of putting together a worksheet, it is sometimes necessary to delete, copy, and move cell contents. Here are instructions for doing these chores:

• Deleting cell contents: Select the cells and then press the Delete key; on the Home tab, click the Clear button and choose Clear Contents; or right-click and choose Clear Contents.
• Copying and moving cell contents: Select the cells and use one of these techniques:
  • Cut or Copy and Paste commands: When you paste the data, click where you want the first cell of the block of cells you’re copying or moving to go. (Book 1, Chapter 2 explains copying and moving data in detail.) Be careful not to overwrite cells with data in them when you copy or move data. After you paste data, you see the Paste Options button. Click this button and choose an option from the drop-down list to format the data in different ways.
  • Drag and drop: Move the pointer to the edge of the cell block, click when you see the four-headed arrow, and start dragging. Hold down the Ctrl key to copy the data.

Handling the Worksheets in a Workbook

A workbook can hold more than one worksheet. Keeping more than one worksheet in the same workbook has advantages. For example, in a workbook that tracks monthly income from rental properties, you can record monthly income on 12 worksheets, 1 for each month. By constructing formulas that calculate income data across the 12 worksheets, you can track annual income from the properties.

As Figure 2-4 shows, Excel places a tab at the bottom of the screen for each worksheet in a workbook. Initially, worksheets are named Sheet1, Sheet2, and so on as you add them, but you can rename worksheets. Click a tab to go from worksheet to worksheet. Right-click a tab to open a shortcut menu with commands for handling worksheets.

Follow these instructions to move among, add, delete, rename, and change the order of worksheets:

• Inserting a new worksheet: Click the New Sheet button (you can find it in the lower-left corner of the screen to the right of the worksheet tab); press Shift+F11; or on the Home tab, open the drop-down list on the Insert button and choose Insert Sheet.
• Moving among worksheets: To go from one worksheet to another, click a worksheet tab along the bottom of the screen. If you can’t see a tab, click one of the scroll arrows to the left of the worksheet tabs.
• Renaming a worksheet: Right-click the worksheet tab, choose Rename on the shortcut menu, type a new name, and press Enter. You can also go to the Home tab, click the Format button, choose Rename Sheet on the drop-down list, and enter a new name. Spaces are allowed in names, and names can be 31 characters long. Brackets ([]) are allowed in names, but you can’t use these symbols: / : ? and *.
• Selecting worksheets: Click the worksheet’s tab to select it. To select several worksheets, Ctrl+click their tabs or click the first tab and then Shift+click the last tab in the set. To select all the worksheets, right-click a tab and choose Select All Sheets on the shortcut menu.
• Rearranging worksheets: Drag the worksheet tab to a new location. As you drag, a tiny black arrow and a page icon appear to show you where the worksheet will land after you release the mouse button. You can also select a sheet, go to the Home tab, click the Format button, and choose Move or Copy Sheet on the drop-down list. The Move or Copy dialog box appears, as shown in Figure 2-5. Select the sheet in the Before Sheet list where you want the worksheet to go and click OK.
• Deleting a worksheet: Select the sheet, and on the Home tab, open the drop-down list on the Delete button and choose Delete Sheet. You can also right-click a worksheet tab and choose Delete. Be careful, because you can’t restore your deleted worksheet by pressing the Undo button.
• Copying a worksheet: Select the sheet, hold down the Ctrl key, and drag the worksheet tab to a new location.
• Moving a worksheet to another workbook: Make sure that the other workbook is open, open the drop-down list on the Format button, and choose Move or Copy Sheet. Then select the other workbook’s name in the Move or Copy dialog box and click OK.
• Color-coding a worksheet: Right-click a worksheet tab and choose Tab Color. Then select a color in the submenu, or choose More Colors and select a color in the Colors dialog box.

You can change the size of columns or apply numeric formats to the same addresses in different worksheets by selecting all the sheets first and then formatting one worksheet. The formats apply to all the worksheets that you select. Being able to format several different worksheets simultaneously comes in handy, for example, when your workbook tracks monthly data and each worksheet pertains to one month. Another way to handle worksheets with similar data is to create the first worksheet and copy it to the second, third, and fourth worksheets with the Copy and Paste commands.

Keeping Others from Tampering with Worksheets

People with savvy and foresight sometimes set up workbooks so that one worksheet holds raw data and the other worksheets hold formulas that calculate the raw data. This technique prevents others from tampering with the raw data. Furthermore, if the worksheet with raw data is hidden, the chance that it will be tampered with is lower; and if the worksheet is protected, people can’t tamper with it unless they have a password. These pages explain how to hide a worksheet so that others are less likely to find it. I also tell you how to protect a worksheet from being edited.

Protecting a worksheet

Protecting a worksheet means to restrict others from changing it — from formatting it, inserting new rows and columns, or deleting rows and columns, among other tasks. You can also prevent any editorial changes whatsoever from being made to a worksheet. Follow these steps to protect a worksheet from tampering by others:

1. Select the worksheet that needs protection.

2. On the Review tab, click the Protect Sheet button.

   You see the Protect Sheet dialog box, shown in Figure 2-6. You can also open this dialog box by going to the Home tab, clicking the Format button, and choosing Protect Sheet.

3. Enter a password in the Password to Unprotect Sheet box if you want only people with the password to be able to unprotect the worksheet after you protect it.

4. On the Allow All Users of This Worksheet To list, select the check box next to the name of each task that you want to permit others to do.

   For example, select the Format Cells check box if you want others to be able to format cells.

   Deselect the Select Locked Cells check box to prevent any changes from being made to the worksheet. By default, all worksheet cells are locked, and by preventing others from selecting locked cells, you effectively prevent them from editing any cells.

5. Click OK.

   If you entered a password in Step 3, you must enter it again in the Confirm Password dialog box and click OK.

To unprotect a worksheet that you protected, go to the Review tab and click the Unprotect Sheet button. You must enter a password if you elected to require others to have a password before they can unprotect a worksheet.