Unit – 4 Electronic Spreadsheet

Class 9 Notes
• Define Spreadsheet

o A spreadsheet is a grid which interactively manages and organizes

data in rows and columns. Its also called Electronic Spreadsheet. It
can also store, manipulate and create graphical representation of
data.
o It is used for managing financial and accounting documents, creating
data reports, generating invoices, and for doing a variety of
calculations on data etc.
Advantages of Spreadsheet
• A spreadsheet software can create graphical representations of data.
• It can be used to calculate and analyze the data for decision making.
• It also provides built-in formulae and functions for common mathematical,
financial, statistical operations.
• It is widely used for data analysis and accounting applications.
Spreadsheet or LibreOffice Calc is used to perform the following activities
accurately and efficiently.
o Tabulation of data
o Simple mathematical calculations
o Complex calculations using formula and functions
o Arranging data in ascending and descending order (sorting)
o Filtering the required data
o Check the validity of data
o Protection of data using passwords
o Saving for future use
Parts of LibreOffice or OpenOffice Calc Window.

(a) Title bar: The Title bar, located at the top, shows the name of the current
spreadsheet.
(b) Menu bar: Menu bar is located just below the Title bar. It contains the
menus with commands for various tasks. Each menu item has a submenu
called pull-down menu.
a. File: Contains commands applied to entire document — Open,
Save, Wizards, Export as PDF, Print, Digital Signatures and so on.
b. Edit: Contains editing commands — Undo, Cut, Copy, Paste,
Select, Find & Replace and so on.
c. View: Contains commands for modifying the user interface —
Toolbars, Column & Row Headers, Full Screen, Zoom and so on.
 d. Insert: Contains commands for inserting elements into a
spreadsheet — Image, Media, Chart, Object, Shapes, Date, Time,
Headers and Footers.

e. Format: Contains commands for modifying the layout of a

spreadsheet — Cells, Rows, Columns, Page, Styles and Formatting,
Alignment and so on.

f. Styles: for managing styles.

g. Sheet: Contains commands to insert and delete cell, rows and

columns, insert sheet, rename sheet, fill cell, etc.

h. Data: Contains commands for manipulating data — Define range,

sort, and so on.

i. Tools: Contains various functions to check and customize

spreadsheet—Spelling, Language, Gallery, Macros and so on.

j. Window: Contains commands to display window — New Window,

Split and so on.

k. Help: Contains links to the help system included in the software and
other miscellaneous functions — Help, License Information, Check
for Updates and so on.

(c) Menu bar: The Calc opens with the Standard and Formatting toolbars at
the top of the workspace by default. These toolbar provide a wide range of
common commands and functions. It gives a brief explanation of the icon
function.
I. Standard toolbar: The standard tool bar shows the icons for most
common operations, such as editing, arranging, filtering, etc., used
while working on the spreadsheet.

II. Formatting toolbar: Formatting toolbar has the most common

operation related to formatting datasheet. It includes buttons for font
selection, size of text, alignment, cell value formatting and
indentation, etc.
 III. Formula toolbar: It allows entering and editing the formula in the
cell. Formula bar consists of the following:
• Name box: shows the cell reference, for example A1.
• Functions wizard: search the function from the list of available
functions.
• Sum: used to total the numbers in the cells above the selected
cell. The sum is placed in the selected cell.
• Function: clicking on the Function icon inserts an equals (=) sign
into the selected cell and the Input line allow formula to be
entered.
• Input line: displays the contents of the selected cell (data,
formula, or function) and allows editing the cell contents. To edit
inside the Input line area, click in the area, then type the changes.
To edit within the current cell, just double-click in the cell.

(d) Worksheet: The worksheet in Calc is also referred to as spreadsheet. The

spreadsheet can have many sheets. Each sheet can have many individual
cells arranged in rows and columns.

(e) Rows and Columns: The sheet is divided into vertical columns and
horizontal rows.

(f) Cell and Cell address: The intersection of a row and column is called a
cell. It is the basic element of a spreadsheet. A cell address is denoted by
its column (letter) and row number. For example, D4, E9 A cell address is
denoted by its column (letter) and row number. For example, D4, E9 are
the valid example of cell address.

