1.2 Cells and Worksheets

All Excel workbooks contain at least one worksheet. Worksheets store data and any calculations done in our workbooks. A single worksheet can contain over 17 billion cells, each of which can contain words, phrases, numbers, dates, and other types of information. They can also contain formulas and functions that perform calculations. Each workbook can contain hundreds of worksheets; the actual number of worksheets that you can store in a single workbook is limited by the memory resources of your computer. Thus, a single workbook can store enormous amounts of information. While most workbook files never come close to using the full capacity of Excel, all workbooks can become unnecessarily complex if they are not managed well. In this section we will highlight how to work with cells and worksheets in the Excel environment.

Understanding the Excel Cell Addressing Scheme

Each cell is addressed the same way – according to column letter and row number. A cell located in column A and in the third row down from the top of a worksheet is referred to as cell "A3" (Figure 1.6). This addressing scheme provides a way to specifically define and refer to cells.

This is a feature that will become invaluable when we perform calculations on the data stored in our worksheets; we often use the values stored in one of the cells of our worksheets as an input to the calculation we perform in another cell. Using the values stored in another cell of a worksheet is called referencing a cell. You can also refer to more than one cell at a time as long as the cells are in a continuous block. This is called referencing a range of cells. To reference a range of cells, you specify the upper left cell in the range followed by the ":" character and the lower right cell in the range. For example, the range reference "A2:C2" refers to cells A2, B2, and C2 while the reference "B1:B4" refers to cells B1, B2, B3, and B4.

Entering Data into Cells

Entering data into cells in the Excel environment is simple: you select the cell you would like to use (by clicking on the cell) and type in the data you would like to store in the cell (see Figure 1.7). Once you are done entering data, press the enter key. To modify data in a cell, you can double click on the cell you wish to change and make the changes to the text that you desire. Alternatively, you can select the cell you would like to change and modify the text in the formula bar that appears above the cells in the worksheet and below the Excel menu items.

Either approach will work well when you plan to add information to or alter a small number of cells in a workbook. However, when you plan to enter data into a large number of cells or when the data you plan to enter follows a repetitive pattern, other approaches are more efficient. Figure 1.8 demonstrates the use of the "Fill" feature in Excel.

The "Fill" feature allows you to automatically populate cells in a worksheet. The "Fill" menu item is in the "Editing" group on the "Home" tab. The fill "Up," "Down," "Left," and "Right" options allow you to automatically copy the contents of one cell to other contiguous cells. To do this, you highlight the range of cells that you would like to "Fill" including the cell that contains the data you would like to copy. Then you select the appropriate directional icon in the "Fill" menu.

You can also use fill to complete a pattern within a range of cells. Figure 1.8 demonstrates the process for inserting the numbers 1 through 5 into the range A1:A5 using the "Fill" command. First, you type the value "1" in cell A1 to provide a starting point for the range of values you would like to insert. Then, select "Series" from the "Fill" menu. The "Series" dialog window allows you to specify several options for the values you would like to insert, which in this case are the numbers 1 through 5 that are to be inserted into column A. Next, set the "Series in" option to "Columns" since you are inserting values into column A. The "Type" remains linear, though there are more advanced options available. The "Step value:" also remains as ‘1’ because we want each cell to be one number larger than the cell above it. Finally, we set the "Stop value:" to ‘5’ to ensure that the last number we insert is ‘5’ in cell A5. When you click "OK" you will notice that Excel will automatically fill cells A1 though A5 with the values 1 through 5.

The "AutoFill" option in the "Series" dialog box is also quite useful. You can use "AutoFill" to have Excel insert values into a range of cells by completing a pattern in previous cells (Figure 1.9). For example, you can fill cells A1 though A5 with the values 1 through 5 by typing 1 and 2 in cells A1 and A2, respectively. This initiates the pattern you would like to have Excel complete. You then select the range of values you would like to fill (in this case range A1:A5). You then select "Fill" and "Series" from the "Home" tab. In the "Series" dialog window, change "Type" to "AutoFill" and click "OK". Excel completes your pattern within the range you selected (A1:A5) by inserting 3 through 5 into cells A3 through A5.

