Spreadsheets EXCEL lessons intermediate


Télécharger Spreadsheets EXCEL lessons intermediate

Formation Excel en ligne par vidéo

205 leçons vidéos + 20 Livres PDF + 20 TP + Sous supervision + Certificat de réussite à la fin du cours



★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :


SPREADSHEETs

Microsoft Excel

¨  It is an electronic spreadsheet, with capability of tabulating data, simplifying numeric calculations and presenting numeric data graphically.

¨  Ms Excel also provides database management facilities whereby it has the capability to develop a database, add records, edit records, and delete records.

¨  It also provides facility to do statistical analysis of data.

Spreadsheet

¨  It is a layout of rows and columns which is used to organize data that is predominantly numeric in tabular manner.

¨  A spreadsheet provides you with worksheet. A  worksheet is a tool that is used for maintaining predominantly numeric data in tabular form and further provides facilities to do calculations and generating charts. Applications of Spreadsheet Programs

¨  Preparing payrolls - Calculating employees salaries i.e. gloss pay, allowances, deductions and the net pay. ? Presenting students performance - to store students information e.g. marks, calculating total marks, average marks and grading.

¨  Creating and maintaining personal budget- tabulating monthly expenses and calculating total monthly expenses.

¨  Calculating mortage payments- to calculate the monthly repayment amount on a mortage loan.

¨  Comparing  student performance in form of  charts.

¨  Calculating profit and loss of businesses.

¨  Preparing income tax statements among others

Other examples of spreadsheet programs

?

Ms Excel

?

Lotus 1-2-3

?

Symphony

?

Quarto pro

?

Multiplan

?

Visicalc

?

Supercal

Methods of loading/starting Ms Excel in windows

¨  Use of  All programs

¨  Use of run

¨  Use windows explorer.

¨  Use of a desktop  shortcut.

¨  Use an existing file

Microsoft Excel Window

(a) Title bar

This bar shows the name of the work book which is open (i.e one you are working on)

(b)Formula bar

It indicates what is being entered in a cell. It is also where editing of content of a cell can be done.

(c)Name box

Contains the cell address of the active cell

(d)Tabs

Contains the logical groups used to perform various operations ,Insert,Page layout,Formulas etc

(e) Rows

They run across the screen horizontally and are named using numbers 1,2,3.The numbers  are referred to as row headers (f) Columns

They run down vertically and are named using letters of alphabet i.e. A, B, C, etc. These  letters are referred as column headers (g)  Cell

It is an intersection between a row and a column. They are named using columns and row     co-ordinates. For example, where column A and row 1 meet they make cell A1. (h) Working Area

It is made of many cells arranged both vertically and horizontally.

(i) Sheet tabs

     It shows the worksheet or chartsheet which is active - i.e. the sheet which is being worked on.  We have sheet 1, sheet 2 , sheet 3 etc.

(j) Active cell

It is the cell on the worksheet which is selected by the cell pointer and it is the cell which is ready to be entered data.

Workbooks And Worksheets Workbook

? In Ms Excel, a workbook is the file, in which you work and store your data. Because each workbook can contain many sheets, you can organize various kinds of related information in a single file.

Worksheet

¨  The primary document you use in Microsoft Excel to store and work with data. A worksheet consists of cells organized into columns and rows and is always part of a workbook. Also called a spreadsheet.

¨  Use worksheets to list and analyze data. You can enter and edit data on several worksheets simultaneously and perform calculations based on data from multiple worksheets. When you create a chart, you can place the chart on the worksheet with its related data or on a separate chart sheet.

¨  The names of the sheets appear on tabs at the bottom of the workbook window. To move from sheet to sheet, click the sheet tabs. The name of the active sheet is bold.

Active sheet

¨  Is the sheet that you're working on in a workbook. The name on the tab of the active sheet is bold.

CREATING MS EXCEL DOCUMENT

Entering Data in worksheet

¨  Position the mouse pointer at the cell to enter the data and click the left button.

¨  Type the data using the keyboard. ? Press enter key on the keyboard. Editing Data (correcting mistakes).

¨  Double click the cell that contains the data you want to edit.

¨  Use the left or right arrow key to move the cursor to the location to make correction. ? Make the necessary change then press enter. Navigating with a worksheet.

¨  You can use mouse to move within a worksheet. Click the cell where you want to move.

¨  Alternatively you can use the arrow keys on the keyboard. Use them to move left, right, up or down depending on the key you press.

Saving a new worksheet

After entering data on worksheet, you need to save it for future use. To save a new worksheet; ? From the file menu select Save As or click save button on toolbar.

¨  On the save as dialog box that appears, default file name Book1 will be displayed as the file name. ? Select the location where to save the file.

¨  Type the file name then click save button.

 

Saving changes on the worksheet

After making any change on the worksheet, it is important to effect the changes to the already saved one.

To save any change;

? Click save button on the toolbar or ? Select save from the file menu.

Closing the worksheet

¨  First ensure everything is saved before closing the worksheet.

¨  Click on the file menu then Select close option or click on close button(X) at the right corner of the screen incase you forget to save the worksheet, Ms-Excel warn you and gives you the last chance to save.

 

¨  You can choose;

Yes: to save the details incase you had not saved.

No:  to close the file and the changes ignored.

Cancel: to ignore closing the file and taken back to the worksheet.

Exiting Excel

¨  When you have finished working with Ms Excel you exit to close the program.

¨  Ways of exiting Ms Excel:

i) Choose exit from the file menu or ii) Click closing button on the top right corner of the screen or iii) Press Alt + F4 simultaneously.

¨  After selecting either of the options, Ms Excel is closed and taken to the desktop.

Opening an Existing Ms Excel File

? To opening an existing Ms Excel file, use either of these options.

(a)-Open the program i.e Ms Excel

-From the Office button, select open.

-On the open dialog box, select the location where the file is found  e.g. drive C, My Documents, or desktop. -Use the scroll bar to locate the file.

-Click on the file icon when you locate it to select it.

 

-Click on open button to open the file. Fig. Opening file known as Sales

(b) -Select where the file located direct e.g. drive C.

      -On opening where the file is located, double click the file to open it.

Adjusting Column Width

It is important to adjust column width especially when entering a longer name on the cell. If  the entry of the cell is too wide to fit into the cell, Ms Excel splits the contents of that cell to the next cell which is overwritten by any entry you make in that cell.

