University of Transnational Business Law

This is “Entering, Editing, and Managing Data”, section 1.2 from the book Using Microsoft Excel (v. 1.1). For details on it (including licensing), click here.








For more information on the source of this book, or why it is available for free, please see the project's home page. You can browse or download additional books there.

Has this book helped you? Consider passing it on:






Help Creative Commons

Creative Commons supports free culture from music to education. Their licenses helped make this book available to you.





Help a Public School

DonorsChoose.org helps people like you help teachers fund their classroom projects, from art supplies to books to calculators.


Previous Section

Table of Contents

Next Section  



1.2 Entering, Editing, and Managing Data


Learning Objectives
1.Understand how to enter data into a worksheet.
2.Examine how to edit data in a worksheet.
3.Examine how Auto Fill is used when entering data.
4.Understand how to delete data from a worksheet and use the Undo command.
5.Examine how to adjust column widths and row heights in a worksheet.
6.Understand how to hide columns and rows in a worksheet.
7.Examine how to insert columns and rows into a worksheet.
8.Understand how to delete columns and rows from a worksheet.
9.Learn how to move data to different locations in a worksheet.

In this section, we will begin the development of the workbook shown in Figure 1.1 "Example of an Excel Worksheet with Embedded Chart". The skills covered in this section are typically used in the early stages of developing one or more worksheets in a workbook.


Entering Data


Follow-along file: Excel Objective 1.0 (This is a blank workbook that was named in the previous section. If you skipped the previous section, open a new workbook and save it with the file name “Excel Objective 1.0.”)

We will begin building the workbook shown in Figure 1.1 "Example of an Excel Worksheet with Embedded Chart" by manually entering data into the worksheet. There are other ways in which you can bring data into an Excel worksheet, such as importing data from a website or a Microsoft Access database. However, we will demonstrate these other methods later. The following steps explain how the column headings in Row 2 are typed into the worksheet:
1.Activate 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 Sales Dollars.

Figure 1.17 "Entering Column Headings into a Worksheet" 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.17 Entering Column Headings into a Worksheet


Integrity Check

Column Headings

It is critical to include column headings that accurately describe the data in each column of a worksheet. In professional environments, you will likely be sharing Excel workbooks with coworkers. Good column headings reduce the chance of someone misinterpreting the data contained in a worksheet, which could lead to costly errors depending on your career.

6.Activate cell location B3.
7.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.
8.
Repeat step 7 by entering the following numbers in cells B4 through B14: 2160, 515, 590, 1030, 2875, 2700, 900, 775, 1180, 1800, and 3560.


Why?

Avoid Formatting Symbols When Entering Numbers

When typing numbers into an Excel worksheet, it is best to avoid adding any formatting symbols such as dollar signs and commas. Although Excel allows you to add these symbols while typing numbers, it slows down the process of entering data. It is more efficient to use Excel’s formatting features to add these symbols to numbers after you type them into a worksheet.

9.Activate cell location C3.
10.Type the number 9.99 and press the ENTER key.
11.Repeat step 10 by entering 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.
12.Activate cell location D3.
13.Type the number 26685 and press the ENTER key.
14.Repeat step 13 by entering the following numbers in cells D4 through D14: 26937, 7701, 10269, 15405, 35916, 26937, 17958, 15708, 23562, 31416, and 53370.


Integrity Check

Data Entry

It is very important to proofread your worksheet carefully, especially when you have entered numbers. Transposing numbers when entering data manually into a worksheet is a common error. For example, the number 563 could be transposed to 536. Such errors can seriously compromise the integrity of your workbook.

Figure 1.18 "Completed Data Entry for Columns B, C, and D" shows how your worksheet should appear after entering the data. Check your numbers carefully to make sure they are accurately entered into the worksheet.


Figure 1.18 Completed Data Entry for Columns B, C, and D


Editing Data


Follow-along file: Excel Objective 1.0 (Use file Excel Objective 1.01 if you are starting with this skill.)

Data that has been entered in a cell can be changed by double clicking the cell location or using the Formula BarThe area just above the column letters on a worksheet. It can be used for entering data into cells as well as for editing data that already exists in cells.. 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.Activate 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 checkmark to the left of the Formula Bar (see Figure 1.19 "Using the Formula Bar to Edit and Enter Data"). This will enter the change into the cell.


