Home COMPUTER STUDIES TOPIC 2 : SPREADSHEET | ICS FORM 2

TOPIC 2 : SPREADSHEET | ICS FORM 2

498

TOPIC 2 : SPREADSHEET | ICS FORM 2

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.

1. 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. This gives 256 columns.

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

3. 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

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

5. 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

6. 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

7. 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.

8. 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

9. 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:

Spreadsheet is used in decision making. This is because it presents the convenient method of quick investigation of
Spreadsheet is also used for records keeping. Eg. Student’s records
Prepare budgets
Plan different activities
Create charts for data presentation
Prepare financial statements

Prepare statical

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
Enter the data in columns 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
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

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

5.5. Adjusting the width of columns

If the Twiga 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
Double –click the left mouse

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

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 )
Point to the highlighted area and click the right mouse
Choose Format Cells window, click on the Number tab (unless it is already )
In the Format cells window, click on the Number tab (unless it already selected).
In the category: box select Currency by clicking on
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

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 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
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 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
Click on the down-arrow of the Font Size button
Select another size, for example,

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 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 Your heading will be centered in that range of cells.
Click in any cell away from the

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 right-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.

Click, with the right mouse button, on the row name
Select Insert (and ignore the Insert Options smart tag).

Try inserting an extra column before column A

Click, with the right mouse button, on the column name
Select Insert

To delete a row or column, right-click on its name and select Delete from the menu which appears.

Spell-checking

Excel allows to check the spelling of your work. You have probably already used this technique in word.

Select a cell near the top of your worksheet
Click on the spelling button on the toolbar (or select Spelling from the Tools menu)

If a spelling mistake is found (or something that Excel thinks is a mistake but isn’t), you will be given the opportunity to correct or ignore it.

Excel will object to Twiga Secondary School. If you wish, change it to Twiga Secondary School by editing the text in the Not in Dictionary: box and clicking on the change All button.

When the end of the sheets is reached, the checking can be continued from the beginning of the sheet down to the cell you selected.

Saving your workbook

Your workbook can now be stored in a file on a floppy disk (the A: drive)flash, CD or on the Networked PC service (the J: drive).

From the File menu select Save As (or Save)

A Save As window appears.

Choose a name for your file

In the file name: box, type a suitable name (such as Twiga Secondary School) for your file.Later, Excel will add the .xis extension automatically.

Excel file names can have up to 218 characters including alphanumeric characters, spaces and the special characters except for ⁄, \ , > ,< ,* , ? ,” ,â”‚, ; and : Remember that Excel does not distinguish between upper and lower case letters.

Decide which drive to use

If you are using the Networked PC service, Excel will offer to save your file one your J: drive If you want to save the file to another drive (such as A:)

Click on the down arrow to the right of the Save in:
Select the drive of your choice Flash disk (F)

Note: On the Networked PC service, a quicker way of selecting the A: drive is to click the Flash Disk icon at the left –hand edge of the Save As window.

Decide which folder to use

If you want to choose a folder in which to save your file.

Double-click on a folder already displayed in the list box or click the Up One Level button (to move up one folder level) and navigate until you can select the folder you want.

On your own PC, you can click on the History button (at the left-hand edge of the Save As

window) to see documents and folders you have worked with recently.

Different file format

It is possible to save the file in a different file format by changing the setting in the Save as type: box.

Finally

Click on the Save button

After you save the file, the workbook window remains open and Excel displays the new name in the title bar.

Note: Once a file has been saved for the first time, subsequent changes can be saved by selecting

Save from the File menu, or by clicking the Save button on the standard toolbar.

Closing your workbook

You can close you workbook at any time

From the File menu, select Close

If you have made any changes to the workbook since it was last saved, you will be asked whether you wish to save those changes.

Click Yes to keep the changes or No to discard them (and leave the workbook as it was when you last saved it).

