PREADSHEET 2 - NCERT

1 SPREADSHEETI ntroductionAfter studying this chapter you will beable to understand: Concept of Spreadsheet and itsfeatures. How to use a A spreadsheet is a configuration of rows andcolumns. Rows are horizontal vectors whilecolumns are vertical vectors. A spreadsheet is alsoknown as a worksheet. It is used to record,calculate and compare numerical or financial value can either be an independent ( basic)value or it may be derived on the basis of values ofother variables. The derived value is the outcomeof an arithmetic expression and/or a function( a formula).Spreadsheet application (sometimes referred tosimply as spreadsheet) is a computer program thatallows us to add ( enter) and process data. Weshall understand spreadsheet with the help ofMS-Excel (or simply, Excel), which is one of theMicrosoft Office Suite of current version of Excel is Excel 2007 andhas a completely redesigned user interface.

2 TheExcel 2007 is now designed with a series ofhorizontal tabs known as Ribbon (Figure ).These tool bars are changed using tabs at the layout is very easy to use than the previousFigure Accounting Systemversions of Excel. On clicking with left button of mouse at Office Button ; we will be able to open an old workbook or createa new one or can save the workbook or can print which were earlieravailable in previous version of Excel in File CONCEPTS OF SPREADSHEETA file in Excel is known as a Workbook . A workbook is a collection ofa number of Worksheets (Figure ). By default, three sheets, namelySheet 1, Sheet 2, and Sheet 3 are available to users. At a time, onlyone worksheet can be made as Active Worksheet and that worksheetis available to a user for carrying out operations.

3 An active worksheet sname will be shown in bold letters in the Sheet Tab at the bottom leftof the screen. Additional sheets can be added, if required, by clickingon the icon (which works as Insert ! W ! W ! W ! W ! Worksheetorksheetorksheetorksheetorkshee t).The Sheet names can bechanged, if required, by right-clicking the mouse over the Sheet1or Sheet 2 or Sheet 3 after selectingand pointing it on the sheet name(which is to be changed) and selec-ting Rename and Derived VBasic and Derived VBasic and Derived VBasic and Derived VBasic and Derived ValuesaluesaluesaluesaluesIf quantity (Q) of an item ispurchased at a price (P), the valueof that item (V) is derived asfollows:V = Q PHere, the values P and Q areBasic Values.

4 While V is theDerived Value as it is obtainedby multiplying Q with P. Theexpression (Q P) is called asarithmetic expression. Addi-tional examples of arithmeticexpressions are given later in : In general, an arithmeticexpression may contain one ormore are numbered numericallyfrom top to bottom while ColumnsColumnsColumnsColumnsColumnsare referred by alpha charactersfrom left to right. In Excel 2007,there are 65536 RowsRowsRowsRowsRows which arenumbered as 1, 2, 3, .. 65,536. Thesenumbers are shown on the left mostportion of the worksheet. ColumnsColumnsColumnsColumnsColumns(tota l 256 in Excel) are identifiedby letters, such as A, B, C,.. , and are shown on the horizontalbox just above Row 1. Thus, thereare 65,536 256 = 1,65,00,000,approximately cells, which is indeed a huge work area, sufficient forall application requirements (Figure ) in one a spreadsheet, a value or function or an arithmetic expression isrecorded in a cellcellcellcellcell.

5 The intersection of a rowrowrowrowrow and a columncolumncolumncolumncolumn is called acell. cell. cell. cell. cell. A cellcellcellcellcell is identified by a combination of a letter and a numbercorresponding to a particular location within the spreadsheet. Forexample, the first cell of a worksheet is identified as A1 as it shown inFigure at row 1 and column (A). When we start Excel, the pointer(cursor) points to the first cell, A1, and this cell is called the ActiveActiveActiveActiveActiveCellCellCe llCellCell. We can move around a worksheet through four arrow keys ( , right, up, down as shown in Figure ). For example, the cellhaving address as G8 correspond to 8th row under G column. Eachcell thus has a unique identification called as cell addresscell addresscell addresscell addresscell Reference A cell reference identifies the location of a cell orgroup of cells in the spreadsheet also referred as a cell address.

