Explanation of Spreadsheet Concepts

CONCEPT 1: CELL

When you look at your spreadsheet, you will see it is made up of lots of small rectangles. Each of these is a CELL.

Each cell has its own ADDRESS. The address is made up of the COLUMN LABEL at the top (A, B, C etc) and the ROW LABEL at the left side (1, 2, 3 etc). You can see the address at the top left of the screen under the menu bar and tool bar.

To work with the concepts in this activity, you will need to do a simple exercise using Excel. This exercise will be completed in 11 steps, with steps being added after the various concepts have been learned. When you have finished this activity, save it in your folder on the network as Activity 1 and email it to your mentor as an attachment.

CONCEPT 2: AUTOFILL

Your mouse pointer in Excel is usually a fat white +

Note the small black square bottom right corner of cell A1: This is called the AUTOFILL HANDLE. When you move your mouse pointer over the autofill handle, it changes to a thin black +

By dragging with the autofill handle, Excel intelligently fills the cells you drag over. ("Intelligently" means Excel will carry on whatever you start with, so if you start with Monday, Excel will know to continue with Tuesday, Wednesday etc. if you start with 2; 4; 6; Excel will carry on with 8; 10; 12 etc - for as many cells as you drag your mouse over.)

CONCEPT 3: FORMULA

A formula is used to perform a calculation. A formula contains:

= at the beginning

numbers

cell addresses

operators: +, -, * (multiply), / (divide)

brackets: ( and ) used in calculations according to BODMAS rules

You type a formula in the cell where you want the result of your calculation to appear.

Examples of formulae:

=B2+B3

=B2+B3/2

=(B2+B3)/2

=(B2*3+B3*4)/2

CONCEPT 4: RANGE

When you want to refer to more than one cell, you use a range. For example, all the cells with numbers of boys are in the range B2:H2. All the cells with numbers of learners, boys or girls, are in the range B2:H3.

A range is a rectangular group of cells. Usually the top left cell is given first, with a colon separating it from the cell in the bottom right.

CONCEPT 5: FUNCTION

A function is a word which is inserted into a formula to achieve a particular result. A function is usually performed on a range. Examples of functions are:

=SUM(B2:H2) [adds up all values in the range]
=AVERAGE(B2:H2) [gives the average value of the range]
=COUNT(B2:H2) [counts the number of values - ie won't count words]
=COUNTA(B1:H1) [counts the number of items, whether values or labels]

CONCEPT 6: ABSOLUTE REFERENCE

When you autofill a formula, all the parts of the formula are incremented. So, where we had =B4*A6 in cell B5, we got =C4*B6 in cell C5. That's why there was a problem... there's nothing in cell B6. So, we need to lock the part of the formula which must not increment. This is called an absolute reference (as opposed to a relative one which changes). We use $ in a formula to indicate an absolute reference.

SUMMARY

By completing this activity, you have familiarised yourself with the key concepts which you will come across in the rest of the module.

Cells, cell addresses, rows and columns; values versus labels;
The use of Autofill to copy text or formulae intelligently to other cells;
The use of a Formula to perform a calculation using values, operators and cell adresses;
A Range of cells: a rectangular selection of more than one cell;
A Function to perform a particular task in a formula;
An Absolute Reference: the way to lock a part of a formula which is to be copied.

In addition, you have learned to:

Resize rows and columns;
Insert and delete rows and columns;
Use the currency format for a cell;
Select non-adjacent cells;
Create a chart.

The notes given here are limited to what you need to get a basic understanding of the concepts involved. You are encouraged to

explore the use of these some more on your own;
consult the tip sheets for more detailed information.