TOPIC 7: ELECTRONIC SPREADSHEETS


SPREADSHEETS

A spreadsheet is a grid of rows and columns that accepts entry of data, allows editing, formatting and manipulation of numeric data. Spreadsheets can also display data graphically with the help of charts and graphs.

TYPES OF SPREADSHEETS

  1. Manual spreadsheets. The manual spreadsheet is the most commonly used type by book keepers as a ledger book with many sheets of papers divided into rows and columns on which various amounts of money are entered manually using a pen or a pencil and manipulated manually with the help of a calculator.
  2. Electronic spreadsheets. An electronic spreadsheet is a spreadsheet prepared using a computer program that enables the user to enter values in rows and columns and to manipulate them mathematically using formulae and functions automatically.

Examples of electronic spreadsheet programs include;

Microsoft Office Excel, Lotus 1-2-3
,Lotus Symphony, OpenOffice, VisiCalc, OpenOffice, Ability Office,Apple works, EasyOffice, Mariner Calc, Star office, PlanMaker  

Advantages of Manual Spreadsheets

  1. They are easy and cheap to acquire
  2. They are easily portable
  3. They are suitable for draft or rough work
  4. They are not electronic, thus, can be used without electric power
  5. No skills are needed, hence, even a computer illiterate can use manual spreadsheets

Disadvantages of Manual Spreadsheets

  1. They require a lot of manual effort and time
  2. Many errors are bound to be made
  3. Rubbing out to correct errors makes the work untidy
  4. They do not have pre-existing tables as opposed to electronic spreadsheets
  5. They are very small in size
  6. They are not durable. They can easily wear and tear out
  7. They do not have automatic formulas that would otherwise quicken the work
  8. You cannot easily insert or delete extra columns and rows

Advantages of Electronic Spreadsheets

  1. They have pre-existing tables, thus, no need to draw gridlines
  2. They have in-built formulas and functions, enabling automation in calculations and work manipulations
  3. There are minimal errors and in case of any, they are easily corrected
  4. They have very large worksheets that can store a lot of work easily and for long
  5. Extra columns and rows can be inserted and deleted without any bad effect
  6. The work can be protected with passwords thus ensuring security
  7. Work can be enhanced to look very attractive with various formats to suit the user’s needs
  8. The records can be sorted and filtered to get only those that you want
  9. They allow printing of multiple copies without re-creation

Disadvantages of Electronic Spreadsheets

  1. They are expensive to buy and maintain
  2. They are electronic, thus cannot be used without electricity
  3. They require computer skills and continuous training
  4. There is data loss due to virus attacks and system failure
  5. There are privacy problems like unauthorised access over networks
  6. Health related hazards as they are associated with use of computers

FEATURES OF ELECTRONIC SPREADSHEET SOFTWARE

  1. Workbook. This is a collection of multiple worksheets in a single file
  2. Worksheet. This is a single page of a workbook. It is an equivalent of a work area in Microsoft Word. A worksheet is made up of rows and columns which intersect to form cells. Worksheets are labelled sheet1, sheet2, sheet3 by default, but they can be renamed. A workbook by default has 3 worksheets, however, these can be increased in the user’s interest and renamed
  3. Columns. These are vertical lines which run through the worksheet. Worksheet columns are labelled by letters; A, B, C, D, E… which are displayed in grey buttons across the top of the worksheet
  4. Rows. Are horizontal lines across a worksheet. Worksheet rows are labelled by numbers; 1, 2, 3, 4, 5… which are displayed in grey buttons across the left of the worksheet
  5. A cell. This is an intersection of a column and a row. Each cell on the spreadsheet has a cell address.A cell addressis a unique name of a cell. It is given by the column letter and row number, e.g. A1, B5, G6, D12, C1, A4, B3, etc.  Cells can contain; text, numbers, formulas, etc.
  6. Range. It is a group of adjacent cells defined as a single unit. A range address is a reference to a particular range. It has a format of top left cell address : bottom right cell address. e.g. D5:G10
  7. Value. This is a numerical entry in a cell. All values are right aligned in a cell by default.
  8. Labels. This is a text entry in a cell. All labels are left aligned in a cell by default
  9. Name box. This displays the address of the selected cell or cells. Also you can rename a selected cell or cells using the name box
  10. Formula bar. Is a bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts.
  11. Autofill. This is the feature that allows you to quickly fill cells with repetitive or sequential data such as chronological dates or numbers, and repeated text. To use this feature, you type one or two initial values or text entries, and then Autofill does the restusing the fill handle, which is the small black square in the lower-right corner of the selection. When you point to the fill handle, the pointer changes to a black cross. Autofill recognises series of numbers, dates, months, times and certain labels.
  12. Sorting data is to arrange records in either ascending or descending order.
  13. Filtering datais the displaying of records that satisfy the set condition from the parent list.
  14. Database. These are data values that can be entered in the cells of the spreadsheet and managed by special spreadsheet features found on the data menu. The special spreadsheet features include; cell referencing, data replication, automatic recalculation, formulas and functions, data filtering, copy, cut and paste, clip art.
  15. Graphs. A graph is a pictorial representation of the base data on a worksheet. Most spreadsheets refer to graphs as charts.A chart is a graphical representation of data. A chart may be 2-D or 3-D
  16. What-if analysis. Is a process of changing the values in cells to see how those changes affect the outcome of formulas on the worksheet. For example, varying the interest rate that is used in the paying-back table to determine the amount of the payments.
  17. Freezing panes. This is where rows and columns are frozen such that they remain visible as you scroll through the data especially if the database is too big to fit on one screen.

