Tech Sense October 2023: Using our Tools: The Spreadsheet
Updated: Oct 25
As long promised, I am now ready to begin my “Using our tools” series on the spreadsheet. Dan Bricklin developed Visicalc, considered by many to be the first spreadsheet available on microcomputers. Visicalc became the first “killer app”, an application that drove the sales of a computer making it successful. Visicalc drove Apple sales for business purposes, making it, more than just a curiosity for hobbyists.
Spreadsheets quickly spread to other platforms including those made by Commodore, Atari, and CP/M systems, which dominated the business space in that age. Multiplan from Microsoft ran on many home computers making it easier to move from one computer to another. The next big success occurred after the release of the IBM PC. Lotus 123 was the killer app for the “PC” becoming the de facto standard in the offices of its day.
Then the Graphical User Interface (GUI) arrived with the Apple Macintosh and Windows from Microsoft. Microsoft began to dominate office applications with Microsoft Office Suite (MS-Office). Microsoft Excel was the spreadsheet bundled with the package and is still the dominant product today.
Today Microsoft Office and Office 365 are the dominant Office Suites in the market and are offered for purchase on most Windows 10, 11, and Apple computers. Microsoft also offers a less capable browser-based version of their office suite as well.
While Microsoft Office dominates the office suite market there are some notable alternatives. Google offers an online suite of products that includes Google Sheets as a reasonably powerful spreadsheet. The Google Docs suite of tools runs on the browser and operates on almost any computer with a standards compatible browser. LibreOffice is a completely free and open-source Office Suite. It is largely compatible with MS-Office, well supported and maintained, and great for systems like Linux where MS-Office is not as readily available.
There are many other Spreadsheets and Office Suites out there, but the three we have mentioned already are the most popular. We will focus on only these products.
What is a Spreadsheet?
Ledgers used to track financial records have been around since clay tablets. Think of a spreadsheet as an electronic ledger. The electronic spreadsheet provides a grid to collect and organize numbers. Each rectangle in the grid, called a cell, accepts data. The data may be a number, a string, or a formula. A formula calculates a result displayed in the cell that holds the formula.
Cells form the rows and columns of the grid. Each row has a number: starting at 1 and continuing until the last row. The last row can be many thousands of rows down even in the millions, although one that size may be a challenge to work with. Columns are lettered A to B on through Z, AA then through ZZ, AAA, through ZZZ, and beyond. The first cell in the top left corner is A1. This is column A, row 1.
Spreadsheets can also have multiple pages. By default, pages are referenced as sheet1, sheet2, and so on but they can be named. This is useful for example for monthly reports where the sheets are named by the month.
The Loan Calculator Example
The diagram figure 1 shows the top corner of a small spreadsheet that helps to calculate the payment when financing the purchase of a new car. The spreadsheet begins in cell A1 with labels going down in column A through row 7. Data values and formulas are contained in column B. This was my choice. I could have just as easily used row 1 for my labels and had my data and formulas in row 2.
The calculation uses the loan amount, the interest percent as an annual rate and the number of months to pay. These values are entered into column B into rows 1, 2, and3. The monthly payment is calculated in row 4 using the formula PMT (interest/1200, months, loan-amount) which is entered as =PMT(B2/1200,B3,B1). The reason for the division by 1200 is to calculate using the monthly interest and not the annual interest. The spreadsheet already has a built-in PMT function as one of several financial functions.
The spreadsheet also calculates the total amount of the payments at the payoff and the total amount of interest that was paid. Total payments is calculated using the number of months times the payment amount. The total interest is calculated by subtracting the loan amount from the total payments.
What Else Can it Do?
I use a spreadsheet for managing and balancing my checkbooks, for tracking my businesses books and expense reports, sending customer invoices, creating quotes for my customers, and for project management. I have also used it to manage wedding seating and mailing lists.
The oddest use I think was creating a map of the borders of my swimming pool to order a new cover. I did this by making a regular set of measurements around the pool from two fixed points. I put the points into the spreadsheet and used trigonometry functions to calculate the points defining the edge of the pool. This worked so well I added a page to the spreadsheet to draw the image of the pool using the points that I had calculated.
You may ask, “Which spreadsheet do I prefer”? I actually use all three. I keep our family checkbooks and accounting in LibreOffice so I can easily share them with my wife. I keep my business spreadsheets on Excel to keep them compatible with what my customers are using. Little spreadsheets like the example loan calculator I keep on Google Sheets, so they are easily usable on my phone or tablet as well as my computers. Yes, I am aware that Microsoft offers MS-Office on Android as well, but I find the Sheets solution easier for these small things.
Well, I guess I am at the end for this month. Feel free to send questions for me to tackle in the future and Fall gently into the autumn.