Incase of values, Ms Excel displays them in harsh(####). It now becomes vital to adjust the cell width.

Way of adjusting the column width

(a)   –    Click on the column to adjust.

-    From the format menu, select  column-width.

-    On the Column Width dialog box, enter the required width.

-    Click OK button.

 

(b)   –Position the mouse pointer at the right boundary of the column  header so that the   shape changes to a cross.

-    Double click the boundary. This will adjust the column width automatically so that the   widest data entry in  the column just fits. This technique is called Best fit method. -If the size is not enough when the mouse is on cross shape, drag the column border while  holding down the left mouse button until you have the desired column width.

Aligning Text:

Ms Excel treats every entry entered in the worksheet as either Label or Value. Label

•  It is any entry that is alphanumeric i.e. A entry that is either made up of letters of the alphabet or a mixture of both letters of alphabet and numerical data that cannot be manipulated mathmatically.

•  Also numeric data with spaces, slashes  between them are regarded as a label in Ms excel entry.

•  NB: Ms Excel aligns label automatically to the left Examples of Labels:

•  124AA, 343-43, CC456, ABASF, 343 890, 786/908.

Value

•   It is any entry that isNumeric.

•   All numeric entries are aligned automatically on the Right in the cell.

Examples of Values

? 8900, 102, -564, 78.890, -767, $890, 566%, Kshs 7845.

 

NB : However the default alignment of  the entries can be changed to others.

For example you can change value alignment  from right to center or left and the vice versa.

Inserting /Deleting  Column(s) and Row(s)

? After creating a worksheet, a situation may arise which requires to add more information in between the existing rows and columns. Therefore a new column or row needs to be inserted depending on what is needed.

 Inserting a Column

-     Click/Highlight on the column on the right of where to insert the column; then right click on the highlighted column and click on insert sheet columns;.

-     Click/Highlight on the column on the right of where to insert the column ;From Home tab click  on insert then select insert sheet columns. A new column is inserted. Inserting more than one column at once.

•  The number of columns to insert at once depends on the number of columns highlighted(selected).

•  To insert many columns at once, select the number of columns to insert the repeat the        procedures for inserting a single column.

Inserting a Rows

•  Click on the row to be pushed down to pave way for the new row.

•  From Home tab, select insert then click insert sheet rows and a new row is inserted.

NB To insert many rows at once, select the number of columns and where to insert then from Insert menu, click rows and new rows will be inserted.

 

•  select delete sheet columns option.

Or Right click on select column and click delete

 

Deleting  a Row

•  Select the row to delete.

•  From the ribbon, select delete

•  Click delete rows

Or Right click on select row and click delete

 

Copying a Data in worksheet.

To avoid retyping information on the worksheet you can duplicate it by copying.

Steps

•   Highlight  the section of the worksheet to copy.

•   From the Home Tab select copy.

•   Click on the first cell  on  where to place the copied data/text.

•   Then click Paste and the data is copied .

Moving Data on  worksheet

You can move data  from one part of the worksheet to another.

To move the data;

•   Highlight the  cells range to move.

•   From Home Tab , select cut.

•   Click on the first cell of where to place the data.

•   Then click paste and the data will be placed on the new location of the worksheet.

ENHANCING THE APPEARANCE OF WORKSHEET

 This involves formatting  of cells contents. It includes  applying different :

•   Number formats

•   Font colours, sizes, styles, underlining.

•   Text  alignments

•   Background colours and patterns

•   Text orientations

•   Cells borders

Formatting Numbers

This involves applying the desired date format(short, long, medium),currency type($,kshs,pounds), time format(e.g.1:30, 1:30:50.,22:30hrs),applying percentages, fractions, setting decimal places(e.g. 56%,678.2, 785.34,)

(a)formatting date

Highlight the column for the date.

Select cells from Home Tab-Right click and select Format cells.

Click on number tab.

Select the date from the given items.

Select the appropriate date format to apply.

 

Click OK button

NB: Repeat the above procedure to apply format for other items on Number category – time, currency, fraction , percentages etc.

Applying different text alignment

There are two major text alignments – Vertical and Horizontal.

On vertical alignments we have : top, center ,justified, bottom placement.

On horizontal alignment we have: left, right, center, justified.

NB: you can also use text orientation by applying specific degree to the text e.g. 90 degrees 45 degrees orientation.

 

Steps for changing  text alignment.

Select (highlight) the text to change the alignment.

From format select cells.

Alignment category,select the desired vertical(either-center, left  or right) and also select required vertical alignment (top, center or bottom)

On orientation section adjust to required degrees (45,80, or 90) Click OK button.

 

NB : H.A- Horizontal Alignment    V.A –Vertical Alignment.

FORMATING FONTS:

This includes the process of applying different font attributes such as font face, font style, font sizes, font colour, and underline type.

STEPS.

•   Highlight the text to format.

•   From the Home Tab-Font Group or Right click and select format cells.

•   Click on the font tabs.

•   On the font category select on the appropriate font type e.g times New Roman, Tahoma, Cambria etc. ? On font style, select the appropriate eg  Bold, Italics etc ? On font size select or type the desired size.

•   On the underline category select the desired type if any.

•   On colour  select the desired type of colour to apply.

NB: You can use shortcut on tool bar to apply  these font attributes.

 

A table with different font formats

 

Applying Borders to worksheet

A worksheet can be enhanced by applying borders. This is  important especially when a worksheet is to be printed because the gridlines will not appear on hard copy. Steps:

•    Highlight the range to apply the borders.

•    Right click and select format cells or from font group select borders  .

•    On Format cells window, select Borders option.

•    Select the border colour, border type, and part to apply(outline, inside, or both by click the button).

•    After setting all the border specifications, click OK button

 

Applying Pattern on worksheet

•    Includes enhancing the worksheet with background colour or patterns.

•    When selecting pattern or colour to the worksheet, it is important to select a combination that does not interfere with the appearance of the worksheet content.

Steps

•    Highlight the cell range to apply the pattern/colour ? Right click and select  Format cells.

•    On Format cells window, select Patterns option.

•    On cell shading, select the colour then pattern to apply.

•    Click OK.

 

CHAPTER 4

FORMULAS AND FUNCTIONS

Ms Excel enables you to work with formulas. It tells you how you want a particular value to be Computed. Considerations to make before doing a calculation ? Mentally figure out what to calculate.

•   Note the values in the worksheet that are required for the calculation.

•   Identify the cell addresses of the values required for calculation. E.g. B3, C7 etc.

•   Type the formula in the cell where you want the calculated value starting with equals signs(=) .Ms Excel uses this sign to differentiate between a label or text and a formula.

•   Press Enter key.

Ms Excel immediately calculates and shows the results in the cell, while the formula is displayed on the formula bar.

Examples;  =B4/B5  but not  B4/B5

        =C3+C5+D3 but not  C3+C5+D3 Formulas are user defined mathematical operations

Examples of Excel Formulas:

Operator             Description         Example 1           Example 2       Example 3

+                           Addition               C3=A2+A4          B1=B3+56       C4=67+50

-                            Subtraction           D5=E4-E2           B6=B1-40        F2=89-45

*                           Multiplication      A4=G1*D3          B2=E1*56        D4=34*23

/           Division           D1=A2/B3      C2=B3/15       B2=60/17 ^     Exponential            C2=(B1+B4)^2           B3=A3^2        C2=(56+34)^3

Functions:

Functions are inbuilt mathematical operations/formulas that replace complex lengthy calculations using formulas. For example instead typing  long formula =(B1+B2+B3+B4+B5), you simply type =SUM(B1:B5) on destination cell.

NB: functions apply when you are calculating formula of a range i.e. when you are including all the cells between a specific range e.g. From B1 to B5.

Examples of Functions

               Function         Use                                                                              Example

               Sum                 Calculating totals                                                        =Sum(A1:A7)

               Product            Calculating product of a number                                =Product(B2:C8)

              Average           Calculating average (mean) of a range.                      =Average(C4:C9)

               Maximum       Calculating highest value in a range                           =Max(D3:F5)

               Minimum        Calculating lowest value in a range                            =Min(B1:B6)

               Round             Rounds off numbers to a specified decimal places    =Round(D4,1)

Example of application of the formulas and functions

 

A

B

C

D

E

F

G

H

I

J

1

Staff

Hours

pay per

Amount

Allowance

Gross

Tax (6%)

Net pay

Round

 

2

Name

Worked

Hour

Payable

Given

Pay

   

off(0 D.p)

 

3

Okello

12

340

4080

300

4380

262.8

4117.2

4117

 

4

Akinyo

8

250

2000

120

2120

127.2

1992.8

1993

 

5

Jennifer

10

400

4000

500

4500

270

4230

4230

 

6

Nickson

34

145

4930

340

5270

316.2

4953.8

4954

 

7

Paul

6

450

2700

500

3200

192

3008

3008

 

8

                   

9

Totals

70

1585

17710

1760

19470

1168.2

18301.8

18302

 

10

Average

14

317

3542

352

3894

233.64

3660.36

3660

 

11

Maximum

34

450

4930

500

5270

316.2

4953.8

4954

 

12

Minimum

6

145

2000

120

2120

127.2

1992.8

1993

 

13

                   

14

                   

15

                   

16

                   

Function for:-

a) Totals Hours worked