(g) Active Cell: When we position the mouse cursor on a cell, it gets selected,
and is ready to take data from the user. This selected or activated cell is
called as active cell. It is always highlighted, with a thick border. The
address of the active cell is displayed in the name box.

(h) Range of cells: A block of adjacent cells in a worksheet which is

highlighted or selected is called a range of cells. The column range is the
number of cells spread across the column. The row range is the number of
cells spread across the row. The row and column range is the number of
cells spread across the row and columns. The range is a matrix with
number of rows and number of columns.
Entering Data: - The data to be entered can be the label, values or formula.
• Label: Label is the any text entered by using a keyboard. It may combine
with letters, numbers, and special symbols.

• Values: The numerical data consisting of only numbers are called values.
By default values are right aligned. There are various forms of values, such
as integer, decimal etc.

• Formulae: Any expressions that begins with an equals ‘=’ is treated as

formula. In the expression, the ‘=’ followed by values, cell address and
functions are called as formula. When a formula is entered in a cell in a
worksheet the value of the equation is displayed in the cell and the formula
is shown in the formula bar.
Mathematical operators used in formulae: - Spreadsheet Software has the
most powerful features to calculate numerical data using formulae. As we use a
calculator for calculation, Calc can add, subtract, divide, multiply and much more.
If you forgot to put “=” sign before formula it will treated as label.

Formulae with cell addresses and operators: - The main advantage of

entering formula with cell addresses and operators, works just like a variable.
When the values of the cells concerned change, the results obtained by the
formula also get updated accordingly.
Formatting the worksheet: - The cell holds any type of data in the spreadsheet.
The cell data can be formatted using formatting toolbar or cell formatting window.
It is also possible to format the cell using Format cells dialog box. The Format
cells dialog box can be opened using Format → cells using the Format menu, or
from context menu opened through right clicking the cell.
Formatting a range of cells to be seen as labels
• Select the range of cells
• Open the ‘format cells dialog’ box
• Click the Number tab
• Select Text
• Click ‘OK’
• Enter numbers
Formatting of a cell range as scientific
• Select the range of cells.
• Open the ‘Format cells dialog’ box
• Click the ‘Number’ tab
• Select the ‘Date’ category
• Select the date format
• Click ‘OK’
Formatting a range of cells to display times
• Select the cell range
• Open the ‘format cells dialog’ box
• Click the ‘Number’ tab
• Select the ‘Time’ category
• Select category Time should be displayed
• Click ‘Ok’
Formatting alignment of a cell range
• Select the range of cells
• Open the ‘format cells dialog’ box
• Click the ‘Alignment’ tab
• Select left, right or center
• Click ‘OK’
Fill handle of a cell:- The small black square in the bottom-right corner of the
cell or range is called fill handle. The fill Handle tool is used to fill the next cells till
you drag it with the next predefined value.
Referencing: - Referencing is the way to refer the formula or function from one
cell to the next cell along the row or column. There are three types of referencing.
o Relative referencing
o Mixed referencing
o Absolute referencing
➢ Relative Referencing: - When you drag any formula in any row or column
in any direction, the formula gets copied in the new cell with the relative
reference. Almost all spreadsheet applications use relative referencing by
default.

➢ Absolute Referencing:- In Absolute referencing, a $ symbol is used

before the column name as well as row number to make it constant in any
formula. For example, $C$12, $D$5, etc. In this case, even if you drag
your formula in any direction, the cell name remains constant.

➢ Mixed referencing: - In Mixed Referencing, the $ sign is used before row

number or column name to make it constant. In mixed referencing in which
one cell address name is variable and one cell address is constant.
Creation of Charts Using Spreadsheets:- It is not easy to comprehend,
compare, analyse or present data when they are represented as numbers. But
when data are presented in the form of charts, they become an effective tool to
communicate. Charts is a pictorial representation of a data given in your
spreadsheet.
Let us learn how spreadsheet applications are used for this purpose. The various
types of charts are given below.
Follow the steps given below to create charts.
➢ Select the range of data
➢ Insert → Chart
➢ Select the type of chart
➢ Select the chart
➢ Click finish