"Copy" and "Paste" provide an alternative to "Fill" for inserting the same values into multiple cells in a worksheet. Both "Copy" and "Paste" are available in the "Clipboard" group on the "Home" tab. As the names imply, "Copy" and "Paste" allow you to reproduce a value in one cell and replicate it in other cells. Figure 1.10 highlights the process for copying the word "Hello" from cell A1 and pasting it into cells A2 though A5. First, you select cell A1 and click on "Copy." You then highlight the range of cells (it could be just one cell) where you want the copied values replicated and click "Paste." Excel will automatically fill the copied information into the highlighted range.

As with most actions you can perform in Excel, there are a number of different ways to do the same thing. As an example, you can use keystroke combinations to "copy" (Ctrl-c) a cell and then "paste" (Ctrl-v) the values to another cell. You can also access a menu of common actions by right clicking on a cell.

Managing Columns and Rows

There are many times that you need to manage an entire row or column in a workbook. The next few examples cover common actions performed on columns and rows. In most cases, the actions described can be performed on both columns and rows even if the action is only demonstrated on a column or a row.

When working with data tables in Excel, it is not uncommon to find that you need to insert a column or a row to add information that was not originally included in the table. Figure 1.11 depicts such a scenario in which a table is missing information. In particular, the table is missing a column (between column A and B) and a row (between row 1 and 2) that would make the table complete.

To insert a column between column A and B, you select a cell in column B because Excel will insert a column just to the left of the cell (or set of cells) you select. Click on "Insert" in the "Cells" group of the "Home" menu tab. Notice that you can insert cells, rows, columns, and worksheets. In this case, click "Insert Sheet Columns".

Repeat the process to insert a row between rows 1 and 2 by selecting a cell in row 2 and clicking "Insert Sheet Rows" from the "Insert" menu items list. Notice the blank column and rows that are inserted in the table. You can now complete the table by adding the missing data.

Occasionally the contents of a cell do not fit within the size of the column or the row; when this happens, the contents of the cell may not display correctly. Notice that the text in cells A1 and A2 of Figure 1.12 does not fit within the width of columns A and B. To fix this, you must resize column A by first selecting a cell in column A and clicking on "Format" in the "Cells" group of the "Home" menu tab. Next, select "Column Width" from the "Format" menu list, and the "Column Width" window will appear. Here you can change the width of the column and display the text correctly. You can repeat the process to resize column B to accommodate the text in cell B1. It appears that some important information was not visible because columns A and B were not wide enough.

There are times that you may wish to hide a row or column in your worksheet. For example, you may want to share the result of a complicated set of analyses with a colleague; but showing all of the steps you needed to arrive at your solution would be more information than your colleague needs.

Figure 1.13 highlights how to hide a worksheet column. To hide column C, you first select a cell in column C (or multiple cells in column C). The "Hide & Unhide" menu options are located after navigating to the "Home" tab, finding the "Cells" group, and clicking on the "Format" menu list. You will notice that the "Hide & Unhide" menu items include options for hiding and un-hiding rows, columns, and worksheets. To hide column C, click on "Hide Columns" and Column C should no longer be visible in the worksheet. Notice that the column label for column C is also omitted. To display column C, highlight a range of cells that includes cells in column C (for example, items in column B and D) and click "Unhide Columns" in the "Hide & Unhide" menu. Follow the same steps as above to find the "Hide & Unhide" menu.

Often you will work with large data tables in Excel. Navigating through large data tables can be problematic, particularly when column and row labels are no longer visible. Figure 1.14 depicts such a large data table in which, after scrolling, the company name and the dates are no longer visible. The table is tracking the weekly ending stock price for 58 companies over the course of 22 weeks. Notice that as you navigate through a large table, it is difficult to know what the individual data points in the table mean – particularly when row and column labels are no longer visible. Cell D5 contains the value $195. Without the ability to see data labels, it is impossible to know that this is the closing stock price for company 3 on August 12.