=Sum(B3:B7)

b) Average Hours worked

 

=Average(B3:B7)

c) Maximum hours worked

= Max( B3:B7)

d) Minimum hours worked

 

= Min(B3:B7)

e) Amount payable

 

=B3*C3

f)   Gross pay

 

=D3+E3

g) Tax

 

=6%*F3

h)   Net pay      =F3-G3

i)    Round off the net pay to 0 d.p.     =Round(I3,0)

NB:The above calculation is for the first staff member i.e. Okello.

To calculate the payments for the other staff members, are supposed to copy the formula used to calculate the payments for Okello. This avoids the repetition work of calculating payments for each staff member. Below are the steps for copying a formula into the subsequent cells:



•   Click the cell with the formula.

•   Move the mouse pointer to the bottom left corner of the cell with formula until it changes to plus sign (+).

•   While holding the left button drag the cell down to cover all the cell range to copy the formula.

•   Release the mouse but when you reach the last cell and formula is automatically copied the other cells.

This type of copying is referred as Relative addressing. This is because Ms Excel adjusts the reference of the formula to the subsequent cells. i.e. if the formula is copied to the next cell it given reference to that new cell.

E.g. formula copied from  D3(C3*B3) to  D4 will  be (C4*B4).

Absolute Reference(addressing).

This is a type of  copying a formula whereby the contents of a cell remain fixed even  when it is copied down. In this  case if a formula refers to a particular cell and you would like to copy the subsequent entries refers to the same particular cell. This is what is referred as absolute referencing.

To fix the a cell in formula, you use dollar sign ($).

Enter the first formula as usual only that;

•   Before the column reference insert the dollar sign. ? Before the row reference insert the dollar sign.

The same formula after the absolute referencing will be appearing on the subsequent cells when the formula is copied.

Example:

If the original formula for the first cell was Cell E4= $A$10*B4 then the subsequent Cells will be; Cell E5 =$A$10*B5

Cell E6 =$A$10*B6

Cell E7 =$A$10*B7

Cell E8 =$A$10*B8

Cell E9 =$A$10*B9

NB: Content of cell A10 is the constant value which is being multiplied  to all the content of the cell refernces;B4, B5, B6, B7, B8 and B9.

Use of IF function

? Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE. ? Use IF to conduct conditional tests on values and formulas. ? It is mostly used to apply remarks.

Remarks

•   Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests. See the following last example.

•   When the value_if_true and value_if_false arguments are evaluated, IF returns the value returned by those statements.

•   If any of the arguments to IF are arrays, every element of the array is evaluated when the IF statement is carried out. If some of the value_if_true and value_if_false arguments are action-taking functions, all of the actions are taken.

Examples

•   In the following example, if the value in cell A10 is 100, then logical_test is TRUE, and the total value for the range B5:B15 is calculated. Otherwise, logical_test is FALSE, and empty text ("") is returned that blanks the cell that contains the IF function.

•   IF(A10=100,SUM(B5:B15),"")

•   Suppose an expense worksheet contains in B2:B4 the following data for "Actual Expenses" for January, February, and March: 1500, 500, 500. C2:C4 contains the following data for "Predicted Expenses" for the same periods: 900, 900, 925.

•   You can write a formula to check whether you are over budget for a particular month, generating text for a message with the following formulas:

•   IF(B2>C2,"Over Budget","OK") equals "Over Budget"

•   IF(B3>C3,"Over Budget","OK") equals "OK"

•   Suppose you want to assign letter grades to numbers referenced by the name AverageScore. See the following table.

 If AverageScore is                        Then return

Greater than 89                              A

From 80 to 89                                B

From 70 to 79                                C

From 60 to 69                                D

Less than 60                                   F

Assuming the cell reference was for the first entry was B2 then, You can use the following nested IF function: =IF(B2>89,"A",IF(B2>79,"B",IF(B2>69,"C",IF(B2>59,"D","F"))))

In the preceding example, the second IF statement is also the value_if_false argument to the first IF statement. Similarly, the third IF statement is the value_if_false argument to the second IF statement. For example, if the first logical_test (B2>89) is TRUE, "A" is returned. If the first logical_test is FALSE, the second IF statement is evaluated, and so on.

CHAPTER 5

WORKING WITH CHARTS Definition of Chart

¨  It is a graphical representation of worksheet data. Chart converts data from the columns and rows on the worksheet into a visual format that can be read at a glance.

¨  Charts are linked to the worksheet data they are created from and are updated when you change the worksheet data.

Types of Charts

? Ms Excel supports different types of charts. The type of the chart to be created is determined by the kind of the data to be plotted.

Examples of   Ms Excel Charts a) Column Chart

¨   It is the best chart for comparison. You can compare two items against each other e.g. comparing the performance of students on a certain subject, or comparing income from different departments in a company.

¨   column chart shows data changes over a period of time or illustrates comparisons among items.

Categories are organized horizontally, values vertically, to emphasize variation over time. Stacked column charts show the relationship of individual items to the whole. The 3-D perspective column chart compares data points along two axes.

 

(b) Bar Chart

 

(c) Line Chart

? It is the most appropriate type of chart  for showing a trend in data over equal intervals. e.g.

distribution of  rainfall over a given period of time. It shows whether it is increasing or decreasing across that period of time.

 

(d) Pie Chart

A pie chart shows the proportional size of items that make up a data series to the sum of the items. It always shows only one data series and is useful when you want to emphasize a significant element.

 

