Information and Communication Technology

Spreadsheets

Using a spreadsheet to analyse numerical information

People have been using spreadsheets for a long time, although not always by that name. They have their origins in the world of commerce, where people needed to keep a count of the goods that they were buying and selling.


The image shows a spreadsheet in the form of a balance sheet for a Mesopotamian farm, written on a clay tablet around 4000 years ago.


The word spreadsheet comes from the large sheets of paper, ruled with rows and columns, that were used to keep accounts. The term was kept when spreadsheets were created with software applications.


This page looks at some of the commoner aspects of using a spreadsheet.

Entering data

Using a spreadsheet means handling numbers

Using a spreadsheet is regarded by examiners as requiring a higher level of skill than using many other software packages. So, for the IGCSE practical examination, and probably for other practical examinations as well, it is likely that you will be given a spreadsheet to work with. It will have already been partially completed and the examination questions will ask you to perform a number of tasks with it.

If you are creating your own spreadsheet however, you will need to be able to put the data into the sheet.

The commonest way is to type the data in using a keyboard, but nearly all modern spreadsheets allow the import of data from external sources as well. The details differ between spreadsheets but they will all accept data from:

Some of these options may allow a link to be formed, so that the spreadsheet updates each time that it opens, or when the data in the source changes.

And of course, you can also copy and paste from anything with a table, such as a word processor document. Although in that case, you may need to try both 'paste' or 'paste special', with appropriate selection of data type, to get the layout correct.

Formatting the data

Making the numbers mean what you want them to

There are two types of formatting to consider when using a spreadsheet.

The first is the type that is used in other applications such as word processors and is concerned with appearance, e.g. text size, colour, font, borders and backgrounds, etc.

The second is more specific to spreadsheets and involves selecting the type of data that is to be shown in each cell.

Formatting for appearance.

This is likely to come under 'making a publication fit for purpose'.

In an examination, there may be marks for specific formatting tasks, e.g. Making the data display as blue,12 point, bold, in the Arial font.

There may also be marks for implied tasks, e.g. you may be asked to ensure that the result of a calculation is clear, or make the spreadsheet easy to understand.

This type of formatting also covers items which are specific to tables. In general, a spreadsheet can perform all the formatting tasks that can be carried out with word processor tables, including; text wrap, row height, column width, gridlines, merged cells, cell borders, shading, etc.


Formatting for data type.

All spreadsheets have a default data type that is used for all the cells when a new sheet is created. It is usually a general type that will will allow you to type numbers or text into a cell.

Common data types that you should be able to use are:

If you enter 1 or greater, the number is displayed with a following % sign. e.g. 1.5         displays as 1.5%

In both cases, if the number is used in a calculation the spreadsheet treats it as the         actual number. i.e. 0.5 and 1.5, not 50 and 1.5

It is important to look at how the date and / or time is formatted. If you get it wrong the         cell will display incorrect data, even if you put in the correct figures. e.g. months and days         may be swapped around.

Some calculations can be performed on date - time cells, e.g. adding 2 to a date should           display the date for two days later. Other calculations such as dividing a date may not         work so well.

Cell referencing

Column first, row second

When using a spreadsheet, you have to be able to tell the spreadsheet, and sometimes other people, where the numbers are. This is done by referring to the cells, the little boxes that make up the sheet. Spreadsheet cells are named with a column letter and row number. e.g. A1, C6, R42. When there are more than 26 columns, two letters are used e.g. AA32,

These cell names may be used to refer to the contents of a cell in a formula or function.

When a formula or function is copied and pasted from one cell into another, the cell reference will change automatically unless you tell it not to. e.g. if cell A1 holds the formula =B1 + C1 and that formula is copied and then pasted into A2, the cell references will change so that the formula in A2 is =B2 + C2

In other words, the spreadsheet will try to anticipate how a cell reference would be changed if you had to do it by hand. In most cases this will give the correct answer. This is because spreadsheets are usually set out in a logical manner, with columns of figures displaying rows of calculations.

Where cell references can be automatically changed by the spreadsheet, the cell reference is known as a relative cell address.

