Lists and Worksheets
|
|
Lists and Worksheets |
|
The Categories or Columns of a List |
|
Introduction to Lists |
|
Imagine you have a list of movie directors and you want to group their names into a list. Here is an example: Rob Reiner, Jonathan Lynn, Bruce Beresford, Jonathan Demme, Adrian Lyne This is a one-dimensional list. While working on this list, you may decide to create a video collection and make the above items into a formal list. A typical movie provides such information as its length, its rating, the year it was released, etc. To create such a list, you would group items by categories. One category may contain the titles of the videos. Another category may contain the names of the directors, and so on. To better organize a list, you may create each category, then enter the value of each category that corresponds to a particular video. Here is an example:
This type of list is called a table: A table is a two-dimensional list that contains one or different categories of items and each category is represented with a particular value. A category of values is also called a column. Under each category, you may have a group of values that belong to the same entry. Such a group of values is called a row or a record. In the above table, the values "A Few Good Men", "Rob Reiner", "1992", "138 Minutes", "VHS", and "R" constitute one row one record.
To make it easy to create a type of list like the above, Microsoft Excel provides a ready-made arrangement of columns and rows. To easily identify each category of the list, a column is created and displays one or two letters or characters. The most left category has a label of A. The second has a label of B, and so on. The area that displays the label of a column can be referred to as the column header. There are 255 of these columns in a document and the last one is labeled IV  When you start a document, also called a workbook, in Microsoft Excel, the application makes all these columns available. You can use all of them or just a few, but they are always available.
At times you will almost want to alter the display of a column or various columns. You have to select that column or the group of columns first. Another reason you may need to select a column or a group of columns is because you would need to take some action on it. Some of these issues will be addressed soon, some others will be reviewed as we move on. You can select a column or a group of columns using the mouse, the keyboard, or a combination of both. To select a column, you can click (with the left mouse button) a column header; it would get selected and all small boxes under it. To select a column using the keyboard, click anything under it, then press and hold Ctrl. While Ctrl is down, press the Space bar and release Ctrl. You can also select more than one column. Selecting columns in a range consists of selecting adjacent columns. To perform this type of selection, you can use either the mouse or a combination of the mouse and the keyboard. To select columns in a range, click one column header and hold the mouse down. Then drag in the direction of the range. Alternatively, to select a range of columns, click one column at one end of the desired range. Press and hold Shift. Then click the column at the other end, and release the mouse. Random selection consists of selecting columns that are not adjacent. For example, this allows you to select columns B, D, and H. To do this, click one column header, press and hold Ctrl. Then click each desired column header. When you have selected the desired columns, then release the mouse.
To display the information under it, a column uses a set width. The width of a column is the distance from its left border to its right border. By default, when Microsoft Excel comes up, all columns use the same width. You are allowed to change the width of one column or a group of columns. To change a column's width, you can right-click it, select Column Width... from the shortcut menu, type the desired width and press Enter. If you want to make sure that all data in the column always displays, you can ask Microsoft Excel to resize the whole column according to the widest entry in the column. To achieve that, you would click any box under that column, then double-click the short line that separates the column header from the column on its right (if you are on the most right column, that will be the line that serves as the column's right border). The other alternative you have is to click and hold the short line that separates a column to its right neighbor, drag left or right until the small yellow box displays the width value you desire, then release the mouse. You can also resize a group of columns. First, select the columns you want to work on and proceed as if they were one column.
When working on a list, you don’t always need all columns displaying all the time. You can hide a column whose presence is not required at a particular time. In Microsoft Excel, you can hide one or more columns. To hide a column, while the column or a box under that column is selected, on the main menu, click Format -> Column -> Hide. The column will disappear from the screen but it is not deleted. To unhide the column, on the main menu, click Format -> Column -> Unhide.
As mentioned for the list of videos above, when creating a list, you typically enter a necessary piece of information under each column until you reach the right end of the list. The values that share the same line are called rows. To make it easy with the creation of a list, Microsoft Excel provides ready made rows and each is identified with a number. The most top row is numbered 1. The row under it is named 2, and so on. The gray area that displays the number of the row can be referred to as the row header. Microsoft Excel provides 65536 rows:  You can use just a few of them for your assignment but all of them are always available.
As done with columns, you can select one row or a group of rows. You can also select rows at random. You can perform selections using the mouse, the keyboard, or a combination of both. To select a row, you can just click its row header. To select a row using the keyboard, click any box on its right, press and hold Shift. While Shift is still down, press the Space bar and release Shift. To select a range of rows, you can click one row header and hold the mouse down. Then drag in the direction of the range. Alternatively, to select a range of rows, click one row at one end of the desired range. Press and hold Shift. Then click the row at the other end, and release the mouse. To select rows at random, click one row header, press and hold Ctrl. Then click each desired row header. When you have selected the desired row, then release the mouse.
To display the contents of boxes on its right, a row uses a set height. The height is the distance from the top to the lower borders of the row. To change the height of a row, you can position your mouse on its lower border, click and drag down until you get the desired height. Then release the mouse. You can also right-click a row header and click Row Height. This calls the Row Height dialog box that allows you to enter the numeric value of the desired height and press Enter. After selecting various rows, you can also change the heights of a group of rows.
As mentioned earlier, Microsoft Excel makes all of the rows of a sheet available while you are working. If at a certain time you don't need to display a certain row, you can hide it. In the same way, you can hide more than one rows. To hide a row, while that row or a box on its right is selected, on the main menu, click Format -> Row -> Hide. The row will disappear from the screen without being deleted. To unhide the row, on the main menu, click Format -> Row -> Unhide.
The white boxes under the column headers and on the right side of the row headers are called cells. The cells are probably the most important objects of a list because they display the actual values of the list. In Microsoft Excel, you cannot change the label on either a column header or a row header. Because of this, if you want to create your own column headers with labels you can easily identify, you must use one or more of the cells under the column headers. In the same way, you cannot change the numbers used to identify the rows. Instead, you can use the cells on the right side of the row headers to enter new values. This means that the primary job of cells is to receive or display values that make up a list. A cell is the intersection of a column and a row. When Microsoft Excel starts a workbook, it creates 255 * 65536 = 16,711,680 cells. You can use just a few of them, as will usually be the case, or you can use as many as you want but they are always available.
|
|
Location of a cell |
|
Whatever you are doing on a cell, it is always important to know what cell you are working on. The minimum piece of information you need about a cell is to know which one you are using at a particular time. To make this recognition a little easier, each cell has an address also called a location. This address or location also serves as the cell's primary name. To know the location of a cell, you refer to its column and its row. The combination of the column and the row's labels provides the address or name of a cell. When you click a cell, its column header receives a border thicker than the other column headers. In the same way, the row header of a selected cell is thicker than the other row headers. To know the name of a cell, you can refer to the Name Box, which is located at the intersection of columns and rows' headers: |
 |