(d)     Area Chart

   

(e)Doughnut Chart

 

(f)XY(Scatter)

 

(h) Bubble Chart

 

(i) Stock Chart

 

(g)     Surface Chart:

 

(h) Chart Terminologies a) Data marker;

Each column in the chart is a visual representation of a value from a worksheet. b) Data series

Data series corresponds to the column or row of related values on a worksheet. Data  series          from on column have the same colour or patterns. E.g a column for cost in a worksheet can be  regarded as a data series.

(c) Axis

It is a reference line for the chart. Ms Excel plots a column along vertical and horizontal axes.

i)    Y- Axis (Value Axis) :-It is the vertical axis of a chart along which numeric data or values are plotted. E.g. Cost, % marks etc.

ii)  X- Axis (Category Axis) :- It is the horizontal axis along which categories are plotted. E.g. Name of customer, student name, month etc.

(d) Legend

This is acts like a key to the graph. It shows the range which have been used to plot the chart. This is very important especially when the chart has more than one range.

In other words, legend is a box that identifies the patterns or colors assigned to the data series or categories in a chart.

You can choose the placement of the legend . The default placement is right of the chart. Other placements are top, left, bottom and corner.

(e) Data Label

It is a label that provides additional information about a data marker, which represents a single data point or value that originates from a worksheet cell. Data labels can be applied to a single data marker, an entire data series, or all data markers in a chart. Depending on the chart type, data labels can show values, names of data series or categories, percentages, or a combination of these. By default the is no data label.

(g)  Data Table

It is a grid in a chart that contains the numeric data used to create the chart. Each row in the data table represents a data series. The data table usually is attached to the category axis of the chart and replaces the tick-mark labels on the category axis. By default there is no data table

(h) Plot Area

This is the area where data series are plotted/displayed on the chart.

(i) Chart Area

 This is the whole area which is covered by the chart. All details of the chart are shown within the  chart  area. E.g. chart title, legends, data series etc.

Creating a Chart

v    First identify the data you want to chart.

v    Highlight the range of the data to chart out.

 

v    From insert menu select Chart or click ChartWizard button on the tool bar.

 

v    This presents you with Chart Wizard step 1 of 4. On this step select the Chart type to use and also the chart sub-type Mostly the chart type is determined by the data you highlighted.

v    Click on Press and  hold to view the sample button to see a preview of the chart you are creating.

v    Click on Next button to move to step 2 of 4.

v    On step 2 of 4, a sample of chart is displayed. Here you are also shown the data range you are creating chart from. Also you are shown whether the series is in columns or rows.

v    If the appearance of the chart is not as you wanted, click cancel to go back to the worksheet otherwise click Next button to move to step 3 of 4.

v    On step 3 of 4 specify all the options that you would like your chart to have. These includes;

-Titles:- Give the Name of the Chart Title, Name of X-axis and Name of Y-axis.

-Axes - Specify whether you want your chart to have either X-axis or Y-axis or not.

-Gridlines - Specify whether to have either or both major  and minor gridlines on either of the axis. To include them tick on the check box provided appropriately.

-Legend  - Click on the check box to apply or deselect the checkbox to remove the legend. If the legend is required, specify the placement - either top, bottom, left, right or corner of the chart by clicking on the relevant checkbox.

-Data labels - Specify whether to show values, labels or none on the chart.

-Data Table - If you want a data table on the chart, select it on the checkbox.

v    After specifying all the options you want click on Next button to move to step 4 of 4. ? On this step (step 4 of 4) specify the location of the chart i.e  the worksheet to place the chart. Chart can be place either;

a)   As new sheet - Here the chart Wizard creates a new sheet with default name chart 1 where it automatically places the chart.

b)   As an object in sheet 1 - This is the default option. On this option, the Chart Wizard chooses the location of the chart to be the current worksheet. However you can choose another worksheet by clicking on the arrow at the end of option As an object on this sheet.

v    Click Finish button after selecting  the chart location and the chart is inserted.

Creating a Chart from Non-Adjacent selections

To create a chart from non-adjacent selections;

§     Select the first group of cells that contain the data you want to include.

§     While holding down CTRL, select any additional cell groups you want to include. NB:T he non-adjacent selections must form a rectangle.

§     Click Chart Wizard  .

§     Follow the instructions in the Chart Wizard to continue generating the chart.

Enhancing Appearance of a Chart.  Importance;

§     Adding new features after creating the chart.

§     To add general appearance or readability of the chart for easy interpretation.

§     This involves; -formatting size of a chart, tittles, axes, legend etc

Also it includes applying patterns and borders to the chart area, plot area, data sereis, etc

Resizing the chart; i.e changing size of chart.

§     Click anywhere inside the chart to highlight..

§     Point at any of the handles on the borders (known as sizing handles) until the mouse pointer  changes to a two sized arrow.

§     Drag outwards to make the chart bigger or inward to make the chart smaller.

a) Formatting Chart Tittle

§     Highlight the chart title by clicking on it.

§     From format menu click selected chart tittle.

§     On format chart tittle, select the desired font type, font style and font size also select the colour of the font and underline type.

§     Click ok.

NB: Repeat the same for chart axes, and legend.

b) Formatting data series

§     Highlight the data series by clicking any of them and the rest will be selected. ? From format -select data series.

§     Click on parttern tab.

§     Click on the colour desired.

§     Click on the pattern required.

§     Select the borders style required.

§     Click OK button.

Changing Chart Type.

By default the Excel creates a column chart, however you can change to any different type of chart. To change chart type;

§     Click anywhere on the chart area or  right-click to get quick menu.

§     Click on chart type

§     Select the chart sub-type  you want then  click OK.

Inserting a New Range to a Chart

A need can arise for introducing a new range after creating the chart. This can be for the purpose of comparison of different items. Ms Excel gives the facilities for  inserting a new range.

Procedure

§     Highlight the range to add on the chart.

§     Point anywhere on the border of the highlighted range until the pointer changes to arrow shape

§     Drag the range toward the chart until it enters into the chart area. When it enters on the chart area, the pointer changes to a small plus sign (+) attached to it.

§     Release the mouse button and the new range is added to the existing chart.

Deleting a Data series from the Chart

To delete a date series from the chart;

§     Click on the data series do to delete.

§     Press delete Key on the keyboard.

Printing A chart

This is important when a hard copy of the chart is required. A chart can be printed alone or together with the worksheet. To print the chart alone;

§     Click anywhere on the chart area to highlight.

§     From the file menu select print.

§     On "Print what" option, select "Selected Chart".

§     Click  OK.

WORKING WITH EXCEL DATABASE

Sorting

This is a process of arranging worksheet data in a range in a particular order or criteria using a sort order.

Sort  order arranges data based on value or data type. Data can be sorted  alphabetically, numerically, or by date. Sort orders use an ascending (1 to 9, A to Z) or descending (9 to 1, Z to A) order. Importance

§     It helps quickly locate the highest or lowest value in a in a list.