Figure 1.19 Using the Formula Bar to Edit and Enter Data

7.Double click cell A15.
8.Add a space after the word Total and type the word Sales.
9.Press the ENTER key.


Mouseless Command

Editing Data in a Cell
•Activate the cell that is to be edited and press the F2 key on your keyboard.


Auto Fill


Follow-along file: Excel Objective 1.0 (Use file Excel Objective 1.02 if you are starting with this skill.)

The Auto FillAn Excel feature used to complete data in either a quantitative or qualitative sequence. It can also be used to copy and paste data in a worksheet. 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.Activate cell A3 in the Sheet1 worksheet.
2.Type the word January and press the ENTER key.
3.Activate 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 HandleA small square in lower right corner of an activated cell. When the mouse pointer gets close to the Fill Handle, the white block plus sign turns into a black plus sign. (see Figure 1.20 "Fill Handle"). When the mouse pointer gets close to the Fill Handle, the white block plus sign will turn into a black plus sign.


Figure 1.20 Fill Handle

5.
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.21 "Using Auto Fill to Enter the Months of the Year"). Release the left mouse button when the tip box reads “December.”


Figure 1.21 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.22 "Auto Fill Options Button". 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.


Figure 1.22 Auto Fill Options Button

6.Left click the Auto Fill Options button.
7.Left click the Copy Cells option. This will change the months in the range A4:A14 to January.
8.Left click the Auto Fill Options button again.
9.Left 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


Follow-along file: Excel Objective 1.0 (Use file Excel Objective 1.03 if you are starting with this skill.)

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.Activate cell C2 by placing the mouse pointer over the cell and clicking the left mouse button.
2.Press the DELETE key on your keyboard. This removes the contents of the cell.
3.Highlight the range C3:C14 by placing the mouse pointer over cell C3. 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.
Left click and drag the mouse pointer up to cell C3 (see Figure 1.23 "Using Auto Fill to Delete Contents of Cell"). Release the mouse button. The contents in the range C3:C14 will be removed.


Figure 1.23 Using Auto Fill to Delete Contents of Cell

6.Click the Undo button in the Quick Access Toolbar (see Figure 1.3 "Blank Workbook"). This should replace the data in the range C3:C14.
7.
Click the Undo button again. This should replace the data in cell C2.


Mouseless Command

Undo Command
◦Hold down the CTRL key while pressing the letter Z on your keyboard.

8.Highlight the range C2:C14 by placing the mouse pointer over cell C2. Then left click and drag the mouse pointer down to cell C14.
9.Click the Clear button in the Home tab of the Ribbon, which is next to the Cells group of commands (see Figure 1.24 "Clear Command Drop-Down Menu"). This opens a drop-down menu that contains several options for removing or clearing data from a cell. Notice that you also have options for clearing just the formats in a cell or the hyperlinks in a cell.
10.Click the Clear All option. This removes the data in the cell range.
11.Click the Undo button. This replaces the data in the range C2:C14.


Figure 1.24 Clear Command Drop-Down Menu


Adjusting Columns and Rows


Follow-along file: Excel Objective 1.0 (Use file Excel Objective 1.03 if you are starting with this skill.)

In Figure 1.22 "Auto Fill Options Button", there are a few entries 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. 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.25 "Adjusting Column Widths". You will see the white block plus sign turn into double arrows.
2.Left 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 boxA box that appears when the width of a column is being adjusted using the click-and-drag method. It displays the number of characters that will fit into the column using Calibri 11-point font.. This box displays the number of characters that will fit into the column using the Calibri 11-point font.
3.
Release the left mouse button.


Figure 1.25 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 4 through 7 illustrate a second method for adjusting column widths when using a specific number of characters:

4.Activate 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.
5.In the Home tab of the Ribbon, left click the Format button in the Cells group (see Figure 1.26 "Cells Group in the Home Tab").
6.Click the Column Width option from the drop-down menu (see Figure 1.27 "Format Drop-Down Menu"). This will open the Column Width dialog box.
7.
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.28 "Column Width Dialog Box").


Figure 1.26 Cells Group in the Home Tab


Figure 1.27 Format Drop-Down Menu


Figure 1.28 Column Width Dialog Box


Mouseless Command

Column Width
◦Press the ALT key on your keyboard, then press the letters H, O, and W one at a time.