|
Cells Selection |
|
Before doing anything on a cell or a group of cells, you must first select it. Selecting cells is almost equivalent to highlighting a word in a text document. And like a text document, various techniques are available here. The Active Cell Other Cells Selections To select one cell, just click it. To select another cell, click the new one. To select cells under the same column, click and hold your mouse on one cell, then drag down (or up) to the last cell. To select cells on the same row, click one of the cells, then drag right (or left) to the last cell. To select cells at random, click one of the cells involved in the selection (any of the cells), press and hold Ctrl, then click each one of the desired cells. When the cells have been selected, release Ctrl. To select cells on the same row using a combination of the mouse and keyboard, give focus to a cell on one end, press and hold Shift, then click the cell that will be at the other end. Release Shift. To select more than one cell, press and hold Shift. If you press the right arrow key, the currently highlighted cell and the cell to its right would be selected. If you press the down arrow key, the current cell and the cell under it will be highlighted. You can also press the left or up arrow key. You can press the arrow key many times in the direction of your choice. Once you have achieved the selection you want, release the Shift key. To select all cells on a worksheet, you can press Ctrl + A. Alternatively, you can click the button at the intersection of the column header and row headers. |
|
|
|
|
The Width of a Cell |
|
A cell in a Microsoft Excel worksheet doesn't have dimensions of its own. It uses those imposed to it. Since at least one cell in the worksheet always has focus, to change the column width under which that cell is selected, on the main menu, you can click Format -> Columns. You have a lot of options:
|
|
|
|
|
Cells Names |
|
We saw that a cell, each cell, has a name, which is also its location, which is provided by a combination of its parent column and its parent row. Instead of using the column and row to identify a cell, Microsoft Excel allows you to change the name of a cell to your liking. In fact, you can combine cells and name the group to anything you like. To change the name of a cell, first click it. Then, in the Name Box, replace the name with the desired name and press Enter. Alternatively, to create a new name for a cell, first select it. Then, on the main menu, click Insert -> Name -> Define... In the Names In Workbook text box of the Define Name dialog box, type the desired name and click OK. To change the name of a group of cells, first select them, using the techniques we learned for selecting cells. Then, in the Name Box, replace the string with the new name. In the same way, to create a name for a group of cells, after selecting them, on the main menu, click Insert -> Name -> Define... In the Names In Workbook text box of the Define Name dialog box, type the desired name and click OK. |
|
|
|
|
Lessons Conventions |
|
|
| Instruction | Means |
| Click G5 | Click cell G5 |
| Select B2:F8 | Select cells from B2 to F8, and that will include all cells in the range B2 to F8 |
| Select cells D4, B10, A2, and H16 | Select only those cells at random |
| Click cell F4 | Using your mouse, click cell F4. On the other hand, if I write press F4, I mean using your keyboard, press function key F4. Remember that "click" refers to using the mouse and "press" refers to using the keyboard |
| Select row 4 | Position the mouse on the row header which is the gray box where the row number is displaying, then click |
| Select rows 4 and 5 | Use one of the methods we learned to select the row headers |
| Select rows 4:8 | Use one of the methods we learned to select the row headers |
| Select column E | Position the mouse on the column header which is the gray box where the column letter(s) is displaying, then click |
| Select columns D and F | Use one of the methods we learned to select the column headers |
| Select columns C:H | Use one of the methods we learned to select the column headers |
|
Worksheets |
|
Introduction |
|
The group of cells that constitute a document is referred to as sheet and those cells are spread all over it. For this reason, a document whose main purpose is to present a list as a group of cells is referred to as spreadsheet. As you may realize, Microsoft Excel's primary purpose is to help you create lists using readily available cells spread on a sheet that resembles a piece of paper. For this reason, Microsoft Excel is referred to as a spreadsheet application. A list created on sheet is called, in Microsoft Excel, a worksheet. When Microsoft Excel opens, it starts with three worksheets. If you don't need all of them, you can delete those that appear useless. You can also add new worksheets as you see fit. If you want Microsoft Excel to always start with less or more worksheets, you can change its default settings in the Options dialog box accessible from the Tools menu. Even when they belong to the same document, worksheets can be treated as independent entities, although it is more suitable that they be treated as a unit. |
|
Selecting a Worksheet |
|
In some circumstances, you will need to perform a general action on a worksheet. Before doing this, you may need to select the contents of the whole worksheet first. Since there are usually many worksheets presented to you, each is represented by a tab on the lower left corner. Therefore, to select a worksheet, you can click its tab. To select a worksheet using the mouse, you can press and hold Ctrl. Then press either Page Up or Page Down. Once the desired worksheet has been selected, you can release Ctrl. If you have many worksheets, to select a range of worksheets, click a tab that is considered one end of the range. Press and hold Shift, then click the tab at the end of the range and release Shift. To select worksheets at random, click one of the desired worksheets. Press and hold Ctrl. Then click each desired worksheet. When the selection has been made, release Ctrl. |
|
|
|
|
Worksheets Names |
|
The starting worksheets are named Sheet1, Sheet2, and Sheet3. You can change any or all of these worksheet names. To rename a worksheet:
|
|
|
|
|
Worksheets Sequence |
|
By default, worksheets are positioned in a numbered format that makes it easy to count them. More often you will find that, after creating a few of them, you are not satisfied with their positions. You should be able to reposition them in a manner that suits your particular scenario. To move a worksheet, click and hold the mouse on its tab, then move the mouse in the direction of your choice. While you are moving the worksheet, the mouse pointer will turn into a white piece of paper and a small down-pointing triangle will guide you. Once the small triangle is positioned in the desired location, release the mouse. |
|
|
|
|
Adding New Worksheets |
|
As mentioned already, when Microsoft Excel comes up, it displays three worksheets. We also mentioned that you can change this default number in the Options dialog box. You can add a new worksheet anytime if you judge it necessary. Some workbooks are quite complete with just one worksheet, but others need as many worksheets as possible. The number of worksheets you use in a particular workbook is conditioned by your needs and the memory of your computer. The easiest way to insert a worksheet in your workbook is by clicking Insert on the main menu, and then clicking Worksheet. This would add a new sheet on the left side of the active worksheet. You can also add a blank or formatted worksheet. Right-click the sheet that will precede the new worksheet and click Insert… From the Insert dialog box, you can choose to insert a blank worksheet or insert one of the existing templates as a worksheet. |
|
|
|
|
Removing Worksheets |
|
As your work progresses, you will decide how many worksheets you need for your particular workbook. Just as we learned to add worksheets, you can delete or remove the worksheets you don't need anymore. Since a worksheet is not a file, when you delete a worksheet, it is permanently gone; that is why you will get a confirmation message. To delete a workbook, you can right-click its tab and click Delete. |
|
|
|
|
Hide and Unhide Worksheets |
|
As mentioned already, by default, Microsoft makes three worksheets available to you. We also reviewed how you can add new ones or delete some of them. Instead of deleting a worksheet, you can hide it for any reason you judge necessary. If the workbook contains more than one worksheet, you can hide one or more worksheets or even the entire workbook. To hide a worksheet in a workbook, click any cell in that worksheet to make it active. On the main menu, click Format -> Sheet -> Hide. The worksheet’s tab will disappear from the screen although it is not deleted. To hide a group of worksheets, selecte the desired worksheets. Then, on the main menu, click Format -> Sheet -> Hide. To unhide the hidden worksheets, on the main menu, click Format -> Sheet -> Unhide. You are presented with a list of the hidden worksheets. If you have more than one hidden sheet, select the desired worksheet and click OK. |
|
|
|
|
Worksheets and the Web |
|
As the Internet has become significantly important and unavoidable, you can publish your data and make it available to almost any computer in the world. An intranet is a privately owned, company centered, group of computers connected inside of a particular company using the same technology as the Internet to share and exchange information. This allows you to publish your information inside of the company even if different employees use different computers and operating systems. In Microsoft Excel, you can publish just one worksheet or the whole workbook. To perform publishing, first decide on what you want to publish, a worksheet or the whole workbook. We will save the current workbook as a web page. |
|
|
|
|
The Workbook |
Microsoft Excel doesn't just stop at mere lists. Once a list has been created, the application allows you to perform various types of operations related to a list. It also can be used to process business and financial calculations of various kinds. To make it even more powerful, you can create more than one list; that is, you can use more than one sheet. The group of worksheet that constitute the lists of a document is called a workbook. |
|
|
|
| Previous | Copyright © 2002-2007 FunctionX, Inc. | Next |
Practical Learning: Introducing Lists
Practical Learning: Controlling Rows Height
C
From now on, unless specified otherwise, the following conventions will be used