§     It helps rearrange data in order of priority e.g. from the best to the poorest.

Procedure of sorting

§     Click a cell in the list you want to sort.

§     On the Data Tab/Home Tab, click Sort.

§     In the Sort by and Then by boxes, click the columns you want to sort.

§     If you need to sort by more than three columns, sort by the least important columns first. For example, if your list contains employee information and you need to organize it by Department, Title, Last Name, and First Name, sort the list twice. First, click First Name in the Sort by box and sort the list. Second, click Department in the Sort by box, click Title in the first Then by box, and click Last Name in the second Then by box, and sort the list.

§     Select any other sort options you want, and then click OK.

§     Repeat steps 2 through 4 if needed, using the next most important columns.

NB: Where more than two sort orders are applied, the first criteria is given priority first  then the second one.

 

Filtering

It enables one to locate a record in a large database(sieving a large volume of data using a criteria). This avoids moving around the whole database to find only one record.

There are two types of filtering.

a)   Autofilter

b)   Advanced filter

Use of AutoFilter

Procedure

•   Click anywhere on the database.

•   Form the Data menu, select filter, then AutoFilter. AutoFilter Arrows appears.

•   Select the appropriate filter arrows depending on the column that you want to filter. For instance if you if you want to filter the student names, use the filter arrow  pointing down adjacent the name Student .

•   Choose the name you want. You will be shown only the details of the record you selected.

NB: To display all the records again, select the option Show All  on that specific column.

 

Before Autofiltering

 

After Autofiltering

Combining criteria

Sometimes you can use more than one filter criteria i.e. filter data using than one column header.

Procedure

•   Click on the database.

•   Form the Data menu, select the filter the AutoFilter.

•   Select the first filter criteria.

•   When the first filter criteria is still on, move to the next criteria to apply.

•   NB You can filter the required records and copy them to another location or another sheet before showing all the records.

To copy the filtered Records;

•   While the filter is still on, highlight the records.

•   Select copy from the Edit menu.

•   Click on the first cell of the location to place the filtered records ? From the Edit menu, select Paste.

After copying the filtered records you can show all the records.

Customizing AutoFilter

•   You can expand the AutoFilter criteria to get more filtering condition to apply on the column you are filtering.

•   To do this;

i)  Select custom option on the column you are filtering.

ii)     Select the criteria to use. E.g. equals to, greater than. ) Click OK to apply the criteria.

 

AutoFilter to show only those records of students who scored more than 65% in Ms Word.

Removing  filters from a list

•  To remove a filter from one column in a list, click the arrow next to the column, and then click All.

•  To remove filters applied to all columns in the list, on the Data menu point to Filter, and then click Show All. ? To remove the filter arrows from a list, on the Data menu point to Filter, and then click AutoFilter.

Use of Advanced Filter

Advanced filter criteria can include multiple conditions applied in a single column, multiple criteria applied to multiple columns, and conditions created as the result of a formula.

Three or more conditions in a single column

? If you have three or more conditions for a single column, type the criteria directly below each other in separate rows. For example, the following criteria range displays the rows that contain either "Davolio," "Buchanan," or "Suyama" in the Salesperson column.

Criteria from two or more columns

? To find data that meets one condition in two or more columns, enter all the criteria in the same row of the criteria range. For example, the following criteria range displays all rows that contain "Produce" in the Type column, "Davolio" in the Salesperson column, and sales values greater than $1,000.

Filter a list by using advanced criteria

Your worksheet should have at least three blank rows that can be used as a criteria range above the list. The list must have column labels.

•   Copy the column labels from the list for the columns that contain the values you want to filter.

•   Paste the column labels in the first blank row of the criteria range.

•   In the rows below the criteria labels, type the criteria you want to match. Make sure there is at least one blank row between the criteria values and the list.

•   Click a cell in the list.

•   On the Data menu, point to Filter, and then click Advanced Filter.

•   To filter the list by hiding rows that don't match your criteria, click Filter the list, in-place.

•   To filter the list by copying rows that match your criteria to another area of the worksheet, click Copy to another location, click in the Copy to box, and then click the upper-left corner of the paste area.

•   In the Criteria range box, enter the reference for the criteria range, including the criteria labels.

•   To move the Advanced Filter dialog box out of the way temporarily while you select the criteria range, click Collapse Dialog  .

•   After specifying all that you want click OK button to apply the advanced filter criteria.

 See the figure below.

 

Use of Data Forms

A data form is a convenient way to enter or display one complete row of information, or record, in a list at a time. Before you can use a data form to add a record to a new list, the list must have labels at the top of each column in the list. Microsoft Excel uses these labels to create fields on the form.

Forms in Ms Excel are used to  when dealing with a big worksheet data. A form transforms the worksheet data into a more convenient way to work on Forms can be used to:

•   Add more records to the worksheet.

•   Edit worksheet data.

•   Locate a record in a worksheet i.e. find a record in a worksheet.

•   Delete a record from the worksheet.

 

Converting worksheet data into form;

•   Click inside the worksheet data.

•   From the data menu, select Form… and the form appears.(See above)

Adding a record to a list by using a data form

•   Click a cell in the list you want to add the record to.

•   On the Data menu, click Form.

•   Click New.

•   Type the information for the new record.

To move to the next field, press TAB. To move to the previous field, press SHIFT+TAB.

•   When you finish typing data, press ENTER to add the record.

When you finish adding records, click Close to add the new record and close the data form.

Notes

1.   Fields that contain formulas display the results of the formula as a label. The label cannot be changed in the data form.

2.   If you add a record that contains a formula, the formula is not calculated until you press ENTER or click Close to add the record.

3.   While you are adding a record, you can undo changes if you click Restore before you press ENTER or click Close to add the record.

4.   Microsoft Excel adds the record when you move to another record or close the data form.

Editing a record in a list by using a data form

•   Click a cell in the list you to want to change.

•   On the Data menu, click Form.

•   Find the record you want to change.

•   Change the information in the record.

•   To move to the next field, press TAB. To move to the previous field, press SHIFT+TAB.

•   When you finish changing data, press ENTER to update the record and move to the next record.

•   When you finish changing records, click Close to update the displayed record and close the data form.

Deleting a record in a list by using a data form

•   Click a cell in the list.

•   On the Data menu, click Form.

•   Find the record you want to delete. ? Click Delete.

NB: When you delete a record by using a data form, you cannot undo the deletion. The record is permanently deleted.

Finding a record in a list by using a data form

•   To move through records one at a time, use the scroll bar arrows in the dialog box. To move through 10 records at a time, click the scroll bar between the arrows.

•   To move to the next record in the list, click Find Next. To move to the previous record in the list, click Find

Prev.

To set search conditions, or comparison criteria; ? click Criteria.

•   Enter the criteria into the data form.

•   To find records that match the criteria, click Find Next or Find Prev.

•   To return to the data form without searching for records based on the criteria you specified, click Form. For

 

more information about types of comparison criteria, click .

CHAPTER 7

WORKING MACROS Macro

