Chapter 3. Entering and Managing Data

Data is the technical term for the text and numbers you enter into an Excel worksheet. Each cell in an Excel worksheet can contain data made up of text, numbers, or any combination of both.

The capability to make changes to the values in your worksheet is what makes Excel such a valuable analysis tool. You can insert a cell, row, or column. You also can delete or change entries, find and replace data, and even check for spelling errors. Besides editing the data in your worksheets, you can add comments to remind yourself of information.

Entering Data and Making Changes

Entering Data

The quickest and easiest way to get data into Excel is to enter the data via the keyboard. You can enter data into a blank worksheet or add data to an existing worksheet. For example, you can enter in the word fruits in cell A2 and press the Enter key.

Click the cell you want to edit, making it active.

Enter some data (in this case, Fruits) in the cell. As you type, the data will also show in the Edit bar.

Press the Enter key when your edit has been made. Excel makes the cell below (the one you just edited) the active cell.

Enter some data into a few different cells, pressing the arrow keys to move around.

Editing and Deleting Existing Data

You can always replace a cell’s contents with new data. This comes in handy when you need 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.

Double-click the cell you want to edit.

Edit the portion of data you need changed.

To delete a value, click on the target cell.

Press the Delete button on your keyboard.

Zooming Into Your Data

If you want to zoom in and get a closer look at data in your worksheet, you can select a higher percentage of magnification. On the other hand, if you want to zoom out so more of the worksheet shows on the screen at one glance, select a lower percentage of magnification. Excel 2010 provides several ways to zoom in and out.

Slide the Zoom Slider on the lower-right corner of Excel. Watch your worksheet increase and reduce in magnification as you slide.

Select a Range and choose the Zoom to Selection command on the View tab. Note how Excel automatically increases or reduces magnification so that only that selection is visible.

Select the Zoom command on the View tab, then choose your desired magnification. After you click the OK button, observe how Excel applies your zoom preference.

Undoing and Redoing Changes

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.

To undo your most recent action, simply click the Undo icon on the Quick Access Toolbar.

Click on the drop-down button next to the Undo icon to undo all actions to a certain point.

To redo your most recent action, simply click the Redo icon on the Quick Access Toolbar.

Click on the drop-down button next to the Redo icon to redo all actions to a certain point.

Copying and Pasting Data

You can avoid the trouble of retyping duplicate information in a worksheet by cutting or copying data from one part of worksheet to another. For example, if you need to duplicate a column of data, there is no need to retype that data. You can copy the data and paste it where you need it. If you want to move the column, you can cut it from its location and paste it to a new location.

To copy and paste, click the first cell in the range you want to copy and then drag down to highlight the entire range.

Click the Copy command on the Home tab.

Click the cell to which you want the range to be pasted.

Click the Paste command on the Home tab. Excel duplicates your range.

To copy and paste, click the first cell in the range you want to copy and then drag down to highlight the entire range.

Click the Cut command on the Home tab.

Click the cell you want the range pasted to.

Click the Paste command on the Home tab. Excel moves your range.

Freezing Rows and Columns

You might create worksheets that are so large that you cannot view all your data on the screen at the same time. In addition, if you have added row or column labels and you scroll down or to the right, you’ll lose sight of your labels and which data field you’re reviewing. For example, if you are reviewing sales data in columns E and F, it would be nice to see the row title of the cells you are referencing. To help, you can freeze the heading rows and columns so that they’re always visible.

Click in the cell below the row you want to freeze.

On the View tab, click the Freeze Panes command.

Click Freeze Top Row. At this point, you will be able to scroll down and still see the first row of your data table.

To unfreeze the panes, go back to the Freeze Panes command and click the Unfreeze Panes command.

Splitting a Worksheet

By splitting a worksheet, you can scroll independently into different horizontal and vertical parts of a worksheet. This is useful if you want to view different parts of a worksheet or copy and paste between different areas of a large worksheet.

Click in the cell where you want to split the worksheet.

On the View tab, click the Split command. You will now see a split bar separating your worksheet into two independently scrollable sections.

Move through the worksheet and see how easily you can simultaneously view other parts of the worksheet.

Inserting Cells

There might be times when you need to insert new cells into the middle of your dataset. For example, if you have a table that shows data by region, you might need to insert blank cells to accommodate a new region. To avoid retyping all the data again or copying and pasting, you can insert cells and shift the current cells to their correct locations.