6 Cellreferences are used in formulas, functions, charts, other Excelcommands and also refer to a group or range of cells. RangesRangesRangesRangesRanges areidentified by the cell references of the cells in the upper left (cell A1)and lower right (cell E2) corners in Figure The ranges are identifiedusing colon (:) A1: E2 which tells Excel to include all the cellsbetween these start and end points. By default cell reference isrelative; which means that as a formula or function is copied andpasted to other cells, the cell references in the formula or functionchange to reflect the new location. The other cell reference is absolutecell reference which consists of the column letter and row numbersurrounded by dollar ($) signs $C$4.

7 An absolute cell reference isused when we want a cell reference to stay fixed on specific cell,which means that when a formula or function is copied and pasted toother cells, the cell references in the formula or function do not mixed reference is also a cell reference that holds either row orcolumn constant when the formula or function is copied to anotherlocation , $C4 or C$ Accounting SystemThe mouse is used for all the operations requiredand for navigation in worksheet (or workbook) exceptdata entry; but some of the important operations andcommon navigations can be performed by using keystrokes (as given below). It is better to understandand know all the keys of keyboard and key a key is called key stroke but to fulfill onecommand for operation in the worksheet some timewe require pressing two keys together to get one keystroke (Figure )MovementMovementMovementMovementMovemen tKey Stroke (Press key)Key Stroke (Press key)Key Stroke (Press key)Key Stroke (Press key)Key Stroke (Press key)One cell downDown arrow key () or EnterEnterEnterEnterEnter keyOne cell upUp arrow key ()One cell leftLeft arrow key ()One cell rightRight arrow key ()

8 Or TabTabTabTabTab keyThe other navigational and operational strokes are used for fastercursor movement than one cell at a time with cluster of filled of filled cells implies a set of consecutive cells in a row or in acolumn having some data that is entered in a cell may be either numeric or alpha-numeric or a date. As a data is typed in a cell, Excel is able to makeout its type ( numeric or alpha-numeric or date) depending on thenature of value typed in a Stroke (Press key)Key Stroke (Press key)Key Stroke (Press key)Key Stroke (Press key)Key Stroke (Press key)Top of Worksheet (cell A1)CTRLCTRLCTRLCTRLCTRL + HOMEHOMEHOMEHOMEHOME ( Keep CTRLCTRLCTRLCTRLCTRL keypressed and then press HOMEHOMEHOMEHOMEHOME keyThe cell at the intersection of theCTRLCTRLCTRLCTRLCTRL + ENDENDENDENDEND keyslast row and last column containing dataMoving consecutively to the first and the lastCTRLCTRLCTRLCTRLCTRL + Right arrow key () orfilled cells of clusters of filled cells in a row byelse ENDENDENDENDEND + Right arrow key ())

9 Successive pressing of CTRLCTRLCTRLCTRLCTRL + Right arrowkey () or else ENDENDENDENDEND + Right arrow key ()Moving consecutively to the first and the lastCTRLCTRLCTRLCTRLCTRL + Down arrow key () orfilled cells of a cluster of filled cells in a columnelse ENDENDENDENDEND + Down arrow key ()by successive pressing of CTRLCTRLCTRLCTRLCTRL + Down arrowkey () or else ENDENDENDENDEND + Down arrow key ()Beginning of the RowHOMEHOMEHOMEHOMEHOME keyBeginning of the ColumnNavigating In ( Moving arNavigating In ( Moving arNavigating In ( Moving arNavigating In ( Moving arNavigating In ( Moving around) The Wound) The Wound) The Wound) The Wound) The Worksheetorksheetorksheetorksheetorkshee t2022-23 Spreadsheet21If the value is entered as 306, its type is automatically taken as Numeric;if the value is entered as Asset, its type will be taken as alpha-numeric;while if the value is entered as 12/07/08, its type is taken as Date.

10 (refer figure )The first step required to use Excelfor a specific application is to decidewhat values will be entered in whichcells and also the cells which will beused for entering the we have decided about the cellswhich are to be used for therelationships; the formulas (arithmeticexpressions) and data can be entered.(See Box at page18)VVVVV aluesaluesaluesaluesaluesA value can be entered from the computer keyboard by directly typinginto the cell itself. Alternatively, a value can be based on a formula(derived), which might perform a calculation, display the current date ortime, or retrieve external data such as a stock quote or a database value rulevalue rulevalue rulevalue rulevalue rule according to computer scientist Alan KayAlan KayAlan KayAlan KayAlan Kay implies inspreadsheet.