Definition

 A macro is a series of commands and functions that are stored in a Visual Basic module and can be run whenever you need to perform the task. It is used to do those tasks that you perform repeatedly in Microsoft Excel, whereby  can automate the task with a macro.

Advantages of use of Macro

? It saves time in that you make just one document then you run to get the others when necessary. ? It ensures the originality of the document is maintained.

Application of Macros

Macro can be   used to produce standard documents like:

•   Student Admission form.

•   Receipt Copies.

•   Certificates.

•   Any other Standard document whereby the structure is the same only ones particulars will be entered. You record a macro just as you record music with a tape recorder. You then run the macro to repeat, or "play back," the commands.

NB: Before you record or write a macro, plan the steps and commands you want the macro to perform. If you make a mistake when you record the macro, corrections you make will also be recorded. Each time you record a macro, the macro is stored in a new module attached to a workbook.

With the Visual Basic Editor, you can edit macros, copy macros from one module to another, copy macros between different workbooks, rename the modules that store the macros, or rename the macros.

Recording a New a Macro

•   On the View menu, point to Macro, and then click Record Macro.

•   In the Macro name box, enter a name for the macro.

The first character of the macro name must be a letter.

•   To run the macro by pressing a keyboard shortcut key, enter a letter in the Shortcut key box. You can use

CTRL+ letter (for lowercase letters) or CTRL+SHIFT+ letter (for uppercase letters), where letter is any letter key on the keyboard. The shortcut key letter you use cannot be a number or special character. The shortcut key will override any default Microsoft Excel shortcut keys while the workbook that contains the macro is open.

•   In the Store macro in box, click the location where you want to store the macro.

•   If you want a macro to be available whenever you use Microsoft Excel, store the macro in the Personal

Macro Workbook in the XLStart folder.

To include a description of the macro, type the description in the Description box.

•   Click OK.

If you select cells while running a macro, the macro will select the same cells regardless of which cell is first selected because it records absolute cell references. If you want a macro to select cells regardless of the position of the active cell when you run the macro, set the macro recorder to record relative cell references. On the Stop Recording toolbar, click Relative Reference  . Microsoft Excel will continue to record macros with relative references until you quit Microsoft Excel or until you click Relative Reference    again.

•   Carry out the actions you want to record.

•   On the Stop Recording toolbar, click Stop Recording  when you are through.

 

Running a macro in Microsoft Excel

You run a macro to produce copies of  the actions you recorded. To run it; ? Open the workbook that contains the macro.

•   On the View menu, point to Macro, and then click Macros.

•   In the Macro name box, enter the name of the macro you want to run.

•   Click Run.

Note   To interrupt a macro before it completes its actions, press ESC.

Editing a macro

This involves making changes on the macro you have already recorded.

Steps

•   On the View menu, point to Macro, and then click Macros.

•   In the Macro name box, enter the name of the macro.

•   Click Edit.

•   Make the changes then click OK

CHAPTER 8

ORGANIZING WORKSHEET WORK FOR PRINTING

Page Setup

? This includes adjusting margins, setting page orientation, Paper size, for quality output after printing. ? Page setup ensures that no part of the worksheet data is outside the printable areas. Also it ensures that only the required size of margins is left on all sides of the worksheet.

Setting Margins

¨  To set page margins for one sheet, click the page layout tab.

¨  To set page margins for more than one sheet, select the sheets.

¨  Select the margins from the samples provided or click on custom marginsvfor Top, Bottom, Left, and Right boxes, enter the margin size you want.

¨  These settings should be larger than the minimum margins required by your printer.

NB: To see how the page margins will affect the printed document, click Print Preview   before the document is printed. To adjust the margins in print preview, click Margins, and then drag the handles.

 

Changing Page Orientation

There are two type of page orientation in Ms Excel. These are: ? Portrait – Height is large than width.

¨  Landscape – Width is large than the height.

To change orientation;

¨  Click on orientation option and choose the orientation (either portrait or landscape).



Page Breaks

¨  These are automatic vertical and horizontal lines inserted in worksheet whereby the page to be printed is  larger than one page. They divides a worksheet into multiple pages for printing if the worksheet is too large to fit onto one page.

¨  These page breaks are based on the paper size, margin settings, and scaling options you set. You can change which rows are printed on the page by inserting horizontal page breaks; you can insert vertical page breaks to change which columns are printed on the page. In page break preview, you can move page breaks by dragging them to a different location on the worksheet.

Inserting a horizontal page break

¨  Click the heading for the row below the row where you want to insert the page break.

¨  On the Insert menu, click Page Break. Inserting  a vertical page break

¨  Click the heading for the column to the right of the column where you want to insert the page break. ? On the Insert menu, click Page Break.

Moving a page break

You can move a page break only in page break preview. Moving an automatic page break will change it to a manual page break.

To move a page break;

? On the View menu, click Page Break Preview. ? Drag the page break to its new location.

Removing page break

¨  On the View menu, click Page Break Preview.

¨  To remove a manual horizontal or vertical page break, right-click a cell below the horizontal page break or a cell to the right of the vertical page break, and then click Remove Page Break on the shortcut menu.

¨  To remove all manual page breaks, right-click any cell on the worksheet, and then click Reset All Page Breaks on the shortcut menu.

 

Figure of Page break Preview

Zoom

? Click Zoom to switch between a full-page view of a sheet and a magnified view. The Zoom feature does not affect printing size. You can also switch between a full-page view and a magnified view of a sheet by clicking any area of the sheet.

Headers and Footers

A header is a line of text that that will appear on the top of each page.

Footer is a line of text that appear on the bottom of each page. Header/footer can include any text such as date, time, page number remarks etc. Defining  header and footer

¨  From insert menu select headers and footer or From file menu select Page set up then Header/Footer option.

¨  To define your own header click  Header and footer tools button.

¨  Choose the position of the header then type the text. It can be on left, centre or right

¨  You can click on the relevant buttons on the top of the text box to add page numbers,date  time etc. ? To format the text typed click on A button to apply font size, font face etc.

.

 

NB: To define the Footer click on  Footer and repeat the same procedure as above.

Freezing Titles

¨  The freezing of titles is applicable whereby the worksheet data grows larger such that you will be forced to scroll horizontally and vertically in order to see the extreme parts of the worksheet data. Under this situation it is important to freeze the titles so as to view them at the same time with what is on extreme end.

¨  It is required to freeze columns along left of the worksheet  or rows along the top of the worksheet or both in so that when you scroll to a distant cell in the worksheet, the row or the column titles are still visible.

To freeze the panes/Titles;

§     Highlight the row or column or row next to the one to freeze. For instance a worksheet where titles are in Row 1 highlight Row 2 or Column C highlight column D.

§     From View tab click Freeze panes. This automatically freezes the column or rows.

 

NB: To unfreeze the pane click on the View tab, then Unfreeze pane.

Printing A Worksheet Print Preview window

¨  Before you print a worksheet, click Print Preview    to see how the sheet will look when you print it. The status bar at the bottom of the screen shows the current page number and the total number of pages in the selected sheet.

