ODISHAADARSHAVIDYALAYA,CHAMAKHANDI

ODISHAADARSHAVIDYALAYA,CHAMAKHANDI
ElectronicSpreadsheetClass9Notes
SPREADSHEET:
 Aspreadsheetisagridwhichinteractivelymanagesandorganizesdat
ain rows and columns.
 It
[Link],manipulatea
nd create graphical representations of data.
 It is used for managing financial and accounting documents,
creating data
reports,generatinginvoices,andfordoingavarietyofcalculationsond
ata etc.
Advantagesof Spreadsheet:
 Therearevariousadvantagesof spreadsheet software.
1. Aspreadsheetsoftwarecancreategraphicalrepresentationsofdat
a.
2. It canbe usedtocalculateandanalyzethedatafordecisionmaking.
3. Italsoprovidebuilt-
informulaeandfunctionsforcommon mathematical,
financial, statistical operations.
4. Itiswidelyusedfordataanalysisandaccountingapplications.

ExamplesofSpreadsheetsoftware:
 Examples are
1. Microsoft Excel
2. LibreOffice Calc
3. OpenOfficeCalc
4. AppleInc. Numbers
 LibreOfficeCalcisusedtoperformthefollowingactivitiesaccuratel
yand efficiently.
1. Tabulationof data.
2. Simplemathematicalcalculations.
3. Complexcalculationsusingformulaandfunctions.
4. Arrangingdatainascendinganddescendingorder.
5. Filteringtherequireddata.
6. Checkthevalidity ofdata.
7. Protectionofdatausingpasswords.
8. Savingforfutureuse.

StartingLibreOfficeCalc:
 LibreOfficeCalcisthespreadsheetapplicationofLibreOfficesuite.
 InLinux(Ubuntu)operatingsystem,theLibreOfficegetsinstalledbydefau
lt.
 InWindows,youneedtodownloadLibreOfficefromitsofficialwebsitea
nd install it on your computer.
StepstoopenLibreOfficeCalcin Window:
 InWindows,findtheshortcutofLibreOfficeonStart
menuoronthe desktop.
 [Link]
u, select LibreOffice application, then click LibreOffice Calc.
StepstoopenLibreOfficeCalcin Linux:
 InUbuntuLinux,findtheCalcicononapplicationlauncherorsearchitb
y clicking on “Show Applications”.

PartsofLibreOfficeCalc:
 Followingfigureshowstheparts
[Link] about the parts is given
below.

 Titlebar:TheTitlebar,locatedatthetop,showsthenameofthecurre
nt spreadsheet. The first created spreadsheet takes the name as
Untitled 1, second is Untitled 2 and so on.
 Menubar:[Link]
e menus with commands for various tasks.
 Toolbars:TheCalcopenswiththeStandardandFormattingtoolbars
atthe top of the workspace by default. These toolbar provide a
wide range of commoncommands [Link]
cursor over any icon displays a small box called a tooltip.
 Worksheet:TheworksheetinCalcisalsoreferredtoasspreadsheet.
The spreadsheet can have many sheets. Each sheet can have
many individual
 cells
[Link]
Sheet1, Sheet2, Sheet3, ….
 Rowsandcolumns:Thesheetisdividedintoverticalcolumns
and horizontal rows. Each sheet can have
amaximumof
1,048,576(220)rowsand1024(210)[Link] numbered
as 1,2,3,4,… and columns are numbered as A, B, C, D, …., Z, AA,
AB, AC, …., AZ …
 Cellandcelladdress:The intersectionofa row and columnis called
a cell. It
[Link],suchastext,numbers
, formulasand so on. A cell address isdenoted by its column(letter)
and row number. For example, D4, E9, Z89 are the valid example
of cell address.
 Active Cell :When we click on a cell it gets selected, and is ready
