BASICS

A spreadsheet programme is a powerful method of number-crunching and a method of presenting the results. Cells can hold the following types of data:-

* Note that dates and times are stored as numbers. Whole numbers start with 0 at midnight at the start of 01 January 1900. Times are represented by decimal fractions of a day. e.g. 0.5 would be noon on 01 January 1900. 37439.75 would be 18:00 today

It is the spreadsheet's ability to use formulae that makes them such a powerful tool.

This demonstration uses Microsoft Excel, but the principals apply to almost any spreadsheet.

Although it's possible to just jump into a new, blank spreadsheet and start calculating, there is a recognised formal approach which I recommend if you are going to use a spreadsheet over a period of time.

Each Cell can hold ONE item. If it holds a formula, the result will be displayed in the cell which holds it. A formula can manipulate numbers, or the contents of other cells.

References can be entered in the formula in one of two ways:

Formulas can do all of the common manipulations that you will be familiar with, and some that you are probably not. In the following examples A1 contains 3.5 and A2 contains 2.1. We can perform the following operations, and get the results indicated.

Add

A1 + A2 (3.5 + 2.1)

= 5.6

Subtract

A1 - A2 (3.5 - 2.1)

= 1.4

Multiply

A1 * A2 (3.5 * 2.1)

= 7.35

Divide

A1 / A2 (3.5 / 2.1)

= 1.66667

Convert to an Integer (whole number)

Int(A1)

= 3

Dates are stored as numbers and it is the formatting which gives them the appearance that we are used to. The following formatting options are available in Custom Formatting to change the appearance of the date and or time of 7 January 2002.

d

7

 

m

1

dd

07

 

mm

01

ddd

Mon

 

mmm

Jan

dddd

Monday

 

mmmm

January

         

m

   

yy

02

     

yyyy

2002

Additionally, spaces and commas may be inserted, so the format "dddd, d mmm yyyy" results in "Monday, 7 Jan 2002"

 

Formal Structure

A spreadsheet should comprise the following 4 sections:

Data Entry Area

This is where you make the space for the user to place their information. If you need any fixed numbers, you would enter them here also. If you have many fixed numbers, you could identify a separate part of the data entry area. This area should also contain any instructions to the user (who may well be you in a couple of weeks time, after you have forgotten how to use it).

Processing Area

This is where you manipulate the information in the Data Entry Area. No new information should be entered in this area, and it should contain ONE cell for every item of output data.

Output Area

This is where the results are presented. If the data is to be printed, then the Print Area will be set to this area. This area may contain titles and other text which may be formatted, but the only formulae which are allowed are the ones which copy the data from the Output Area. (e.g. "=A73")

Documentation Area

I prefer to use a separate worksheet for Documentation. If your spreadsheet doesn't allow this, just use another area of the worksheet that you are using. The Documentation Area should contain the following:

 

Demonstration and Examples

This demonstration will include some simple spreadsheets which I will put on the web-site so that you can look at them later, at your leisure.

In these examples I have coloured the cells as follows:

Data Entry

Blue

Processing

Yellow

Output

White

Documentation

Green

 

All formulae start with "=".

Start text with " ' ". Text can be formatted in all the usual ways (font, size, bold, underlined, etc). In addition to being aligned Left; Right and Centre, text can be made to occupy more than one cell. Later versions of Excel allow for cells to be merged. Do this with care! It severely limits what you can do with the cells afterwards.

 

Simple Calculator

 

A calendar

 

Membership records

 

 

Other functions

e.g. COUNT(A1:A7) will count the numbers in the seven cells.

e.g. COUNTIF(A1:17, ">23") will count all of the cells which have more than 23 in them. NOTE that it will not SUM them.

e.g. IF(B10="","", B10) tests the cell B10 to see if it is blank. If it is, then we keep this cell blank (by inserting ""), otherwise, we will copy the contents of B10 into here.

See Help for other Functions

 

Charts

Having crunched your numbers, you will want to show them in a pretty chart. Excel can produce many types of graphical presentation styles. The following show the numbers attending the 5 days of the Tiddlywinks World Championship, last month. The numbers (in 10,000's) are

Monday

8

Tuesday

10

Wednesday

12

Thursday

14

Friday

18

Total

62

 

 

A PITFALL TO AVOID

Excel has two methods of referencing cells. The absolute method is that each cell is labelled with a letter and a number (e.g. A1 is the cell in the top left hand corner of the spreadsheet). The other method is the Relative Referencing method in which a cell is referenced by where it is, from the current cell, (e.g. =R[1]C[2] means the cell on the row below, and two columns to the right). The referencing method can be changed in Tools | Options | General Tab. I recommend only working with the absolute referencing method.

 

ADVANCED TOPICS

Excel can be used for keeping records and changing the format of Data.

KEEPING RECORDS

One row is used for each record, and a separate column for aspect of that record. An example would be a Name and Address List. Each row would be for one person, and the columns would be Name, Address_1, Address_2, Address_3 and Postcode,

Name

Address_1

Address_2

Address_3

Postcode

Mrs Goggins

The Post office

Greendale

Yorkshire

YK10 2TV

Postman Pat

The Station House

High St

Dent

YK10 1TY

Ted Glen

Hams Farm

Watchit Lane

Greendale

YK10 2TD

 

SORTING DATA

Excel can sort records in ascending order. This can be useful to change the order of Jess's Address book, or any other situation where data is presented in one order, and you want to re-order it.

Jess's Address Book can be changed into Postcode order by :-

 

PROTECTION

You can create a spreadsheet and control how much of it people can change. If you had created a complex spreadsheet, it may be appropriate to lock the users out from all cells except the ones appropriate to Data Entry.

By default, all Cells are LOCKED, and protection is OFF. This allows all cells to be edited. The Protection of Cells is in Format | Cells | Protection.

To allow a cell of a protected worksheet to be edited after it is protected, remove the tick from Locked.

To Protect the worksheet, go Tools | Protection | Protect Sheet. When you click OK, the sheet will be protected. To remove the protection, so that you can edit any cells, repeat the operation. If you entered a password when you protected the worksheet, you will need to enter it to UnProtect the worksheet. If you lose the password go File | New | Worksheet, and start again.

A different form of protection is to use the File | Save As - Options. You can set a file so that it requires a password to open it - Read Only and a password to allow a person to amend it.