INTRODUCTION

Being able to make changes to the values in your worksheet is what makes Excel such a valuable analysis tool. You can change any of your entries and see how that affects the formulas in your worksheet. To put the active cell in Edit mode, press F2 on the keyboard or click the mouse pointer on the data line of the Edit bar. You can then continue typing at the end of the current data, use the Backspace key to delete some data, use the left and right arrow keys to move through the data, or use the Home and End keys to move quickly to the beginning or the end of the data. Press Enter when you have finished editing the data.

TIP

Pressing the Enter key

You can't press an arrow key to confirm the entry and move to another cell. You must press Enter.

TIP

Selecting characters

You can drag the mouse pointer across characters to select them. Press the Delete key to delete the selected characters.

TIP

Copying and moving

In addition to editing data, you might find it useful to copy and move data to a different location. See the tasks "Cutting, Copying, and Pasting Data" and "Moving Data" later in this part for more information on copying and moving data.

INTRODUCTION

When you overwrite a cell, you replace the cell's contents with new data. Overwriting is handy when you want to correct typing errors or when a cell contains the wrong data. You can also easily erase the contents of a cell by using the Delete key. Erasing a cell is useful when you change your mind about the contents after you enter the data in the cell. You might find that a piece of data you initially typed into a cell is incorrect and needs to be changed.

TIP

Overwriting formulas

Be careful not to overwrite formulas if that is not what you intended. If you overwrite a formula with a constant value, Excel no longer updates the formula. If you accidentally overwrite a formula but you've saved your spreadsheet recently, you can reopen the spreadsheet to a version saved before you overwrote the formula.

INTRODUCTION

If you make a mistake while working on your spreadsheet, and you detect your error immediately, you can undo your action. In addition, if you undo an action by mistake, you can use Excel to quickly redo it.

TIP

Keyboard undo

A quick and easy way to undo an action is to use the Ctrl+Z shortcut key; you can redo an action using Ctrl+Y.

TIP

Undo not available?

When you save the worksheet, the available actions in the Undo/Redo lists are erased, so make sure you are happy with any changes before you perform a save operation.

INTRODUCTION

There might be times when you are entering data into your worksheet and notice that you typed the wrong information, so that you are off by one cell in a column or row. To avoid retyping all the data again, or copying and pasting, you can insert cells and shift the current cells to their correct locations.

TIP

Using the shortcut menu

Another way to insert a cell is to right-click the spot in your worksheet where you want the new cell to appear and choose Insert from the shortcut menu that appears. The Insert dialog box opens; proceed as normal.

TIP

Using the Insert Options Format Painter smart tag

When the new cell is inserted, click the Insert Options Format Painter smart tag to apply formatting to the inserted cell. Format the new cell like the cell above it or below it, or specify that no formatting be applied.

INTRODUCTION

As you work with worksheets, you might find that data needs to be eliminated to keep the worksheet up-to-date. Or you might accidentally add an extraneous cell of data in a row or column. To avoid typing all your data again to rectify the error, you can delete extraneous cells and shift other cells to their correct locations.

TIP

#REF! error

If the #REF! error appears in a cell after you delete a cell, it means you deleted a cell or cells that contained data that your worksheet needs to calculate a formula. To resolve the problem, undo the change (refer to the task "Undoing and Redoing Changes" earlier in this part to learn how).

INTRODUCTION

You can insert extra rows into a worksheet to make more room for additional data or formulas. Adding more rows, which gives the appearance of adding space between rows, can also make the worksheet easier to read. Alternatively, you can delete rows from a worksheet to close up some empty space or remove unwanted information.

TIP

Automatic formula row updates

When you insert a new row, Excel automatically updates any formulas affected by the insertion (see Part 5 for more information).

TIP

#REF! error

If the #REF! error appears in a cell after you delete a row, it means you deleted a cell or cells that contained data that your worksheet needs to calculate a formula. To resolve the problem, undo the change (refer to the task "Undoing and Redoing Changes.")