to take
[Link]
is always highlighted, with a thick border.
Key or Key
ResultofKeyor Combination
Combination
Arrowkeys
Moveasinglecellinarrowdirection
(←↑→↓)

Movesthecelltotheendofthedatarangeinaparticular direction
Ctrl+ArrowKeys

Home MovestocolumnAalongtherowwheretheactivecellis

Ctrl+ Home MovesthecelltoA1position

Ctrl+End Movestobottomrightcellofthedatarange

PageUp Movestheworksheetonescreenup

PageDown Movestheworksheetonescreendown

 Rangeofcells:Ablockofadjacentcellsinaworksheetwhichishighlight
ed or selected is called a range of cells.
 The column range is the number of cells spread across the
column. The cell addressis
representedbysinglecolumnletterandmultiplerownumberina
sequence. for example C1 : C6, A9 : A18 etc
 [Link]
ss isrepresentedby singlerow numberwithdifferent [Link]
exampleC5
: H5,A2:J2etc.
 Therow andcolumnrange isthenumberof
cellsspreadacrosstherowand columns. This range is a matrix with
number of rows and number of columns. for example A3 : G5,
D2 : H4 etc.
 Entering data:
 Thedatatobeenteredinaworksheetcanbethelabel,valuesor formula.
1. Label:[Link] may
combine with letters, numbers, and special symbols. By default
the labels are left aligned.
2. Values:Thenumericaldataconsistingofonlynumbersarecalledval
ues. By default values are right aligned.
3. Formula:Anyexpressionsthatbeginswithanequals‘=’istreate
das formula.
Mathematicaloperatorsusedinformula:
 SpreadsheetSoftwarehasthemostpowerfulfeaturestocalculatenum
erical data using formulae.
 LibreOfficeCalcusesstandardoperators
forformulae,suchasaplus(+), minus(-), multiplication (*), a
division (/) for arithmetic operation.

MathematicalOperators Operator precedence

Subtraction(-) Second ^

Multiplication(*) Third/,*

Division(/) Third/,*

Exponentiation(^)

1. Note:Formulastartswith‘=’signandnothingshouldbewrittenonth
e left side of the equal sign (‘=’). If you forgot to put the ‘=’
before the formula, it will be treated as a label.

Working on worksheet
Steps to rename sheet:
2. Torenamethesheet
1. SelectthemenuSheet→Rename Sheet.
2. GiveappropriatenameandclickOK
StepstoInsert sheet:
3. ToInsertasheet
1. Rightclickonselectedsheet,menucommand appear.
2. ClickonInsertSheet
3. Sheet willbe Inserted.
 Stepsto Delete sheet:
 Todeletethe sheet
1. Rightclickonselectedsheet,menucommand appear.
2. ClickonDelete Sheet
3. Sheet willbe Delete.
Stepstosavetheworksheet:
 Tosavethe worksheet
1. clickon File→ Save.
2. ASavedialogboxwillappear.
3. Selectthelocationwhereyouwanttosavethefile.
4. Enterthenameofthefilesay,‘Bill’
5. ClickontheSavebutton.
Stepsto insertthecolumnbeforeanycolumn:
 Position the cursor on any cell of the column before which you
want
toinsertthecolumnandselectSheet→InsertColumns→Columns→Colu
mns left
Stepstoinsertthecolumnafter(rightside)anycolumn:
 Position the cursor on any cell of the column after which you want
to insert
thecolumnandselectSheet→InsertColumns→Columns→Columnsrigh
t.

Function:
 FunctionsarepredefinedformulainCalcwhichisusedfortaskslikefindi
ng the sum, count, average, maximum value, and minimum
values for a range of cells.
CommonlyusedbasicfunctionsinCalc
Function Syntax Use
Addsthevaluescontainedina range
SUM =SUM(Number1,Number2,…..)
of cells.

Returntheaverageofthevalues
AVERAGE =AVERAGE(Number1,Number2,….)
contained in a range of cell

