What's the Deal with Spreadsheets?

It helps to understand the deal in order to use software effectively. The deal is what the developers and programmers constructed into the program. You do some things and the software will do other things for you. There’s an implicit deal in well designed software.

Knowing the deal helps in using and understanding a program. I’ve already talked about the deal built in to word processors. For word processors the deal is reasonably obvious. When it comes to spreadsheets the deal isn’t as obvious. This is the first of two posts on using spreadsheets, the underlying deal, and the ramifications of that deal.

Spreadsheets are simple on the surface. If you know a lot about modern spreadsheets with all their added bells and whistle this will seem a bit too basic - but it covers the core idea behind spreadsheets.

A spreadsheet is a grid of boxes or cells. The grid is typically thought of as starting at the top left and working right and down.

Each cell is given a name - which is its location in the grid. Columns are called “A”, “B”, “C”, and so on. With “A” being the leftmost column. Rows are numbered. “1”, “2”, “3”, with row 1 being the top row.

Each cell can hold:

• Nothing - which is how all the cells start when you create a new spreadsheet.
• A number - for example “10” or “1234567.89”.
• A bit of text - for example “Total” or “Number of widgets”.
• A formula - which can be self contained “=10 * 25” or it can refer to values in other cells “=A1 + A2”

Cells display their contents however you want them to. For example:

• A number such as “10” can be shown as “$10.00”. A number you enter as “1234567.89” can be shown as “1,234,568” - rounded up to show no decimal places.
• Text can be shown in bold or italic. It can be aligned in the cell to the left or the right or it can be centered.
• Formulas aren’t displayed. The results of formulas are displayed and made to look pretty. “=A1 + A2” takes the number in cell A1 and the number in cell A2 and adds them together. The result can be formatted like any other number and displayed however you want it to be displayed.

And that’s about it. Everything else is built on that core.

You enter numbers, text, and formulas and the spreadsheet calculates the results and formats the values in each of those little boxes or cells.

That may be the deal programmed into spreadsheets - you put in stuff and it calculates and displays it - but that may not be enough to understand spreadsheets. A point brought home to me years ago when my mother asked me a seemingly simple question.

My mother had asked me, the proverbial teenager who could fix your computer, to teach her spreadsheets. Which I then tried to do. I explained cells. I explained numbers, text, and formulas. After trying this a few times, with some frustration, she asked me a question:

I know all of that. How do I use it?

It took me a while to wrap my head around what she was really asking. You see my mother is a smart person. She’s an accountant - she knows numbers. She was working in the hospital finance at the time. I don’t remember if she was already VP Finance for a hospital or if she would go on to be the VP Finance at a few hospitals. The Vice President of Finance was the position now known as CFO. As I said - a smart person.

After a while, and several attempts to “teach” her spreadsheets, I clued in to what she was really asking.

If I presented her with a spreadsheet she could figure out how to use it. She could dig in to how the formulas worked. She could figure out where to change numbers to see different results. Presented with a budget or invoice spreadsheet she could use it without difficulties. Understanding what makes up a spreadsheet wasn’t the problem. Understanding how to use a pre-built spreadsheet wasn’t the problem.

The problem was… faced with a blank grid of cells… how do you create spreadsheets that do what you want? How do you turn a blank screen into something useful?

It still took me a while to come up with an answer after I understood what she was asking.

There isn’t one answer to “how do I use it?”. There are many. I finally decided to divide the spreadsheets you could create into two overlapping categories:

1. Spreadsheets that are meant to look pretty and
2. Spreadsheets that are mainly meant to figure something out.

At the time I wouldn’t have said “look pretty”. I would have said “intended to be printed out”. Most of the time a spreadsheet would be created, updated, and then printed out. Nowadays we’re more likely to look at it on screen. Looking pretty is now more appropriate than looks good when printed out.

By pretty - I mean that the result was something that laid out the information in a visually understandable manner. A Make-it-Look-Pretty spreadsheet could be:

• a table of accounts
• a list of shifts and the employees assigned to them
• an invoice
• a summary of a budget
• a chart of delinquent accounts

Or any other type of report you could imagine. Sometimes these spreadsheets were replicating and automating an existing paper form. Other times they were net new creations that took information and made it look… well… pretty.