INTRODUCTION

You can insert extra columns into a worksheet to make room for more data or formulas. Adding more columns, which gives the appearance of adding space between columns, can also make the worksheet easier to read. Alternatively, you might want to delete columns from a worksheet to close up some empty space or remove unwanted information.

TIP

Automatic formula column updates

When you insert a new column, Excel automatically updates any formulas affected by the insertion (see Part 5 for more information).

TIP

#REF! error

If the #REF! error appears in a cell after you delete a column, it means you deleted a cell contained data that your worksheet needs to calculate a formula. To resolve the problem, undo the recent change.

INTRODUCTION

You can save the time and trouble of retyping duplicate information in a worksheet by cutting or copying cell text and data and pasting it. In addition to the cut, copy, and paste commands, use the Office Clipboard task pane to work with multiple items known as "scraps." For example, if you need to copy two different selections of data from the beginning of a worksheet to two different locations toward the end of a worksheet, you can use the Clipboard to perform the procedure in fewer steps than if you were to copy and paste each separately.

TIP

Cutting versus copying

When you want to move (rather than copy) data from its current location to a new location, click the Cut button on the Standard toolbar. The Cut option removes the selected value from the old location.

TIP

Using the Paste button

You can still use the Copy or Cut and Paste buttons on the Standard toolbar when you want to perform a single cut/paste or copy/paste.

TIP

Using the Clipboard

If you want to clear all the items copied to the Clipboard, click the Clear All button in the Clipboard task pane. To paste all the items saved to the Clipboard in one location, click the Paste All button in the task pane.

TIP

Pasting formulas

If you paste cells using Ctrl+V, you can paste cell formulas. If you paste cells with formulas from the multi-element Clipboard, you paste the values, not the formulas.

Click the first cell in which you want to paste the range.

INTRODUCTION

Transposing (flipping rows and columns) is a special copy feature you might need to use if you want to change the layout of your worksheet. For example, suppose your worksheet is set up with quarters in rows and divisions in columns, but that your boss prefers the opposite: quarters in columns and divisions in rows. In a case such as this one, you can flip the worksheet.

CAUTION

You cannot transpose a range of cells onto the original range selection. You must first transpose the range and then move the range.

TIP

Using the Paste Special dialog box

Notice that the Paste Special dialog box enables you to paste all different types of formulas, values, formats, and so on. Practice using this dialog box and see the different types of results that you get.

TIP

Eliminating the original data

To eliminate the original data that you transposed, you must delete it. Refer to the task "Overwriting and Deleting Data" earlier in this part for more information.

INTRODUCTION

Excel lets you move information from one cell into another cell, which means you do not have to type the data into the new cell and then erase the data in the old location. You might want to move data in a worksheet because the layout of the worksheet has changed.

TIP

Undoing a move

If you move the wrong data or move the data to the wrong location, click the Undo button on the Standard toolbar to undo the most recent move. Then start over. (See the task "Undoing and Redoing Changes" earlier in this part for more information.)

INTRODUCTION

There might be times when you'll need to find specific information in a large spreadsheet. For example, suppose you want to quickly find the row that deals with sales data in Region 5 of your company. Instead of scanning each row for the data you need, which can be time-consuming, you can use Excel's Find feature.

TIP

Finding all instances of data

Click the Find All button in the Find and Replace dialog box to view a list, complete with cell locations and worksheet tab names, of all the instances of the data you entered in the Find what text box.

INTRODUCTION

Suppose you discover that you consistently misspelled a company's name in your worksheet, or that a person you reference in several cells has gotten married and changed her name. Fortunately, Excel enables you to search for instances of incorrect or outdated data and replace it with new data using its Find and Replace feature.

TIP

Narrowing search criteria

