4.5 SPREADSHEET
A Spreadsheet, as you all would know, is a grid made of columns and rows known as cells and is used for making tables and charts used for mathematical and statistical analysis as well as business calculations. One of the widely used spreadsheet software is MS-Excel. A few features of MS Excel 2007 are described below.
4.5.1 Introduction
Each cell on a spreadsheet can have any of the following data:
- Text or Labels
- Numbers or Constants
- Formulae which are mathematical equations to do all calculations
The following example shows the three basic types of data that a cell may have:
- Labels Text which simply is the column heading like cell B2, C2, C3 etc.
- Values which are constant fixed numbers entries like C3, C4, D3, E4 etc
- Formulas which are mathematical calculations as in the highlighted column E8. Formula cells always begin with an EQUAL SIGN (=). It is best to use the reference to data within the formula cells by giving the cell location. This helps in keeping the formula unchanged even if the values change.
4.5.2 Basic Features
The spreadsheet consists of various sheets like sheet1, sheet2, sheet3 etc. within
the workbook. These sheets can be can be given meaningful names. New sheets
can be inserted and worksheets can be deleted from the workbook.
The format of the cells on the sheets can be modified using the Home->Cells-
>format->format cells option on the menu. This will give the following dialog
box which is used to format datatype of the cell, alignment, font etc.
Formulas : Only pastes the formulas from the cells on the clipboard
Values : Will paste the values of the cells on the clipboard. If the cells copied
contained formulas, only the results of the formulas will be pasted. 
Column Widths : Will paste only the column widths of the cells that you
copied to the clipboard.
Operations : This is useful if you want to scale a group of numbers or
add/subtract a certain amount from each. Whatever value is copied to the
clipboard, the current operation will be done using that number on the cells in
which you are pasting to.
Skip blanks : Will paste data, skipping any blank cells.
Transpose : Will transpose the data and paste it (columns to rows, rows to
columns).
It is possible to protect the cells so that the data cannot be changed or modified.
By default all cells in a worksheet have the locked property turned on (the cell is
capable of being protected). is also possible to protect worksheet or the whole
workbook using password protection.
Protecting a Worksheet
1. Click Tools, click Protection, and click Protect Sheet.
2. Enter a password (only if desired).
3. Confirm password (only if entered).
4. Click OK.
Protecting a Workbook
1. Click Tools, click Protection, and click Protect Workbook.
2. Enter a password (optional).
3. Choose to protect the Structure, Windows, or both.
4. Click OK.
4.5.3 Formulae and Functions
There are different categories of functions that can be incorporated in the sheets
like Date & Time, Mathematical, Statistical, Logical, Text functions etc.
The different categories of functions are:
Date and Time:
MONTH - Converts a serial number to a month
YEAR - Converts a serial number to a year
NOW - Returns the serial number of the current date and time
Math and Statistical
SUM – Adds its arguments
MEDIAN – Returns the median of the given numbers
SUMPROUCT – Returns the sum of the products of corresponding array
components
COUNTIF - Counts the number of cells within a range that meet the given criteria
Text:
CONCATENATE – Joins several text items into one text item
RIGHT - Returns the rightmost characters from a text value
LEN - Returns the number of characters in a text string
EFT - Returns the leftmost characters from a text value
Logical:
IF - Specifies a logical test to perform
NOT - Reverses the logic of its argument
AND - Returns TRUE if all of its arguments are TRUE
Advanced Functions:
MATCH- Looks up values in a reference or array
INDEX - Uses an index to choose a value from a reference or array
SEARCH - Finds one text value within another (not case-sensitive)
REPLACE - Replaces characters within text
Database Functions:
DGET- Extracts from a database a single record that matches the specified criteria
DCOUNT - Counts the cells that contain numbers in a database
DSUM - Adds the numbers in the field column of records in the database that
match the criteria
DPRODUCT - Multiplies the values in a particular field of records that match the
criteria in a database
Lookup & Reference:
VLOOKUP - Looks in the first column of an array and moves across the row to
return the value of a cell
HLOOKUP - Looks in the top row of an array and returns the value of the
indicated cell
INDIRECT - Returns a reference indicated by a text value
4.5.4 Charting
Charts and Graphs can be created based on data in the sheets. To create a chart to
represent data graphically:
1. Select the data
2. Go to Insert
3. Select the chart type from the options available like Bar, Line, Pie, Scatter etc.
4. The chart will get automatically populated with the selected data on which the
chart is to be based.
4.5.5 Macros
A macro is a short program written using VBA that can be used to carry out a specific task. VBA is the language that Excel macros are written in. It is a programming language that is included with all of the Microsoft Office applications e.g. Word, Access, Power Point, Excel as well as others. VBA is a subset of Microsoft Visual Basic, an extremely popular programming language that has been around for over 10 years. The Macro has to be recorded as follows:1. Go to the Tools menu, go to ―macro‖ and then ―Record New Macro‖
2. Assign a name to your macro if you'd like, as well as type a short description.
3. You can also assign a keyboard shortcut to it (so you can press a sequence of keys to run the macro).
4. Now click on OK. You'll be returned to Excel.
5. Simply perform the actions you want the macro to do.
6. Once you're done recording your macro, press the Stop button which should now be visible on your screen.
Once the Macro is recorded it can be executed in the following ways:
1. Run a macro by using the menu command
2. By pressing a CTRL combination shortcut key
3. Clicking a toolbar button or an area on an object, graphic, or control
4. Run a macro automatically when the workbook is opened.
4.5.6 A Small Example in Excel 2007
Take a small example given in the table below for calculation of Salary and
Income tax deduction and compute the result in excel to give the net monthly and
annual income as shown below.
