choose what to learn computer index The ABC Study Guide, University education in plain English alphabetically indexed. Click here to go to the main index, or the ABC image for the cover
Choose what to learn Home Page 		the ABC Study Guide home page ABC Mathematics

home page to all of Andrew Roberts' web site

Understanding Spreadsheets
A spreadsheet is a computer program that does calculations on figures in rows and columns.

Rows go across
 

Columns go up and down:  

 







 






The basic screen design of a spreadsheet is a table of rows and columns.

The oblongs made by the intersections of the rows and columns are called cells.

Each of these cells has a name (address) that is made by putting the name of the column with the name of the row.

Cell B3 is the cell at the intersection of column B and row 3.

Cells are labelled:
    alphabetically from left to right: A,B,C, etc
    Numerically from top to bottom: 1,2,3 etc
A1 B1
A2 B2
A3 B3

A table can be created in any size. This table has six rows, three columns and eighteen cells.

We can fill in some of the cells with formulas that use the cell addresses of figures in other cells. If we alter the figures, the formulas will recalculate the results for us.

A1 B1 C1
A2 B2 C2
A3 B3 C3
A4 B4 C4
A5 B5 C5
A6 B6 C6

We can use a cell to enter: a title (see green cells), a number (see blue cells) or a formula (see yellow cells)

We can then alter the numbers whenever we like, and recalculate. The formula will automatically calculate the totals and percentages.

Age Groups Numbers Percentages
15 to 29 109 (B2/B6) x 100
30 to 44 218 (B3/B6) x 100
45 to 59 242 (B4/B6) x 100
60 to 74   46 (B5/B6) x 100
Total B2+B3+B4+B5 C2+C3+C4+C5

Here the spreadsheet has calculated the results for the numbers we have entered above. If we entered new numbers, the spreadsheet would calculate new results. Age Groups Numbers Percentages
15 to 29 109   18
30 to 44 218   35
45 to 59 242   40
60 to 74   46     7
Total 615 100

Percentages
The formula for a percentage is:
number divided by total, all multiplied by one hundred
If you look at the above diagrams you will see how a formula is entered in a cell to calculate this in a spreadsheet.
The formula (B2/B6)/100 in the example will work perfectly well
But, if you copy it to other cells you need to mark the cell reference for the total (B in this example) so that it does not alter when you copy it. In Excel spreadsheets this is done by putting a $ before the letter and number of the cell reference you want to stay the same. So the formula becomes: (B2/$B$6)/100

Normally, if you copy a formula from one cell to another in a spreadsheet it uses intelligent calculations to alter it to what you are likely to want. It uses relative rather than absolute positions. So, if you copy the formula down a row, instead of copying B2/B6, it will enter B3/B7. With percentages, you want it to do this with the number (B2 to B3), but to use the same total address each time (B6)

As well as the specialist spreadsheet programs that do this, wordprocessors often include a simple spreadsheet as a tool. In WordPerfect 5.1 and Wordperfect for Windows it is an aspect of tables. This is very useful if you need to include a table of figures in an essay.


External link to a site where you can try these ideas out and find out much more:
"What is a spreadsheet? on MathsNet

Notes on the Excel spreadsheet

  • clicking on the grey button at the top of a column highlights the whole column

  • clicking on the grey button at the left of a row highlights the whole row

  • clicking on a cell gives it a black border. This means that the cell is active - which means you can enter things in it from the keyboard.

  • You can enter words or numbers

  • If you enter words as headings, they will appear to stretch across several cells. You can adjust the row or column to fit by using the format menu or by mouse movements between the grey cells at the top or left side.

  • If you laid out a spreadsheet with headings down the side and headings across the top - ready to enter data - you could call that a template

  • When you create a template, or enter data, you should name your spreadsheet by saving it to your disk. Simple eight letter names are best for computer files. So, if your spreadsheet shows crime statistics for 1981 and 1994 you could save it as crime8194


    Study links outside this site
    Andrew Roberts' web Study Guide
    Picture introduction to this site
    Top of Page Take a Break - Read a Poem
    Click coloured words to go where you want

    Andrew Roberts likes to hear from users:
    To contact him, please use the Communication Form

    © Andrew Roberts

    		• Spreadsheet
    software

    Tables