Advanced Spreadsheet Techniques

This unit has four short lessons, but the topics fit nicely into two categories:

  1. Using a spreadsheet to solve real-world problems
  2. Advanced spreadsheet features

Lesson 1 explains the steps in planning a large spreadsheet. These steps are:

  1. Decide what it is you want to accomplish.
  2. Determine what your input data is (raw data and reference values).
  3. Format and label the information.
  4. Determine the first (and possibly second and third) order calculations.
  5. Chart any graphs that are necessary.

Lesson 2 explains how to use a spreadsheet for decision making. We said at the beginning of Unit 1 the two main uses of an electronic spreadsheet are:

  1. to perform calculations that provide an answer, and
  2. to perform what-if scenarios.

This lesson shows how what-if scenarios can be used to generate the data you need to make a decision.

Also in this lesson are the VLOOKUP() and CHOOSE() functions. These functions are conceptually harder to understand than the other built-in functions already discussed. These functions operate on data which is found inside the spreadsheet.

Lesson 3 is short but probably the most challenging and useful topic in this module. This lesson explains how to create an amortization table. An amortization table shows the exact cost of borrowing money. Not just the monthly payment, but how much you are paying in interest each month. With a slight modification an amortization table can be used to calculate the effect of making extra payments each month.

Lesson 4 demonstrates a technique for integrating a Word and Excel document. It shows how you can copy a section of a spreadsheet from an Excel document to a Word document. What is amazing is that you can also link a section of a spreadsheet to a Word document. That is, the section appears as though it were copied to the Word document, but any changes to the spreadsheet section are propagated to the Word document. If not useful, it is certainly impressive!

The assignment at the end of this unit is on-line. That is, it is a question-and-answer type assignment like you had for the Computer Concepts and Internet modules.