CIS 314: Advanced Software Solutions

Lesson 3: Microsoft Excel Basics

Objectives:

This course requires students to review basic functions and learn new, more advanced functions in Microsoft Office applications. This lesson discusses basic features in Microsoft Excel that the student should already know well, and introduces features required for the course project. Objectives important to this lesson:

1. Starting the Excel program
2. Excel files and commands
3. Excel-specific commands

Concepts:

As stated on the previous note page, the process to start any Office application in Windows XP should be familiar:

1. Click Start
2. Click All Programs (or Programs)
3. Click Microsoft Office
4. Click the application you need

It is also possible to start an associated application by finding a data file in Windows Explorer (or on your desktop) and double-clicking that file. Windows will open the file in the application that it has been told to use for files of its type: Word for .doc files, Excel for .xls files, PowerPoint for .ppt files, etc. Note that this file association with an application depends on filename extensions. This is one reason to make sure you save files with appropriate extensions, avoiding the temptation to leave the extensions off your filenames.

Basic Excel file concepts vary just a bit from what you learned about Word:

• What is an Excel file?
  
  Word files are typically single documents, although they may have many pages. Excel files used to be this way, but this stopped being true a few versions back. The blank file that opens when you start Excel is a workbook. Workbooks, by default, contain three separate worksheets. (Worksheets are also called spreadsheets.) It is easy to add more worksheets to your workbook if they are needed.
  Each of the three worksheets can act as a separate Excel document. However, you can also perform calculations on one worksheet based on data on another. In this way, Excel demonstrates data integration all by itself.
  
  
• How do I navigate in a worksheet?
  
  You have several choices. One is to just click on the cell you want to go to. Another is to use arrow keys or the page up and page down keys to move to cells you can't see on the current screen. You can also use the scroll bars to move quickly to different parts of a worksheet.
  
  
• How do I select all cells on a worksheet?
  
  The first Excel project tells you to click the Select All button in your current worksheet. The location of this button is not clear because it has no icon or label. It does not even look like a button, nor does it have the simulated 3D behavior of a button when it is clicked.
  It appears to be just a corner of the border of your worksheet, above row 1, and to the left of column A. In the image on the right, I have drawn a red arrow pointing to the Select All button. Yes, that's it, the beige rectangle that my arrow is pointing to.
  When you click this "button", all cells in the current worksheet are selected. Click any single cell to deselect the entire worksheet.
  
• What about Absolute vs. Relative cell references?
  
  Your text introduces a concept that previous users of Excel should already know more about, but it sidesteps an important point. When you create a formula in Excel, such as =A5*B7, you are using cell references.
  In this example, both references are relative. This means that if you were to copy this formula from the cell it is in to a cell two columns to the right, each of the references would be adjusted by two columns. In the new cell, the formula would read =C5*D7.
  If you were to copy the original formula to a cell two cells below it, your formula would be adjusted by two rows. The formula in the new cell would read =A7*B9.
  The text tells you that you can prevent this relative adjustment of your formula by making the cell references absolute. This is done by putting a $ before each element of the reference. (e.g. =$A$5*$B$7)
  What the text has not described is that you are not required to make both elements (column and row) of a cell reference absolute. You can use finer control by only making the element that you don't want to change absolute.
  
  

  =A5*B7	All relative. Excel will adjust column references if copied horizontally, row references if copied vertically.
  =$A5*$B7	Column references are absolute. Excel will only adjust row references if copied vertically.
  =A$5*B$7	Row references are absolute. Excel will only adjust column references if copied horizontally.
  =$A$5*$B$7	All absolute. Excel will not adjust column references or row references if copied.

  
  
• What is a What-if?
  
  The first Excel project creates a budget worksheet and walks you through several what-if questions. A what-if is when you change the data in some cells in a worksheet to see what effect that change will have on formulas in other cells. This is typically done to determine what effect changing items in our budget, our income, or our expenses would have on other dependent items in our worksheet. This requires that the worksheet be constructed with formulas that accurately depict dependencies, and that you make changes to data or formulas in appropriate cells.