Part A: Subject Skills

Unit 4: Electronic Spreadsheet

Notes

Spreadsheet is a long sheet of rows and columns on the computer screen to do data analysis and calculation. In other
words, a spreadsheet is a grid which interactively manages and organizes data in rows and columns. It is also called as
Electronic Spreadsheet.

Spreadsheet software is used for managing financial and accounting documents, creating data reports, generating
invoices, data analysis from scientific and statistical researches, and for doing a variety of calculations on data. It can
also store, manipulate and create graphical representations of data.

A group of worksheets is called a workbook.

LibreOffice Calc is the spreadsheet component of the LibreOffice software package. It is used to perform the following
activities accurately and efficiently.

• Tabulation of data

• Simple mathematical calculations

• Complex calculations using formula and functions

• Arranging data in ascending and descending order (sorting)

• Filtering the required data

• Check the validity of data

• Protection of data using passwords

• Saving for future use

To start the LibreOffice Calc in Windows

• Double click on the icon to open LibreOffice.

• Or Click the window menu, select LibreOffice application, then click LibreOffice Calc. The Calc spreadsheet window
will open.

Parts of LibreOffice Calc:

(a) Title bar: The Title bar, located at the top, shows the name of the current spreadsheet. When the spreadsheet is
newly created, its name is Untitled X, where X is a number. The first created spreadsheet takes the name as Untitled 1,
second is Untitled 2 and so on.

(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. The various menu items are: (i) File (ii) Edit (iii) View (iv)
Insert (v) Format (vi) Styles (vii) Sheet (viii) Data (ix) Tools (x) Window (xi) Help

(c) Toolbars: The Calc opens with the Standard and Formatting toolbars at the top of the workspace by default. These
toolbars provide a wide range of common commands and functions.
(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 equal (=) sign into the selected cell and the Input line allow formula
to be entered.

• Input line: It 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. The sheet tab shows its default name as Sheet1,
Sheet2, Sheet3, ….

(e) Rows and columns: The sheet is divided into vertical columns and horizontal rows. Each sheet can have a maximum
of 1,048,576 (220) rows and 1024 (210) columns. The rows are numbered as 1,2,3,4,… and columns are numbered as A,
B, C, D, …., Z, AA, AB, AC, …., AZ, BA to BZ, CA,…., AMJ.

(f) Cell and cell address: The intersection of a row and column is called a cell. It is the basic element of a spreadsheet.
It holds data, such as text, numbers, formulas and so on. A cell address is denoted by its column (letter) and row number.
For example, D4, E9, Z89 are the valid example of cell address.

(i) Active cell: In a spreadsheet, cell is the place where we enter the data. Before entering any data in the cell, it has to be
first selected by placing a cursor on it. 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.

(ii) Active cell in a worksheet: To enter data in the cell, it should be selected first. Active cell in a worksheet can be
moved and selected by the key or a combination of keys.

(iii) Range of cells: A block of adjacent cells in a worksheet which is highlighted or selected is called a range of cells.
Observe the worksheets below.

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 range is the number of cells spread across the row.
 Entering data

The data to be entered can be the label, values or formula.

(a) Label: Label is the any text entered by using a keyboard. It may combine with letters, numbers, and special symbols.
By default, the labels are left aligned.

(b) Values: The numerical data consisting of only numbers are called values. By default, values are right aligned.

(c) Formulae: Any expressions that begins with an equal ‘=’ is treated as formula. In the expression, the ‘=’ followed by
values, cell address and functions are called as formula.

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. 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. LibreOffice Calc uses standard operators for formulae, such as a
plus (+), minus (-), multiplication (*), a division (/) for arithmetic operation. The expressions within the brackets are
evaluated first. Formula starts with ‘=’ sign. When you enter formula with the equal sign (‘=’), a formula bar gets
activated automatically. This helps in knowing whether the entered text is a formula or not.

The steps to create and save the spreadsheet

Step 1: Open the LibreOffice Calc by using the standard process.

Step 2: Observe that the Calc has created the worksheets automatically. Give the specific name to the worksheet. To do
this

• Select the menu Sheet → Rename Sheet.

• Give appropriate name to the worksheet and click OK. Notice the change in the worksheet name.

Step 3: Enter the data in the worksheet.

Step 4: Save the worksheet with appropriate name so that the data may not be lost. To save the worksheet, click on File
→ Save. The extension of Libre office calc file is .ods.

To insert the column before any column, position the cursor on any cell of the column before which you want to insert
the column and select Sheet → Insert Columns → Columns → Columns left.

Similarly, to insert the column to the right select Sheet → Insert Columns → Columns → Columns right.
Various ways in which a function can be used:
 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.

Use of dialog boxes to format values

The format cells dialog box in the toolbar can be used to find more about formatting a cell or range of cell. You can
open this Format Cell dialog box by pressing the shortcut key Ctrl+1.
 Formatting a range of cells with decimal places

The division of different numbers may result in varied decimal places in the quotient. In such cases it is necessary to
format the number to a fixed number of decimal places. Following are the steps to format a cell to the required number
of decimal places:

• Select the range of cells.

• Open the ‘format cells dialog’ box

• Click the ‘Number’ tab

• Select the ‘Number’

• Change the decimal places as required

• Click ‘OK’

Formatting a range of cells to be labels

The steps below to format a range of cells as text:

• 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

The steps below to format a range of cells 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

the steps below to format a range of cells to display the time.

• Select the cell range • Open the ‘format cells dialog’ box

• Click the ‘Number’ tab

• Select the ‘Time’ category

• Select category Time should be displayed

Formatting alignment of a cell range

The labels and values can be aligned to the left, center or right of a cell range by using the alignment icons (Left, Right,
Center) on the standard toolbar.

• Select the range of cells

• Open the ‘format cells dialog’ box

• Click the ‘Alignment’ tab

• Select left, right or center

• Click ‘OK’

Using the fill handle

The Calc Fill Handle tool is used to fill the next cells till you drag it with the next predefined value.

(a) For number series: Type the numbers 1, 2 in two consecutive cells and select them using a mouse. Click on the right
down corner of the selected cells, hold down the first button of mouse and drag downward till you want to continue.

(b) Copying a formula: If you wish to apply the same formula to the number of cells in the rows or columns, you need
not enter the formula again and again in each cell. A formula can be copied. It saves the time and efforts in the case of
long and complex formulae and reduces chances of errors.

Use of copy and paste commands for copying formulae

Following are the steps to copy a formula using the copy-paste command

• Select the cell

• Copy the contents of the selected cell (Ctrl+C)

• Select the cell to which the formula is to be pasted

• Paste the contents in the selected cell (Ctrl+V)

(c) Fill handle of a cell: The small black square in the bottom-right corner of the selected cell or range is called a fill
handle.

(d) Uses of fill handle for copying formulae: Follow the steps below to copy formula using the fill handle.

• Select the cell which contains the formula

• Click the small black square in the bottom-right corner of the selected cell

• Drag the fill handle up to the required cell.

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.

• Relative referencing
• Mixed referencing

• Absolute referencing

(a) 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.

(b) Mixed referencing: As we have seen, when we drag the formula, row number or column name get change in
relative reference.

(c) 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.

Thumb rule for referencing

Types of Cell Reference

Creation of Charts Using Spreadsheets

It is not easy to comprehend, compare, analyses 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.

Steps to create a chart:

• Follow the steps given below to create charts.

• Select the range of data (A1:F7)

• Insert → Chart

• Select the type of chart

• Select the chart and Click finish.