Steps 8 through 10 demonstrate how to adjust row height, which is similar to adjusting column width:

8.Activate cell A15 by placing the mouse pointer over the cell and clicking the left mouse button.
9.In the Home tab of the Ribbon, left click the Format button in the Cells group (see Figure 1.26 "Cells Group in the Home Tab").
10.Click the Row Height option from the drop-down menu (see Figure 1.27 "Format Drop-Down Menu"). This will open the Row Height dialog box.
11.Type the number 24 and click the OK button on the Row Height dialog box. This will set Row 15 to a height of 24 points. A pointMetric used when measuring the height of a row; equivalent to approximately 1/72 of an inch. is equivalent to approximately 1/72 of an inch. This adjustment in row height was made to create space between the totals for this worksheet and the rest of the data.


Mouseless Command

Row Height
•Press the ALT key on your keyboard, then press the letters H, O, and H one at a time.

Figure 1.29 "Excel Objective 1.0 with Column A and Row 15 Adjusted" shows the appearance of the worksheet after Column A and Row 15 are adjusted.


Figure 1.29 Excel Objective 1.0 with Column A and Row 15 Adjusted


Skill Refresher: Adjusting Columns and Rows

(click to see video)
1.Activate at least one cell in the row or column you are adjusting.
2.Click the Home tab of the Ribbon.
3.Click the Format button in the Cells group.
4.Click either Row Height or Column Width from the drop-down menu.
5.Enter the Row Height in points or Column Width in characters in the dialog box.
6.Click the OK button.


Hiding Columns and Rows


Follow-along file: Excel Objective 1.0 (Use file Excel Objective 1.04 if you are starting with this skill.)

In addition to adjusting the columns and rows on a worksheet, you can also hide columns and rows. This is a useful technique for enhancing the visual appearance of a worksheet that contains data that is not necessary to display. These features will be demonstrated using the Excel Objective 1.0 workbook. However, there is no need to have hidden columns or rows for this worksheet. The use of these skills here will be for demonstration purposes only.
1.Activate cell C1 in the Sheet1 worksheet by placing the mouse pointer over the cell location and clicking the left mouse button.
2.Click the Format button in the Home tab of the Ribbon.
3.Place the mouse pointer over the Hide & Unhide option in the drop-down menu (see Figure 1.27 "Format Drop-Down Menu"). This will open a submenu of options.
4.
Click the Hide Columns option in the submenu of options (see Figure 1.30 "Hide & Unhide Submenu"). This will hide Column C.


Figure 1.30 Hide & Unhide Submenu


Mouseless Command

Hiding Columns
◦Hold down the CTRL key while pressing the number 0 on your keyboard.

Figure 1.31 "Hidden Column" shows the workbook with Column C hidden in the Sheet1 worksheet. You can tell a column is hidden by the missing letter C.


Figure 1.31 Hidden Column

To unhide a column, follow these steps:

5.Highlight the range B1:D1 by activating cell B1 and clicking and dragging over to cell D1.
6.Click the Format button in the Home tab of the Ribbon.
7.Place the mouse pointer over the Hide & Unhide option in the drop-down menu (see Figure 1.27 "Format Drop-Down Menu").
8.
Click the Unhide Columns option in the submenu of options (see Figure 1.30 "Hide & Unhide Submenu"). Column C will now be visible on the worksheet.


Mouseless Command

Unhiding Columns
◦Highlight cells on either side of the hidden column(s), then hold down the CTRL key and the SHIFT key while pressing the close parenthesis key ()) on your keyboard.

The following steps demonstrate how to hide rows, which is similar to hiding columns:

9.Activate cell A3 in the Sheet1 worksheet by placing the mouse pointer over the cell location and clicking the left mouse button.
10.Click the Format button in the Home tab of the Ribbon.
11.Place the mouse pointer over the Hide & Unhide option in the drop-down menu (see Figure 1.27 "Format Drop-Down Menu"). This will open a submenu of options.
12.
Click the Hide Rows option in the submenu of options (see Figure 1.30 "Hide & Unhide Submenu"). This will hide Row 3.


Mouseless Command

Hiding Rows
◦Hold down the CTRL key while pressing the number 9 key on your keyboard.

To unhide a row, follow these steps:

13.Highlight the range A2:A4 by activating cell A2 and clicking and dragging over to cell A4.
14.Click the Format button in the Home tab of the Ribbon.
15.Place the mouse pointer over the Hide & Unhide option in the drop-down menu (see Figure 1.27 "Format Drop-Down Menu").
16.Click the Unhide Rows option in the submenu of options (see Figure 1.30 "Hide & Unhide Submenu"). Row 3 will now be visible on the worksheet.


Mouseless Command

Unhiding Rows
•Highlight cells above and below the hidden row(s), then hold down the CTRL key and the SHIFT key while pressing the open parenthesis key (() on your keyboard.


Integrity Check

Hidden Rows and Columns

In most careers, it is common for professionals to use Excel workbooks that have been designed by a coworker. Before you use a workbook developed by someone else, always check for hidden rows and columns. You can quickly see whether a row or column is hidden if a row number or column letter is missing.


Skill Refresher: Hiding Columns and Rows

(click to see video)
1.Activate at least one cell in the row(s) or column(s) you are hiding.
2.Click the Home tab of the Ribbon.
3.Click the Format button in the Cells group.
4.Place the mouse pointer over the Hide & Unhide option.
5.Click either the Hide Rows or Hide Columns option.


Skill Refresher: Unhiding Columns and Rows

(click to see video)
1.Highlight the cells above and below the hidden row(s) or to the left and right of the hidden column(s).
2.Click the Home tab of the Ribbon.
3.Click the Format button in the Cells group.
4.Place the mouse pointer over the Hide & Unhide option.
5.Click either the Unhide Rows or Unhide Columns option.


Inserting Columns and Rows


Follow-along file: Excel Objective 1.0 (Use file Excel Objective 1.04 if you are starting with this skill.)

Using Excel workbooks that have been created by others is a very efficient way to work because it eliminates the need to create data worksheets from scratch. However, you may find that to accomplish your goals, you need to add additional columns or rows of data. In this case, you can insert blank columns or rows into a worksheet. The following steps demonstrate how to do this:
1.Activate cell C1 in the Sheet1 worksheet by placing the mouse pointer over the cell location and clicking the left mouse button.
2.
Click the down arrow on the Insert button in the Home tab of the Ribbon (see Figure 1.32 "Insert Button (Down Arrow)").


Figure 1.32 Insert Button (Down Arrow)

3.
Click the Insert Sheet Columns option from the drop-down menu (see Figure 1.33 "Insert Drop-Down Menu"). A blank column will be inserted to the left of Column C. The contents that were previously in Column C now appear in Column D. Note that columns are always inserted to the left of the activated cell.


Mouseless Command

Inserting Columns
◦Press the ALT key and then the letters H, I, and C one at a time. A column will be inserted to the left of the activated cell.


Figure 1.33 Insert Drop-Down Menu

4.Activate cell A3 in the Sheet1 worksheet by placing the mouse pointer over the cell location and clicking the left mouse button.
5.Click the down arrow on the Insert button in the Home tab of the Ribbon (see Figure 1.32 "Insert Button (Down Arrow)").
6.Click the Insert Sheet Rows option from the drop-down menu (see Figure 1.33 "Insert Drop-Down Menu"). A blank row will be inserted above Row 3. The contents that were previously in Row 3 now appear in Row 4. Note that rows are always inserted above the activated cell.


Mouseless Command

Inserting Rows
•Press the ALT key and then the letters H, I, and R one at a time. A row will be inserted above the activated cell.


Skill Refresher: Inserting Columns and Rows

(click to see video)
1.Activate the cell to the right of the desired blank column or below the desired blank row.
2.Click the Home tab of the Ribbon.
3.Click the down arrow on the Insert button in the Cells group.
4.Click either the Insert Sheet Columns or Insert Sheet Rows option.


Moving Data


Follow-along file: Excel Objective 1.0 (Use file Excel Objective 1.05 if you skipped the previous skill and are starting with this skill.)

Once data are entered into a worksheet, you have the ability to move it to different locations. The following steps demonstrate how to move data to different locations on a worksheet:
1.Highlight the range D2:D15 by activating cell D2 and clicking and dragging down to cell D15.
2.
Bring the mouse pointer to the left edge of cell D2. You will see the white block plus sign change to cross arrows (see Figure 1.34 "Moving Data"). This indicates that you can left click and drag the data to a new location.


Figure 1.34 Moving Data

3.Left click and drag the mouse pointer to cell C2.
4.Release the left mouse button. The data now appears in Column C.
5.Click the Undo button in the Quick Access Toolbar. This moves the data back to Column D.


Integrity Check

Moving Data

Before moving data on a worksheet, make sure you identify all the components that belong with the series you are moving. For example, if you are moving a column of data, make sure the column heading is included. Also, make sure all values are highlighted in the column before moving it.


Deleting Columns and Rows


Follow-along file: Excel Objective 1.0 (Use file Excel Objective 1.05 if you are starting with this skill.)

You may need to delete entire columns or rows of data from a worksheet. This need may arise if you need to remove either blank columns or rows from a worksheet or columns and rows that contain data. The methods for removing cell contents were covered earlier and can be used to delete unwanted data. However, if you do not want a blank row or column in your workbook, you can delete it using the following steps:
1.Activate cell A3 by placing the mouse pointer over the cell location and clicking the left mouse button.
2.Click the down arrow on the Delete button in the Cells group in the Home tab of the Ribbon.
3.
Click the Delete Sheet Rows option from the drop-down menu (see Figure 1.35 "Delete Drop-Down Menu"). This removes Row 3 and shifts all the data (below Row 2) in the worksheet up one row.


Mouseless Command

Deleting Rows
◦Press the ALT key and then the letters H, D, and R one at a time. The row with the activated cell will be deleted.


Figure 1.35 Delete Drop-Down Menu

4.Activate cell C1 by placing the mouse pointer over the cell location and clicking the left mouse button.
5.Click the down arrow on the Delete button in the Cells group in the Home tab of the Ribbon.
6.Click the Delete Sheet Columns option from the drop-down menu (see Figure 1.35 "Delete Drop-Down Menu"). This removes Column C and shifts all the data in the worksheet (to the right of Column B) over one column to the left.


Mouseless Command

Deleting Columns
•Press the ALT key and then the letters H, D, and C one at a time. The column with the activated cell will be deleted.


Skill Refresher: Deleting Columns and Rows

(click to see video)
1.Activate any cell in the row or column that is to be deleted.
2.Click the Home tab of the Ribbon.
3.Click the down arrow on the Delete button in the Cells group.
4.Click either the Delete Sheet Columns or the Delete Sheet Rows option.


Key Takeaways
•Column headings should be used in a worksheet and should accurately describe the data contained in each column.
•Using symbols such as dollar signs when entering numbers into a worksheet can slow down the data entry process.
•Worksheets must be carefully proofread when data has been manually entered.
•The Undo command is a valuable tool for recovering data that was deleted from a worksheet.
•When using a worksheet that was developed by someone else, look carefully for hidden column or rows.


Exercises
1.
When entering numeric data into an Excel worksheet, you should omit symbols such as commas or dollar signs because:
1.These numbers will not be usable in mathematical functions or formulas.
2.Excel will convert this to text data.
3.Excel will not accept these entries into a cell location.
4.It slows down the data entry process.

2.
Which of the following statements is true with respect to editing the content in a cell location?
1.Activate the cell location and press the F2 key on your keyboard to edit the data in the cell.
2.Double click the cell location to edit the data in a cell location.
3.Activate the cell location, click the Formula Bar, and make any edits for the cell location in the Formula Bar.
4.All of the above are true.

3.
Which of the following will enable you to identify hidden columns in a worksheet?
1.The column letter appears in a tip box when the mouse pointer is moved over a hidden column.
2.Clicking the Page Layout View button in the View tab of the Ribbon shows all columns in the worksheet and shades hidden columns.
3.The column letters that appear above the columns in a worksheet will be missing for hidden columns.
4.Click the Hidden Columns indicator in the Status Bar.

4.
Which of the following is true with respect to inserting blank rows into a worksheet?
1.Blank rows are inserted above the activated cell or cell range in a worksheet.
2.Blank rows are always inserted in the center of a cell range. At least two or more cells in a worksheet must be highlighted before a row can be inserted.
3.The command for inserting blank rows and columns can be found by clicking the Format button in the Home tab of the Ribbon.
4.When inserting blank rows into a worksheet, the Undo button is disabled. You must use the Delete button in the Home tab of the Ribbon to remove unwanted blank rows.



Previous Section

Table of Contents

Next Section