Note: To open the workbook later, select Open from the File menu, navigate to the location of the file, select it and click OK. You can have several workbooks open at the same time.

Calculations

You can now try doing some simple calculations using a worksheet that was prepared earlier.

If you are not using the ITS Networked PC service, you may like to get a copy of this file from the ITS Web pages before working through the rest of the document.

The file is called Twiga Secondary School.xls and can be found in the information /Guides section of the ITS

Opening a worksheet

From the File menu select Open
Change the Look in: setting to work file on ‘Dudley’(T:).
In the box of folders, double-click on its, and then on
Click on Twiga xls.
Click on Open

Entering formulae

All formulae begin with an equals (=) sign. If you forget to type the =, the rest of the line could be entered into the cell as a piece of text. Formula can contain values, cell addresses, mathematical operators and functions.

Some of the mathematical operator that can be used are:

They are listed here in decreasing order of priority starting with Percent, which has the highest priority (done first), and ending with comparison, which has the lowest (done last). If a formula contains operators with the same priority, they are evaluated form left to right.

If you want to alter the order of evaluation, use parentheses (brackets) to group expressions. Any parts of a formula that are in parentheses are done first. For example:

7 + 3/2 will be evaluated as 8.5 (not 5.0)

(7 + 3)/2 will be evaluated as 5.0

Entering a formula for total per person

In this case is the marks of maths, Geog, Chem and Biology

Click in G5
Type = C5 + D5 + E5 + F5 or = SUM (C5:F5)

Note that you have entered the cell references in the formula, not the values in the cells since those particular values might be changed later.

Press the Enter

Have a critical look at the value now displayed in G5. Make sure that it is correct. If it isn’t click in G5 and look at the formula displayed on the formula bar. If you find an error in your typing, correct it.

Next copy the formula to cells G6-G13,This will display the total marks for each student of form 1 in Tanga secondary for the four (4) subjects

Entering a formula for Average now calculate the average marks for each student in form 1 for the four subject

Click in H5
Type = (C5 + D5 + E5)/4 or type = G5/4 or

Type = AVERAGE (C5:F5)

Press the Enter key. The average marks will be

Copying a formula to another cell

A formula can be copied to another cell in the same way as text or numbers.In this case from h5- h13

Sorting

If you have a rectangular block of data surrounded by blank cells, then Excel recognizes this as a list. Data in a list can be sorted alphabetically numerically or chronologically. Excel rearranges the rows according to the contents of one or more columns.

If you select a single cell in the list, Excel will automatically select the whole list for you. If there are labels in the first now, excel excludes them from the sort. Alternatively, you can select the area of data that is to be sorted. Be careful though, sorting data selected by you does not move any non selected data in adjacent columns or rows.

On the summer worksheet of the Charity_Barbecue workbook, you have a list in cells B13:E19. (If you added extra people, the range will be different) it can easily be sorted by surname.

Click on the summer
Click on any name in the Party column
Click the sort Ascending button on the toolbar.

The list will be sorted in ascending order by name.

Immediately click the Undo button if you want to restore the list to its original order.

The sort Descending button can be used to sort a list in descending order. If you wish to sort by more than one column, select Sort from the Data menu.

For further information about lists, including sorting, see Guide 36: Lists and data management in Microsoft Excel 2003.

Printing

There are various stages to getting your information printed

Page Setup

From the File menu, select Page

By changing the options in the Page Setup window, you can control the appearance of your printed sheets. This includes choice of margins, headers/footers, page orientation (portrait/landscape), scaling, and precisely what will be printed (grid lines for example).

Click on OK in the Page Setup

Print preview

From the File menu, select Print Preview (or click on the Print Preview button on the toolbar)

In this mode you can, if you wish, view the next/previous page, zoom, use a magnifying glass, adjust margins, and change the page breaks.

Click the close button to return to your

Dotted lines will now be displayed on your worksheet indicating page boundaries.

Selecting a printer and printing your work

