How spreadsheets work

A spreadsheet appears as a grid, each row has its own number and each column its own letter. This labelling of rows and columns is used to give each cell a cell address or reference, for example, C5 means column C, row 5.

It is also possible to refer to a range of cells collectively, ie E4:E12 includes E4, E12 and all of the cells in-between.

Cells can contain numbers, text or formulaeformula (plural formulae): in computing, a calculation.

Example spreadsheet

A grocer uses a mobile shop to sell groceries to customers who live out of town. He uses a spreadsheet to keep a record of his daily sales:

Daily sales

There are a number of formulae in the above spreadsheet but they're hidden, only the results are displayed.

Formulae

The formula in cells E4 to E10 works out the total value of sales for each item. It does this by multiplying the number sold by the individual price. For example, seven kilograms of apples were sold at 70p per kilogram, the sales total is calculated as £4.90 and is displayed in the cell E4. The formula in E4 is =C4*D4, ie 7 × £0.70.

Cell E12 contains a formula that calculates the total value of the sales column. The formulae used is =SUM(E4:E10). This formula includes cells E4, E10 and all of the cells in-between. You could write it =E4+E5+E6+E7+E8+E9+E10 which will give the same result but is overly complex and leaves more room for mistakes. Note, the =SUM function should only be used when adding a range of cells.

Constructing a spreadsheet

A self-employed builder charges £15.00 an hour. He's constructed a spreadsheetspreadsheet: A spreadsheet is made up of cells, rows and columns. Each cell holds a piece of numeric (numbers) or alphanumeric (text) data. Cells can also contain formulae to calculate their contents. to keep a record of his work but it's incomplete.

Complete the builder's spreadsheet

Download the incomplete spreadsheet on the left and perform the tasks below. To open it you will need a spreadsheet application, eg Microsoft's Excel, OpenOffice.org's Calc, Google Docs or another application that can open *.xls files.

Please note that the actual steps required to complete each tasks may differ slightly depending on the spreadsheet application you're using.

Task 1

The builder has just completed some work for Jason Williams. It took him four hours and the materials cost £37.00.

1. Enter Jason’s information into the spreadsheet.

The formulae to calculate the bill should be automatically inheritted from the cell above.

Task 2

Changing the category to currency

The values stored in columns C and D are currency. Change the number category to currency so the spreadsheet recognises this.

1. Click cell C2 and hold down the left mouse button. Move the cursor across to cell D2 and then down to cell D6. The cells should now be highlighted.
2. Release the left mouse button and right-click on a highlighted cell. Click Format Cells.
3. A new screen appears, click the Number tab and click currency in the list.
4. Click OK.

Task 3

There’s a mistake in the formula used to calculate the total bill, the builder has entered his hourly rate incorrectly – correct his mistake (remember, his hourly rate is £15).

1. Double-click cell D2 to edit the formula. Make the correction and press enter on the keyboard to save the changes.
2. Copy the changes to the formula to the cells below. Click cell D2, left-click the small black square in the bottom right corner and drag it down to cell D6. Release the left mouse button.

Task 4

You can select specific cells to use in your formula

The builder wants to calculate his daily earnings, write a formula in cell D8 to do this for him. Press enter to save the changes.

The formula can be written two ways, decide which one to use:

1. =D2+D3+D4+D5+D6
2. =SUM(D2:D6)

The spreadsheet program should realise you’re adding together currency and automatically change cell D8’s number category to match.

Download the completed spreadsheet on the left to see how you did or to learn where you went wrong.

Advantages of using spreadsheets

Using a spreadsheetspreadsheet: A spreadsheet is made up of cells, rows and columns. Each cell holds a piece of numeric (numbers) or alphanumeric (text) data. Cells can also contain formulae to calculate their contents. rather than a calculator or pen and paper offers the following advantages:

More effective data handling

• Formulaeformula (plural formulae): in computing, a calculation can be used to instantly recalculate totals. For example, if the builder changed his call-out charge, the total cost would be instantly recalculated taking the change into account.
• You can carry out "what if?" investigations. For example, the grocer could increase his prices to see the effect on sales and the builder could increase his hourly charge to see the effect on his daily total.
• The informationinformation: data with context or meaning can be presented in different ways. For example, the grocer could produce a graph of fruit and vegetable sales by choosing the ranges A4:A10 and E4:E10 in order to make the graph.
• It is easy to make these changes, save your work and print it out again.

More flexible presentation

• Alter column widths and easily delete or add columns and rows.
• Underline, embolden text and use different fontsfont: style applied to text, eg Times New Roman, Arial, Verdana are all types of font and graphicsgraphics: visuals intended to brand, inform, illustrate or entertain, eg photographs, drawings, maps, diagrams etc..
• Justify your datadata: information without context, eg a list of students with numbers beside their names is data, when it's made clear that those numbers represent their placing in a 100 metre race, the data becomes information to the left, centre or right.
• Control the types of numbers you enter - for example you can choose percentage, currency or set the number of decimal places.

Modelling: what if?

A spreadsheetspreadsheet: A spreadsheet is made up of cells, rows and columns. Each cell holds a piece of numeric (numbers) or alphanumeric (text) data. Cells can also contain formulae to calculate their contents. can be used as a modelling tool.

The model is controlled by a set of rules introduced by formulaeformula (plural formulae): in computing, a calculation. These rules can be changed easily to vary the model and, for example, provide information about running costs and profit margins.

A company could use a spreadsheet to find out what would happen if they reduced the price of their product, and the effect it would have on their income from sales. To do this they lower the value in the price column and the data in the income from sales column will be automatically recalculated (downwards). If the lower price resulted in higher sales, by adjusting the data in the number of sales column, the income from sales data will again be recalculated (upwards).

Being able to answer "what if?" questions like this is vital and allows a company to predict future trends in its income and outgoings.

Why use computer models?

As well as financial modelling, spreadsheetspreadsheet: A spreadsheet is made up of cells, rows and columns. Each cell holds a piece of numeric (numbers) or alphanumeric (text) data. Cells can also contain formulae to calculate their contents. softwaresoftware: a general term used to describe an application or a program can be used for many other kinds of computer model:

• modelling supermarket queues
• modelling the stresses which will be borne by a new bridge
• modelling traffic flow in a new road system

Computer models are cheaper to setup than alternative methods that could be used to predict what will happen in a system, eg building a prototype. Other benefits include being able to:

• make alterations and quickly see the outcomes
• repeat tests several times over
• learn from "what if?" scenarios
• model dangerous situations safely

Limitations of computer models

A model is only as good as the rules used to create it. A mistake could have been made by whoever wrote the model and not every situation might have been considered – both of these issues could cause the model to give incorrect answers.

Now try a Test Bite