COMPUTER O LEVEL(FORM TWO) NOTES – TOPIC:2 SPREAD SHEET

TOPIC:2 SPREAD SHEET

Sheet is a piece of paper with vertical and horizontal line.

Spreadsheet allows the operator to enter and to store data in a computer, in a grid format. Calculations are made according to the formulate entered. The programme makes changes easily and quickly.

Any entry can be changed and the effect of the change calculated automatically. Entries can be numbers for calculation. Entries can be words to form headings or formulae.

The worksheet of a spreadsheet is divided into a number of sections.

i. Column: Line of cells down the worksheet. Each is identified by a letter. The identification letters starts with A, B, C…. They go through AA, AB, AC,…..,BA,BB,BC,….. and so on up to IV. This gives 256 columns.

ii. Row: Horizontal lines cross the worksheet. These are identified by numbers from 1 on wards up to 65536.

iii. Row: Horizontal lines cross each other they form a cell. Each cell is identified by a column letter followed by a row number. For example the first cell is A1. On the worksheet information is fed in cells. Moving about the on the spreadsheet is shown by a cell printer. Current cell is indicated highlighted rectangle. There are 256 x 65536 cell in a worksheet.

iv. Label: Text entries on the worksheet are called labels. These should be kept to minimum. They are normally limited to columns and rows headings only.

v. Status area: The area on the screen display which gives information about the current worksheet. The information includes content of the current cell, name of the worksheet formula and graphical actions.

vi. Cell address: The sell which is currently on use. The combination of row and column coordinates make up a cell address. Sometimes is known as active cell or selected cell.

vii Range: is the assortment of cells. Referring to all cells between and including the reference. It consists of two cell addresses separated by a colon. Eg. For A1:A3 this includes cells A1, A2 and A3.

Viii.Union: A union reference includes two or more references. It includes two or more cell addresses separated by a comma. E.g. A7, B8, C9 this refers to cells A7, B8 and C9

ix. Status area: the area on the screen display which gives information about the current worksheet. The information includes the content of the current cell, name of the worksheet formula and graphical actions

Applications of the spreadsheet:

i. Spreadsheet is used in decision making. This is because it presents the convenient method of quick investigation of data.

ii. Spreadsheet is also used for records keeping. Eg. Student’s records

iii. Prepare budgets

iv. Plan different activities

v. Create charts for data presentation

vi. Prepare financial statements

vii. Prepare statical data.

Click start
Go to all programs
Go to Microsoft office
Click Microsoft excel

Close spreadsheet
Go to the right corner of the bar click close

Entering data

Data is entered into the worksheet by moving the cursor to the appropriate position on the screen, clicking the left mouse button to select that cell, and then typing the information required. The characters you type will appear in the active cell and on the formula bar.

When you have finished typing data into a cell you should signal the end of that data in one of the following ways:

Press the Enter key (the cell below becomes the active cell)
Press one of
the arrow keys (the active cell move one place in the direction of the arrow
Click on the Enter box (marked by a green tick) on the formula bar (the original cell is still the active cell).

The are two basic types of information that can be entered into a worksheet: Constants and formulae.

The constants are of three types: numeric values, text values, and date and time value. Two special types of constants, called logical values and error values, are also recognized by Excel but are not discussed in this document.

Numeric value include only the digits 0-9 and some special characters such as + – E e () . , % /

A numeric cell entry can maintain precision up to 15 digits. If you enter a number that is too long, Excel converts it to scientific notation. For example, if you type 12345678901234567, it will be stored as 12345678901234500, and displayed as 1.23457E16. sometimes, although the number is stored correctly in the cell, the cell is not wide enough to display it properly. In those cases, Excel might show a rounded number or even a string of # signs –just increase the width of the column

A text entry can contain up to 32,767 characters (only 1024 will display in the cell but all will shown on the formula bar). If the text you enter will not fit in the particular width of your cell, Excel lets it overlap the adjacent cell unless that cell already contains an entry, in which case the extra text can be thought of as being tucked behind the adjacent cell.

Creating a simple worksheet

Instructions are given for creating the worksheet shown below. The first stage is to get the correct values in the cells. Later you can make the worksheet more attractive by the use of bold text, different alignment and color.

5.1. Entering the data

Click in cell B2 and type

Twiga secondary school

Press the Enter key (or one of the arrow key).
Click in cell B4 and type

ID NO.

Press the down-arrow key to move to cell B5
Type

Rachel Maingu

Press the down-arrow key and then complete column B as shown above.
Enter the data in co
lumns D, E, F and G (but not H and I)

5.2. Amending data

If you are in the process of entering data in a cell and you notice that you have made a mistake, it is easy to correct it. Press the Backspace ( ) key to delete a character to the left of the cursor or the Delete key to delete a character to the right of the cursor.

If you want to edit the contents of a cell you dealt with earlier, you should double-click in that cell and make the alternations either in the cell itself or on the formula bar.

If you want to clear a cell of its contents (formula and data), formats, comments or all three you can selects that cell with a single click of the left mouse button, select clear from the Edit menu and then click on contents F formats, Co comments, or All.

There are a couple of quick ways of clearing the contents of a cell. The first is to use the right mouse button to click in the cell and then select clear contents from the menu that appears. The second is to select the cell by clicking the left mouse button and the press the Delete key on the keyboard.

Do not confuse using the Delete key with selecting Delete from the Edit menu. That command carries out the more drastic action of removing the entire cell from the worksheet and shifting the surrounding cell to fill in the resulting space.

5.3. Ranges

In Excel, any rectangular area of cells is known as a range. The range is defined by the top-left and bottom –right corner cell references separated by a colon(:).

So, B4:D7 represents the range of cells cornered by B4 and D7.

5.4. Copying and pasting

The text in cell E5:E8 ( in other words E5, E6 and E8) is the same as in the range B5:B8 so you can copy that.

Point to cell B5 and hold down the mouse button
Keeping the button held down, drag the pointer to B8 and then release the mouse button. The range B5:B8 will be highlighted.
s
tyle=”background-color: transparent;color: black;font-family: Calibri,sans-serif;font-size: 11pt;font-style: normal;font-variant: normal;font-weight: 400;list-style-type: decimal;text-decoration: none;vertical-align: baseline”>
Click on the copy button on the toolbar (or selected Copy from the Edit menu).
Click in E5 (where you want the copy to be place).
Click the Paste button on he toolbar (or select Paste from the Edit menu).

In addition, there is a smart tag(the Past Options one) near the bottom right-hand corner of the pasted data.

A smart tag can be:

Ignored –it will disappear when you carry out some other action
Removed –by pressing the Esc key
Used – point to it and click on the down arrow to display several options; select one and press the Enter key

For now, just ignore the Paste Options smart tag and finish the process of entering the data.

Click in any cell to remove the highlighting
Press the Esc key to remove the flashing dotted line (marquee) round the cells you copied.

Note: selecting non-contiguous ranges

If you need to select cell that are not contiguous, (ie, ranges that are not necessarily next to each other), proceed as follows.

Select the first area of cells
Hold down the Ctrl key while selecting the other areas.

5.5. Adjusting the width of columns

If the Twig
a secondary school text is too wide for columns B try widening those column.

Move the cursor to the division between the areas containing the column names. Note how the shape of the cursor has changed to a vertical black line with arrows pointing right and left.
Double –click the left mouse button.

The column will be widened (if necessary) automatically.

If you wish to have control over the sizing of the column, it can be done manually.

Move the cursor to the division between the areas containing the column names
Hold down the left-hand mouse button and drag that column divider the required distance to the right
Release the mouse button.
Repeat the process if you want to make fine adjustments.

5.6. Entering currency values

If you have column containing amounts of money. You do not have to type the â‚¤ sign, instead you can enter the numbers and format the cells as currency later.

Formatting the cells

Highlight the cells ie F5 to F7 (Point to F5, press the mouse button; while keeping the button held down, drag the pointer to F7; release the button.)
Point to the highlighted area and click the right mouse button.
Choose Format Cells window, click on the Number tab (unless it is already selected.)
In the Format cells window, click on the Number tab (unless it already selected).
In the category: box select Currency by clicking on it.
Make sure that Decimal places: is set to 2; symbol: show â‚¤ and that the first option in the Negative numbers: box is highlighted. Sample: shows you what the result of the formatting will be (â‚¤0.50).
Click on Ok.

Alternatively, you could have formatted the cell first and then entered the values.

If you begin a numerical entry with a â‚¤ sign, Excel assigns currency format to that cell. Similarly. If you end a numerical entry with a % sign, Excel assigns Percentage format to that cell.

Note: Excel 2003 includes the Euro symbol € for currency.

5.7. Formatting a cell before entering a value

Now try formatting a cell before entering its value.

Click in F4
Click on the Bold and Align Right buttons. The cell will not look any different until you enter a value
Type

Costs

And press the Enter key.

The text should be in a bold front and right-aligned in the cell.

5.8. Formatting text

You have entered all the data you need but you might like to make the worksheet easier to read by formatting some of the text.

First make the word Allocation bold.

Click in B4.
Click on the Bold button on the toolbar.

Next apply a bold font to Adult in C4 and child in D4 and center the text in the cells as follows:

Highlight cells C4:D4
On the toolbar, click on the Bold, italic and Center button. If you can’t remember which those are, try pointing to each button in turn –a small pointer message will appear showing its name.

Next apply a bold italic font to Adult in C4 and Child in D4 and center the text in the cells as follows

Highlight cells C4:D4
On the toolbar, click on the Bold, Italic and Center buttons. If you can’t remember which those are, try pointing to each button in turn

–a small pointer message will appear showing its name.

5.9. Using different sizes and colors

The charity Barbecue heading of this worksheet can be made more interesting by changing its size and colour.

Click in B2.
Click on the down-arrow of the Font Size button
Select another size, for example, 12.

Your text in B2 will increase in size.

Click on the down-arrow of the Font color button.
Select a suitable colour for your text.

It is possible to colour the background of cells and to centre a heading across a range of cells.

Highlight the cells B2:F2
Click on the down-arrow of the Fill color button.
Select a suitable colour for your background. Be sure to make it different from the colour of your text or you will not be able to see the heading!
Make sure that the range B2:F2 is still selected and click on the Merge and center button. Your heading will be centered in that range of cells.
Click in any cell away from the heading.

5.10 Naming a sheet

The information on sheet 1 might refer to Twiga Secondary School to be held in Term II. It would make sense to name the sheet accordingly.

Right –click on the tab sheet 1
Select Rename from the menu that appears
Type

Term II

And press the Enter key (or click in a cell).

Adding a border

Sometimes a worksheet can be made more attractive by adding borders around cells.

Select the rectangle of cells starting with B2 in the top left-hand corner across to F7 in the bottom ri
ght-hand corner.
Click on the down-arrow to the right of the Borders button on the toolbar
Click on the second example in the third row (All Borders). This shows borders around all the cells.
Click on a cell not currently selected (to remove the highlighting).

Another way of adding borders is to select Cells from the Format menu and click on the Border tab. First select a style for a line, then apply it by clicking the presets, preview diagram or buttons on that Border tab in the Format cells window. This approach gives you more options.

If you would prefer to draw the border around cells yourself, click on the down-arrow to the right of the Borders button on the toolbar and select Draw Borders. On the Borders toolbar that appears, select a colour and style for the line, and choose to draw either a grid or just a border. Then, just draw on your

worksheet. An eraser is also available.

Inserting and deleting rows and columns

Extra rows and columns can be inserted whenever you wish. As an example, insert a row between rows 4 and 5.