MICROSOFT EXCEL HANDOUT

MICROSOFT EXCEL (SPREADSHEET APPLICATION PACKAGE)

INTRODUCTION TO SPREADSHEET APPLICATIONS/PACKAGES

Spreadsheet applications are programs made of rows and columns which are basically used for calculations.

Before the existence of spreadsheet packages, ledger were used by accountants and business men for their daily financial calculations.

TYPES OF SPREADSHEET PACKAGES

1.    Lotus 1,2,3

2.    Quartro Pro

3.    Ms Excel

4.    Peachtree Accounting etc

Among the above mentioned packages, we will concentrate with Microsoft Excel.

INTRODUCTION TO MICROSOFT EXCEL

Ms Excel is a spreadsheet package designed by Microsoft Cooperation. It belongs to a big application known as Ms office or office Suite.

DEFINITION OF MS EXCEL

This can be as a spreadsheet application used basically for calculations and data analysis.

STEPS TO OPEN EXCEL

1.    Click on start button

2.    Click on All program

3.    Click Ms Office

4.    Click on Ms Excel

Note: It can also be opened through the Desktop or Start button menu.

SCREEN ELEMENTS OR PROPERTIES OF MS EXCEL

1.    Title Bar:-  It contains the file and program a user is working with at a given time.

2.    Menu Bar:-  It contains all the commands or instructions used to communicate with Excel

3.    Standard Bar:-  It is the most frequently used bar. It contains shortcut commands which can be accessed easily without using the menu bar.

4.    Formatting Bar:-  It is a bar used to beautify a workbook. It is also a shortcut bar.

5.    Drawing Bar:-  It is a bar used to manipulate graphics in Excel.

6.    Horizontal & Vertical Scroll Bar:- These are bars used to navigate within an  environment.

7.    Status Bar:- It displays a detailed information concerning the environment a user is working with.

8.    Editing Window:-  It is an environment used for data manipulation

9.    Task Bar:-  It a bar that contains the start button with date and time.

TERMS OR CONCEPTS USED IN EXCEL

1.    Spreadsheet:-  It is the environment used for data manipulation. It is made of rows and columns.

2.    Worksheet:-  It is the current environment a user is working with at a given time.

3.    Workbook:-  A workbook is made up of worksheets. Note in excel, a file is saved as workbook.

4.    Cell:-  It is a rectangular box formed by the intersection of rows and columns.

5.    Active cell:-  It the thicker cell used for data manipulation.

6.    Cell range:- It is a group of cells that are selected together.

7.    Cell pointer:- It is used to indicate an active cell

8.    Cell address:-  It is a special way of naming a cell by considering the cell alphabetical and numerical position.

9.    Name box:- It is a box that displays the address of an active cell

10.Formula bar:- It is a bar used to edit the content of an active cell.

PRACTICAL APPROACH TO CALCULATIONS

As a sales manager of a certain organization calculate the following using the table below.

S/N	DESCRIPTION	QTY	PRICE/EACH	TOTAL AMOUNT
1	Mother board	5	10000
2	Hard disk	4	4000
3	Monitor	5	3500
4	Processor	1	5000
5	Joystick	10	500
6	Mouse	20	500
7	Printer	2	10000
8	Modem	2	8000
9	Adapter	3	5000
10	USB cable	1	500

Calculate:

1.    Total amount for each item

2.    The sub-total of the overall items

3.    In exception of the amount of mother board, calculate the sub-total for other items.

4.    Save the work.

STEPS TO SAVE A WORKBOOK

1.    Click on file menu

2.    Click on Save As

3.    Under save in, select the file location where you want to save the file

4.    Type the file name

5.    Click on save.

Also, through the standard bar, save can also be achieved.

STEPS TO OPEN A WORKBOOK

1.    Click on file menu

2.    Click on open

3.    Select the file location where you saved the file

4.    Click on the file

5.    Click on open.

Autofil:-This is a command used to automatically calculate the subsequent values in a column.

To Autofil, simply point at the Auto fil handle, allow it to turn to a thick cross, then drag it to last value in the column.

PAGE SET-UP:- This can be defined as a command used to adjust the look or appearance of a workbook.

When setting a page, users are advised to observe the margin, orientation, scarling and paper size.

STEPS TO SET-UP A PAGE

1.    Click on file menu or page layout

2.    Click on page setup

3.    The observe the necessary adjustment under margins, orientation, scarling and size.

BORDERS:- it is a command used to apply real lines in a worksheet especially the type to be produced as hardcopy.

STEPS TO APPLY BORDER:

1.    Select the area you want to apply the border

2.    Click on format menu

3.    Click on cell

4.    Click ,on alignment

5.    Click on outline and inside

6.    Click on ok.

HEADER AND FOOTER:-  These are commands used to display descriptive notes on top and bottom of a workbook.

STEPS TO APPLY HEADER AND FOOTER

1.    Click on insert menu

2.    Click on header or footer

3.     Type the text for the header and the footer

4.    Then double click on the working area.

FORM:-This can be defined as a command used to add or remove an information from a database.

STEPS TO APPLY FORM