Click the Options button on the Find and Replace dialog box to make your search criteria more specific. To conduct a case-sensitive search (for example, finding all instances of Hoffman but not hoffman), choose the Match case option. Choose Match entire cell contents to limit your search to cells that contain no more and no less than the data you type (for example, to find all instances of Hoffman, but not Hoffmann. You can also specify where the search should be conducted (Within Sheet or Within Workbook), ways to search (By Rows or By Columns).

INTRODUCTION

Some cells contain data or formulas that require an explanation or special attention. Comments provide a way to attach this type of information to individual cells without cluttering the cells with extraneous information. A red triangle indicates that a cell contains a comment, which you can view in several different ways.

TIP

Displaying comments

You can make it so that Excel automatically displays the full text of a cell's comments while you work in the worksheet. To do so, right-click the commented cell and choose Show/Hide Comment from the shortcut menu that appears. To re-hide the comment, right-click the commented cell and choose Hide Comment.

INTRODUCTION

Excel lets you quickly edit or delete a comment. For example, if you no longer need the note to yourself or want to change the information about a cell.

TIP

Displaying comments

You can make it so that Excel automatically displays the full text of a cell's comments while you work in the worksheet. To do so, right-click the commented cell and choose Show/Hide Comment from the shortcut menu that appears. To re-hide the comment, right-click the commented cell and choose Hide Comment.

INTRODUCTION

The Symbol command enables you to insert special characters, international characters, and symbols such as the registered trademark (®) and trademark (™) symbols. You can easily add these and other special characters to your Excel worksheets. You delete symbols and special characters just as you delete any other text—by using the Backspace or Delete key.

TIP

Finding symbols

You can locate different types of symbols by clicking the Font drop-down arrow and selecting from the various fonts. Each font provides you with different symbols to choose from.

TIP

Adding foreign letters

Many foreign spellings (for example, Latin) include acute accent (´), grave accent (`), tilde (~), or other marks. To display those marks in the Symbol dialog box, open the Font drop-down list in the Symbol dialog box and select (normal text).

INTRODUCTION

Suppose you're working on a team project, and each member has access to the same workbook. To keep track of who makes what changes to the workbook, you can use Excel's Track Changes feature; that way, each person's edits appear in a different color. An edit can be changing the current information or adding completely new information to the workbook. (The only time the colors won't be different is when two people use the same computer or user information—such as login or password.)

TIP

Rejecting changes

If a member of your team has changed the value in a cell in error, you can reject the change; see the next task to learn how.

TIP

Stopping sharing/tracking

The word [Shared] in the title bar of your workbook indicates that other people can use it. This is mostly useful in a network setting, where others can easily access your worksheet via the network. When you turn on Excel's Track Changes feature, you automatically share the workbook. To disable sharing and tracking, open the Tools menu, choose Track Changes, and select Highlight Changes to open the Highlight Changes dialog box. Then, deselect the Track Changes While Editing… check box and click OK.

INTRODUCTION

When you are ready to finalize a worksheet containing tracked changes, you must determine which changes you want to keep, or accept, and which you want to reject. When you reject a change, Excel restores the cell to its previous value.

TIP

Accepting or rejecting all changes

Click Accept All if you want to accept all changes in the workbook; select Reject All if you want to reject all the changes in the workbook.

TIP

Viewing descriptions of changes

Any changes made to your work book while the Track Changes feature is on will be displayed in the Accept or Reject Changes dialog box.

INTRODUCTION

If your worksheet is for your eyes only, you might not think that misspellings are a big deal. But if you plan to turn over your worksheet to your manager, she might not think the mistakes are so minor. Fortunately, you can use Excel to check your spelling quickly and easily.

TIP

Adding words to your dictionary

If you notice that Excel incorrectly flags a word as a misspelling, you can add that word to the Office dictionary that Excel uses to check spelling. To do so, click the Add to Dictionary button in the Spelling dialog box.

TIP

Checking from the beginning

You don't have to be at the beginning of a workbook when you check for spelling errors. If you start in the middle of a workbook, Excel checks until it reaches the end and then asks you whether you want to continue checking from the beginning of your workbook.