There are different ways to answer this question. Functionally, spreadsheets are a tool for mathematical calculations, but have branched out into related areas like data analysis. Some people even use them as a quick-and-dirty database tool. If you are in a financial profession of some kind you probably live in spreadsheets all day.
Spreadsheets are the original “killer app”. Early examples were implemented on mainframe computers in the 1960s, but the big step was the creation of VisiCalc for the Apple II in 1979, which was then ported to the IBM PC in 1981. VisiCalc set the conventions that guided all subsequent spreadsheets, and the essential methods have not changed since then. VisiCalc was called the first killer app because people would buy the computer just to run the program, and the usefulness of spreadsheets is what promoted the initial entry of personal computers into the corporate world, with all of the change that has caused.
The next major development was Lotus 1-2-3, which was written specifically for the IBM PC, and which, along with Borland’s Quattro Pro, gradually replaced the older VisiCalc as the spreadsheet of choice. Lotus 1-2-3 was a DOS program, and thrived in that environment. But with the advent of Windows a new competitor emerged, Microsoft. They had a spreadsheet program originally developed for Macintosh computers, Excel, which they then moved to Windows. And with the assembly of the Office suite by Microsoft, Lotus gradually dropped from the picture, being bought by IBM and turned to other challenges. This was helped along by Microsoft, of course. There was a saying in those days that “Windows isn’t done until Lotus won’t run.” And whether or not this was true, it expressed the advantage that Microsoft built up by controlling both the operating system and the major applications that ran on it. Prior to Microsoft Office there were many individual programs that were excellent, like WordPerfect and Lotus 1-2-3, but the power of the suite controlled by Microsoft pushed all of them into the background. Many of them can still be found today, but they are shadows of what they once were.
But because of this history we have a set of conventions that have stood the test of time and define how spreadsheets operate no matter which one you use. So if you originally learned Lotus 1-2-3, moving to Excel was not at all difficult because it worked the same way. By contrast, WordPerfect was significantly different from Word which made switching much harder. But with spreadsheets the basic conventions and operations have been fairly constant, with the innovation happening around the edges in terms of adding features. So if you learn spreadsheets properly the first time, you can sit down to any spreadsheet program and be productive immediately.
But very few people have ever learned to use spreadsheets properly. That is what we want to correct in this series of tutorials. We’ll start at the very beginning, and build up the concepts and techniques so that you have a good idea of what spreadsheets should be used for, and equally, what they should not be used for.
Spreadsheets vs. Databases
To begin with, a spreadsheet is not a database. This may surprise some people who have been using spreadsheets for their database needs. It is true that you can do some simple database functions in a spreadsheet, and I suspect this may be due to the fact that the row-and-column format of a spreadsheet resembles very closely the format of a table in a database. But this is a very limited application and runs into problems quickly. A spreadsheet can only function as a single table, while a database can have many tables that are linked through key fields. Unless your needs are very simple you really are better off creating a database when you need to do database functions.
What we are talking about here is a variation on the old saying “If all you have is a hammer, everything starts to look like a nail.” I much prefer the idea of choosing the right tool for every job. So while a spreadsheet can do some of the simpler functions of a database, it is not the right tool for doing database work, and if you start down the road of using a spreadsheet this way you will sooner or later get frustrated by its limitations.
Spreadsheet Uses
So what should you use a spreadsheet for? Well, here is a list of some of the more useful things you can do. It is not exhaustive by any means, but it should help to set the stage for what we will do in this series of tutorials.
- Mathematical calculations – Well, it is called Calc for a reason.<g> Many times I open up Calc just to do some arithmetic. I suppose I could use a calculator program, but I find Calc convenient for my needs and much easier when a calculation starts to get complex. Using Calc, I can edit my calculation in case I made a mistake, while with a calculator program I would have to do it over from the beginning.
- Graphing – The built-in graphing capability is pretty good, and certainly sufficient for most people’s needs. It is not the equivalent of a high-end scientific graphing program, but it never tried to be that. But you can quickly generate bar graphs, scatter plots, pie charts and the like based on the data in your spreadsheet.
- Modelling – This is a key use in finance. You can create a model of a financial situation, such as paying off a loan, and then the “what-if?” analysis by changing one variable and seeing how it affects the model. For example, if I pay an additional $100 per month on my mortgage, how much sooner will I pay it off? This kind of question is easy in spreadsheets. Back when I was teaching Corporate Finance to college students I had them do a business plan in a spreadsheet, and then investigate what happened if the price of the product, or some other variable, changed. Note that this is extremely useful, but is also critically dependent on using spreadsheets properly! Bad habits will kill you here.
- Statistical analysis – You can do a variety of statistical tests and analyses in a spreadsheet using the built-in functions. Back when I taught Business Statistics I used specialized Statistical software to teach the course. Today, I could teach the exact same course using spreadsheets. Built-in functions include Chi-square, F-distribution, linear regression, and T-test, to name just a few.
This is just scratching the surface of what you can do, but you can expect that each of these will get attention in this series, and probably some other topics as well. But I will start with the most basic concepts and build from there for two very good reasons:
- Many people who have used spreadsheets have “holes” in their knowledge that can make things difficult when we get to more advanced topics. I want to make sure we have a uniform base of concepts to build on.
- Many people have “bad habits” in their use of spreadsheets, and this will also come back to bite you when you try to do more interesting things. We really need to get rid of bad habits and enforce proper usage if we are to become experts in spreadsheet usage.
Listen to the audio version of this post on Hacker Public Radio!
Save as PDF