On the other hand, the Figure-Something-Out spreadsheets were created to do just that… figure something out. The process, steps, and result are the important part. The spreadsheet will never be printed out as-is. Instead the results of the calculations and work will be taken and used somewhere else.

For example - I’ve managed large amounts of disk storage over the years. Every quarter at one job, the Operations Manager and the accountants wanted to know how much space we had, how much we’d used up, how much space was left, how long that space would last before we ran out, and when we needed to buy more.

There are a lot of calculations behind these numbers. Just figuring out how fast storage is being used and then projecting into the future can be a lot of work. Lots of data with lots of calculations.

Not that the Operations Manager and the accountants care. They just want the answers. They might want to dive in a bit when things seem unusual. Let’s say we’re not using storage as fast as we used to. That may need explanation. Still - the core deliverable for that spreadsheet would be the numbers to answer their quarterly questions on capacity. I wouldn’t give them the spreadsheet with all the calculations in it - I’d give them the answers in a summary email.

Again - there are many ways of answering the question of “how do I use it” when it comes to spreadsheets. This is only one of them. A useful one though.

Let me explain.

If you are making a spreadsheet that needs to look pretty - then you can start by creating the pretty part. You create the resulting invoice. From that you know it will need an invoice number, a name and address of the client being invoiced, a section listing what you are invoicing them for, and formulas to work out totals and taxes.

You know what the result needs to look like and you built a spreadsheet around that pretty looking result.

If you are creating a spreadsheet to help you figure something out… you can lay out the figuring step by step. You can put places to enter the information you need to figure with. You can work your way through calculation after calculation that does the figuring. Finally you end up with the results of all the figuring.

In that type of spreadsheet what each number or calculation represents isn’t as obvious as it is in the spreadsheet that looks pretty. The sections of an invoice are pretty obvious. The steps in a string of calculations isn’t. It makes sense to label each step along the way. This is where you type in number of staff you need on each shift. This is where you assign certain staff to certain shifts. Or whatever. Otherwise you end up with a dense collection of information and formulas that doesn’t make sense the next day when you continue to work on your spreadsheet.

These aren’t the only ways to think about spreadsheets. It is one way to help focus your thinking when creating a new spreadsheet. A spreadsheet to help you solve whatever problem you are working on.

I found this way of understanding spreadsheets very useful during one of my last summer jobs. Part of which I spent teaching managers at a hospital how to use spreadsheets.

The VP of Finance at that hospital had a plan. To send floppy disks to each manager with a budget spreadsheet for their department already filled in with the numbers from last year. The managers would then update the spreadsheet and send the floppy disk back to finance. Finance would take the pile of floppy disks and merge all the spreadsheets together into a draft hospital budget. Considering that most of the managers hadn’t used spreadsheets before - this was an ambitious plan.

My part was to help managers learn how to use spreadsheets. It was one-on-one training in their office with the manager in front of their computer.

I always started with the basics. Launching and exiting the spreadsheet program. Saving and loading spreadsheet files. I’d then quickly cover the basics - that spreadsheet cells contained numbers, text, or formulas. At which point their eyes would start to glaze over - no matter how gentle I made the training.

So I’d stop and ask them about the forms or work they had to do repeatedly in their job. After all they had calculators on their desk for a reason. What form did they not look forward to filling out? Which one took the most time to figure out and calculate? What did they have to redo every week or month? They’d pull out a binder or file folder and show me their personal nemesis.

I’d look at it and then offer to help them put it into a spreadsheet so that all the hard work would go away.

I didn’t create the sheet for them. We built it together. They did the work at their computer and I sat beside them telling them what to do next and explaining why.

They ended up with a spreadsheet they would use all the time. They ended up appreciating what the computer could do for them. And they were that much closer to being able to work on the budget sheets they’d be sent a few months later.

With spreadsheets the deal isn’t as obvious as with word processors. Or at least how to use the deal isn’t as obvious. With word processors it fits text on pages. Spreadsheets can do almost anything you can think of. You enter numbers, text, and formulas - and the spreadsheet will calculate, update, and show the results. You change a number or a formula and the spreadsheet will update everything for you. Automatically, correctly, and without being asked.

The spreadsheet will do whatever calculations you tell it to. That’s one heck of a deal and the reason why spreadsheets are as popular and useful as they are.

The deal does come with a catch though. I’ll explain the catch in my next post about spreadsheets.