What's the Catch with Spreadsheets?

This is my second post on understanding spreadsheets. In the first post I covered the deal that underlies spreadsheets and how the deal isn’t necessarily enough to know how to use spreadsheets effectively. This post covers the catch to using spreadsheets. Understanding the catch and its ramifications is important.

A catch? Yes - there is a catch to using spreadsheets. Otherwise it wouldn’t be news that the UK is missing 16,000 COVID cases being tracked in spreadsheets. If there wasn’t a catch we wouldn’t need spreadsheet warriors to help solve spreadsheet problems. There is definitely a catch to using spreadsheets.

But before I begin I need to make two separate apologies.

When I was teaching people completely new to computers how to use spreadsheets I tried to make them less intimidating. I’d mention that when it comes to computers - lots of people were involved in coming up with the computers we have today. Same with word processors. There isn’t a person or group or even a single product where we can say - that’s it - before that there were no word processors and after that there were word processors. If you didn’t like computers or word processors you couldn’t blame anyone in particular.

Then I told them that it’s different when it comes to spreadsheets. We do know where to lay the blame. Then I’d name Dan Bricklin as the creator of the first spreadsheet.

My first apology is to Dan Bricklin the co-creator of the first spreadsheet program. If, between 1995 and 1998, your ears started randomly burning or there was a string of negative karmic episodes that may have originated north of the border in Canada, I apologize. My intent was never to have my students send bad vibes your way.

My second apology is to Bob Frankston - the co-creator of the first spreadsheet program. Sorry I didn’t mention you as the co-creator. I only mentioned Dan’s name. Forgive me for not potentially subjecting you to a wave of Canadian karma during those years.

There. I feel better. With that done… back to the catch behind spreadsheets.

A quick recap. Spreadsheets are made of cells in a grid. You can enter numbers, text or formulas into cells. The deal is that the spreadsheet program will calculate things out for you. So far so good.

If you aren’t involved in the world of computers and how to make computers do what you want them to do… this sounds like a good thing. No problems at all. If you are involved in making computers do what you want… even that short summary should sound familiar.

Let me give 5 examples of how it should sound familiar.

Example 1. You can put numbers into cells and then reference those cells in other cells via formulas.

• You can put 10 in cell C5. Representing the number of widgets you want to buy.
• You can put 123.65 in cell D5. Representing the cost of each widget.
• You can put the formula “= C5 * D5” into cell E5. The spreadsheet will automatically calculate the total cost of $1,236.50 and display it in E5.

Now you can go back and make changes.

• Change cell C5 from 10 to 21 because you want to buy more widgets - and the total will be updated from $1,236.50 to $2,596.65.
• Change cell D5 from 123.65 to 115.42 to reflect a volume price discount - the total will be updated to $2,423.82.

Cells C5 and D5 hold numbers that are referenced by the formula in E5. Change the numbers and the formula updates. Places that hold values that are used elsewhere? Those are called variables.

Example 2. I created two categories that cover most of the spreadsheets that people create. There are spreadsheets that look pretty and those that are just used to calculate things.

For those spreadsheets that are intended to look pretty - you can start creating the spreadsheet by laying out the design of the pretty part. You can build an invoice, a form, a report, or whatever.

The look of how information will be entered and the results displayed? That’s a user interface.

Example 3. For spreadsheets that will be used to calculate things I pointed out that it is good practice to put labels next to cells to explain what that part of the spreadsheet is doing. You could:

• put a label next to where you enter numbers to let you know what the numbers mean.
• put a label next to where you perform a calculation - reminding you what that formula is doing.
• put a block of text in the spreadsheet reminding you how to make updates or changes when you revisit the spreadsheet.

Labels are also known as comments. Blocks of text instructions are known as documentation.

Example 4. Let’s take the example of an invoice spreadsheet. How do you know the sheet is working properly?

I have a few suggestions on how you could check.

• Enter an invoice for 10 hours of consulting at $25 per hour. Is the line total $250 and does the total cost before tax also equal $250?
• Set the tax rate to 10% and check to see that the tax shows as $25 and the grand total is $275.

From there make other changes and make sure the results update as expected.

