Mouse Clix

By: Hobie Lunin

Surprise yourself with Spreadsheets!

Here’s a, FAQ (Frequently Asked Question): What’s a spreadsheet, and why is it different from a word processor? Well, if a word processor is good for words then a spreadsheet is great for numbers. A paper spreadsheet is something an accountant will recognize. These have been used for years to sort out charges in various accounts.

What can a computer spreadsheet be used for, is another FAQ. It’s great for doing your budget, keeping track of your stocks, making a holiday gift list as well as a mailing list. So, it’s good to use when there are columns of words and numbers and when there are calculations you may want to do. The most popular spreadsheet by far is Microsoft’s Excel, but the spreadsheet that comes with Microsoft Works, Corel’s Quattro and Lotus 1-2-3 are other familiar names. All are similar.

When you open a spreadsheet, and I will use Excel 2000 as the example, you see that it is columns and rows that intersect in little rectangles called cells. The cursor looks like a cross rather than a pointer and the rows and columns are numbered along the left and lettered under the tool bars. The cell that is at the intersection of Column B and Row 3 is identified as cell B3.

For the sake of a case study, let us say you are going to keep track of some securities in Excel. In the first column click the cursor in the uppermost cell, a heavy black border means that this is the cell that is active. Now type the word “Security”. Then hit the right arrow at the lower right of the keyboard to move the active cell to the right. Enter the words “Num/shrs” and hit the right arrow again. In the next cell enter “Price”, and click the right arrow again. Put the word Totals in the next cell. In a like manner, starting in the second row, add some actual names and values in the columns, moving the active cell by either clicking with the cursor or using the arrow keys. List least 3 securities in the first column and 3 sets of share numbers in the second column and then three sets of prices in the third column. At the top of the columns that have the dollar amounts (Price and Totals), click on the letter at the top of the column to highlight the column and click on the $ sign on the tool bar. This will supply the $ sign and decimal point to any number in the column.

Next we will set up the spreadsheet to multiply the number of shares times the price and put the result in the total column. Click on the uppermost Total cell (now empty). Type, in the long blank field just above the numbers (the formula field) at the top of the columns: =PRODUCT (A2: A3), then click in any blank cell. The total of the multiplication will appear. Click in that cell and move to place the cursor on the lower right corner of that cell and when you see a black cross appear, drag it down to the last row and release. Note that you now see the totals in all the other rows. Click in the cell below the last total to highlight it and then click on the “S” (sigma) symbol on the tool bar and you will see the totals of the all the values of the three securities. Now you have created a chart that updates itself. To see how this works, change the price of any of the securities (hopefully higher) and the totals will now reflect the change. To change the price, just click the cursor in that cell and type the new number. Only the number is needed, the program will add the dollar sign and place a decimal point if you enter a whole number. Now add a few more column headings like: Quotron symbol, date purchased, cost at purchase, date of sale, value at sale and anything else that will be useful to you and the IRS.

To make the Spreadsheet look at little better, click on the number 1 in the left most column. This will highlight all your column headings. Click on Bold (B) and on Align Center. This makes the headings stand out. To make a line under all the headings, click on Format, Cells, Border, Outline and then OK. Looks nice, doesn’t it?

To add a column between two existing ones, highlight the column that will be to the right of the new column and then click, Insert and Column. To add a Row, highlight the row just below where the new one will appear and click on Insert and Row. To alphabetize your list by the first letter in the Security column, click on the space between the row of letters and the row of numbers, in the upper left. This will highlight everything. Then click on Sort A-Z. (This button has an A over a Z and an arrow pointing down.) Notice that in the lower left corner of the sheet there is a tab labeled Sheet 1. If you double click on the words, you can change the wording there to reflect the year or the name of the owner of the securities if you have more than one. This will allow you to keep track of a number of portfolios in this one book of worksheets. To add additional sheets, click on Insert (top tool bar) and then Sheet.

In this way you have made a useful spreadsheet that can be used to evaluate your portfolio(s) at any time with some ease. If you have completed the above I am sure you will have surprised yourself with how “cool” this is.

Next time in Mouse Clix: Recently asked questions.

Hobie Lunin will answer questions at hlunin@email.com. See http://mouseclix.tripod.com for previous articles.