Returnthelargest/Maximumvalue
MAX =MAX(Number1,Number2,……)
contained in a range of cells

ReturntheSmallest/Minimumvalue
MIN =MIN(Number1,Number2,……)
contained in a range of cells

ReturntheCountsofthenumberof cells
COUNT =COUNT(Number1,Number2,…..) within a range
of cells.
Formattingtheworksheet:
 ThecelldatacanbeformattedusingFormat cellsdialogbox.
 The Format cells dialog box can be opened using Format→cells
using the
Formatmenu,orfromcontextmenuopenedthroughrightclickingthec
ell.
 Shortcuttoopenthis FormatCelldialogboxisCtrl+1.
 Variousoptionsof Formatcelldialogboxareshownbelow.
a) Formattingarangeofcellswithdecimalplaces:Followingar
ethe steps to format a cell to the required number of
decimal places:
1. Select therangeof cells.
2. Openthe‘formatcellsdialog’box
3. Clickthe‘Number’tab
4. Selectthe ‘Number’
5. Changethedecimalplacesasrequired
6. Click ‘OK’
b) Formattingarangeofcellstobeseenaslabels:
Followthesteps below in order to format a range of cells as
text.
1. Selecttherangeof cells
2. Openthe‘formatcellsdialog’box
3. ClicktheNumbertab
4. SelectText
5. Click ‘OK’
6. Enternumbers
c) Formattingofacellrangeasdifferentdateformat:Inaspreads
heet application, the user can change Date in many different
formats. To do these follow the below steps.
1. Select therangeof cells.
2. Openthe‘Formatcellsdialog’box
3. Clickthe‘Number’tab
4. Selectthe‘Date’ category
5. Select thedateformat
6. Click ‘OK’
d) Formattingarangeofcellstodisplaytimes:Followthestepsbelo
wto format a range of cells to display the time.
1. Selectthecellrange
2. Openthe‘formatcellsdialog’box
3. Clickthe‘Number’tab
4. Select the‘Time’category
5. SelectcategoryTimeshouldbe displayed
6. Click ‘Ok’
e) Formattingalignmentofacellrange:Followthestepsbelow
toformat the alignment of range of cells.
1. Selecttherangeof cells
 2. Openthe‘formatcellsdialog’box
3. Clickthe‘Alignment’tab
4. Select left,rightor center
5. Click ‘OK’
Fillhandleofacell:Thesmallblacksquare inthe bottom-rightcornerof
the selected cell or range is called a fill handle.

Referencing:
 Referencingisthewaytorefertheformulaorfunctionfromonecelltoth
e next cell along the row or column. There are three types of
referencing.
1. Relativereferencing:Whenyoudraganyformulainanyroworcolu
mn inany direction,the formula gets copied inthe new cellwith
the relative reference. for example A1, H15 etc.
2. Mixedreferencing:InMixedReferencing,the$signisusedbefore
row number or column name to make it constant. for example
A$5, $C14.
3. Absolutereferencing:InAbsolutereferencing,a$symbolisusedb
efore the column name as well as row number to make it
constant in any formula. For example, $C$12, $D$5, etc.
Identifythecellreferencinginthefollowingtable:
Example TypeofRefencing
A1 Relative

F45 Relative

D$4 Mixed

$A23 Mixed

$A$2 Absolute

CreationofChartsUsing Spreadsheets:
 It isnoteasytocomprehend,compare,analyzeorpresent data
whenthey are represented as numbers.
 But
whendataarepresentedintheformofchartstheybecomeaneffective
tool to communicate.
 Thevarioustypesofchartsaregivenbelow.
1. Column Chart
2. BarChart
3. Line Chart
4. Pie Chart
5. XYScatterChart
Stepstocreateanytypeofchart:
1. SelecttherangeofdatasayA1:F7
2. Clickon Insert→Chart
3. Select thetypeof chart
4. ClickFinish

--::==========::--