Between the Sheets
An Introduction to Spreadsheets
These notes are designed to help you understand the principles of using spreadsheet packages. They have been written by Duncan Williamson, who has also written the downloadable spreadsheets available in this section.
The notes are split into various sections. You can either work through gradually by scrolling down the page or access the sections directly by following the links below:
- The basics of spreadsheets
- Sample formulae
- Spreadsheet design: before not after
- Input separate from Output
- Working with two worksheets: it's a problem isn't it?
- Glossary of terms
There is good news and bad news as far as using spreadsheets in the teaching learning process is concerned:
- The good news is that students and teachers can enjoy using them and they can use them to build models, draw graphs and make predictions...
- The bad news is that studies show that students who use spreadsheet packages in an attempt to improve their understanding of accounting or economics are wasting their time.
Of course, students who have used the spreadsheet package have learned at least a few things about those packages; it's just that, for example, trying to help students to get a better understanding of management accounting via spreadsheeting does not work.
Never mind, let's continue in the vein that using spreadsheets for teaching and learning is good for us on the following grounds. We can
- learn more about spreadsheeting packages and that is a good thing
- organise our work in a way that paper based systems often don't allow
- present our work in a really smart way
- build mathematical and statistical models
- simulate reality
- share our work with other people
- find work in one format and translate it into a spreadsheet format
What you are about to read is a gentle introduction for spreadsheet novices and doesn't cover all of these advantages of good spreadsheeting practice. You are about to work through setting up a simple accounting spreadsheet that contains a Trading Account first of all and then a Trading and Profit and Loss Account. Along the way, you will learn about inputting data, formulae, formatting data, input versus output sheets and having more than one window on the screen at the same time.
A key message that you need to appreciate from what follows is the need to design your spreadsheet carefully to save you making mistakes, to save you having to redo your work; and generally wasting your time and getting frustrated.
What follows is based on the Microsoft Excel spreadsheet package
In the early days of personal computing, even the most sophisticated spreadsheet packages were little more than glorified, and not that good, calculators. The only benefits to using a spreadsheet rather than a calculator were:
- The computer on which we worked would store our data for use over and over again
- The spreadsheet itself could hold a lot more data and relationships than a calculator
The 3D spreadsheet is not that old either: the spreadsheet that has several sheets or pages and, if my memory serves me correctly, even the Commodore 64 computer had 3D spreadsheets before many of the more sophisticated computers of the time.
Nowadays, the number of functions and features in spreadsheets is little short of fantastic; and not only do mathematicians, statisticians and scientists of all kinds use them for their calculative work; but accountants, economists and business people are major users of them too.
Essentially, a spreadsheet is divided into rows and cells. Each cell can contain text, values or formulae or even a mixture of all three. A sheet (or page) in a spreadsheet usually starts with cell A1 and moves horizontally to B1 then C1 ... AA1 ... CD1 ... and downwards to A2, A3, A4 ... A16489 ... We can read about the size limits of modern spreadsheets being 65,536 rows and 256 columns, or some other such finite number. In reality, the number of cells that a spreadsheet can actually contain is limited by the memory and other features of the PC or network we are using. Small memory means small spreadsheet. Large memory means large spreadsheet. So, the average blank spreadsheet looks like this:
Some of you may know that some spreadsheets have a different notation to A1, B1 ... they use R1C1: Row 1, Column 1 ... it's all the same in the end, however!
In our spreadsheet, then we can simply type things: letters, words numbers and symbols: such as headings, instructions, directions, explanations ... whatever we like.
We can also use the spreadsheet for the things it was best designed to do: store values and then carry out calculations based on those values and relationships. For example, here's a picture (screenshot) of a simple Trading Account developed in a spreadsheet:
Now, since we might have a Pentium III based computer with 750MHz speed and 128 Mb of RAM, this Trading Accounting doesn't exactly use the spreadsheet to its fullest extent. However, that's not the point. What I have done is typed in the outline of the account, entered the sales and cost of sales information and then asked the spreadsheet to calculate gross profit. Take a look at the logic built into this model:
This screenshot shows that I typed in the sales value of 10000 in cell B4 and typed in the value of 7500 for the cost of sales in cell B5 and then entered the formula =B4-B5 into cell B6:
sales - cost of sales = gross profit
So simple; but so powerful in that it does not matter what the value of sales is and what the value of the cost of sales is, the spreadsheet will give you the correct value of gross profit.
Let's look at something a bit more complicated now: a trading and profit and loss account.
Overall, this spreadsheet confirms the relationships
Sales - cost of sales = gross profit; and
Gross profit - expenses = net profit
The formulae used in this spreadsheet?
Firstly, to make my accounting statement look smart, I have changed the title and then moved the sales, cost of sales and gross profit data to column C and put the profit and loss account information in column B. I've also added a new formula =SUM(B10:B14). Otherwise, it's the same as before.
A SUM function tells the spreadsheet, in this case, to add together all of the values in the range of cells B10 to B14 inclusive: that is, cell B10 + cell B11 + cell B13 + cell B13 + cell B14. Again, whatever is in cells B10 to B14 will be added together; and again, this is so simple but so powerful.
Even though what we have seen so far is simple and powerful, we can improve on the way we do these things. For example, imagine the Trading and Profit and Loss Account of a large organisation that has, say, 12 different divisions and 150 different types of expense in each division. How simple is it now for the spreadsheet to display these data? More importantly, how should we input the data so that the spreadsheet can present it to us?
You see, once you've set it up, it's not difficult for the spreadsheet to deal with 12 divisions and 150 expense accounts for each division. No, the key to a successful spreadsheet is its design: how you set it up in the first place. After all, haven't we just seen even the simplest Trading Account having to be moved around when we added just about the simplest Profit and Loss Account?
You probably know it's true that somewhere between 70 and 90% of the production cost of all products has been built into the product AT THE DESIGN STAGE. So, if something goes wrong with a product and we need to save some money on making it, it can be really difficult to do because it's often difficult to redesign it once we've started making it.
The same is true of a spreadsheet, if we don't predesign our spreadsheet carefully, we will pay for it later in terms of time, effort and potential mistakes. What does this mean for us? It means developing a good habit: think before you act. I have demonstrated already that a relatively simple change to my accounting report mean moving things around, adding things: all because I didn't think ahead.
What do we do about this? It REALLY is a good idea to write down what you want to do before you even switch on your computer. Take a piece of old fashioned paper and write down what you need, then design it and then check it. With something like a Trading and Profit and Loss Account, you can go through it all with your accounting data so that you know exactly what it looks like as you are designing the spreadsheet.
It is helpful if you have a piece of paper that is divided into squares to represent the cells in the spreadsheet. Don't have squared paper? ... make your own ... paper isn't big enough? ... join two or three pieces together! No excuses!
One golden rule that I find useful in my work is firstly to have an input section and secondly to keep input separate from output. Let's go back to the accounting statement to see what this means.
What are the inputs in this case? The inputs are the values of sales, cost of sales and each of the expenses.
What do I mean by keeping the inputs separate from the outputs? I mean literally having a separate section, in this case a separate sheet. Look at this:
Firstly, notice at the bottom of the sheet you see and you will see that there are only two sheets now, not three. Also notice that the tab of the first sheet has been renamed as Inputs and the second tab has been renamed Accounts: to do this, right click on the tab name you want to change, choose rename and then type in your new name.
So, I now have two sheets: one for the inputs and another one for the outputs ... but why?
Well, if I just have one sheet that contains the accounts, I don't have the flexibility I might need. After all, imagine if we were working for the 12 division company and that we had to prepare their accounts ... it could be very difficult. What we really want to do is to simply type in what changes (the values of sales and so on) and let the design of the accounts take care of everything else.
All we have in the inputs sheet is a list of things that we simply type in and then change as necessary. Firstly, we typed in the date: for the period ended then we typed in the values for sales and so on. That's all we have done and no more.
The outputs sheet will now look for, find and then process these input data. Have a look at the formulae in the output sheet now:
Eesh! What does all of this mean? Firstly, it gives us a set of relationships: where we relate one cell to another one by way of a formula or function. What we have here is our accounting report and all of the values are fed in from the inputs sheet. For example, for Sales we see that in cell C5 there is a formula that says
meaning that the value of sales is found in the Inputs sheet in cell B6
The value of the cost of sales is found in the Inputs sheet in cell B7; and so on for the other input values.
Entering such formulae is easy since all we have to do is to enter the = sign and then click on the cell from the other sheet that we want to get our value from. We can type in these formulae if we wish; and if you do, just notice that the name of the sheet you want to get data from has to be typed exactly as it appears in that sheet's tab AND that name MUST end with a ! before you type in the cell or range reference from that sheet.
Look at the date cell, though, in cell A3. Here, we have used something fancy called concatenation: this just means joining together. This can be a really useful thing to learn because it allows us to combine words, values and formulae as we wish. In this case, the formula we have used is
="for the period ended"&" "&Inputs!B4
which tells the spreadsheet to show the words "for the period ended" and then add to that a space [& means add to and to tell the spreadsheet to add a space, we have to type " "] and then add to that the contents of cell B4 in the Inputs sheet [again, we have to enter the & sign, otherwise the spreadsheet won't understand what we are trying to do].
To save typing in "for the period ended" this time, and then, for example, having to change it to "for the year ended" next time, why not enter these two options in the Inputs sheet, too and ask the formula to get that bit from there? I could have had a simple formula to enter the name of the business in the outputs sheet, too, couldn't I?
If you like the look of concatenation, go to the spreadsheet help file and learn a bit more about how to use it there.
OK so we have an input section and an output section now but I have to keep moving from one to the other to see what's happening ... guess what? They thought of that! Have a look at the solution to the two separate sheets problem:
How do we get it to do this? In MS Excel work down the Window menu at the top of the screen as follows
- Click Window
- Select New Window
- Click Window
- Select Arrange...
- Select Vertical
- Click Window of active workbook
- Click OK
Magic! Two windows side by side. We could have one on top of the other. We can have three windows side by side or one above the other.
Watch out for larger worksheets, though, because you might have to play around with column sizes to see what you need all at the same time.
That's your gentle introduction to spreadsheeting. I hope you have read everything carefully! My aim here has been to start you off with some good ideas and some good habits. If you can work successfully with all of these ideas in mind, you can then move on to look at more advanced topics and ideas.
If you are working with a spreadsheet package apart from Microsoft Excel, what you have seen here still applies; but some of the ways that other packages work will be a little bit different to the way Excel works: not much, but just be careful. Look at the help files, ask your IT expert ... play around with it and see how much you can learn.
Finally, spreadsheets are very powerful tools these days. You can program a spreadsheet to achieve a huge array of things: from basic arithmetic to stock control, to payroll analysis to the preparation of accounts for a large organisation. An average spreadsheet package can also be made to work hand in hand with a word processing package and a database package.
So, roll your sleeves up and get stuck in: but do it properly!
In this introduction to spreadsheeting I have used the following spreadsheeting terms:
A computer program that divides pages into rows and columns into which can be entered numbers, text and relationships for manipulation and storage
The smallest working unit of a spreadsheet: the intersection of a column and a row. Data etc are entered into one cell at a time
A group of cells: ranges must be square or rectangular and cannot be irregular
Joining together the contents of two or more cells in one cell
A full screen of information
Sheet (or worksheet)
A part of a spreadsheet that contains up to 256 columns and 65,536 rows, that is 16,777,216 cells. The words sheet and worksheet are synonymous.
A spreadsheet file that contains two or more sheets or worksheets that can be linked by means of formulae and functions.
A predefined routine or algorithm built into a spreadsheet package. For example, there is a SUM function that automatically adds together the contents of a range that a user must specify in the SUM function. There are hundreds of in built functions in modern spreadsheet packages
A routine composed by the user of a spreadsheet. For example, a formula might look like =A1+B2*B3/D1 which tells the spreadsheet to add the contents of cell A1 to cell B2 having multiplied cell B2 by cell B3 having divided cell B3 by cell D1. A formula can also contain functions.
A vertical array of cells in a spreadsheet.
A horizontal array of cells in a spreadsheet.
A scientific term that relates to the speed with which computers are able to calculate. A hertz is equal to one cycle per second, so a megahertz is equal to one million cycles per second
Random Access Memory and is the basic memory form of modern computers allowing computers to put into and take from memory at random rather then in sequence.
Megabytes means one million bytes. A byte consists of 8 bits and a bit is a binary digit such as 0 or 1
A in built spreadsheet function: see function above
Connections that bring data, cells, and ranges together. For example: there is a relationship between the input and output sections of a spreadsheet.