Trails End Computer Club

Lesson 11 - - Spreadsheet


Within a Spreadsheet you can enter information in rows and columns then use formulas to manipulate that information into useful information. Simply, you can add, subtract, multiply and divide any number to compare results or report results for both personal as well as business.

I use spread sheets to:

Keep track of daily and weekly market changes and compare my gain/loss with market indicators.

Watch for changes billed by utilities or  rent and fuel usage by vehicles.

Checking book registry and compare with bank statement and/or online.

Download CSV (comma separated value) files from Government or business.


Of the many Spreadsheet programs, the best known ones are probably Microsoft Excel, Open Office Calc, Libre Office Calc. Over the years there have been many other fine programs, many still with us and many no longer available. There may be special features some have but the way they work is very similar.

Web based spreadsheets are developing that may have some unique features like real time updates from remote sources like exchange rates and current stock prices.

You can view a spreadsheet as having an array both horizontal and vertical cells much like postal office boxes that data, formula or words may be placed into. Each cell has an address based on a number down the side and a alpha character across the top..

spread

The selected cell to the left is referred to as cell A1. An entire sheet may contain a few cells or many cells only limited to the memory of the computer. Even a small computer can handle a giant spreadsheet.

We will now program a spreadsheet that may be used as a check book registry. I recommend that it be for one year beginning Jan 1. Next year create a spreadsheet for the new year.



TextAcross the top we will click on A1 and enter "Checking 2014". In C1 enter "Pay To". In D1 enter "Amount". In E1 enter "Deposit". In F1 enter "Deposit". In A2 enter "Check #". In B2 enter "Date" In E2 enter "Description". In F2 enter "Amount". In C3 enter "Beginning Balance"



dataNow we are ready to enter some data. We can always use a positive figure. Do not use -$23.00 for checks. Just enter 23. In G3 enter a beginning balance, say 1000. in A4 enter 1001. In B4 enter 1/2/14. In C4 enter Trails End. In D4 enter 100.



formatClick on the B in column B to select the entire column. Click on Format in the top tool bar. Select Date under Category and the format that works for you under format. Click OK at the bottom of the window.

Place the mouse pointer between column A and B till a double arrow appears then double click the mouse. Do the same between column C and D. This sets the column width so everything fits in the cells.





formulaNow we will do the programming in G4 to give you the balance in the account. Click on G4. Click on the "=" sign in the formulas box, click in G3 the - key D4 the +key then F4 then enter.




formatWe now can format the Dollar columns to show a dollar sign, comma to indicate over $1,000.00 and cents as 2 digits. Click on the D in column D, Hold down the ctrl key while clicking on the F then on the G. Now you have column D, F & G selected. Click on Format then Cells then Currency under Category then under Format what works best for you then OK to set those columns.





Would you rather download the results of this lesson??
For Open Office or Libra Office goto tecc.apcug.org/lesson11.ods
For Microsoft Excel goto tecc.apcug.org/lesson11.xls









TECC, Harold Buechly, 3/9/14