1. ENTERING, EDITING, AND MANAGING DATA

                                                                                                      




In this section, we will begin the development of the workbook shown in Figure 1.1 (the previous section - Overview of Excel in which we Saved As CH1 Merchandise City Sales Data). The skills covered in this section are typically used in the early stages of developing one or more worksheets in a workbook.

Entering Data

You will begin building the workbook shown in Figure 1.1 by manually entering data into the worksheet. The following steps explain how the column headings in Row 2 are typed into the worksheet:

  1. Click cell location A2 on the worksheet.
  2. Type the word Month.
  3. Press the RIGHT ARROW key. This will enter the word into cell A2 and activate the next cell to the right.
  4. Type Unit Sales and press the RIGHT ARROW key.
  5. Repeat step 4 for the words Average Price and then again for Sales Dollars.

Figure 1.15 shows how your worksheet should appear after you have typed the column headings into Row 2. Notice that the word Price in cell location C2 is not visible. This is because the column is too narrow to fit the entry you typed. We will examine formatting techniques to correct this problem in the next section.


Figure 1.15 Entering Column Headings into a Worksheet



Click cell B3.
  1. Type the number 2670 and press the ENTER key. After you press the ENTER key, cell B4 will be activated. Using the ENTER key is an efficient way to enter data vertically down a column.
  2. Enter the following numbers in cells B4 through B14: 2160, 515, 590, 1030, 2875, 2700, 900, 775, 1180, 1800, and 4560.
  3. Click cell C3.
  4. Type the number 9.99 and press the ENTER key.
  5. Enter the following numbers in cells C4 through C14: 12.49, 14.99, 17.49, 14.99, 12.49, 9.99, 19.99, 19.99, 19.99, 17.49, and 14.99.
  6. Click cell D3.
  7. Type the number 26685 and press the ENTER key.
  8. Enter the following numbers in cells D4 through D14: 26937, 7701, 10269, 15405, 35916, 26937, 17958, 15708, 23562, 31416, and 75125.
  9. When finished, check that the data you entered matches Figure 1.16.






Figure 1.16 shows how your worksheet should appear after entering the data. Check your numbers carefully to make sure they are accurately entered into the worksheet.

Numbers have been entered into columns B, C, and D without symbols such as dollar signs or commas.
Figure 1.16 Completed Data Entry for Columns B, C, and D


Editing Data

Data that has been entered in a cell can be changed by double clicking the cell location or using the Formula Bar. You may have noticed that as you were typing data into a cell location, the data you typed appeared in the Formula Bar. The Formula Bar can be used for entering data into cells as well as for editing data that already exists in a cell. The following steps provide an example of entering and then editing data that has been entered into a cell location:

  1. Click cell A15 in the Sheet1 worksheet.
  2. Type the abbreviation Tot and press the ENTER key.
  3. Click cell A15.
  4. Move the mouse pointer up to the Formula Bar. You will see the pointer turn into a cursor. Move the cursor to the end of the abbreviation Tot and left click.
  5. Type the letters al to complete the word Total.
  6. Click the check mark to the left of the Formula Bar (see Figure 1.17). This will enter the change into the cell.

Figure 1.17 Using the Formula Bar to Edit and Enter Data

  1. Double click cell A15.
  2. Add a space after the word Total and type the word Sales.
  3. Press the ENTER key.




Auto Fill

The Auto Fill feature is a valuable tool when manually entering data into a worksheet. This feature has many uses, but it is most beneficial when you are entering data in a defined sequence, such as the numbers 2, 4, 6, 8, and so on, or nonnumeric data such as the days of the week or months of the year. The following steps demonstrate how Auto Fill can be used to enter the months of the year in Column A:

  1. Click cell A3 in the Sheet1 worksheet.
  2. Type the word January and press the ENTER key.
  3. Click cell A3 again.
  4. Move the mouse pointer to the lower right corner of cell A3. You will see a small square in this corner of the cell; this is called the Fill Handle (See Figure 1.18) When the mouse pointer gets close to the Fill Handle, the white block plus sign will turn into a black plus (+) sign.
Cell A3 is activated with "January" entered. Fill handle is shown at bottom right corner of cell.
             Figure 1.18 Fill Handle