From the File menu, select
If you are working on the Networked PC service, by default, the printout should be directed to the printer in the room in which you are working. Check that this is the

If you want a different printer.

Click on the beside the Printer Name: box
Select the printer you want to

You can specify what is to be printed, which pages are to be printed and (To print your work, click on OK)

GRAPHS AND CHARTS

Data may be presented in graph or charts as well. From the table below

To change the colour, click on the bar you want to change the colour.

If you want to change that chart type,click on the on the chart and insert,select the type of chart.

PAGE PROPERTIES AND PRINTING

Includes:-

1) PAGE SETUP

It may include

Header footer setup
Selecting a printer
Paper layout (page layout)

Procedures

From the menu bar, click file then select page set up.

Page tab

On the page tab you may choose either portrait or landscape for the page
Select the page size as A4

ii. Header/footer

Click header/footer tab
Click custom header
There are three sections here, you, may type your header in any section depending on what type of the paper do you want your footer/header to appear. But in most cases the left section is prefered for
After typing your header click
Then click on custom

The footer may be either in the center section or right section

Click on the center section
Click the button shown as # the word and page will disappear in the centre section also, data and time may be set in the footer right section. After completion of footer and header setup click OK>

PAGE PREVIEW.

From the Menu bar click File. Then select print preview.

Note: Your page may default to two pages while the document is for one page only.

Procedures:

Click page break preview

Click OK to the window appearing.

If the document is making up two pages, there must be dotted separating those pages. Place your cursor on the dotted line and drug it to the right until you meet the solid line

WORKING WITH MS EXCEL WHILE YOU ARE IN MS WORK

Select ms excel form tools bar , work with your data

THE FOLLOWING IS SUMMARY OF PROCUREMENT

To shift/Return to Ms Excel Double click the Table.

EXERCISE

From the given data below,

Find, total, Average, and Grades
Draw bar graph, pie chart
Save file name with your name
Put the file in folder, save in desk top an my document

Formulating the formula

Note =, the number of opening brackets must be the same as closing brackets

Example, a student whose average is equal, or greater than 80, is placed in A grade, average equal or greater than 70 is grade B, average equal or greater than 50 is in C grade, otherwise F grade.

FORMULA

=IF(F2>=80,”A”,IF(F2>=60,”B”,IF(F2>=50,”C”,”F”)))

EXERCISE

Banda Enterprises had the following data.

FORMULA:

Discount=Basic price * Discount Basic Price

VAT=Sale Price* VAT Selling price*VAT

Selling Price = Sale Price + VAT

Question

Present your information in Chart form (column chart)
Cut the graph and copy it on the separate worksheet and rename worksheet as chart Create header and footer

“Excel test”as header (left section)
“You name,Data and time as footer(left section)
Save the work in your name and put it in the folder “on the desktop (My work)

1CHART

Answer 2

Practical work:

Manipulation:

This includes

Mathematical operation (+, -, x, and ÷)
Sum III.Average IV.Count
Minimum VI.Maximum VIII.Logical formulae etc

When using function, remember the following

Use an equal sign to begin a formula
Specify the faction name
Enclose arguments within parentheses
Use a comma to separate the arguments

SUM

The sum function is used to calculate the sums. For example

=SUM(2, 13, 10, 65)

In this function:

The equal sign begins the function SUM is the name of the function 2, 3, 10 and 65 are the arguments Parentheses enclose the arguments

Comma separates each of the arguments The other way of using sum function:

=SUM (A1:A5)

AVERAGE

Average function is used to calculate an average from a series of numbers.

Min(Minimum)

You can use Min function to find the lowest number in a series of numbers

Max (Maximum)

You can use Min function to find the highest number in a series of numbers Count

The count function is used to get the number of entries in a number field that’s in a range.

If (logical functions)

This is conditional formula in a logical manner (true or false). You can use “if” function to group or grade the marks/scores/data in a series of numbers.

Example 1