How to Create Spreadsheets Using Microsoft Excel

Step 1: After having signed onto a computer, on which Microsoft Excel 2007 has been installed, locate the icon. The icon may be found in a number of locations, such as the desktop as a shortcut, the start menu as a recently used listing, or under all programs in the Microsoft Office folder. To ensure that the program is located, move the cursor to the start menu (indicated by the word “Start” in the bottom left of the screen. Directly above shall be a tab called “All Programs”. Under All Programs, there is a Microsoft Office File folder, in which the Microsoft Office Excel 2007 icon and title may be found. Double-click on this tab, after which the program initiates.

Step 2: A number of formulae may be applied in the application Microsoft Office Excel 2007. In the creation of spreadsheets, addition, subtraction, multiplication and division are the only operations necessary. To begin the spreadsheet, begin by organizing the data by placing titles in appropriate places. For the purposes of this tutorial, a mortgage table shall be used. The situation is as follows: “A mortgage is set up with a principal of $55 000.00 at a rate of 7.75% annual rate. The mortgage payment is $675.00 a month. Create a table representing the mortgage payments over a period of two years”.



Step 3: To enter information into a cell, simply click on the desired cell and type the information you wish to input. Continue this process and enter the titles of this spreadsheet. For the purposes of this tutorial, the titles are placed in the following sequence of cells:

A1: Mortgage Table
A2: Month
B2: Payment
C2: Rate
D2: Interest
E2: Credit
F2: Balance



Step 4: The information of the spreadsheet can only be derived when there is preliminary information from which the output can be derived. To begin, we input all information that we are provided with into the table, such as the initial balance, the months, the constant mortgage payments, as well as the constant annual rate. These values are all predictable and/or given, and will help in obtaining values not yet acquired. We shall being with months. Click on the second box beneath ‘Month’ (A4) and enter the number 1. To fill the rest of the months, simply click and drag, such that the first month and the 23 others immediately after are highlighted. Now move the cursor in the vicinity of the top right. Next to the fill command is a small downward pointing arrow. Click on this arrow and a box with various options appears. Select ‘Series…”, which is the second command from the bottom.



Step 5:Yet another box shall appear; click on the ‘linear’ option for series. Since the columns have been highlighted before hand, the ‘columns’ option shall already be selected under the “Series in” tab. Ensure that the “Step value” is set at 1 by clicking on the box and typing in 1. After these adjustments have been made, simply click OK, and the months from 1 to 24 shall be listed.

Step 6: Next, click two cells below “Payment” (B4) and type 675 into the box. Because this monthly payment remains constant throughout the entire mortgage, we must once again highlight the box (B4) and 23 boxes below it (that is, highlight B4 to B27). Similar to the last step, we must now click on the arrow immediately adjacent to “Fill”, and then select “Down” by clicking on it. What this does is re-enter the same information into all of the boxes highlighted. This spares the creator of the spreadsheet the trouble of re-entering the information manually.



Step 7: Click two cells below “Rate” and enter 0.0775. We express the value as a decimal, rather than a percentage, to avoid having to perform the added operation of dividing by 100. Now highlight all of the cells from C4 to C27 inclusive. Click on the same arrow adjacent to “Fill” mentioned in previous steps. Now simply click on “Down”. This simple process has now re-entered the rate for all months represented on the chart.

Step 8: We are now approaching the end of all previously provided information (as indicated by the question listed above). The last bit of information that we are aware of without any calculations is the starting balance. To input this, simply click on cell F3 and enter 55000.



Step 9: Now that all of the information initially provided has been transmitted into the table, the next procedure would be to input a formula which shall generate a series of values for the interest calculated. Common knowledge of mortgage rates would dictate that the interest must be found by multiplying the rate of interest by the remaining balance at the time. Interest is found as a percentage of the remaining balance. For convenience, 0.0775 has been inserted instead of 7.75% to avoid having to divide by 100 afterwards. We calculate interest by analyzing the cells which need to be operated on. For instance, F3 is the starting balance, and C4 represents the rate of interest. To obtain the interest, we multiply this rate of interest by the starting balance to get the amount of interest (a monetary value). Firstly, click on the cell into which the interest must be calculated (D4), and then type in the following sequence into the function box above: =F3*C4/12 (and then press enter). The reason we write the cells rather than the numbers within them is to set a template, rather than a value. Ex: 2x, as opposed to 2(2), can calculate twice any number, while 2(2) can only calculate one operation. The interest shall now appear in the cell D4.



Step 10: Now that the interest has been generated, the credit to principal may also be obtained. This value may be arrived at by subtracting the interest from the monthly installment. Click on cell E4 and enter the following formula into the formula box at the top:
=B4-D4 (and press enter).



Step 11: As a final step in the generation of the first complete line of the mortgage table, we can obtain the new remaining balance. This is procured by subtracting the credit to principal from the previous principal. Simply enter the following formula into the formula box after clicking on cell F4: =F3-E4 (and then press enter).




Step 12: Now that the formulas have been entered into the spreadsheet and the values have been generated, we may again apply the “Fill-Down” method to generate all remaining desirable values. At this point the first of the rate, interest, credit and balance options are highlighted, along with thee 23 rows which follow them (i.e. all of the rate, interest, credit and balance options for the duration of the entire two year period). Now go once more to the fill option and select down after the options appear. The formulas have now repeated themselves over a course of two years, resulting in the completion of the table. The Mortgage Table has now been successfully created.