The "Freeze Panes" feature in Excel allows you to "Lock" rows and columns so that they don’t disappear when you navigate through the data table. To freeze the data labels for the table in Figure 1.14, select cell B3 and click "Freeze Panes" which is found in the "View" menu tab and the "Window" group. There are three options in the "Freeze Panes" menu list; you can freeze the first column, the first row, or you can select the worksheet area to freeze. The "Freeze Panes" option will freeze both the column directly to the left and the row directly above the selected cell. In this case, selecting cell B3 will freeze column A and row 2 in the worksheet. Notice that once the "Freeze Panes" option is set, it is easy to determine the dates and companies for each cell in the table. To "Unfreeze" data labels, you simply select "Unfreeze Panes" in the "Freeze Panes" menu list.

Adding and Deleting Worksheets

From time to time, you will find it necessary to add or delete a worksheet from a workbook. To add a worksheet to a workbook, go to the "Home" menu tab, find the "Cells" group, click on the "Insert" button, and select "Insert Sheet." A new worksheet (Sheet 4) will be added to the workbook. The worksheet is automatically given a name with a number reflecting the number of worksheets that have been added to the workbook. You can rename a worksheet by double-clicking on the worksheet tab (for example, ‘Sheet 4’) and editing the text of the worksheet name.

The process for deleting a worksheet is similar to the process for adding a worksheet. To delete a worksheet, you first select a cell within that worksheet; this makes the worksheet you wish to delete the active worksheet. You then navigate to the "Home" tab, the "Cells" group, the "Delete" button menu, and then click on "Delete Sheet." You will be asked to verify that you wish to delete the worksheet.

Practice Working with Cells and Worksheets

Task 1: Identify the row number for "Student 1." Type your answer for the row number in cell O5.

Task 2: Identify the column letter for "Exam 5". Type your answer for the column letter in cell O7.

Task 3: Identify the cell address for the extra credit earned by "Student 6". Place your answer for the cell address in cell O9.

Task 4: Next, you are going to insert a Column between "Gender" and "Quizzes & Homework" and label the new column "Student ID" in cell D4. To do this, select a cell in column D (the column just to the right of where you would like to "Insert" the new column) and then go to the "Home" menu tab, find the "Cells" group, open the "Insert" menu list, and find "Insert Sheet Columns." Once the new column is inserted, type "Student ID" in cell D4.

Task 5: Use the "Fill" feature in Excel to add a "Student ID" for each student ranging from 1 to 66. Type the number "1" in cell D5 and highlight range D5:D70 (where you want to add the student IDs), Navigate to the "Home" menu tab, find the "Editing" group, click on the "Fill" button, and select "Series." In the new window, set the "Series in" button to Columns, "Type" to Linear, and "Step Value" to 1 – these should have been the default settings. Click OK.

Task 6: Use the "Cut and Paste" method to move all of information for "Student 67" in range P2:AB2 into the last row of the table. There should not be any data in range P2:AB2 when you have completed this task.

Task 7: Hide Column C ("Gender") by selecting any cell in column C and then finding the "Home" menu tab followed by the "Cells" grouping and the "Format" menu list. In the menu, find "Hide & Unhide" and then select "Hide columns."

Task 8: Resize Column E (now "Quizzes & Homework" since you inserted "Student ID" into the table) to a width of 20. Start by selecting any cell in column E, and then in the "Home" menu tab’s "Cells" group the "Format" menu list will have an option for "Column Width." Select this and the "Column Width" dialog window will appear. Enter 20 as the column width and click "OK".

Task 9: Use the "Freeze Panes" feature in Excel to lock the column labels (found in row 4) while you scroll through the rest of the table. To do this, select cell A5 and then go to the "View" menu tab, find the "Window" group, select the "Freeze Panes" menu list and then click "Freeze Panes." Note: the "Freeze Panes" action locks the row above and the column to the left of the cell you select. Selecting cell A5 will lock row four but because there is not a column to the left of column A no columns will be locked.

Task 10: Delete the "Excel Basics" worksheet since there are not any practice problems for Part 1 of this tutorial. To delete the worksheet, select the "P1 Excel Basics" worksheet by clicking on the worksheet tab, then go back to the "Home" menu tab, find the "Cells" group, click the "Delete" button and select "Delete Sheet." Finally, confirm that you really want to delete the sheet.

Navigate to the "Assignment" menu and click on the submit button to see how you did.