Sometimes though, it is essential that the cell reference does not change when a formula or function is moved into a new cell. e.g. the cell reference may point to a single figure that must be used in many different calculations. This might be a tax rate or a conversion factor for currencies. Having relative addressing for that cell would mean having to duplicate the figure and put it into a new cell whenever the formula or function is moved. This is possible but a big waste of time and effort.

The answer is to use absolute addressing. This involves putting $ symbols into the cell reference e.g. $a$1, $A1, A$1

The $ symbol tells the spreadsheet that whatever comes after the symbol must not be changed if the formula or function is moved.

So, $A$1 means that the cell referred to must always be A1.

$A1 means that the column must always be A, but the row number can be changed

A$1 means that the column can be changed but the row must always be 1.

Formulae and Functions

Both formulae and functions are ways of manipulating the contents of a spreadsheet.

For the purposes of the IGCSE, a formula is something you write yourself. e.g. =A1 + A2

while a function is something that is built into the spreadsheet and is indicated by a key word e.g. =SUM(A1...A2)

Both of these would in fact give the same answer, they just get there in a different way.


This distinction is a bit artificial though, since a formula can contain a function

= A1 / SUM(A1...A2)

and a function can contain a formula =SUM(A1/2+ B1)


For examination questions, you will be expected to write formulae which use the common mathematical operators, + - * / . You may need to use more than one operator in a single function. e.g. = A1 + 2 / A2 - 6

When using more than one operator, you must remember the order in which the calculations are performed () * / + -

Thus 3 * 1+2 = 5 because the 3*1 will be calculated first, then the 2 will be added

But. 3*(1+2) = 9 because the (1+2) will be calculated first and then multiplied by 3.


For the IGCSE ICT, you will need to know how to use a few simple functions:

SUM, AVG and IF...THEN

SUM adds up the contents of a set of cells. The cells do not have to be grouped together, although they often are. e.g. =SUM(A1...A3)

A common mistake with SUM is to include + signs e.g. =SUM(A1+ A2+ A3) This will give a correct answer but is likely to lose marks in an examination.

AVG This calculates the average of the contents of a set of cells. e.g. =AVG(A1...A3)

IF...THEN This allows the spreadsheet to make a decision based on the content of a cell. The exact format of this function differs between spreadsheets but in general terms it will take the form =IF(decision, true value, false value) e.g. =IF(A1=B1,1,0) will give an answer of 1 if cell A1 = cell B1, otherwise it will give an answer of 0.

Linking tables

Quite often, you will develop or use a spreadsheet that has more than one sheet. These extra sheets will show as tabs, usually at the bottom of the spreadsheet window.. You may also be able to see them in a separate navigation window, depending on which spreadsheet you use.

When you reference a cell on one sheet from one on another sheet, the cell reference must include the sheet name. Apart from that, the rules for relative and absolute addressing are the same as when working on a single sheet.

It is also possible to reference a cell on a completely different spreadsheet by including the name of the other sheet in the cell reference.

The only complication is that the second sheet must be open on your computer while the data is being looked at.


Fortunately however, references to other spreadsheets is not needed for the IGCSE practical examination.

Macros

A macro is the name given to a piece of computer code or scripting which allows you to perform a particular task in a spreadsheet.

Macros are usually written in a programming language such as Java or Visual Basic for Applications, or in a scripting language such as Java Script.

Fortunately, at IGCSE level, you don't need to actually write a macro as modern spreadsheets include a macro recorder.

The recorder tracks all of the mouse clicks and keystrokes that are involved in performing a task and writes a macro for you.

The macro can then be run to repeat that task automatically.

Modern spreadsheets usually have a number of built-in macros that you can use. These will allow you to create items such as drop-down boxes or buttons which will run another macro when clicked.

For the IGCSE practical examination, it is unlikely that you will need to produce a macro at all, although you may have to explain what one is and justify how it might be used.

Graphs and Charts

First of all, what is the difference between a chart and a graph?

Trick question, they're both the same, a visual representation of data. i.e. a picture. Different spreadsheets use different terms, some call them charts, some call them graphs.

