A Few Additional Points about Microsoft Excel

  • Several of you have asked why, when you print out your spreadsheet, don't the gridlines show up. By default gridlines and row and column headings are not printed. You can choose to have them printed by going to the File Menu and choosing Page Setup... Choose the Sheet tab and there are checkboxes for Gridlines and Row and column headings.

  • There are two different ways of removing cells and cell contents. You can choose Edit Clear to remove contents of cells but leave the cell where it is. Therefore, other cells in the worksheet that refer to the cleared cell will not damaged. However, if you choose Edit Delete..., then you will remove that cell and all cells in the worksheet which refer to that cell will have reference errors, #REF!.

  • To select a range of cells you may either use the mouse or the keyboard. To use the keyboard to select a range of cells, move to the upper left corner of the range and hold down the SHIFT key. Then move the arrow keys until you select the desired range.

  • Recall that cell references are automatically changed if we insert or delete a row or column.

  • You will need to remember how relative cell references are changed when we copy a formula from one cell to another in a worksheet.

    Final Exam Review

    General Concepts
    1. A spreadsheet is a computerized version of an accountant's ledger.

    2. It is divided into rows and columns. The rows are identified by numbers, and the columns are identified by letters.

    3. The intersection of a row and column is called a cell. It is identified by its column letter followed by its row number.

    4. Recall that a single spreadsheet within an Excel document is called a worksheet. The collection of multiple worksheets is called a workbook. Recall that the default number of worksheets in a workbook is 3. We can add multiple worksheets to our workbook. Recall that the title of the workbook is displayed in the Title Bar at the top of the Microsoft Excel window.

    5. The easiest ways to make a worksheet active are to either click within the worksheet or click that worksheet's tab.

    6. We may open an Excel workbook by either using the File Menu and selecting Open... or by an icon on the Standard Toolbar.

    7. We can display each worksheet in a workbook in its own window.

    8. The active cell within a worksheet is identified by a heavy border. You can change the active cell by either moving the mouse pointer to the other cell and clicking or by using the arrow keys to move to the other cell.

    9. Recall that like in most other Windows applications the right mouse button will bring up a context-sensitive shortcut menu, that is a menu based on where the mouse is pointing.

    10. You also have available the Office Assistant. The Office Assistant can be summoned by clicking the Office Assistant icon on the Standard Toolbar. One purpose of the Office Assistant is to allow you to ask it questions and respond with an appropriate answer.

    11. We have discussed the ideas of Object Linking and Embedding. The difference between the two is that in Object Linking the destination of the link contains an external reference to the source whereas in Object Embedding the destination contains a copy of the soure with no reference to it. So Object Linking would be used if you wanted to include the same Excel spreadsheet simultaneously in two other applications which support OLE.

    12. Recall that if you have Microsoft Excel version 5.0/95, then if you saved an Excel document from the computers in the lab, and then tried to take it to your machine, then your machine wouldn't be able to read it. That is because earlier versions of Microsoft Excel are not able to read documents saved in later versions. In order for both versions of Excel to be able to read the document, you must choose the Excel 97 5.0/95 option that you find when you bring up the Save As... dialog box.

    13. The Window Menu contains a list of all open workbooks with the active workbook indicated by a check mark.

    14. We use Page Setup under the File Menu to change from a portrait to a landscape orientation or change the margins for a printed worksheet.
    Editing within a worksheet
    1. Recall that a cell can contain labels or values. Values can included numbers, arithmetic expressions, formulas, functions, etc.

    2. If we place text in a cell which is too long for the cell to contain, then if the cell to its right is empty, the number will run into that cell. If the cell to its right is not empty, then the number is truncated starting at the right-most characters to fit into the cell.

    3. When you place a value or a label into a cell, and that cell is active, its contents are displayed in the formula bar. Its address is displayed in the Name box to the left of the formula bar. One way to edit the contents of a cell is by making changes to the contents displayed in the formula bar.

    4. When you open a new Excel document, there is a default column width and row height set. You may change these settings under the Format Menu. However, there are shortcuts. If you change the font size of text on a row its height will automatically be adjusted. If you have cells in your column whose contents exceed the width of the column, then if you double-click on the column border, the column width will automatically be adjusted to fit the longest cell in the column.

    5. Recall that you can insert rows, columns, or worksheets under the Insert Menu. You may change the name of a worksheet by going to its tab and double-clicking its current name. The name will become highlighted, and you may then type the new name of the worksheet.

    6. You may delete entire rows or columns under the Edit Menu by choosing the Delete... option. You may delete worksheets by choosing the Delete Sheet option.

    7. The Edit Delete combination will allows to remove the contents of a particular cell which will change the contents of any cells in the worksheet which depended on the cell we deleted.

    8. We may format text the same way we did in Microsoft Word. We can find formatting commands in the Formatting Toolbar, the Format Menu, and shortcut menus. We can change the textcolor using a button on the Formatting Toolbar.
    Putting Microsoft Excel to work
    1. There are many features of Microsoft Excel that are useful to us. We have discussed some of them this semester, but there are many more that are available. Let us examine some of the features about using Microsoft Excel that we have discussed.

    2. A range of cells is a rectangular block of cells identified by its upper left corner and lower right corner. A cell range can be as small as one cell or as large as the entire worksheet.

    3. Recall that we can place values or labels into a cell. One of the values that we can place in a cell is arithmetic expressions involving cell contents. We discussed two types of referencing of cell contents, absolute and relative. It is very important to understand the distinction between these two not only for this class, but if you plan to use Excel to do work at your home or business. Recall that we copy cell contents from the source cell(s) to the destination cell(s).

    4. Relative cell referencing means that when we copy a formula from one cell to another, cell references will change. For example if the formula =B1+C1 is in cell A1, and we copy it to cell F4, then the contents of cell F4 will be =G4+H4.

    5. Absolute cell referencing means that when we copy a formula from one cell to another, cell references will not change. We indicate that a reference is absolute by the $ sign. We can make both the column letter and row number absolute if we want.

    6. Recall that we can copy the contents of a cell or a group of cells to another location within the same worksheet or into another worksheet. Recall that a simple copy and paste will copy the contents of the cell (value, label, formula, etc.) to the destination. This is a disadvantage if we simply want to copy the results of formulas. To do this choose Copy Paste Special... and then select Values before you paste. This will copy the numbers in the cell to the destination. We can invoke the Copy command by selecting Copy under the Edit Menu, clicking a button on the Standard Toolbar, or by hitting CTRL+C.

    7. Recall that when you select a range of cells and then select the Copy command, the selected range is surrounded in Las Vegas lights (we could also call this a flashing border) which indicate that the contents of the cell have been copied to the clipboard.

    8. The Copy Paste operation is an example of Object Embedding. Recall that if we use the Copy Paste Special... option then we are presented with a dialog box which an option for Paste Link. If we select this option and click OK, then that is an example of Object Linking. Recall that when we do this, any change that is made to the source will affect the destination, that is if we place a chart in a Microsoft Word document which is linked to one in an Excel document, then any change we make to the Excel document will be mirrored in the Microsoft Word document. Recall that changes made to data ranges will also affect the chart drawn in the Excel document.

    9. A function in Excel is a predefined computational task such as summing up a group of cells or averaging a group of cells.

    10. There is also a shortcut for copying formulas to contiguous cells. Recall that we invoke the drag and copy feature of Microsoft Excel when we move the mouse pointer to the lower right corner of the cell we wish to replicate until the mouse pointer turns into a plus and then drag the mouse pointer across a row or down a column and then let up on the mouse pointer.

    11. A command such as a formula will affect all cells in the selected range.

    12. All formulas must begin with an equal sign. Recall that we can place arithmetic operations in a cell in Microsoft Excel. Examples of valid arithmetic operations are addition (+), subtraction (-), multiplication (*), division (/), and exponentiation (^). Formulas can contain Cell references, Functions, and Numeric constants. All calculations are performed from left to right with the following precedence: parentheses, exponentiation, multiplication and division, and then addition and subtraction.

    13. There are several formats for numbers that are available in Microsoft Excel. One of these is the general number format which makes results look like they would on your hand-held calculator. For instance, =1/2 would result in .5. There are also other formats available including, currency and accounting. Numeric entries are right-justified by default.

    14. There are built-in functions which are at our disposal in Microsoft Excel. Some of these include SUM, AVERAGE, MIN, MAX, TODAY, and COUNT. Recall that =TODAY() displays the current date that is stored within the system. COUNT displays the number of cells within a range you specify that contain numbers. Recall that when you click the bold equal sign to the left of the Formula Bar the Name Box disappears and it is replaced by a button with the name of a function on it. If you click this button you will see the Formula Palette which contains a description of each argument.

    15. The easiest way to display formulas in cells is by the combination of the CTRL button and the tick mark.
    Charts
    1. Recall that charts allows to get a graphical representation of data.

    2. There are several types of charts which are available to us in Microsoft Excel. Some of these include Pie charts, 3D Column charts, combinations of these, etc. We also have a line chart which can be used to display time related information.

    3. A line chart shows trends in data at equal intervals. This is especially useful to plot data that changes over time.

    4. A pie chart shows the proportional size of items that make up a data series to the sum of the items. A pie chart has only one data series.

    5. A bar chart illustrates comparisons among individual items. Categories are organized vertically, values horizontally, to focus on comparing values and to place less emphasis on time.

    6. A stacked bar chart shows the relationship of individual items to the whole.

    7. After a chart is created with the Chart Wizard, we can modify things such as Chart Type, Legend, Formatting of the Title, Size, Position, etc.

    8. Recall that after we select a range the F11 key will create a default chart in its own worksheet. To change the default chart type click the right mouse button in an open area of a chart. Choose Chart Type... Pick the type of chart you want and then hit the Set as default chart button.

    9. To create a chart first select a range and then click the Chart Wizard. Recall that a wizard is a feature of Microsoft Excel which asks a series of questions and then creates a template based on the answers. The Chart Wizard is a four step process. At any step we may click Finish and accept Microsoft Excel defaults.

    10. Recall that to select noncontiguous ranges hold down the CTRL key.

    11. The easiest way to delete a chart is to select it and then hit the DEL key.
    The Internet
    1. The Internet is basically a network of networks connecting computers throughout the world. Its origins can be traced back to the reaction of the United States to the launching of the Sputnik rocket by the Soviet Union. It began as a network of four computers in 1969 and has grown ever since.

    2. The basic language of the Internet is HTML (HyperText Markup Language). In order for you to view an HTML document, you must use a program which will interpret this language. These programs are called Web browsers. Examples of Web browsers include Netscape Communicator and the Internet Explorer.

    3. In order to locate an HTML document on the Internet, you need to specify its location. We do this by using a URL (Uniform Resource Locator). This contains a method of access called a protocol, the Internet address, an optional directory path and document name. The most common protocol used is HTTP (HyperText Transfer Protocol).

    4. Another common protocol used if FTP (File Transfer Protocol). It is a protocol used to transfer files from one node (location) on the Internet to another. The process of transferring files from your machine to a remote machine is called Uploading. Tranferring a file from a remote machine to your machine is called Downloading. Downloading carries with it the risk of downloading a computer virus.

    5. Both a modem and communications software is required for you to communicate with a remote computer from your home.

    6. You can see the source code of a Web document in Netscape by going to the View Menu and choosing Page Source. You can see the source code of a Web document in Internet Explorer by going to the View Menu and choosing Source.
    7. HTML documents, also referred to as web documents, contain a head section and a body section HTML documents can contain links to other HTML documents. These are called Hyperlinks.

    8. In order to locate information on the Internet that we need, we have programs called Search engines. Examples of these are Yahoo, Lycos, Alta Vista, and InfoSeek.

    GOOD LUCK!