¨  To preview a specific range of pages, click Print preview from the office button..

¨  The way pages appear in the preview window depends upon the available fonts, the resolution of the printer, and the available colors.

¨  If a worksheet contains an embedded chart, print preview displays both the worksheet and the chart. You can move or resize the chart in normal view or page break preview. If you select an embedded chart before you click Print Preview,  Microsoft Excel displays only the embedded chart.

Printing

This is process of getting a hard copy of the worksheet work. This is done by use of printer.

Steps

¨  Office button, select print . ? Print window appears.

¨  On this window, specify,

-Print page (either All pages, or specific pages)

-Print what (selection from a worksheet, Entire workbook, or Active sheet [s]). -Number of copies (how many copies to be printed per page). ? Click OK to print.

 

Renaming Worksheet

¨  Click sheet tab to be renamed.

¨  Double click on the sheet name or right click on the sheet name and click rename ? Type the new name. ? Press enter.

Inserting a  new worksheet

¨  Click  on where you want  the worksheet.

¨  From insert menu select worksheet.

¨  A new  worksheet is inserted.

Hiding a worksheet

? Right Click on sheet tab to hide. ? Then select hide.

Unhiding a worksheet

¨  Right click on the sheet tab to unhide.

¨  Select unhide

Deleting a worksheet

¨    Select the worksheet and select delete sheet.

.

Using Help in excel

You can get help about the topics we have covered from the help menu.

¨    To access the topics you can use: Contents, Index or Find option on Help menu. Searching for help using contents

¨    From the form that appears select contents tab. It displays a list of all topics.

¨    Select the topic you want then display.

¨    Read the details and follow instructions.

¨    You can print the topic for future reference.

CHAPTER 9

PRACTICE QUESTIONS AND EXAMINATION SAMPLE PAPERS

MS EXCEL FINAL EXAMINATION 2002  SAMPLE 1

INSTRUCTIONS

i.          Answer all the questions ii.     Answer section 1 on the answer sheet  provided. iii.     Marks will be awarded on basis of logical answers and clarity. iv.           Time: 2hrs  30min

SECTION 1: THEORY

Question 1: 11 mks

(a)   Define the following terms;   4mks

i. Spreadsheet ii. Ms excel

(b)   Give three examples of spreadsheet application programs.    3mks

(c)   Discuss four areas where spreadsheet programs are applied.  4mks

Question 2: 10mks

Differentiate between the following terms;

(i)     Workbook and worksheet

(ii)     Cell and active cell

(iii)      Row header and column header

(iv)    Header and footer

Sorting and filtering

Question 3;  15mks

(a)   Give five methods of loading Ms excel in windows.

(b)   Name and discuss various parts of Ms Excel window.

Question 4:   10mks

(a)     Any entry entered in Ms excel worksheet is treated as either a Label or Value. Differentiate between the two terms.

(b)     What is the default alignment of labels.

(c)     What is the default alignment of value.

(d)     Give three examples of each category.

(e)     Give three types of alignment in Ms excel

Question 5;  16mks

(a)   Define a chart.

(b)   Give examples of charts.

(c)   Give three examples of using charts in Ms excel

(d)   Give an important consideration to make before choosing a chart to use. (e) Explain the meaning of the following chart terms.

i. Plot area ii. Chart area iii. Data series iv. Table data

v.      Axis

vi.       Gridlines vii. Legend

Question 6;   8mks

(a)   Define a macro.

(b)   Discuss the three advantages of using macros in Excel.

(c)   Give three areas in Excel you can apply macros.

SECTION B: PRACTICAL

Create the workheet shown below and answer all the questions that follows. (3mks)

 

i.      Average mark

ii.    Total marks

iii.    Total for only those students who are doing Computer courses.

iv.     Totals for only those students who are ladies.

(b)   Compute the grade obtained by each student given; (5mks)

                70% and above           –A

                60%-69%          -B

                 50%     59%          -C

                 49%     and below         -D

(c)   Computer the Remark for each student given; (5mks)

A       -Excellent

B       -Good

C       -Fair

D       -Poor

d)   Insert a new column between Course and Marks for Codes. Using the relevant formula, apply codes as follows;

                                Computers        -CO

                                 Sales                  -SA

H/Management   -HM

e)    Format the worksheet data as follows; (10mks)

i.    Title –Bold ,size 20, font face-Comic San MS, color-Red

ii.    Sub Titles – Bold , size 15, font face- Impact, color – blue

iii.     All other records – Italics, Centred across the cell, font face – Tahoma, color – Magenta.

Create a column chart as a New Sheet  to compare student names against the marks. On the chart show the following; (12mks) (i) Chart title

(ii)      Both axis

(iii)    Major gridlines on Y-axis

(iv)     Values

(v)       Data table

(f)   Using Advanced filter option, retrieve only those students who obtained 65% and above on their relevant courses. Copy them below the main table. (6mks)

(g)  Sort the worksheet data according to students marks starting with the student who scored the      highest mark. (3mks)

(h)  Insert headers and footers on our worksheet as follows; (6mks)

Header – Student Progress Record

Footer  - Institute of Professionals

(i)    Print all your work. (3mks)

(j)    Save your work on My Documents folder. Us your fist name as the file name. (3mks)

CREATIVE EXERCISE 1

Sweet Bread ltd. is a small bakery, which has opened recently in the heart of Kagumi town.

They specialize in manufacturing bread and cakes.  They normally produce four brands of items as listed below. They have recorded their January sales units as follows:

White bread                 318

Brown Bread               430

Round buns                 428

Block cakes                 370

They have made a projection that sales will grow by varying percentage every month as shown

White bread

10%

Brown bread

8%

Bound buns

13%

Block cakes

12.5%

Required;

1.   Create a worksheet showing the unit sold up t the month of April using the given January figures and growth percentages

2.   Calculate the  total units sold for each month and also for each products

3.   Create a chart (Pie) below the worksheet showing the products and the totals.  Give it a suitable title.

4.   Create a chart (Column) as a new sheet showing all the products and their sales for the four months.  Give it appropriate titles.

5.   Format all the figures with the comma sign and decrease all the decimals

6.   Enhance the worksheet with the following features;

Title - bold, font size - 14, centered across the columns

Other titles - bold, aligned to the right

Totals - bold

Save your worksheet with the name Mkate tamu  in the PMK folder.

CREATIVE EXERCISE 2

