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.
Download the file from the link below to follow along with the text example or video and to practice on your own.
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.
The cell addressing scheme 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 Using the values stored in another cell of a worksheet. . 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 Referring to more than one cell at a time as long as the cells are in a continuous block. of cells. To reference a A group of cells in a continuous block. 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.
You can use AutoFill to have Excel insert values into a range of cells by completing a pattern set up in previous cells (Figure 1.8). 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 (in this case, range A1:A2). You then place your mouse pointer on the bottom right corner of the selection. The mouse pointer will display as a cross. Drag down to cell A5, and Excel will complete your pattern within the range you selected (A1:A5) by inserting numbers 3 through 5 into cells A3 through A5.
The pattern you enter isn’t limited to increments of one. For example, you can fill cells A1 through A5 with the values 10 through 50 by typing 10 and 20 in cells A1 and A2, respectively. This initiates the pattern you would like to have Excel complete. You then select the cell with the pattern and drag the right corner of the selection down to fill the rest of the cells with the pattern of values.
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 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.
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). 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; then, 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, 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 1) will be added to the workbook. The worksheet is automatically given a name with a number that reflects the number of worksheets in the workbook. You can rename a worksheet by double-clicking on the worksheet tab (for example, ‘Sheet 1’) 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, you click on Delete Sheet. You will be asked to verify that you wish to delete the worksheet.