For the IGCSE, the term graph tends to be used for pictures that represent the data by a single line, while other forms such as pie charts are called charts. As long as you understand what sort of picture is being asked for, it doesn't really matter whether it is called a graph or a chart. I'll refer to them all as charts for the rest of this section.

There are lots of different types of chart, but for the IGCSE you only need to know: pie chart, bar chart, single line graph, scattergram.

Pie chart.This is a circular chart divided into sectors. The whole circle represents a total quantity of something and each sector represents a sub-division of that something. The area of each sector is proportional to the quantity it represents.

Pie charts work best when there are only a few sectors and none of them are very small compared to the others.

Bar chart. This has rectangular bars where the bar length is proportional to the quantity it represents. The bars can be horizontal or vertical. Bar charts work best for data which has specific values, such as age or class size. It is not as useful for continuous data such as height unless the data is rounded off. e.g. height to nearest centimetre.

Single line. This shows the data as a series of points which are joined by a line. In many cases, the data points should be joined by a smooth curve representing the line of best fit. Most spreadsheets cannot produce such a curve and just join the points with a series of straight sections.

Scattergram. This shows the data as a set of points but does not draw a line through them. Scattergrams are often used when looking for relationships between two values. e.g. height and weight.

Creating graphs and charts

How to score marks in the examination

It is almost certain that an IGCSE ICT practical examination will have a question on spreadsheets.

It is very likely that the question will include the creation of a chart. There are lots of marking points in a typical chart and it's easy to mark.

It should be simple to get all the marks for a chart, but it is also very easy to throw marks away by creating a poor chart.

When creating a chart, you need to think about: appropriate format, titles, data range, axes, labels, legends.

Appropriate format. This covers two main aspects. Firstly, what type of chart is best for showing the required information. You may be explicitly told the type of graph, bar chart, line, etc. but if not you must read what the question says and decide what type is best. If you have to make a choice, you will probably have to justify that choice as well.

Secondly, should you use landscape of portrait orientation. You are more likely to have to make this decision than you are the one about chart type. It is harder to set a mark scheme that allows for different types of chart, but easy to give a mark for landscape / portrait. The information you need will be in the question. e.g. it may ask for more divisions on the X axis than the Y axix. In which case landscape is probably best. It may be that the chart has to fit into a column of a newsletter, so making portrait a better idea.

Titles. Read the question, if a specific title is given, make sure you use it, and spell it correctly, and use capital letters where needed. If the question just says the chart is about xxxx or must show xxxx, then you will need to make up your own title. In that case, the title must reflect what is asked for, and be spelled correctly, and have capital letters where needed. e.g. If you are asked to create a chart showing the number of pupils in each class of Bankside College, giving the title 'pupil numbers' is unlikely to get a mark. The title 'Class sizes in Bankside College' probably will.

Data range. This not a specific item in the IGCSE specification, but it is essential to get it right. If a specific range is not given in the question, it should be safe to assume that all of the data is needed. A common mistake is to include blank columns when selecting the data. The spreadsheet will still create a chart but you will lose marks.

Axes. Get them the right way round. Conventionally, the data type that you control goes on the X, horizontal axis and the data type that you are measuring goes on the Y, vertical axis. e.g. if you are charting the number of people who have particular shoe sizes, the show size is X because you decide what shoe sizes to look at. The number of people who have each shoe size is Y, because each person's shoe size is what you measure.

Labels. You need some. At the least you should label the axes. X and Y don't count as labels, make sure you put in something appropriate, and spell it correctly, and use capital letters where needed. You may also want to label other parts of the chart. e.g. on a line chart showing two or more lines, you may want to label each line, although that could also be done with a legend (see below).

Legend. This is a key to what the lines or bars or points on the chart represent. Most spreadsheets will put one in automatically when you create a chart. If you want a legend, make sure that the automatic labelling of that legend makes sense..

Important. If there is only one set of bars, line, etc. you do not need a legend. Get rid of it, you will lose marks if you don't.

Contact Privacy Lycett-King.com Forum ICT index Learning Objectives Home
Home Learning Objectives ICT index Donate Lycett-King.com Privacy Contact