• If you can put ten billable items on the invoice - fill each one in and make sure the totals work correctly. Let’s say you made a mistake and the invoice only totals the first 9 items. You wouldn’t notice if you only put in 2 or 3 items.
• Check you can change the tax value. Currently it’s set to 10% - change it to 20%, 50%, and 100%. In each case make sure that the results are correct.
• Set the tax to 0% or even an absurd value like -5%. What happens. Does 0% work or does it cause a problem? Does the sheet calculate negative taxes? Should it?

You get the idea. Check that the sheet works when you enter numbers you can figure out in your head. That’s testing. Putting in weird numbers like 0% tax or a negative tax? That’s testing edge cases. Things that aren’t usually going to happen - but they could.

Finally - Example 5. You should always make sure to use your common sense to check that the sheet is working properly. You are asking a spreadsheet to calculate things for you. Why? Because the calculations are long or tedious and you need to know the answer. Still - you should know roughly what the answer is. Or at least how big the answer should be.

• Let’s say you are merging two departments with 5 employees each and you are working out how many staff you need to cover all the shifts. If the sheet says you need 50 staff - something is probably wrong.
• If you are doing the budget for your department this year and things haven’t changed too much since last year - the spreadsheet shouldn’t say you’ll need half the money or twice the money. If it does there is probably something wrong.

Having an idea of what the answer should be, or at least a sense of if the answer is in the right ballpark, is another way to test that all is working properly.

If you put the wrong numbers in to be calculated the results will be wrong. If you enter a formula incorrectly then the results will be wrong. If you create a complicated spreadsheet without labels or instructions then you’ll have to figure what you were doing the next time you use the spreadsheet.

The deal is that the spreadsheet will take the numbers, text, and formulas you enter and calculate them correctly. The spreadsheet will do what you tell it. It won’t figure out what you want it to do. It will calculate incorrect numbers and incorrect formulas correctly.

Look at those examples. Variables to store information. A user interface designed to make the results of the spreadsheet easy to understand and use. Comments and documentation to let you know what you are doing. Testing to make sure the results are reasonable - even if the numbers you enter aren’t reasonable. All those are part of programming a computer.

Yes. Programming. Not in a complicated computer language - but inside a spreadsheet.

This is why spreadsheets are so useful, enduring, and popular. Dan Bricklin and Bob Frankston didn’t program their computers to solve one particular problem. They programmed their computer to solve any problem they could fit into a spreadsheet. They created a tool that allows people with no programming background or experience to be able to solve almost any problem with their computers.

I contend that spreadsheets are the most popular computer programming tool. You could have an app for each thing you want to figure out - or you could just do it in a new spreadsheet.

As a programmer all the responsibilities of programming are now your responsibilities. Does the spreadsheet work correctly? Are the numbers you started with correct? Are all the formulas correct? If not - it isn’t the fault of the spreadsheet program. It’s your fault for not telling it to do the right thing. You are responsible for using the tool properly.

You’d think this would be obvious. That a tool that lets you figure almost anything out would be considered a programming tool. But if you look at the lists of the most popular progamming languages, like TIOBE - you won’t find spreadsheets. Why? I can think of several reasons.

• Most computer programs are long bits of text that are turned into code the computer can run. Spreadsheets are two dimensional grids where you construct a program piece by piece interactively.
• Most programs are written to create stand alone programs or apps. The spreadsheets you create need to be loaded into a spreadsheet program to be used.
• Not many people make their living by programming in spreadsheets. The majority of people creating spreadsheets are just using them as a tool in their job. Though those spreadsheet warriors are making their living dealing with the spreadsheets others tried to create.

These are some of the reasons spreadsheets aren’t considered a true programming tool or programming language. But they should be.

Let me finish by thanking both Bob Frankston and Dan Bricklin for creating the first spreadsheet program - Visicalc. They made it easy for all of us to make our computers solve any problem we can fit into a spreadsheet. Thank you.

Now - just be mindful of the catch. You get to harness the power of the computer - just be careful to check that you are doing it properly. Otherwise you’ll need that group of spreadsheet warriors to help you out.