Uses/Applications of Spreadsheets

  1. Preparation of budgets
  2. Preparation of cash flow analysis
  3. Preparations of financial statements
  4. Processing basic business information, like, job costing, payment schedules, stock control, tax records
  5. Analysis of data from questionnaires
  6. Presentation of information in tabular form, graphical or charts forms
  7. Mathematical techniques and computation like trigonometry
  8. Statistical computations like standard deviations.

OPERATORS

Operators specify the type of calculation that you want to perform on the elements of a formula. There is a default order in which calculations occur, but you can change this order by using brackets.

Types of Operators

There are four types of calculation operators: arithmetic, comparison, text concatenation, and reference.

Arithmetic operators

These are used to perform basic mathematical operations such as addition, subtraction, division or multiplication; combine numbers; and produce numeric results.

Arithmetic operator      Meaning                         Example

+ (plus sign)                   Addition                         3+3

– (minus sign)                Subtraction                     3–1

Negation                         –1

* (asterisk)                     Multiplication                3*3

/ (forward slash)             Division                         3/3

% (percent sign)             Percent                           20%

^ (caret)                          Exponentiation              3^2

Comparison Operators

These are used to compare two values, and the result is a logical value either TRUE or FALSE.

Comparison operator                      Meaning                        Example

= (equal sign)                                     Equal to                         A1=B1

> (greater than sign)                          Greater than               A1>B1

< (less than sign)                               Less than                       A1<B1

>= (greater than or equal to sign)      Greater than or equal to          A1>=B1

<= (less than or equal to sign)           Less than or equal to     A1<=B1

<> (not equal to sign)                        Not equal to                             A1<>B1

Reference Operators

combine ranges of cells for calculations. Examples include;

Reference operator       Meaning                                                              

Example

: (colon)      Range operator, which produces one reference to all the.      

B5:B15       cells, between two references, including the two references.    

, (comma)   Union operator, which combines multiple.

SUM(B5:B15,D5:D15)  references into one reference

(space)        Intersection operator, which produces one reference    

B7:D7 C6:C8       to cells common to the two references

& (ampersand)     Connects two values to produce one continuous text value (“North”&”wind”)

CELL REFERENCES

A Cell reference is an address given to a particular cell or group of cells on a worksheet. e.g. A2, B6, B3.

There are three types of cell references;

  1. Relative cell reference. Here, the address of a cell is based on the relative position of the cell that contains the formula and the cell referred to. If you copy the formula, the reference automatically adjusts. A relative cell reference takes the form: A1, B17, G20, C2.
  2. Absolute cell reference. Here, the exact address of a cell is used in the formula, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form: $A$1, $D$6, $B$3, $E$6.
  3. Mixed cell reference. This is a type that uses both relative and absolute cell references at once. It may use an absolute column reference and a relative row reference or vice versa, e.g. $G17, B$14, D$2, $E2.

FORMULAS

Formulas are equations that perform calculations on values in your worksheet and return a value in a chosen cell, e.g. =A2+B2,   =(A3+B3+C3+D3)/4,   =A6*B4,   =C4-D4,   =E10/G10

LOGICAL FUNCTIONS

Function     Description

AND           Returns TRUE if all of its arguments are TRUE; Returns FALSE if any argument is FALSE

FALSE        Returns the logical value FALSE

IF                Specifies a logical test to perform

IFERROR  Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula

NOT           Reverses the logic of its argument

OR             Returns TRUE if any argument is TRUE

TRUE        Returns the logical value TRUE

ERROR ALERTS

Microsoft Excel displays an error value in a cell when it cannot properly calculate the formula for that cell. Below are some common error values and their meanings.

Error message     Meaning

  1. #####              Column is not wide enough, or a negative date or time is used.
  2. #DIV/0!           A number is divided by zero
  3. #N/A!              A value is not available to a function or formula
  4. #NAME?         Microsoft Office Excel does not recognise text in a formula.
  5. #NULL!           You specified an intersection of two areas that do not intersect
  6. #NUM!            The numeric values used in a formula or function are invalid
  7. #REF!              The cell reference is not valid, e.g. 6E instead of E6
  8. #VALUE!        An argument or operand used is of wrong type

FUNCTIONS

A function is a prewritten formula that takes a value or values, performs an operation, and returns a value or values in a chosen cell. Examples of functions in MS Office Excel include the following:

Function Description Example
SUM Adds all the numbers in a range of cells =SUM(B2:G2)
PRODUCT Multiplies numbers given as arguments to return product =PRODUCT(A2:D2)
MAX Returns the largest value in a set of values =MAX(D4:D10)
MIN Returns the smallest number in a set of values =MIN(A2:A12)
LARGE Returns largest value in a data set, e.g. 5th largest value =LARGE(B1:B9,5)
COUNT Counts number of cells in a range that contains numbers =COUNT(A1:E9)
COUNTIF Counts number of cells in a range that meet given criteria =COUNTIF(A1:C9,”<10”)
COUNTBLANK Counts number of empty cells in specified range of cells =COUNTBLANK(A2:H8)
AVERAGE Returns the average (arithmetic mean) of the arguments =AVERAGE(B2:B15)
MEDIAN Returns number in the middle of the set of given numbers =MEDIAN(D4:D10)
MODE Frequently occurring value in arange of data. =MODE(C2:C9)
RANK Returns the size of a number relative to other values in a list of numbers. =RANK(F3,$F$3:$F$11,0)
SQRT Returns a positive square root =SQRT(B5)
IF Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE. =IF(A2<50,”fail”, “pass”)
VLOOKUP Searches for a value in the first column of a table array and returns a value in the same row from another column. =VLOOKUP(lookup_value, lookup table, column)
HLOOKUP Searches for a value in the top row of a table array and returns a value in the same column from a row you specify in the table or array =HLOOKUP(lookup_value,lookup_table, column_index )

Have any Question or Comment?

Leave a Reply

Your email address will not be published. Required fields are marked *