Enter the data above using a data form.

     NAME                        BASIC PAY                 DEPARTMENT         AGE                            STATUS

     Mary Anne                  17,000                            Research                      26                                 Single

     Francis Kihara            28,000                            Research                      33                                Married

     Lena Achieng             40,000                            Computer                    35                                Married

     Helen Wanjiku           35,000                            Finance                        23                                 Single

     Mwangi Peter             15,000                            Finance                        17                                 Single

     Sospeter Ngeno          13,000                            Research                      27                                 Divorced

     Arnold Maswai          22,000                            Computer                    26                                Married

    Gerald Wanjau           15,000                            Computer                    33                                 Divorced

     Anditi Anna                32,000                            Finance                        22                                 Single

     Josephine Allot          45,000                            Computer                    50                                Married

     Calara Mulwa             18,000                            Research                      20                                Married

     Martin Mato               55,000                            Computer                    70                                 Divorced

     Cyrus Okinyo             25,000                            Finance                        37                                 Single

     Pius Mwaniki             70,000                            Finance                        55                                Married

     Rono Kirwa                20,000                            Research                      22                                 Single

     Bett Korir                    12,000                            Computer                    23                                 Single

     Caroline Makanga     35,000                            Computer                    26                                Married

     Raymond Rashid       23,000                            Finance                        38                                 Divorced

     David Songo               48,000                            Computer                    31                                Married

     Stephen Chege           26,000                            Research                      35                                Married

    Alice Ambundo          17,000                            Finance                        23                                 Single

1.   Use the sorting function to determine

(a)   The oldest employee

(b)   The oldest employee in the finance  department

(c)   Which employee earns the most in the research department

(d)   All married employees in the company

(e)   All employees earning more than Ksh.30,000

2.   Use the subtotaling feature to determine

(a)   The basic pay salary for computer and finance department

(b)   The average age of the finance and  research department

(c)   Determine the total basic pay for each department and the grand total for people in that category.

3.   Use the AutoFilter function to determine

(a)   The number of people with a basic salary greater than 32,000

(b)   The number of people with a basic salary less than 45,000

(c)   The number of people who are either married or single

(d)   The number of people with a basic pay greater than or equal 25,000 & less than or equal to 50,000

CREATIVE EXERCISE 3

Create and complete the simple workbook shown below Save the workbook as SAKINA in you diskette

SAKINA MOTORS INC.

JANUARY AUTOMOBILE SALES RECORD (KSH’000, 000)

VEHICLE                   COST VAT               SELLING                 PROFIT

PRICE               2.5%              PRICE   

X

                TOYOTA 300                     X                   X

X

                NISSAN 321                      X                   X    

X

               HONDA 280                       X                   X            

X

                 HYUNDAI              450                      X X         

X

Further instruction

§     The VAT is calculated as 2.5% of the cost price

§     Selling  price    is said to be more than the cost price  at least by 42%

§     Determine the correct formulae for the profits

§     Make the heading bigger and BLUE  in color

§     Add 3 more similar records  of  your  choice – complete with the correct calculations

§     Make the column headings bold

SPREADSHEET  EXAMINATION SAMPLE 2

PRACTICAL:60 Mks

Attempt all the questions

Q1. Create the worksheet shown below and answer the questions that follows.

Student        Course               Test 1         Test 1         Test 3         Totals    Average     Maximum

Jane               Ms word            56%            78%            90%

Mike             Ms Access          78%            89%            45%

John              Windows 95       88%            77%            66%

Susan            Ms Word            45%            60%            78%

Olao              Ms Access          69%            88%            56%

Grace            Windows 95       76%            43%            39%

 

                iii)        Maximum mark

(b)   Calculate Totals, Average, and Minimum for Test 1, 2, and 3

(c)   Calculate the totals for the female students alone for all the three tests.

(d)   Insert a new column for Test Date between Course and Test 1. Enter dates of your own choice. (e) Apply borders to your table with red colour.

(f) Format the content of the table as follows;

i)          Headings : Font face –Tahoma, bold, Size 14, colour = violet ii)         Other records – font face Comic San MS , size 12, colour = blue (g) Apply pattern to your table.

(h)   Create a column chart as a New Sheet to compare the students against their total marks. On the chart include;

i)          Chart Title ii)            Both Axes – Y and X

Enhance the appearance of your chart.

(i)  Using AutoFilter option, retrieve only those students who attained 70% and above in Test 1 and copy them below the main table.

(j)  Save your workbook with file name Student Exams on diskette.

SPREADSHEET  EXAMINATION SAMPLE 3

PRACTICAL:100 Mks

Attempt all the questions

Please set up a spreadsheet using the following information. Do NOT put any lines or borders on it yet.

   

•   Left-align the heading NAMES and the data in that column.

•   Put the other colunm headings on 2 rows. Right align these headings and the figures.

•   Adjust the column widths to fit the text.

•   Please use the SUM  formula to calculate the TOTAL WEIGHT (kg) of fruit packed by each worker.

•   Format all the numbers as integer (0 decimal place).

•   Set up the spreadsheet ready for printing in portrait format and save using the filename FRUIT 1.

-I have just receive amendments to the sales figures for last week. Please amend the spreadsheet to show the following:

•   Delete the row   for  PARTHA as he has left.

•   MAHID also  packed plums- please insert 100 instead of 0 in the appropriate cell.

•   Please insert a row (after HALL) for the new member of staff, KINGSTON. His detail are:

NAMES                WEIGHT              WEIGHT              WEIGHT              TOTAL

                                        PEACHES            PLUMS                ORANGES           WEIGHT

49KINGSTON     120                        10                          29

Please enter this data in the appropriate row  and copy the formula for TOTAL WEIGHT.

•   I would  prefer to have the employee names  and their payroll numbers separately. Will you  please  insert 2 columns after the NAMES column and insert the headings  EMPLOYEES, PAYROLL NUMBER. Enter the information into the 2 columns (see the example  started below).

 

I would like the PAYROLL NUMBER heading and the numbers centered in the column please.

Now delete the NAME column.

Please set up the spreadsheet ready for printing in portrait format and save using the filename FRUIT 2. I would like you to do a few more changes.

•   Add columns to the right of the spreadsheet , right-aligned and format them for currency in $ (0 decimal places).

PENSION

•   Some workers are in the pension scheme. Their payments per week are:

         Allen $20  Dion $15                   Mahid $18      Tradestrah $25

All other workers pay $0. Put these figures into the table , including those paying $0.

•   Calculate the GROSS PAY for each worker by multiplying the TOTAL WEIGHT by 1.2 and then subtracting the PENSION payment (0 decimal  places.)

•   Add a row at the bottom of the sheet and label it AVERAGE .Calculate the average for the 4 columns containing weights only maintaining the format of the column (0 decimal places)

•   Add a tittle WEEKLY PRODUCTION FIGURES in bold capitals and a lager font above the spreadsheet.

•   Add shading to the column headings and a boarder  and a lines to the full table (include the column headings but not the title in the border.)

•   Please set up the spreadsheet ready for printing in landscape format and save the file as FRUIT 3. Make these more amendments.

•   Sorry . I forgot to tell you that the workers have had a pay rise. The rate has been increased to $1.3(per kg). Please amend the formula and recalculate all the data.

•   Allen withdrew from the pension scheme and made no contribution this week. Please remove his payment.

•   We spelt FAGEN wrongly. It should be FAGAN. Please amend it.

•   Change the spreadsheet to display formulae. Adjust the column widths so that he formula e are displayed in full and the sheet fits into one side of A4 landscape format .Set up the spreadsheet ready for printing in landscape format and save the file as FRUIT4.



6