1.    Position your cell in the first cell that start your database

2.    Click on data menu

3.    Click non form

4.    Use find next and find preview to select the information you want to delete

5.    Click on delete in order to delete the information

6.    Click on new order to add new information, and then add the information.

SORT:- This can be defined as a command used to arrange information in a database in ascending or descending order.

STEPS TO APPLY SORT

1.    Position your active cell at the beginning of the database

2.    Click on data menu

3.    Click on sort

4.    Under column sort by, select the field name you want to sort its contents

5.    Under order, select ascending or descending

6.    Click on ok.

FILTER:- Filter is a command used to select information that meet to a certain proposed information in a database.

STEPS TO APPLY FILTER

1.    Position your active cell in the first cell that starts the database

2.    Click on data menu

3.    Click on filter

4.    Then select the field name you want to filter its contents

5.    To filter any record of your choice, select number filter or text filter by

6.    Select any condition of your choice e.g greater than, less than

7.    Then select the value to be filtered

8.    Click on ok.

    

PAYROLL:- This can be defined as a database feature that contains information of workers in a giving company. It can also be defined as a feature used to determine the paying structure of workers in a company.

FEATURES OR PROPERTIES OF A PAYROLL

1.    Serial Number:- It is used to arrange information in a database sequentially.

2.    Names;-  It contains the names of workers in a certain company

3.    Sex:-  It is used to describe the gender of workers in an organization.

4.    Age:-  It shows the age of workers in a firm or organization

5.    Position:-  It describe the job title of workers.

6.    Basic Salary:-  This is the actual amount of money a worker earns at the end of the month. Not basic salary is not calculated.

7.    Allowance(s)/Bonus:-  This are additional money giving to a worker as a compensation. Allowance or Bonus is calculated using a specified or a giving percentage. E.g giving that Feeding Allowance is 4% and Basic Salary is 10,000. Then to calculate the Feeding Allowance: =4% of Basic salary. Which is equivalent to: =4%*10000.

8.    Gross Pay (G.P):-  This can be refers to the total amount of money a worker earns at the end of the month. In order words, it is the sum of Basic Salary with all the allowance. To calculate G.P: =sum(Basic Salary to all The Allowances) e.g =sum(B2:i2)enter.

9.    Tax or Levy:-  These are money removed from a workers salary. They are calculated using a giving percentage. E.g giving that Tax or Levy is 3%. To calculate it, =3% of Basic Salary and enter key. i.e =3%*B2 enter key.

10.Total Deduction:-  This is the total sum of money removed from a workers salary. In order words, it is the addition of tax and levy or debt. E.g tax=levy i.e i2+j2 enter.

11.Net Pay(N.P):- This can be defined as the money a worker goes home with after observing all the necessary payments. It can be referred to as a go home salary. It can be calculated as Gross Pay-Total Deduction i.e m2-o2 enter

METHODS OF CALCULATION IN EXCEL

1.    Using the function menu

2.    Using the Cell Address

1.    Using the Function Menu:- This involved the user to select form the predefined commands from his/her computer.

USING THE FUNCTION MENU

1.    Position your active cell were the answer will appear

2.    Click on insert menu

3.    Click on function or insert function

4.    Under select a category, click on All

5.    Under available functions, select any function of your choice e.g sum, min, max, average, IF etc

6.    Click on ok

7.    Type the logical statement in the space provided

8.    Enter the value for each of the expression

9.    Click on ok.

USING THE CELL ADDRESS

1.    Position your active cell where the answer will display

2.    Press equal to sign

3.    Type the logical statement and the values using the appropriate format

4.    Press enter from the keyboard e.g to calculate gross pay: =sum(j2:m2)enter

LOGICAL ANALYSIS (BUILDING CONDITION)

Logical analysis can be defined as an expression used to select records that meet to a proposed condition in a database.

TYPES OF LOGICAL ANALYSIS

1.    Simple Logical Analysis

2.    Complex Logical Analysis

1.    Simple Logical Analysis:- This is an expression used to select records that appears in only two ways. E.g pass and fail, retire and retain, yes and no, merit and demerit etc.

2.    Complex Logical Analysis:- It is an expression used to select records that appears in several options. E.g distinction, upper credit, lower credit, merit, pass an d fail. Logical analysis is basically used in Institutions of learning such as Jamb, Waec, Neco, RBC etc.

CHART:-  It can be defined as a command used to present information in a diagrammatical or pictorial form.

STEPS TO APPLY CHART

1.    Select the information you want to apply the chart on.

2.    Click on insert menu

3.    Click on chart

4.    Select the chart type of your choice and next

5.    Select column or row and next

6.    Specify the chart title, value on x and y axis and next

7.    Under as object in, select an empty sheet

8.    Click on as new sheet

9.    Click on finish.

PRINT PREVIEW AND PRINTING

Print preview is a command used to produce the softcopy of an information. While printing is use to produce the hardcopy of an information.

To print preview, simply click on file menu and click on print preview.

STEPS TO PRINT

1.    Click on file menu

2.    Observe your printing options

3.    Click on print or ok.