Left click and drag the Fill Handle to cell A14. Notice that the Auto Fill tip box indicates what month will be placed into each cell (see Figure 1.19). Release the mouse button when the tip box reads “December.”

   Figure 1.19 Using Auto Fill to Enter the Months of the Year

Once you release the left mouse button, all twelve months of the year should appear in the cell range A3:A14, as shown in Figure 1.20. You will also see the Auto Fill Options button. By clicking this button, you have several options for inserting data into a group of cells.
12 months appear in cell range A3:A14. Auto Fill Options Button includes Copy Cells, Fill Series, Fill Formatting Only, Fill Without Formatting and Fill Months.
Figure 1.20 Auto Fill Options Button

  1. Click the Auto Fill Options button.
  2. Click the Copy Cells option. This will change the months in the range A4:A14 to January.
  3. Click the Auto Fill Options button again.
  4. Click the Fill Months option to return the months of the year to the cell range A4:A14. The Fill Series option will provide the same result.


Deleting Data and the Undo Command

There are several methods for removing data from a worksheet, a few of which are demonstrated here. With each method, you use the Undo command. This is a helpful command in the event you mistakenly remove data from your worksheet. The following steps demonstrate how you can delete data from a cell or range of cells:

  1. Click cell C2.
  2. Press the DELETE key on your keyboard. This removes the contents of the cell.
    Excel for Mac icon Mac Users: Hold down the Fn key and press the Delete key
  3. Highlight the range C3:C14. Then left click and drag the mouse pointer down to cell C14.
  4. Place the mouse pointer over the Fill Handle. You will see the white block plus sign change to a black plus sign (+).
  5. Click and drag the mouse pointer up to cell C3 (see Figure 1.21). Release the mouse button. The contents in the range C3:C14 will be removed.
Fill Handle dragged up from cell C14 to cell C3 highlighting data in these cells will be deleted. Undo button top left of Home ribbon.
Figure 1.21 Using Auto Fill to Delete Contents of Cell

  1. Click the Undo button in the Quick Access Toolbar (see Figure 1.2). This should replace the data in the range C3:C14.
  2. Click the Undo button again. This should replace the data in cell C2.



Clear button and Clear Command drop-down menu: Clear All, Formats, Contents, Comments, Hyperlinks and Remove Hyperlinks.
Figure 1.22 Clear Command Drop-Down Menu


Adjusting Columns and Rows

There are a few entries in the worksheet that appear cut off. For example, the last letter of the word September cannot be seen in cell A11. This is because the column is too narrow for this word. The columns and rows on an Excel worksheet can be adjusted to accommodate the data that is being entered into a cell using three different methods. The following steps explain how to adjust the column widths and row heights in a worksheet:

  1. Bring the mouse pointer between Column A and Column B in the Sheet1 worksheet, as shown in Figure 1.23. You will see the white block plus sign turn into double arrows.
  2. Click and drag the column to the right so the entire word September in cell A11 can be seen. As you drag the column, you will see the column width tip box. This box displays the number of characters that will fit into the column using the Calibri 11-point font which is the default setting for font/size.
  3. Release the left mouse button.
Column A and Column B with double arrows on column border between them. Column width tip box indicates width of column, wide enough now to show whole word "September".
Figure 1.23 Adjusting Column Widths

You may find that using the click-and-drag method is inefficient if you need to set a specific character width for one or more columns. Steps 1 through 6 illustrate a second method for adjusting column widths when using a specific number of characters:

  1. Click any cell location in Column A by moving the mouse pointer over a cell location and clicking the left mouse button. You can highlight cell locations in multiple columns if you are setting the same character width for more than one column.
  2. In the Home tab of the Ribbon, left click the Format button in the Cells group.
  3. Click the Column Width option from the drop-down menu. This will open the Column Width dialog box.
  4. Type the number 13 and click the OK button on the Column Width dialog box. This will set Column A to this character width (see Figure 1.24).
  5. Once again bring the mouse pointer between Column A and Column B so that the double arrow pointer displays and then double-click to activate AutoFit. This features adjusts the column width based on the longest entry in the column.
  6. Use the Column Width dialog box (step 6 above) to reset the width to 13.