First semester 2000-2001

	 Oct			Nov		       Dec
 S  M Tu  W Th  F  S    S  M Tu  W Th  F  S    S  M Tu  W Th  F  S
 1  2  3  4  5  6  7             1  2  3  4                   1  2
 8  9 10 11 12 13 14    5  6  7  8  9 10 11    3  4  5  6  7  8  9
15 16 17 18 19 20 21   12 13 14 15 16 17 18   10 11 12 13 14 15 16
22 23 24 25 26 27 28   19 20 21 22 23 24 25   17 18 19 20 21 22 23
29 30 31               26 27 28 29 30         24 25 26 27 28 29 30
In Labs we will go through the following 9+2 projects.
  1. Creating a worksheet and embedded chart.
    In this project students are introduced to Excel terminology, the Excel window, and the basic characteristics of a worksheet and workbook. Topics include starting and quitting Excel; entering text and numbers; selecting a range; using the AutoSum button; copying using the fill handle; changing the font size; formatting in bold; centering across columns; using the AutoFormat command; charting using the ChartWizard; saving and opening a workbook; editing a worksheet; using the AutoCalculate area; and using the Excel Help system.
  2. Formulas, functions, formatting, and web queries.
    This project continues to emphasize the topics presented in the first project and presents some new ones. The new topics include: formulas, verifying formulas, changing fonts, adding borders, formatting numbers, conditional formatting, changing the widths of columns and heights of rows, spell checking, e-mailing from within an application, and alternative types of worksheet displays and printouts. Finally, this project covers Web queries to obtain real-time data from a Web site.
  3. What-if analysis, charting, and working with large worksheets.
    This project introduces you to techniques that will enhance your abilities to create worksheets and draw charts. You will learn about other methods for entering values in cells and formatting these values. You also will learn how to use absolute cell references and how to use the IF function to assign a value to a cell based on a logical test.

    In the previous projects, you learned how to use the Standard and Formatting toolbars. Excel has several other toolbars that can make your work easier. One such toolbar is the Drawing toolbar, which allows you to draw shapes and arrows and add drop shadows to cells you want to emphasize.

    Worksheets normally are much larger than those created in the previous projects, often extending beyond the size of the window. Because you cannot see the entire worksheet on the screen at one time, working with a large worksheet can be difficult. For this reason, Excel provides several commands that allow you to change the display on the screen so you can view critical parts of a large worksheet one at a time. One command lets you freeze the row and column titles so they always display on the screen. Another command splits the worksheet into separate window panes so you can view different parts of a worksheet.

    From your work in Project 1, you are aware of how easily charts can be created. This project covers additional charting techniques that allow you to convey your message in a dramatic pictorial fashion.

    When you set up a worksheet, you should use as many as cell references in formulas as possible, rather than constant values. The cell references in a formula often are called assumptions. Assumptions are values in cells you can change to determine new values for formulas. This project emphasizes the use of assumptions and introduces you to answering what-if questions such as, what if you decrease the marketing expenses assumption -- how would the decrease affect the projected six month net income? Being able to quickly analyze the effect of changing values in a worksheet is an important skill in making business decisions.

  4. Creating static and dynamic web pages using Excel.
    Excel 2000 provides fast and easy methods for saving workbooks as Web pages that can be stored on the World Wide Web, a company's intranet, or a local hard drive. A user then can display the workbook using a browser, rather than Excel.

    You can save a workbook, or a portion of a workbook, as a static Web page or a dynamic Web page. A static (noninteractive) Web page is a snapshot of the workbook. It is similar to a printed report in that you can view it through your browser, but you cannot change (or modify) it. A dynamic (jnteractive) Web page includes the interactivity and functionality of the workbook, such as formulas, charting, and the recalculation features of Excel.

    In this Web feature you will save a workbook as a static Web page and view it using your browser. Then you will take the same workbook and save it as a dynamic Web page and view it using your browser. After displaying the dynamic Web page in your browser, you will change certain values to test the Web page's interactivity and functionality.

  5. Financial functions, data tables, amortization schedules, and hyperlinks.
    In this project you will learn how to use financial functions and learn more about analyzing data in a worksheet. Topics include applying the PMT function to determine a monthly payment; the PV function to determine the amount due on a loan at the end of the year; adding as hyperlink to a Web page; using names to reference cells; protecting a worksheet; and analyzing data by: (1) goal seeking, (2) creating a data table, and (3) creating an amortization schedule.
  6. Creating, sorting, and querying a worksheet database.
    A worksheet database, also called a database or list, is an organized collection of data. For example, a list of club members, a list of students attending a college, an instructor's grade book, and a list of company sales representatives are databases. In these cases, the data related to a person is called a record, and the data items that make up a record are called fields. In a database of sales representatives, each one would have a separate record; some of the fields in the records might be name, hire date, age, and gender. A database record also can include formulas and functions. A field in a database that contains formulas or functions is called a computational field. A computational field displays results based on other fields in the database.

    A worksheet's row-and-column structure can be used to organize and store a database. Each row of a worksheet can be used to store a record and each column to store a field. Additionally, a row of column titles at the top of the worksheet can be used as field names that identify each field. In this project you will learn how to create, sort, and filter a database. Topics include using a data form to create and maintain a database; creating subtotals; finding, extracting, and deleting records that pass a test; outlining a worksheet; and applying a database and lookup functions.

  7. Creating templates and working with multiple worksheets and workbooks.
    Many business-type applications, such as the one described in the Case Perspective of this project, require data from several worksheets in a workbook to be summarized on one worksheet. Suppose, for example, your firm maintains data for three different units within the company on three separate worksheets in a workbook. You can click the tabs at the bottom of the Excel window to move from worksheet to worksheet. You can enter formulas on one worksheet that reference cells found on the other worksheets, which allows you to summarize worksheet data. The process of summarizing data found on multiple worksheets on one worksheet is called consolidation.

    Another important concept is the use of a template. A template is a special workbook or worksheet you can create and then use as a pattern to create new, similar workbooks or worksheets. A template usually consists of a general format (worksheet title, column and row titles, and numeric format) and formulas that are common to all the worksheets. One way to create the workbook is to first create a template, save it, and then copy it as many times as necessary to a workbook.

    Several other techniques are introduced in this project, including rounding, custom format codes, creating a format style, adding comments to a cell, headers and footers, using WordArt to create a title, using the Find and Replace commands, various print options, and linking workbooks.

    In Project 6 you will learn to create a template and consolidate data into one worksheet. Topics include building and copying a template, multiple worksheets; 3-D cell references; customized formats; styles; charting; WordArt; adding notes to a cell; adding a header and a footer; creating and modifying lines and objects; changing page setup characteristics; and finding and replacing data.


  8. Linking an Excel worksheet to a Word document.
    With Microsoft Office 2000, you can incorporate parts of documents or entire documents, called objects, from one application into another application. For example, you can copy a worksheet created in Excel into a document created with Word. In this case, the worksheet in Excel is called the source document (copied from) and the document in Word is called the destination document (copied to). Copying objects between applications can be accomplished in three ways: (1) copy and paste; (2) copy and embed; and (3) copy and link.

    All of the Microsoft Office applications allow you to use these three methods to copy objects between applications. The first method uses the Copy and Paste buttons. The latter two use the Paste Special command on the Edit menu and are referred to as Object Linking and Embedding, or OLE.

    You would use copy and link over the other two methods when an object is likely to change and you want to make sure the object reflects the changes in the source document or if the object is large, such as a video clip or sound clip. Thus, if you link a portion or all of a worksheet to a memorandum, and update the worksheet monthly in Excel, any time you open the memorandum in Word, the latest updates of the worksheet will display as part of the memorandum.

  9. Using Visual Basic for Applications (VBA) with Excel.
    Before a computer can take an action and produce a desired result, it must have a step-by-step description of the task to be accomplished. The step-by-step description is a series of precise instructions called a procedure. A procedure also is called a program or code. The process of writing a procedure is called computer programming. Every Excel command on a menu and button on a toolbar has a corresponding procedure that executes when you click the command or button. Execute means that the computer carries out the step-by-step instructions. In a Windows environment, the instructions associated with a task are executed when an event takes place, such as clicking a button, clicking a command, dragging a scroll box, or right-clicking a cell.

    Because a command or button in Excel does not exist for every possible worksheet task, Microsoft has included a powerful programming language called Visual Basic for Applications. The Visual Basic for Applications (VBA) programming language allows you to customize and extend the capabilities of Excel.

    In this project, you will learn how to create macros using a code generator called a macro recorder. A macro is a procedure made up of VBA code. It is called a macro, rather than a procedure, because it is created using the macro recorder. You also will learn how to add buttons to toolbars and commands to menus and associate these with macros. Finally, you will learn the basics of VBA including creating the interface, setting the properties, and writing the code.

    This project shows you how you can automate tasks by incorporating VBA into their workbooks. Topics include protecting worksheets with passwords; using the macro recorder; executing macros; security; adding buttons to toolbars and commands to menus; creating a user interface by adding controls to a worksheet; setting properties; writing VBA code; using the Visual Basic Editor; validating incoming data; and using event-driven programs.

  10. Auditing, data validation, and solving complex problems.
    This project introduces you to auditing a worksheet, data validation, and solving complex problems. Auditing allows you to check both the cells being referenced in a formula and the formulas that reference a cell. Auditing a worksheet is especially helpful when you want to learn more about a workbook created by someone else or you want to verify the formulas in a newly created workbook.

    Data validation allows you to define cells in such a way that only certain values are accepted. This feature also allows you to display a prompt message when the user selects the cell and an error message if the user attempts to enter a value outside of a valid range that you define.

    The majority of this project introduces you to solving complex problems that involve changing values of cells to arrive at a solution, while abiding by constraints imposed on the changing values. Topics include: using the Auditing toolbar; using data validation; goal seeking; using Solver to solve multiple problems; saving multiple scenarios with Scenario Manager; analysis of Solver answers and multiple scenarios; and workbook properties.

  11. Importing external data, tracking and routing changes, and creating data maps, pivotCharts, and pivotTables.
    In today's connected world, the ability to use and analyze data from a wide variety of sources is a necessity. In this project, you will learn how to import, or bring, data from various external sources into an Excel worksheet and then analyze that data.

    Suppose that you routinely do business with a company that stores data in text format rather than in a workbook. To make use of that data, you first input the data, and then format and manipulate it. Businesses also receive data in various database formats, such as Microsoft Access tables, and from Web pages.

    Businesses routinely create charts to represent data visually. A data map is a type of chart that is a geographic map. It displays logistical data such as demographics, inventories, or marketing by state, region, or country.

    Another visual way to analyze data is through the use of PivotCharts and PivotTables. A PivotChart is an interactive chart that provides the user with ways to graphically analyze data by varying the fields and categories to present different views. Excel creates and associates a PivotTable with every PivotChart. A PivotTable is an interactive view of worksheet data that gives you the ability to summarize the database and then rotate the table's row and column titles to show different views of the summarized data. An inexperienced user with little knowledge of formulas, functions, and ranges can employ powerful what-if analyses of the data simply by clicking a field from a list of fields in a PivotChart or PivotTable.

    Other techniques introduced in this project apply to issues of multiple users of the same workbook, including routing, tracking changes, and comments. If you want several people to look at a workbook and make comments, you can route it, or pass the workbook around electronically. You can track changes to a workbook by displaying who changed what data, in which cell, and when. Comments, or descriptions, which do not regularly display as part of the worksheet data, can be added to the workbook itself to alert the recipients to special instructions, and later edited or deleted.


Unless otherwise specified each project takes one lab period.

The Lectures will follow the Chin/Harris/Roberts book (Green Book) as follows.

Tentative plan:

Week 1
Chapters 1-2.

Week 2
Chapter 3 (Measurement and Descriptive Statistics)

Week 3
Chapter 4 (Multivariate Data Analysis: Correlation and Causality)

Week 4
Midterm Review and Midterm Exam

Weeks 5(1/2)-6
Chapter 5 (Regression Analysis)

Week 7
Final Review and Other Topics (to be announced).

Lecture notes will try to keep you up to date even if you can't make it to class.

Last updated on Oct 26, 2000 by Adrian German for A113