Select the cell or cells that need to be shifted to insert new cells.

Right-click and choose the Insert option.

In the Insert dialog box, choose whether you want to shift existing cells right or down to make room for the new cells. Then press the OK button.

Click in your newly created cells and start adding data.

Deleting Cells

As you work with worksheets, you might find that data needs to be eliminated to keep the worksheet up to date. You can easily delete extraneous cells and shift existing cells to their correct locations.

Select the cell or cells that need to be deleted.

Right-click and choose the Delete option.

In the Delete dialog box, choose whether you want to shift existing cells left or up to fill the empty space left by the deleted cells. Press the OK button.

Observe that Excel has deleted the specified cells.

Inserting and Deleting Rows

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.

Right-click on the row number of the existing row where you want to insert a new row.

Click the Insert option. Excel automatically adds a new row.

To delete a row, right-click on the row number of the existing row that you want deleted.

Click the Delete option. Excel automatically removes the specified row.

Inserting and Deleting Columns

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.

Right-click on the column letter of the existing column where you want to insert a new column.

Click the Insert option. Excel automatically adds a new column.

To delete a column, right-click on the column number of the existing column that you want deleted.

Click the Delete option. Excel automatically removes the specified column.

Moving Data

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.

Select the cells you want to move.

Click the border of the selected cells and drag the cells to the location in the worksheet where you want to move the data.

Observe that Excel has moved your data.

Finding Data

You will often encounter situations where you will need to find specific information in a large spreadsheet. For example, suppose you want to quickly find the row that shows the sales information for Jerry Thompson. Instead of scanning each row for the data you need, which can be time consuming, you can use Excel’s Find feature.

On the Home tab, click Find and Select, and then click Find or press shortcut key Ctrl+F.

In the Find What text box, enter the data you want to find.

Click the Find Next button. Excel finds the first instance of the data you typed and makes the cell that contains it the active cell. You can click the Find Next button to search for the next instance.

Click the Close button when you are done searching.

Replacing Data

Imagine that you discover some data in your table in which a company’s name has been consistently misspelled, or that a salesperson you reference in a data table has changed his last name. You definitely would not want to find and replace all that data manually. 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.

On the Home tab, click Find and Select and then click Replace.

In the Find What text box, type the data you would like to find. Press the Tab key to move the cursor to the Replace With text box, and type the replacement data.

Click Replace All to replace all instances of the data you typed. (Alternatively, click Find Next to find the first instance of the data and click Replace to replace it.)

Excel notifies you of the number of replacements it made. Click OK.

Applying a Data Filter

When working with a large data table, it is sometimes useful to filter the table so that you can only see or work with a specific set of records. For instance, let’s say you only want to see the sales reps in Region 2. When you want to work with a subset of records, you can use Excel’s Filter. The Filter function allows you to see only those records that meet the criteria you select.

Click on the Filter command on the Data tab.

You will immediately see filter drop-downs inside each of your header columns. Click on the drop-down selector for the column you want to filter.

Click the (Select All) option to clear all the check boxes, then click the check box next to the value by which you want to filter.

Click the OK button to apply the filter. Observe the funnel icon on the filtered column, indicating a filtered state.

Sorting Data

You will often want to change the ordering of your data. For example, you might want to sort a table of sales reps by their regions, then by YTD sales. You can meet this need by using Excel’s Sort function.

Click on any cell within the data table you are going to sort.

On the Data tab, click the Sort command.

The Sort Dialog box opens. Select the field you want to sort and then specify whether you want to sort Smallest to Largest or Largest to Smallest.

Click the Add Level button to add another level of sorting.

Select another field to sort by, then specify whether you want to sort Smallest to Largest or Largest to Smallest.

After you click the OK button, Excel immediately applies your custom sort.

Adding and Managing Cell Comments

Some cells contain data that requires 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. After a comment is in place, Excel makes it easy to edit or delete it.

Right-click the cell to which you want to add a comment.

Select Insert Comment.

Type the desired text into the comment area. When you’re finished, click anywhere in the worksheet to accept the comment.

The cell’s upper-right corner now contains a red triangle, indicating the presence of a comment. To view the comment, hover the mouse pointer over the triangle.

To edit a comment, right-click on the commented cell.

Select the Edit Comment option.

To delete a comment, right-click on the commented cell.

Select the Delete Comment option.