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, by 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 with 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, in this case, numbers 1 through 5 in 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 automatically fills cells A1 through 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 through 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 their 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 through 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 several different ways to do the same thing. For 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 columns A and B) and a row (between rows 1 and 2) that would make the table complete.

To insert a column between columns 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). To find the Hide & Unhide menu options, navigate to the Home tab, find the Cells group, and click on the Format menu list. You will notice that the Hide & Unhide menu items include options for hiding and unhiding 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 columns B and D) and click Unhide Columns in the Hide & Unhide menu. Follow the same steps as above to find the Hide & Unhide menu.

You will often 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 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 visible data labels, it is impossible for you 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 a specific worksheet area. The Freeze Panes option will freeze both the column directly to the left of 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, and the Delete button menu. Then, click on Delete Sheet. You will be asked to verify that you wish to delete the worksheet.