GCSE Information and
Communication Technology

.: Home .: Application Software .: Spreadsheets

 

Spreadsheets

handwritten accounts

A spreadsheet is a piece of application software that is specifically designed to handle numbers, calculations and graphs and charts. Spreadsheets have been around for nearly as long as personal computers and they have always been popular pieces of software for business people, scientists, engineers, and anyone who uses numbers.

Spreadsheets are based on the ledger books that accountants used to use to record the cash flow in and out of a business. These books consisted of many rows of ruled lines on which business transactions were written. The records would be aligned in columns down the page. It was the job of the accountant to make sure these records were in order and that the calculations were correct. You can think of a spreadsheet as a computer-based version of these handwritten record sheets.
The account sheet shown here is a scan of an account held by a Mr I.W. Longbottom at an ironmongers store in 1878. You can see that there are columns of dates, text and numbers. The numbers are formatted as currency (pounds, shilling and pence in this case) and then added up at the bottom of the column.

Paper-based accounts are useful: you don't need any expensive equipment to use them, and they are fairly portable. A computer-based spreadsheet, on the other hand, will carry out your calculations accurately and allow you to print out many copies and even email your accounts to other countries.

Rows, Columns and Cells

an excel spreadsheet

Spreadsheets are divided up into many small boxes called cells. Each cell in a spreadsheet has a different row number and/or column letter than any other cell.
The spreadsheet shown in the picture here shows a number of cells, rows and columns. In the picture column B has been coloured in yellow. Row 6 has an orange colour.
The cell coloured blue is referred to as D2. This is because it is at the point where column D and row 2 cross. The green cell is referred to as C10 (it is in row 10 and column C).

Spreadsheets are useful because you can store numbers in these cells. If I typed '5' into the green square I could say that C10=5.

With a bit of work a spreadsheet can be made to look more attractive than just lists of numbers.

The spreadsheet shown here is part of a score board used in a school talent competition. The spreadsheet adds up the scores and calculates the positions of each of the forms. The leading forms are then highlighted by a change of colour.
Buttons around the score board are linked to macros: small pieces of program code that do a certain job every time they are needed (for example; one button draws a graph of the results; another one clears all the scores off the score board).

Milford Haven School's 2002 Eisteddfod

Excel Formulas (or formulae if you prefer)

  • =A1+B1
  • =C23-7
     
  • =sum(A:A)
  • =sum(A1:A4)
     
  • =average(B1:B7)
  • =min(A2:X2)

If you want your spreadsheet to carry out a calulation then you have to enter a mathematical formula (like an equation in algebra ... do you do algebra?). In Microsoft Excel you type in the equal sign '=' followed by the formula.

The first formula takes the number in cell A1 and the one in cell B1 and adds them together. The second formula takes seven away from the number in cell C23.
Formula 3 adds up all the numbers in column A. Formula 4 adds up only the first four numbers in column A (A1, A2, A3 and A4).
The fifth formula works out the average of all the numbers stored in cells B1, B2, B3, B4, B5, B6 and B7. And the last formula returns the smallest number in the first twenty-six columns of the second row.

You can get spreadsheets to carry out much more complex calculations and even write your own. The fact that spreadsheets can be used for so many different purposes means that they will remain popular types of application software.

 

Now, use the spreadsheet to answer these questions:

  1. What is the value in C2?
  2. What is the value in B1?
  3. What format are the numbers in column B ?
  4. How many decimal places are the numbers in column D ?
  5. What format are the values in column A ?
  6. How many decimal places are the numbers in column C ?
  7. What formula would you use to calculate D2?
  8. If you change C4 then what 2 cells will also change ?
  9. What formula would you use in D8 ?
  10. What other formula would work in D8?

You scored out of 10 on that test

example spreadsheet
© 2003 J Ewart | S Peters